Update multiple tables from one the value on a form

[mhk]

I have a database that has one parent table and multiple child tables. The parent table has a RecordID field (primary key) that is an AutoNumber. All the child tables have RecordID field with a one-many relationship to the parent RecordID field.

What I am attempting to do is have the RecordID in all of the Children automatically populate with the parents RecordID upon entry into the Parent table. I've been trying to use VBA to generate a SQL Insert Into statement, but have not had luck getting it to work. Here is my SQL statement:

Dim sql As String
sql = "Insert into CentralAir (CA-RecordID) Values ('" & Me.RecordID & "');"
DoCmd.RunSQL sql

I have the SQL statement in an OnClick event prior to an update routine. Perhaps I'm making this to hard and the is an easier solution.

Thanks for any help/advise
MK

 

[dkinley]

Hi.

What I am attempting to do is have the RecordID in all of the Children automatically populate with the parents RecordID upon entry into the Parent table.

You want this to occur regardless if you have created a child record? Or only upon creation of a child record?

Is the data presented in forms? Are the child tables bound to subforms of the parent table form? Or are you wanting the child forms to spawn from the parent form?

-dK

 

[mhk]

I would like this to occur regardless of creating a child record. All tables have seperate data entry forms. This is basically a database used for tracking different types of repates. There are many types of rebates available and some people may qualify for one rebates or multiple rebates. I will be generating a report that shows customer information (parent table) and all data from all rebates (regardless if they qualified or not). For data entry purposes, I do not want to have to access the forms for children tables that do not qualify for the rebates.

Thanks
MK

 

[dkinley]

Your code should work although I am not sure about hyphens in field names, for spaces and hyphens I've always used underscores.

Other than that, I know following code can be used; however for relationship purposes, the parent record has to be saved prior to the creation of the child records so ensure your event is triggered after saving.

Dim sqlChild1 As String
Dim sqlChild2 As String
 
sqlChild1 = "INSERT INTO tblChild1 (ParentID)" & _
    " Values ('" & Me!txtParentID & "');"
sqlChild2 = "INSERT INTO tblChild2 (ParentID)" & _
    " Values ('" & Me!txtParentID & "');"
 
DoCmd.RunSQL sqlChild1
DoCmd.RunSQL sqlChild2

I seem to recall some sort of auto-method you could have the database itself do it for you but that was long ago and not sure if my mind is playing tricks on me. Maybe someone else can weigh in on a better solution?

-dK

 

[mhk]

Thanks dK. That seams to work (I had a minor Syntex error). One more questions. It displays a pop-up box that states "you are about to append 1 row". Any idea on how to turn this off?

Thanks
MK

 

[dkinley]

You bet mhk. Oh yes .. the warnings ... apologies. You can turn those off but I highly recommend turning them back on (so you know if you have errors with other things as you input and work.)

Also, I am throwing in hourglass in case you never seen it before and depending on the speed of execution and # of child forms you may want to turn the cursor into an hourglass to let the user know somethings going down and they need to wait.

'set warnings off
DoCmd.SetWarnings False
 
'turn on hourglass
DoCmd.Hourglass True
 
... do your stuff here
 
'turn off hourglass
DoCmd.Hourglass False
 
'set warnings on
DoCmd.SetWarnings True

-dK

 

[mhk]

That's great. Works just like I wanted it to. Thanks for the help dK.