Creating a form in access that adds fields to more

Question

So I have 3 tables, and I'd like to be able to create form that will add a new row to table 1, and then use that data to add rows on table 3 with values from table 1 and 2. I've included a brief overview of the table structure. Please let me know if I can clarify anything.

Table 1 - Things

Table 2 - TypesofThings

Table 3 - ThingType

Example of records in the tables:

Table1: 
ID - 1 Name: "This"

ID - 2 Name: "That"

Table2: (This table is predefined types of things capped at 15)

`ID - 1 TypeName: Yellow`

`ID - 2 TypeName: Red`

`ID - 3 TypeName: Green`

Table3: This is a "transaction" table that matches the field from table 1 to table 2

`ID: 1 Table1_ID:1 Table2_ID:1`

`ID: 2 Table1_ID:1 Table2_ID:2`

`ID: 3 Table1_ID:1 Table2_ID:3`

`ID: 4 Table1_ID:2 Table2_ID:2`

Example Form of what I'd like to be able to do:

Thing Name: "whatever"
[x] yellow
[] red
[x] green

This submission would add a row to table 1 with the name whatever, and then using the values from table 2 and 2 rows to table 3 like:

id:1 table1_id:1 table2_id:1
id:2 table1_id:1 table2_id:3

I have created the form that will add new rows, but I can't figure out how to do the rest. If you guys could point me in the right direction it would be greatly appreciated!

Answer 1

Looks like many-to-many relationship. Conventional data entry structure:

1. single form bound to table 3 with comboboxes to select items from tables 1 and 2

2. main form bound to table 2 and subform bound to table 3 with combobox to select items from table 1

3. main form bound to table 1 and subform bound to table 3 with combobox to select items from table 2

If you want to be able to add items to table that is source for combobox 'on-the-fly' during data entry, look into the combobox NotInList event. MS NotInList event