Combo box based on select Criteria 1

[traycee]

This should be simple but no matter what I try it's just not working for me.

I have a main form called Client that has two subforms. One subform is called Family Members and the other subform is Training. The main form contains a primary key. Each of the subforms has a foreign key. I am using tab controls to display the data. The main form and each subform are on a separate tab. So 3 tabs total.

I'd like to place a combo box on the Training subform that would filter by the primary key field and list only the family members in the drop down that have that primary key in common. That way the user would only be able to choose from the family members within that specific ID number to enter the training information on. If I changed to a different client, then click on the training tab, I would only see their list of family members on the combo drop down list.

Thanks for any assistance you can provide.

tc

 

[PHV]

no matter what I try it's just not working for me
What have you tried ?
What is not working ?
What is the actual RowSource property of your combo ?
Did you try to set it by code in some event procedure of the mainform ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traycee]

I've tried basing the combo box on different variations of queries as well as by event.

Example: Using the family members table I'm using the foreign key field (let's call it ID), the autonumber primary key field for Family Members (FamilyID), and then a field combining the first and last names of the family member. 3 columns in the query.

In the foregin key field across from criteria in the query I have tried [Forms]![Training]![ID] as well as [Forms]![Client]![ID]. I've also tried it 'on got focus' as well as trying to requery the form etc. etc. etc. I want to store the Family ID into the training table where I will later separate the info on reports.

The training form shows that it is receiving the correct number for the primary key field and matches the main form just fine. But the combo box isn't filtering on the primary key. Wow! Trying to explain it will let you know how confused I am.

 

[PHV]

Again, what is the RowSource property of the combo ?

 

[traycee]

SELECT [Family Members].[ID], [Family Members].[MemberID], [Family Members].[Members] FROM [Family Members] ORDER BY [Members];

 

[PHV]

And what is the mainform's primary key and how is it related to your [Family Members] table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traycee]

Thanks for the answer. I appreciate the assistance, but it doesn't filter the information by ID. It shows all the people listed not just the members of a single family unit.

 

[traycee]

The main forms primary key is ID. ID is the foreign key in the Family Members table. ID is also the foreign key in the Training table.

 

[PHV]

In the GotFocus event procedure of your combo you may try somethin like this:
Me![combo name].RowSource = "SELECT ID,MemberID,Members FROM [Family Members] WHERE ID=" & Forms!Client!ID & " ORDER BY 3"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traycee]

Thanks so much for your assistance. Maybe the problem lies with the fact that I'm using tab controls...which as a rule I don't do.

I'm going to step away from it for a day or two and take a fresh look at it.

Again, thank you.

 

[traycee]

I got it!

I clicked on the combo box and under its events 'on got focus' I created an event procedure using this line:

DoCmd.RunCommand acCmdRefreshPage

The subform refreshed the data correctly but the combo box which was based on a query did not refresh with it...now it does.