propagate SSN field to other tables

[rockjockb]

I need to propagate the SSN field to multiple tables when a new record is created. SSN is the pk in all tables. Examples of some of the tables are: Demographics (where the initial record is created), StudentProgressNotes, Practicum, StudentDisenrollment, etc. I have created one-to-one relationships incl cascade update and delete for all of these (all relate to the Demographics table via SSN), but that only works once the record is created.

Several attempts at Append Queries and Macros later, I can't get it to happen. This seems like it should be simple, but I'm missing it.

Thanks in advance for your help.

 

[Eupher]

You can do it pretty easily with VBA code. Here's an example:

Dim CurDB As Database
Dim Rs As Recordset

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblMyTableName")
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.Close

Set Rs = Nothing
Set CurDB = Nothing

You could put this code behind the Click event of a command button, or perhaps in the After Update event of the SSN field on your main form. You might want to include some error checking to prevent duplicate entries, but this should get you started...

Ken S.

 

[rockjockb]

Ken,
I'm not sure if I run the code for each table I need SSN propagated to?

ie. should tblMyTableName be the Demographics table (where the record is created)?

Or should I run the code multiple instances with tblStudentProgressNotes, tblPracticum, etc in tblMyTableName?

Obviously the code is beyond my skillset, but the stuff you did for me before worked perfectly, and I am greatly appreciative.

 

[rockjockb]

I keep getting duplicate entry errors when I enter a new record. I tried both ways that I stated above.

The SSN field is the primary key for all of the tables. I need it to basically be copied from tblDemographics to tblStudentProgressNotes, tblPracticum, etc. all in one swoop.

 

[rockjockb]

I got it!! Ken, if you are ever in L.A. I pledge to buy you a beer, dinner, or something. Thanks again.

This is what I did:

Private Sub SSN_AfterUpdate()
On Error GoTo Err_SSN_AfterUpdate

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim CurDB As Database
Dim Rs As Recordset

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblCertification&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblAdverseCertificationEntries&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentPracticum&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentProgressSheet&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentProgressNotes&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentProbation&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentRemediation&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentWarning&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentDisenrollment&quot
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.close

Set Rs = Nothing
Set CurDB = Nothing

Exit_SSN_AfterUpdate:
Exit Sub

Err_SSN_AfterUpdate:
MsgBox Err.Description
Resume Exit_SSN_AfterUpdate

End Sub

 

[rockjockb]

btw, I was serious. If you are in town, email me: brandon at zylan dot net.

 

[Eupher]

No, tblMyTableName should be the destination table. Yes, you will need to run the code for each table you wish to write the data to, i.e.:

Dim CurDB As Database
Dim Rs As Recordset

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblStudentProgressNotes")
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.Close

Set Rs = CurDB.OpenRecordset("tblPracticum")
Rs.AddNew
Rs![SSN] = Me![SSN]
Rs.Update
Rs.Close

Set Rs = Nothing
Set CurDB = Nothing

Check your table structure and data. If the SSN field is indexed with no duplicates allowed (which would be expected if it's the PK), the code will fail if the SSN already exists in the destination table.

Ken S.

 

[Eupher]

Sorry, I forgot to mention that with your existing relationships, the record in your main table must be written before you can write the corresponding info to the other tables. Glad you were able to figure it out. BTW, I think you can remove the multiple "Set CurDB = CurrentDb()" statements - the first one should be sufficient. Also, not sure what version of Access you're using, but FWIW the DoMenuItem method was replaced in Access 97 by the RunCommand method. DoMenuItem was included for backward compatibility. The RunCommand equivalent for saving the record would be:

DoCmd.RunCommand acCmdSaveRecord

I only mention it because I've had some difficulty using the DoMenuItem method in Access XP - some things just flat don't work. For future reference...

Thanks very much for your kind offer. Not sure when I'll ever be in LA, but your generosity is much appreciated. Glad I could be of assistance!

Ken S.