Displaying Many-to-many table results

[Joe]

Hello.

I have an app where I have a table for Agency data (pk =
AgencyID), a table for Member data (pk = MemberID) and a
table to link them in a many-to-many relationship (which
contains AgencyID and MemberID as its only fields).

I can display all of the members of that agency in a
subform in datasheet view. I use a query to get the
member data for an agencyID and I link the subform to the
query.

I also want to be able to add a member to an agency if the
person is listed in the Member table. I would like the
first field in the datasheet view to be a dropdown list
containing all of the member names from that member table
and, when the user selects a member name from the dropdown
list, I want the member's data to populate the remaining
fields in the subform.

Does anyone know how to do this?

Thanks in advance.

Joe

 

[Roger Carlson]

On my website (see sig below) is a small sample database called
"ImplementingM2MRelationship.mdb" which illustrates how to do this.