Record Copy No-Brainer

I feel I should know this and I feel a forehead slapping moment approaching
but I don't believe I've ever had to do this before ...

I have a main form with a sub-form on a 1:M relationship. My user wants to
be able to copy a main record with all of its children into a new record.
Copying the main record is easy but how do I copy its children and relate
them to the new record? I know I need to get the PK value for the new main
record, but how?

Here's the code I have but the second append fails and rs![ID] returns the
PK for the last but one record in the recordset instead of the PK for the
new one just added. Slapping hand at the ready ...

Many thanks.
Keith.

Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = Me.RecordsetClone
Dim strSQL As String

strSQL = "INSERT INTO qryCertificates (CertNo, BoatNo, SysNo, CertTitle,
Project, OrderStatus, InspectionDate, Finish) " _
& "SELECT CertNo, BoatNo, SysNo, CertTitle, Project, OrderStatus,
InspectionDate, Finish " _
& "FROM qryCertificates Where [ID] = " & Me.txtID

db.Execute (strSQL), dbFailOnError

rs.Requery
rs.MoveLast

strSQL = "INSERT INTO qsfrmItems (CertID, Item, DrgNo, DrgTitle,
SysCode, TestForm) " _
& "SELECT " & rs![ID] & " As NewID, Item, DrgNo, DrgTitle, SysCode,
TestForm FROM qsfrmItems Where [CertID] = " & Me.txtID

db.Execute (strSQL), dbFailOnError

Set db = Nothing

Re: Record Copy No-Brainer

"Keith Wilby" wrote in
news:4785e576$1_1@glkas0286.greenlnk.net:

> I feel I should know this and I feel a forehead slapping moment
> approaching but I don't believe I've ever had to do this before ...
>
> I have a main form with a sub-form on a 1:M relationship. My user
> wants to be able to copy a main record with all of its children into a
> new record. Copying the main record is easy but how do I copy its
> children and relate them to the new record? I know I need to get the
> PK value for the new main record, but how?
>
> Here's the code I have but the second append fails and rs![ID] returns
> the PK for the last but one record in the recordset instead of the PK
> for the new one just added. Slapping hand at the ready ...
>
> Many thanks.
> Keith.
>
> Dim db As DAO.Database, rs As DAO.Recordset
> Set db = CurrentDb
> Set rs = Me.RecordsetClone
> Dim strSQL As String
>
> strSQL = "INSERT INTO qryCertificates (CertNo, BoatNo, SysNo,
> CertTitle,
> Project, OrderStatus, InspectionDate, Finish) " _
> & "SELECT CertNo, BoatNo, SysNo, CertTitle, Project,
> OrderStatus,
> InspectionDate, Finish " _
> & "FROM qryCertificates Where [ID] = " & Me.txtID
>
> db.Execute (strSQL), dbFailOnError
>
> rs.Requery
> rs.MoveLast
>
> strSQL = "INSERT INTO qsfrmItems (CertID, Item, DrgNo, DrgTitle,
> SysCode, TestForm) " _
> & "SELECT " & rs![ID] & " As NewID, Item, DrgNo, DrgTitle,
> SysCode,
> TestForm FROM qsfrmItems Where [CertID] = " & Me.txtID
>
> db.Execute (strSQL), dbFailOnError
>
> Set db = Nothing

Assuming ID is an autonumber, you could try:

Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
..ConnectionString = CurrentProject.BaseConnectionString
..Open
..Execute "INSERT INTO qryCertificates (CertNo, BoatNo, SysNo, ...

..Execute "INSERT INTO qsfrmItems (CertID, Item, DrgNo, DrgTitle, SysCode,
TestForm) " _
& "SELECT " & .Execute("SELECT @@Identity").(0) & " As NewID, Item,
DrgNo, DrgTitle, ...

End With

Yes, there is a reason for creating a new connection, although it may
well be un-needed.

Re: Record Copy No-Brainer

"Keith Wilby" wrote in message
news:4785e576$1_1@glkas0286.greenlnk.net...
> the second append fails and rs![ID] returns the PK for the last but one
> record in the recordset instead of the PK for the new one just added.

Further investigation reveals that the new record is at the beginning of the
rs recordset ... however the append still fails.

Re: Record Copy No-Brainer

Keith Wilby wrote:

> I feel I should know this and I feel a forehead slapping moment
> approaching but I don't believe I've ever had to do this before ...
>
> I have a main form with a sub-form on a 1:M relationship. My user wants
> to be able to copy a main record with all of its children into a new
> record. Copying the main record is easy but how do I copy its children
> and relate them to the new record? I know I need to get the PK value
> for the new main record, but how?
>
> Here's the code I have but the second append fails and rs![ID] returns
> the PK for the last but one record in the recordset instead of the PK
> for the new one just added. Slapping hand at the ready ...
>
> Many thanks.
> Keith.
>
> Dim db As DAO.Database, rs As DAO.Recordset
> Set db = CurrentDb
> Set rs = Me.RecordsetClone
> Dim strSQL As String
>
> strSQL = "INSERT INTO qryCertificates (CertNo, BoatNo, SysNo,
> CertTitle, Project, OrderStatus, InspectionDate, Finish) " _
> & "SELECT CertNo, BoatNo, SysNo, CertTitle, Project, OrderStatus,
> InspectionDate, Finish " _
> & "FROM qryCertificates Where [ID] = " & Me.txtID
>
> db.Execute (strSQL), dbFailOnError
>
> rs.Requery
> rs.MoveLast
>
> strSQL = "INSERT INTO qsfrmItems (CertID, Item, DrgNo, DrgTitle,
> SysCode, TestForm) " _
> & "SELECT " & rs![ID] & " As NewID, Item, DrgNo, DrgTitle,
> SysCode, TestForm FROM qsfrmItems Where [CertID] = " & Me.txtID
>
> db.Execute (strSQL), dbFailOnError
>
> Set db = Nothing
>
>
You could, of course, use DAO and Addnew and you'd have the autonumber
record key when updating the children.

Re: Record Copy No-Brainer

"Salad" wrote in message
news:13oc8heau6mmcb4@corp.supernews.com...
>>
> You could, of course, use DAO and Addnew and you'd have the autonumber
> record key when updating the children.

Yeh I did consider that but I just wondered why the second append query was
failing.

Re: Record Copy No-Brainer

"Salad" wrote in message
news:13oc8heau6mmcb4@corp.supernews.com...
> Keith Wilby wrote:
>
> You could, of course, use DAO and Addnew and you'd have the autonumber
> record key when updating the children.

I'm still ready for that forehead slapping moment on this. Any idea why the
action query in this code *still* fails? The AddNew works just fine.
Thanks.

Dim db As DAO.Database, rs1 As DAO.Recordset, rs2 As DAO.Recordset,
strSQL As String

Set db = CurrentDb
Dim lngNewID As Long

strSQL = "SELECT * FROM qryCertificates"

Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQL)

With rs1
.MoveFirst
Do Until .EOF
rs2.AddNew
rs2![CertNo] = ![CertNo]
rs2![SysNo] = ![SysNo]
rs2![CertTitle] = ![CertTitle]
rs2![BoatNo] = "2"
rs2![Project] = "1123"
rs2.Update
lngNewID = rs2![ID]

strSQL = "INSERT INTO qsfrmItems (CertID, Item, DrgNo, DrgTitle,
SysCode, TestForm) " _
& "SELECT " & lngNewID & " As NewID, Item, DrgNo, DrgTitle,
SysCode, TestForm FROM qsfrmItems Where [CertID] = " & ![ID]

db.Execute (strSQL), dbFailOnError

.MoveNext
Loop
End With

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

Re: Record Copy No-Brainer

"Keith Wilby" wrote in message
news:47864681$1_1@glkas0286.greenlnk.net...
> "Salad" wrote in message
> news:13oc8heau6mmcb4@corp.supernews.com...
>> Keith Wilby wrote:
>>
>> You could, of course, use DAO and Addnew and you'd have the autonumber
>> record key when updating the children.
>
> I'm still ready for that forehead slapping moment on this. Any idea why
> the action query in this code *still* fails? The AddNew works just fine.
> Thanks.
>



The action query needs to be based on a table, not another query ... DUH.
;-)

Re: Record Copy No-Brainer

Keith Wilby wrote:

> "Salad" wrote in message
> news:13oc8heau6mmcb4@corp.supernews.com...
>
>> Keith Wilby wrote:
>>
>> You could, of course, use DAO and Addnew and you'd have the autonumber
>> record key when updating the children.
>
>
> I'm still ready for that forehead slapping moment on this. Any idea why
> the action query in this code *still* fails? The AddNew works just
> fine. Thanks.
>
> Dim db As DAO.Database, rs1 As DAO.Recordset, rs2 As DAO.Recordset,
> strSQL As String
>
> Set db = CurrentDb
> Dim lngNewID As Long
>
> strSQL = "SELECT * FROM qryCertificates"
>
> Set rs1 = db.OpenRecordset(strSQL)
> Set rs2 = db.OpenRecordset(strSQL)
>
> With rs1
> .MoveFirst
> Do Until .EOF
> rs2.AddNew
> rs2![CertNo] = ![CertNo]
> rs2![SysNo] = ![SysNo]
> rs2![CertTitle] = ![CertTitle]
> rs2![BoatNo] = "2"
> rs2![Project] = "1123"
> rs2.Update
> lngNewID = rs2![ID]

Well....I might assign the id prior to update or after updating make
sure I'm on the same record.
rs2.Bookmart = rs2.LastModified.
I'm not saying you aren't getting the right id, just making sure of it.

If that's not the prob, I might put a Stop after the Do Until and step
thru the code and see what the values are.

>
> strSQL = "INSERT INTO qsfrmItems (CertID, Item, DrgNo,
> DrgTitle, SysCode, TestForm) " _
> & "SELECT " & lngNewID & " As NewID, Item, DrgNo, DrgTitle,
> SysCode, TestForm FROM qsfrmItems Where [CertID] = " & ![ID]
>
> db.Execute (strSQL), dbFailOnError
>
> .MoveNext
> Loop
> End With
>
> rs1.Close
> rs2.Close
> Set rs1 = Nothing
> Set rs2 = Nothing
> Set db = Nothing
>