Many to Many Forms question

[BillA]

I have a database with a many-to-many relationship and would like to
construct data entry forms that would step through these M:M tables. The
tables are: tbl_Projects, lk_Join and tbl_Investigator. My theory is to
start with a form for the project, create a button that would take me to a
form for investigators. The problem I believe will be with the joining
table, which holds a critical piece of information; the investigator may be
involved with multiple projects at different capacities (principal
investigator; co-investigator, assistant etc…); the lk_join table holds the
investigator’s relationship to the project.

I have been attempting to build thes tables. I know it is possible to bring
the PK from tbl_Project and fill the ProjectID FK in lk_join table. My
question is how I fill the InvestigatorID FK in the lk_join table with a new
record number from tbl_Investigator, as I enter investigator information. On
top of this, I realize that some investigators will already be in the
database and could be selected using a simple combo box or possibly a search.
Also, there are usually multiple “investigators†with varying relationships
to the project, so I will need to maintain a connection to the project while
entering data.

I want to create the forms in this sequence to simplify data entry for the
user. I’m hoping for some advice and possible code suggestions.

Thank you in advance.
Bill

 

[Douglas J. Steele]

The normal approach is to use a form/subform combination.

In your case, I'd suggest that the parent form be bound to a query based on
tbl_Projects, and that the form being used as a subform be bound to a query
based on lk_Join. On that form, rather than displaying the InvestigatorID in
a text box, have a combo box that uses a query based on tbl_Investigator as
its RowSource, binding the InvestiagorID column of that combo box to the
field in the form's recordset.