Primary Keys populationg foreign keys in other tables.

[kermitforney]

How do I do this?
I have the tables setup but, how do I get the numbers imported into the foreign tables.

Once a record is created I would like the primary key in one table populate the foreign keys in the other tables.

 

[fneily]

Let's say you have a form where you create a new record. You could have a Save button with code on the OnClick event that will populate the other tables. Such as:
Private Sub Command38_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("SomeTable", dbOpenDynaset)
RS.AddNew
RS![ForeignKeyName] = Me![PrimaryKeyName]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("SomeOtherTable", dbOpenDynaset)
RS.AddNew
RS![ForeignKeyName] = Me![PrimaryKey]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

ETC.
End Sub

 

[kermitforney]

Could I make it a tiny bit simpler?

Maybe, place a text box with the Primary Key (Autonumber) on the form as well as the Foreign Keys I would like updated. Then work in a little =Me![txtForeignKey] love in there? )

 

[jebry]

Hi!

If you have it set up as a form and a subform with the appropriate links (just run through the subform wizard) then, when a user starts to enter a record in the subform Access will automatically save the record to the main table and store the foreign key in the sub table.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[fneily]

If you have any independent tables, then you need code.