Updating all primary keys in other tables

[tania20]

Hi, i have 7 tables that all have the same primary key (but repeated fields for patient testing at different weeks). I have the one form containing all this information and linked by one query. The problem is i need all the tables to have their primary key updated as soon as the form ID is entered so that this form is available when i go to add more patient data at a later date (ie ALL the tables need to have the primary key manually entered before it will appear as a form).Any advcie would be greatly appreciated!!

cheers

Tania

 

[fneily]

Below is an example of updating three tables primary keys and some fields. I didn't create the tables so you'll notice that they're not normalized. Just change the table names and fieldnames, copy the sections 4 more times, and that should do it. The code was on a command button's OnClick event.

Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
Dim db As DAO.Database
Dim RS As DAO.Recordset

Set db = CurrentDb()
Set RS = db.OpenRecordset("Attendance", dbOpenDynaset)
RS.AddNew
RS![ID] = Me![ID]
RS![FirstName] = Me![FirstName]
RS![LastName] = Me![LastName]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing

Set db = CurrentDb()
Set RS = db.OpenRecordset("Bus Information", dbOpenDynaset)
RS.AddNew
RS![ID] = Me![ID]
RS![FirstName] = Me![FirstName]
RS![LastName] = Me![LastName]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing

Set db = CurrentDb()
Set RS = db.OpenRecordset("Nurse", dbOpenDynaset)
RS.AddNew
RS![ID] = Me![ID]
RS![FirstName] = Me![FirstName]
RS![LastName] = Me![LastName]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing

Exit_Save_Record_Click:
Exit Sub

Err_Save_Record_Click:
MsgBox Err.Description
Resume Exit_Save_Record_Click

End Sub

Also it doesn't sound like your tables are normalized.

 

[tania20]

Thanks for the advice-I have adapted the code (for a very simple 2 table test file) and it is now doing what i want. The only thing now though is that after i have saved the record using the command button, when i close the form it tries to save it again so i get a duplicate error msg for the primary key.Any ideas on how to avoid this?

Cheers

Tania

 

[fneily]

You must be saving to the first table through code AND on close. Don't do that. The code is for the tables that are NOT connected to the form. The close button will save it the just the bound table.

 

[tania20]

Hi, I am a bit confused-the form is linked to a query which is a combination of the two tables..

 

[tania20]

ok, ive sorted the problem.thanks for the help