Restrict Combo Box Content

[dee]

Hi,

I'm trying to create a form that lists:

Main Form: Family Name (from Family table)
Sub Form: Meeting information such as date, notes (linked via FamilyID PK
in Family table and FK in Meetings table)

Each family may have up to four children attending the school and 1, 2, 3,
or 4 of them may attend a family meeting. I have fields called Student1,
Student2, Student3 and Student4 in the Meetings table that I use in the sub
form. I would like to populate them with a combo box that displays the names
of the students from the Student table.

The problem is: the combo box displays the names of ALL students, not just
those of the currently displayed family. How can I "link" it to the
FamilyIDI?

Thanks!

 

[Al Camp]

Dee,
Unfortunately, you're going to have a very difficult time doing that
because your database design has problems.
The Primary or "ONE" table should contain the Family information, like
FamilyName, Address, City, etc.. That table should have a key field like
FamilyID that is a unique identifier for that family. Ex. the Bakers'
FamilyID=12223, the Jones' FamilyID = 31224
You should have a Secondary "MANY" table that holds all the information
for each child, like Name, DOB, Sex, etc... and "linked to the family table
via that same FamilyID
Family Andersons 12321 (One Family)
Child Robert 12321 (Many children)
Child Susan 12321 (Many children)
Etc...
This is a One to Many relationship.

If your database were designed this way, your combo box could use the
FamilyID as a filter, and it would only display Children from that
particular family.
Because you've created 4 different fields to hold Children, there's no
"practical" way to do what you want.

Later you can add a Meetings table that lists multiple meetings against a
Family using that same FamilyID...
Family Andersons 12321 (One family)
1/1/05 12321 (Many meetings)
2/15/05 12321 (Many meetings)
Etc...

hth
Al Camp

 

[Klatuu]

What is the control source for your combo box? If it is a query based on
your family table, then you need to use the criteria row of your query to
filter on the family ID

 

[Al Camp]

Klatuu,
Don't think that will help here...
This is not a One to Many relationship, so a combobox won't be able to
list the children as "selectable" values.
If linked via FamilyID, the combo could only display...
Child1 Child2 Child3 Child4 Smith

Al Camp

 

[dee]

Hi and thanks for your input.

I actually do have my tables set up as you mentioned:
Family table with address, etc.
Student table with student names related by FamilyID PK in Family table

I guess the design of the meeting table is not correct. I am trying to
populate the Meetings table with names of students taken from Student table
(well, not really names, but StudentIDs, which are autonumbered PKs). Maybe
I don't need to do this? Somehow just pull the data together for forms and
reports?

I'm new to this, and appreciate your assistance and patience. I have
reached a very high level of frustration!
--
Thanks!

Dee

Dee,
Unfortunately, you're going to have a very difficult time doing that
because your database design has problems.
The Primary or "ONE" table should contain the Family information, like
FamilyName, Address, City, etc.. That table should have a key field like
FamilyID that is a unique identifier for that family. Ex. the Bakers'
FamilyID=12223, the Jones' FamilyID = 31224
You should have a Secondary "MANY" table that holds all the information
for each child, like Name, DOB, Sex, etc... and "linked to the family table
via that same FamilyID
Family Andersons 12321 (One Family)
Child Robert 12321 (Many children)
Child Susan 12321 (Many children)
Etc...
This is a One to Many relationship.

If your database were designed this way, your combo box could use the
FamilyID as a filter, and it would only display Children from that
particular family.
Because you've created 4 different fields to hold Children, there's no
"practical" way to do what you want.

Later you can add a Meetings table that lists multiple meetings against a
Family using that same FamilyID...
Family Andersons 12321 (One family)
1/1/05 12321 (Many meetings)
2/15/05 12321 (Many meetings)
Etc...

hth
Al Camp

 

[Al Camp]

Dee,
Sorry... I misunderstood how your Children table was set up. You did it
right... we should be able to work it out.
If each meeting is with a particular family, I'd try using the Meetings
table as the main form, with all the meeting particulars (date, subject,
resolution, etc..) with a combo that would choose a FamilyID.
In a subform of that form would be the Children that participate, with
each child a record in a continuous subform. Any combo used to select the
children (like Kaltuu suggested) can be filtered by the Main form FamilyID.
=Forms!frmMeetings!FamilyID
How does that sound to you?
It would seem to be a simple matter to print out the meetings (and
attendees) for a date range, or all meetings for a particular FamilyID.

hth
Al Camp

Hi and thanks for your input.

I actually do have my tables set up as you mentioned:
Family table with address, etc.
Student table with student names related by FamilyID PK in Family table

I guess the design of the meeting table is not correct. I am trying to
populate the Meetings table with names of students taken from Student
table
(well, not really names, but StudentIDs, which are autonumbered PKs).
Maybe
I don't need to do this? Somehow just pull the data together for forms
and
reports?

I'm new to this, and appreciate your assistance and patience. I have
reached a very high level of frustration!

 

[Al Camp]

After receiving Dee's reply...
as Emily Latella used to say on SNL... "Never mind..."
Al Camp

 

[dee]

Hi,

Thanks so much for the help. I'm so tired now that I can't think straight,
but will try it tomorrow. It may help with other forms I am trying to
create. I have another post or two regarding a Contact form I'm trying to
create and having huge problems with. (I think it's Please help subform)

If you see that post, I would appreciate any help you can provide. Thank
you so much for your assistance and patience. It's very frustrating being a
"newbie".

 

[dee]

Hi and thanks again.

I sort of get the idea of what you're saying. In the Meetings table, I have
MeetingID, FamilyID FK, MeetingDate Parent1, Parent2, Student1, Student2,
notesetc.

I am creating the form and filling the meeting date and notes, but want to
choose the parent names and students names from individual combo boxes that
look up the parent first name and student first names from - parent info from
FamilyContacts table and student names from StudentInfo table.

So, I would populate the Meetings table fields of parent and student
attendee names with the PK number of those individuals from the Contact and
StudentInfo tables.

Does this make sense? Also, where do I put the expression you mentioned?
Do I create the combo box, select it and use the Expression Builder? I can't
see where to put it.

Thanks so much!!!