How to tell a subform what data to display...

[bladernr]

OK, so now I made the application I'm working on a little more complex.
My client changed the spec on me a bit and it seems that the only easy
way to do what they want is to use some sub forms.

What I am working on is a database app in Access that will store
faculty information like ID, Name, credentials, classes taught, grad
hours taken, and so forth. The biggest problem I had was that the form
that they were using historically was a paper form that was not easily
transcribed to electronic means without becomming unweildy. I fixed
that problem using a tab structure.

Basically, for each faculty member, that person could be teaching in
several different disciplines such as: University Transfer,
Non-Transfer, Phys Ed, Basic Skills, Community Outreach (Con Ed),
Developmental (ESL, Reading for illeterates, basic Math skills, GED,
etc). For each of thsoe disciplines, they could be teaching several
different classes. (more like only one, but thats how it works).

So, the teacher could have one class in the University Transfer
discipline, One class under Non-Transfer, and 2 classes under Basic
Skills.

So, I have a tab for each discipline, and the administration only fills
out the appropriate tabs. NOW. For courses assigned, I am using a
seperate table and a sub form in each tab. The layout for each tab
looks something like this:

|University Transfer|
----------------------------------------------------------------------------------------------------------------------------------------------
Highest applicable credential: [DROP DOWN BOX OF DEGREES (MA, BS, AS,
DIP, LICENSE)]
Highest credential applied: [TEXT for their actual credentail (M.A. -
Eng from UNC Chapel Hill)]
----------------------------------------------------------- 18
Graduate Hours: Additional Qualifications:
SUB FORM | [TEXT BOX TO LIST]
[TEXT BOX TO LIST ANY ]
ENG 111 Intro English T (transfer) | [GRAD COURSES ]
[ADDITIONAL QUALS. ]
ENG 114 American Lit T (transfer) | [TAKEN ]
[ ]
-----------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------

That is a VERY poor diagram of the basic layout. What I am trying to
figure out, however, is how to do the sub form for each tab. What I
want is for the sub form in the Univ. Tranfer tab to only display
assigned courses that are for that discipline, and the sub form in the
Phys Ed discipline to only display assigned courses that are phys ed
and so on.

Currently, I have a secondary table that holds courses assigned by
Faculty ID number. The basic lay out is like this:
ID (Id number), Discipline (ENG, PHY, MAT, etc), CourseNo (111, 112,
114), CourseTitle (30 char text), NDT (N for nontransfer, T for
transfer, D for developmental).

I thought the best way to do this would be to add an additional field
to this table to differentiate the type of course.

So the real question is, how do I tell the sub form to only display the
correct courses? Right now its just using the table for data as a
proof of concept. Eventually, I need to base that subform on queries.
But how to I query that table and pass the tab that owns the sub form
as a parameter?? VBA? or is there some magic that will allow that to
work.

Did that even make sense? Anyone wiht some wisdom to help a guy out?

Cheers
Jeff

 

[strive4peace]

Hi Jeff,

Instead of making a tab for each discipline, why not have
ONE subform for the Discipline information? Make
DisciplineID an unbound combobox or listbox on the main form
that stores the ID and shows the text. Use DisciplineID in
the LinkMasterFields property of the subform. You would
then have DisciplineID on the subform as a hidden control
and specify it in the LinkChildFields property of the subform.

In the Disciplines table, DisciplineID will be an autonumber
field. In related tables, DisciplineID will be defined as a
long integer.

Do not use seperate tables for each discipline! Combine all
like data into one place and distinguish the records with
DisciplineID.

DisciplineID will be an autonumber field in the Disciplines
table and will be its primary key.

DisciplineID (long integer) will be stored as a foreign key
in your Courses table with CourseID (autonumber) as the
primary key.

You will have an Instructors table with InstructorID
(autonumber) as the primary key.

You will then have a table, Classes with InstructorID (long
integer), CourseID (long integer), and ClassID (autonumber)
as the primary key. Because each Course is linked to a
Discipline, you can link Instructors to Disciplines
depending on the Classes they teach.

Once you have your data restructured, it will be a LOT
easier to work with the forms and subforms.

Set up a table for each "noun" you will track ... course,
instructor, discipline, class, etc. Do not have two tables
with the same set of fields.

The ID fields will be used behind the scenes to link all the
information.

When you lay out your relationship diagram, stretch out your
fieldlists so you can see everything; put the "1" side of
the relationship on the left and the "many" side on the
right so that you data FLOWS from left to right. Put all
your tables on the diagram whether they have any
relationships or not.

I like to press PrintScreen (which copies screen to the
clipboard), paste into Paint, crop, save as a JPG, then
Insert, Picture to a PowerPoint slide (or Word doc) with the
diagram. I then add additional notes where necessary.

Print the relationship diagram and keep it in front of you
while you are working on your database. Finding the best
structure takes time and, in the beginning, evolves daily.
Your data structure is the foundation of what you will
build; spend a lot of thought on this very important first
step before you build any forms or reports.


Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

OK, so now I made the application I'm working on a little more complex.
My client changed the spec on me a bit and it seems that the only easy
way to do what they want is to use some sub forms.

What I am working on is a database app in Access that will store
faculty information like ID, Name, credentials, classes taught, grad
hours taken, and so forth. The biggest problem I had was that the form
that they were using historically was a paper form that was not easily
transcribed to electronic means without becomming unweildy. I fixed
that problem using a tab structure.

Basically, for each faculty member, that person could be teaching in
several different disciplines such as: University Transfer,
Non-Transfer, Phys Ed, Basic Skills, Community Outreach (Con Ed),
Developmental (ESL, Reading for illeterates, basic Math skills, GED,
etc). For each of thsoe disciplines, they could be teaching several
different classes. (more like only one, but thats how it works).

So, the teacher could have one class in the University Transfer
discipline, One class under Non-Transfer, and 2 classes under Basic
Skills.

So, I have a tab for each discipline, and the administration only fills
out the appropriate tabs. NOW. For courses assigned, I am using a
seperate table and a sub form in each tab. The layout for each tab
looks something like this:

|University Transfer|
----------------------------------------------------------------------------------------------------------------------------------------------
Highest applicable credential: [DROP DOWN BOX OF DEGREES (MA, BS, AS,
DIP, LICENSE)]
Highest credential applied: [TEXT for their actual credentail (M.A. -
Eng from UNC Chapel Hill)]
----------------------------------------------------------- 18
Graduate Hours: Additional Qualifications:
SUB FORM | [TEXT BOX TO LIST]
[TEXT BOX TO LIST ANY ]
ENG 111 Intro English T (transfer) | [GRAD COURSES ]
[ADDITIONAL QUALS. ]
ENG 114 American Lit T (transfer) | [TAKEN ]
[ ]
-----------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------

That is a VERY poor diagram of the basic layout. What I am trying to
figure out, however, is how to do the sub form for each tab. What I
want is for the sub form in the Univ. Tranfer tab to only display
assigned courses that are for that discipline, and the sub form in the
Phys Ed discipline to only display assigned courses that are phys ed
and so on.

Currently, I have a secondary table that holds courses assigned by
Faculty ID number. The basic lay out is like this:
ID (Id number), Discipline (ENG, PHY, MAT, etc), CourseNo (111, 112,
114), CourseTitle (30 char text), NDT (N for nontransfer, T for
transfer, D for developmental).

I thought the best way to do this would be to add an additional field
to this table to differentiate the type of course.

So the real question is, how do I tell the sub form to only display the
correct courses? Right now its just using the table for data as a
proof of concept. Eventually, I need to base that subform on queries.
But how to I query that table and pass the tab that owns the sub form
as a parameter?? VBA? or is there some magic that will allow that to
work.

Did that even make sense? Anyone wiht some wisdom to help a guy out?

Cheers
Jeff

 

[Ron2005]

If you still want the tab approach. The query for each of the tabs will
have to be a different query or the tab control will have to apply a
filter to the common query to apply a criteria that matches the tab.

Have fun after you decide which way you want to go.

That is the "Benefit"? with Access: "There are so many ways to skin the
cat."

(Sorry, cat lovers. Nothing personal.)

 

[strive4peace]

With all due respect, you do not need to put criteria into a
query; you can use the SAME subform for each tab. If, for
instance the filter is on DisciplineID

Make calculated controls on your main form

Name --> DisciplineID1
ControlSource --> = 1
Visible --> False

Name --> DisciplineID2
ControlSource --> = 2
Visible --> False

Name --> DisciplineID3
ControlSource --> = 3
Visible --> False

for ControlSource, these would be your actual values, I just
used 1, 2, and 3 for examples.

subform1:
LinkMasterFields --> DisciplineID1
LinkChildFields --> DisciplineID

subform2:
LinkMasterFields --> DisciplineID2
LinkChildFields --> DisciplineID

subform3:
LinkMasterFields --> DisciplineID3
LinkChildFields --> DisciplineID


but, there is really no reason to load all these subforms
.... it is much faster if you just use one...

If you want to include Course or something else in your link
fields, you can. Seperate the link fields with semicolon ;

ie:

make an unbound combobox on your main form for CourseID

LinkMasterFields --> DisciplineID1; CourseID
LinkChildFields --> DisciplineID; CourseID

any controls in LinkChildFields do not have to be visible,
but they do have to be on the subform.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day