combo box on subform has too many choices

  • Thread starter Thread starter Theresa via AccessMonster.com
  • Start date Start date
T

Theresa via AccessMonster.com

I have an Access 2000 database whose tables were created in Epi Info. I
have all of the forms created, but I'm having trouble displaying the
correct data in a combo box on a subform. I have a table called [Group],
whose primary key is the foreign key in tables of [GroupParticipants] and
[Meeting] (both are 1 to many). I have another table of [MeetingAttendees],
the foreign key of which is the primary key from the [Meeting] table
(again, 1 to many). I have a query which displays all of the group
participants and their group number:

SELECT DISTINCT tblGroupParticipants.P_FullName, tblGroupParticipants.FKEY
FROM tblGroupParticipants
ORDER BY tblGroupParticipants.P_FullName;

The query gives me all of the records in the table, which is fine.

I have a subform of meeting attendees on the Meeting form. I have a combo
box on the subform of the full names from the above query. I'm trying to
limit the list of names seen to only those participants from the current
group. The following in the row source for the combo box gives me ALL of
the names from the above query:

SELECT DISTINCT qryGroupParticipants.P_FullName
FROM tblMeeting INNER JOIN qryGroupParticipants ON tblMeeting.FKEY =
qryGroupParticipants.FKEY
WHERE (((tblMeeting.FKEY)=[qryGroupParticipants].[FKEY]))
ORDER BY qryGroupParticipants.P_FullName;

What am I doing wrong?

Thanks in advance,
Theresa
 
Have your query filter using data from the main or parent form.

Try something like this:
SELECT DISTINCT tblMeeting.P_FullName, tblMeeting.FKEY,
FROM tblMeeting
WHERE (((tblMeeting.FKEY)=[Forms]![MainFormNameHere]![FKEY]))
ORDER BY tblMeeting.P_FullName;

Hope this helps.
Good Luck!
 
Thanks! That helped. I didn't realize you could reference form fields
in a query.

Theresa
 
Back
Top