Create a data entry form using multiple tables with many-to-many relationships in Access 2007

Question

I am trying to create a data entry form using multiple tables with many-to-many relationships. I have 3 tables -- Table1, Table2 and Table3. Table! contains 2 fields - Table1_ID and Table1Data. Table2 contains 3 fields -- Table2_ID, Table3_ID, and Table2Data. Table3 contains 2 flelds -- Table3_ID and Table3Data. Each record in Table1 is corrresponds to multiple records in Table2 and each record in Table2 corresponds to multiple records in Table3. I need a data entry form that allows me to enter a record in Table1 and its corresponding records in Table2, and add records in Table3 that correspond to the records in Talbe2. I was successful at creating a form that allows me to enter records into Table1 and Table2, and another form that allows me to enter records into Table2 and Table3, but I have had no luck linking all 3 tables.

Answer 1

You are probably going to need to use a form with a subform.  The problem is going to be that the primary form needs to be a single form (not continuous).  

The subform can be a continuous form.

You might be able to use a single form for Table1.

A subform (continuous) for Table2

A subform (continuous) for Table 3 that is coordinated with the current record on on the table2 subform.  Coordinated sub-forms usually involve some VBA code to control the linkage.

One way to handle the linking properties is to add a control on the table1 form that gets its value from the current record in table2 subform.  Then you can link table3 subform to the control value of that control on the table1 form.

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County