By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Creating Master->Details record using Code (Autonumber)

P: 2
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_details (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_details 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
Aug 4 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
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_details 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_details 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_details 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_Details 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
Aug 10 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.