Expert: Scottgem - 7/3/2009

Question
Dear  scott
I'm designing a form using access 2007, in this form I have a page which has  two drop down lists the first one is  groups names, and the second is the members of the groups  the question is how to make  the members of each group in the second  appear according to the group name in the first? where I have a table which its fields are  group 1 names,  group 2 names …..etc  each field contains the specific group members.
I did sth. Like that, query on the table has the statements criteria for each field but it returns no thing in the form  
"field return:(iif([forms]![apform]![mebers field]=[group 1 names])"
"field return:(iif([forms]![apform]![mebers field]=[group 2 names])"
.
.
             Sorry if It's not clear or  long question
Thanks in advance


Answer
This is actually a standard technique called cascading or synchronized comboboxes. This article:
http://support.microsoft.com/kb/289670
explains how to do them.

Two points. You really should have three tables here:

tblGroup
GroupID (Primary Key Autonumber)
GroupName

tblMember
MemberID (PK autonumber)
FirstName
Lastname

tblGroupMember
GroupID (Foreign Key)
MemberID (FK)

The third table may not be necessary if a member can only belong to ONE group. If that's the case, then GroupID becomes a FK in tblMember.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA