Creating Master->Details record using Code (Autonumber)

[pkpanda]

I have similar but bit different issue that is

I have a table INVOICE when user enter data in invoice I have to insert record into Transactions (Master ) and Transaction_det ails (detail) both the tables have ID type autonumber i have a code that insert into record into Transaction table then when I try to insert record into transaction_det ails record I don't have the trasnsation ID which links these 2 tables. I am using MSaccess 2003. can any one help me in this.

Thanks ,
PP

[Stewart Ross]

Hi pkpanda, and Welcome to Bytes.

Autonumber fields can often be used as primary keys - but the ease with which Access offers them can lead to incorrect choices where such use is not appropriate at all. This is the case here in trying to use a separate autonumber field for your transaction_det ails table and applying that as if it has some relationship to your Transaction table - it doesn't. It can be used to uniquely identify the particular detail record involved, but not the one it is linked from. For that you need to understand that the key from the Transaction table has to be carried forward.

If a table is related one-many to another, the primary key of the one-side table is carried forward as a foreign key in the second table. Your transaction_det ails table should have not an autonumber for the foreign or secondary key from your Transactions table, but a numeric (long integer) field which will store the value of the autonumber field carried forward from the Transactions table.

If you change the data type of the transaction_det ails foreign key field to Numeric you can make use of a form-subform structure to set the parent-child links between the records shown in the forms. Using this approach Access will itself take care of carrying forward the autonumber value from your Transaction table to the corresponding Transaction_Det ails records. There are good examples of mainform/subform structures in the example Northwind database supplied with Access.

To avoid such PK/FK errors in future you may find it of help to review the following HowTo article on database normalisation and table structures. Of particular relevance to your situation is the customer-order-order_details example shown in the article.

-Stewart