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
2
