Primary Key from main form/table to be ID in other forms/tables

[jessmith07]

I am building a database in Acess 2007 and I have three tables housing data...one table houses the main data, one houses fiscal data and the third houses accounting line data. tblMain has a primary key set to auto number, tblFiscal and tblAccountingLine both have their ID field set to number (no primary key, as they need to allow duplicate values).



What I want is to have the primary key from tblMain be automatically inserted into the ID field of tblFiscal and tblAccountingLine when new records are added...

By way of example...In the forms, themselves, if record 9 in frmMain is active, and record 9 also has already had information entered in frmFiscal and frmAccountingLine, then clicking the buttons to display the frmFiscal and frmAccountingLine does bring up the correct information for record 9.

What I want relates to new record entries. So, if I'm making a new entry in frmMain for record 10, and then click the buttons to add new data into frmFiscal and frmAccountingLine, I want the record number from frmMain (i.e. primary key in tblMain) to auto-populate the ID fields in frmFiscal and frmAccountingLine, thereby updating ID field tblFiscal and tblAccountingLine.

[ssanfu]

What your structure should look like (IMO)

tblMain
----------
MainID_PK  Autonumber
other data fields
...
...

tblFiscal
------------
FiscalID_PK Autonumber
MainID_FK  Long ' foreign key link to table tblMain
other fields
...
...

tblAccountingLine
-------------------
AccntLineID_PK Autonumber
MainID_FK Long  ' foreign key link to table tblMain
other fields
...
...

I always have an autonumber as the PK in a table. The autonumber field just ensures you have a unique field in the record - nothing else. (PKs and FKs are not visible to the users.)

HOW you get MainID_PK into the foreign key fields in the other two tables depends - you can use a main form/sub form arraignment or you can use code. I tend to use code - and not so much sub forms.


Here is a link to an example:
http://msofficeuser.com/pages/access...crosoft-access