Append and then Update Query

[GrandMasterTuck]

Hi folks. I've built queries using the Access Query builder, and I'm hoping one of you can help me with this.

I have two tables: tblModels and tblParts. tblModels has primary key ModelID; tblParts has primary key PartID, and also has a ModelID field that's hooked to tblModels's ModelID with referential integrity (cascade all). I have a button on my form that duplicates the model (creating a new record in tblModels and getting a new ModelID), and when that happens, I'd like for the duplication process to run an Append Query that captures all records in tblParts that matches the FIRST ModelID (the original one), and makes new copies of them in the same table, but then sets the ModelID for each new record to the NEW ModelID (that was created with the button press).

I have done this using TempVars in my Append Query, and everything is working great, except for when all the Parts records get copied and added, they all have the OLD ModelID on them, which essentially duplicates the records in the old Model and leaves the new Model empty of associated records!

The Append Query is using the ModelID field to find the records I want, so how do I then get it to write a NEW ModelID to each record after they're added to the table?

EDIT: I thought of maybe doing the old TempTable, Append Query dumps to that, Update Query changes ModelID's, Append Query dumps them back to the original table... but that seems like the long way around... any ideas on how to shorten that process? There has to be a way!

 

[TJPoorman]

This should work for what you're looking for. You will need to replace all the Field1, Field2, etc. with your field names from tblParts.

Private Sub CommandButton_Click()
Dim strQuery As String
Dim lngID As Long
Dim db As DAO.Database

strQuery="INSERT INTO tblModels SELECT * FROM tblModels WHERE ModelID=" & Me.ModelID

Set db = CurrentDB
db.Execute(strQuery)
lngID = db.OpenRecordset("SELECT @@IDENTITY")(0)

strQuery="INSERT INTO tblParts (ModelID, Field1, Field2) SELECT " & lngID & ", Field1, Field2 FROM tblParts WHERE ModelID = " & Me.ModelID
db.Execute(strQuery)
Set db = Nothing
End Sub

 

[GrandMasterTuck]

TJPoorman, a couple of questions, if you please (because I'm really a horrible newb at SQL, so I like to learn how things work... please bare with me!). My questions are numbered, and in RED below:

Private Sub CommandButton_Click()
Dim strQuery As String
Dim lngID As Long
Dim db As DAO.Database

strQuery="INSERT INTO tblModels SELECT * FROM tblModels WHERE ModelID=" & Me.ModelID

1. Is this line above making the new Model Record for me, too?? If not, then what, exactly, is this line doing?

Set db = CurrentDB
db.Execute(strQuery)
lngID = db.OpenRecordset("SELECT @@IDENTITY")(0)

strQuery="INSERT INTO tblParts (ModelID, Field1, Field2) SELECT " & lngID & ", Field1, Field2 FROM tblParts WHERE ModelID = " & Me.ModelID
db.Execute(strQuery)
Set db = Nothing
End Sub

2. Do I attach this to the DUPLICATE button? And this will do EVERYTHING for me? Create the new ModelID, copy Parts records from the OLD ModelID, paste them into that same Parts table and assign them all the NEW ModelID??

Let me ask a different question:

Say I have frmMain that's attached to tblModels. And on frmMain is frmSubForm, which is attached to qryParts. qryParts filters the records based upon the ModelID that's visible on frmMain. The duplicate button is on frmMain.

Can I create a button on frmMain that will:

1. Copy all parts records from qryParts for that ModelID,
2. Create a new Model record in tblModels, and switch to it on frmMain
3. Paste all parts records, and set their ModelID to the new one?

And if so... would you be willing to show me how to do that? ...Or does what you posted do that?

 

[GrandMasterTuck]

I've added that code to a new command button on frmMain, and have updated it so the 'fieldX' designations match my actual field names, but clicking it does nothing. No new Models are added to tblModels, and none of the items in tblParts are duplicated and set to the new ModelID.

Did I miss a step, or place the code in the wrong place? Any help would be appreciated! Thanks again!

 

[TJPoorman]

What i posted should do exactly what you've requested with the exception of switch to the new part. To do that you would want to add a line something to the effect of:

Me.Filter = "ModelID=" & lngID
Me.FilterOn = True

When you say that it isn't creating the new models are you referring to your form or the underlying recordsource?