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