Form button to add new row to two tables (1 Viewer)

Hi all,

Question I’m hoping you can help me with.

We have two tables called “Documents” and “Documents2” that contains around 300 columns (150/table) with unique information related to a specific document number and revision level e.g. Document “Doc001”, revision “A”. The primary key for both tables is an auto number field.

We are trying to create a form button that when clicked will add a new row to both tables with the same autonumber primary key, document number and an updated revision level e.g. Doc001 Rev B.

We’re struggling however on how to achieve this. Any help would be greatly appreciated.

Thanks in advance,

Hi. Welcome to the forum! I (personal opinion only) would think trying to synchronize an autonumber field would be unsafe. Instead, I would suggest you simply add a foreign key to the second table and then store the autonumber field value from the first table into this foreign key field. You can still have an autonumber PK field in the second table, I am just saying don't use it to link the two tables to each other.

theDBguy said:

Hi. Welcome to the forum! I (personal opinion only) would think trying to synchronize an autonumber field would be unsafe. Instead, I would suggest you simply add a foreign key to the second table and then store the autonumber field value from the first table into this foreign key field. You can still have an autonumber PK field in the second table, I am just saying don't use it to link the two tables to each other.

Click to expand...

I agree with theDBguy. I would think that linking the 2 tables by using a Foreign Key (a field which holds the reference to a row in another table) in one of the tables would be safer, and easier.

Smitster said:

We have two tables called “Documents” and “Documents2” that contains around 300 columns (150/table) with unique information related to a specific document number and revision level e.g. Document “Doc001”, revision “A”. The primary key for both tables is an auto number field.

Click to expand...

This doesn't sound at all like a well structured database.