Many to Many Forms question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Back
Top