Populating Cross-Ref Table by Form (2003)

Topic

New Reply

Newbie problem I hope someone can help me with:

I have 2 lookup tables:

tblCollaborantLkUp
Collaborant_ID_#
Collaborant_Name

tblInstitutionLkUp
Institution_ID_#
Institution_Name

and a cross-reference table that holds the matches between Collaborants and Institutions.

tblCollaborantInstitutionCrossRef
Collaborant_ID_#
Institution_ID_#

tblCollaborantLkUp and tblInstitutionLkUp are populated. tblCollaborantLkUp is related one-to-many via Collaborant_ID_# to tblCollaborantInstitutionCrossRef . Similarly tblInstitutionLkUp to tblCollaborantInstitutionCrossRef.

I need to design a form that allows users to make associations between between Collaborants and Institutions in tblCollaborantInstitutionCrossRef.

I thought this would be easy, using subforms and combo boxes, but I can’t get it to work. Does anyone have any thoughts as to a better approach?

Thanks!

Reply | Quote

Replies

See the sample database attached to post 364203. It demonstrates how to implement a many-to many relationship in a form and subform.

Reply | Quote

Base the form on the join (cross-ref) table. Use an unbound list or combobox for tblCollaborantLkUp and another for tblInstitutionLkUp. After the user has made a selection from each list or combobox, let them click a button to create the join record. Then you can either run a query that references the form to append a new record to the join table or you can do it in code.

Reply | Quote

Thanks, both of you. I will take a look at these solutions and get back to you with my results. And I thought it would be straightforward!

Reply | Quote

Yup. Worked. Thanks very much!

Reply | Quote