Duplicate record from Combo Box - autonumber and multiple tables

[Ann in CA]

Hello,
I have a form based on "TID." Each TID may have multiple Coord Sheets (PK =
CSID). Combo91 is a combo box, the source of which is the CSID (hidden),
Type, CSTitle, Owner from tblCoordSheets). I would like the user to be able
to select a record in combo91, and press btnCopyCS, where it will copy all
fields of the chosen record into a new record. It will also duplicate any
records in tables that are linked by a one-to-many relationship with
tblCoordSheets, and finally, it will add to table TID-CSID link the TID from
the main form and the CSID from the newly created record.

And yes, all of these things are needed, every Coord Sheet has details in
several "subtables" where 25 details may be the same but 5 may be
different--so I want them to be able to choose one record that already
exists, and copy it to a new record so that they can modify/delete/add
details without messing up the original record.

Here is the current code. The two biggest problems I have are:
- it seems to ignore the recordset I was trying to declare, and instead
copies the record currently on the subform; and
- it will not copy the matching record out of the second table (tblCSRevision)

Any help would be greatly appreciated--it seems so easy in theory, but my
VBA just isn't there yet.

Private Sub btnCopyCS_Click()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngMainID As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblCoordSheets WHERE CSID = " &
Me.Combo91)
With rs
.AddNew
!Owner = glPrimary
!Type = Me!Type
!CSTitle = Me!CSTitle
.Update
.Bookmark = .LastModified

lngMainID = !CSID ' this is the autonumber field
.Close
End With
Debug.Print lngMainID
Set rs = Nothing
Set db = Nothing
Debug.Print lngMainID
If Nz(DLookup("CSID", "tblCsRevision", "CSID = " & Me.Combo91), 0) <> 0
Then
DoCmd.RunSQL "INSERT INTO tblCsRevision (CSID, Revision) " & _
"SELECT (" & lngMainID & ", Revision) FROM tblCoordSheets WHERE
CSID = " & Me.Combo91, dbFailOnError
End If


ProcExit:
Exit Sub
ProcError:
Select Case Err.Number
Case 3075
DisplayMessage "You haven't specified a Coord sheet to copy!"
Resume ProcExit
Case Else
MsgBox Err.Number & Err.Description
' SendError Err.Number & Err.Description & "frmCoordSheetsAvailableSub
btnCopyCS_Click"
Resume ProcExit
End Select
End Sub

 

[Carl Rapson]

Hello,
I have a form based on "TID." Each TID may have multiple Coord Sheets (PK
=
CSID). Combo91 is a combo box, the source of which is the CSID (hidden),
Type, CSTitle, Owner from tblCoordSheets). I would like the user to be
able
to select a record in combo91, and press btnCopyCS, where it will copy all
fields of the chosen record into a new record. It will also duplicate any
records in tables that are linked by a one-to-many relationship with
tblCoordSheets, and finally, it will add to table TID-CSID link the TID
from
the main form and the CSID from the newly created record.

And yes, all of these things are needed, every Coord Sheet has details in
several "subtables" where 25 details may be the same but 5 may be
different--so I want them to be able to choose one record that already
exists, and copy it to a new record so that they can modify/delete/add
details without messing up the original record.

Here is the current code. The two biggest problems I have are:
- it seems to ignore the recordset I was trying to declare, and instead
copies the record currently on the subform; and
- it will not copy the matching record out of the second table
(tblCSRevision)

Any help would be greatly appreciated--it seems so easy in theory, but my
VBA just isn't there yet.

Private Sub btnCopyCS_Click()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngMainID As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblCoordSheets WHERE CSID = "
&
Me.Combo91)
With rs
.AddNew
!Owner = glPrimary
!Type = Me!Type
!CSTitle = Me!CSTitle
.Update
.Bookmark = .LastModified

lngMainID = !CSID ' this is the autonumber field
.Close
End With
Debug.Print lngMainID
Set rs = Nothing
Set db = Nothing
Debug.Print lngMainID
If Nz(DLookup("CSID", "tblCsRevision", "CSID = " & Me.Combo91), 0) <> 0
Then
DoCmd.RunSQL "INSERT INTO tblCsRevision (CSID, Revision) " & _
"SELECT (" & lngMainID & ", Revision) FROM tblCoordSheets WHERE
CSID = " & Me.Combo91, dbFailOnError
End If


ProcExit:
Exit Sub
ProcError:
Select Case Err.Number
Case 3075
DisplayMessage "You haven't specified a Coord sheet to copy!"
Resume ProcExit
Case Else
MsgBox Err.Number & Err.Description
' SendError Err.Number & Err.Description & "frmCoordSheetsAvailableSub
btnCopyCS_Click"
Resume ProcExit
End Select
End Sub

I can see why your first problem is occurring. You fetch the recordset from
tblCoordSheets for the CSID you're interested in, but you never use that
information. Instead, after the AddNew, you copy values using Me!, which
uses the current record on the form. You'll need to either create two
recordsets (one for the existing record and one for the new record you want
to add), or use DLookUps to fetch the values you want:

Set rs = db.OpenRecordset("SELECT * FROM tblCoordSheets WHERE False")
With rs
.AddNew
!Owner = glPrimary
!Type = DLookUp("Type","tblCoordSheets","CSID=" & Me.Combo91)
!CSTitle = DLookUp("CSTitle","tblCoordSheets","CSID=" & Me.Combo91)
.Update
.Bookmark = .LastModified
lngMainID = !CSID ' this is the autonumber field
.Close
End With

I use this method frequently, and it works well. As for your other problem,
I think it's in the RunSQL method. The second parameter of RunSQL should be
a Boolean value to specify whether or not a transaction is used. It looks
like you may be thinking of the Execute method instead, which is part of the
Database object. Try:

db.Execute "INSERT INTO tblCsRevision (CSID, Revision) " & _
"SELECT (" & lngMainID & ", Revision) FROM tblCoordSheets WHERE
CSID = " & Me.Combo91, dbFailOnError

instead, and see if that works.

Carl Rapson

 

[Ann in CA]

Thanks, Carl, I will try this.
Don't laugh, but I found the error in the second part--
INSERT INTO tblCsRevision (CSID, Revision) " & _

"SELECT (" & lngMainID & ", Revision) FROM tblCoordSheets

should be
INSERT INTO tblCsRevision (CSID, Revision) " & _

"SELECT (" & lngMainID & ", Revision) FROM tblCsRevision

If it works after I implement your suggested change, THANK YOU!!!!

 

[Carl Rapson]

Good catch, I didn't notice that. However, what I said about the RunSQL
versus Execute methods still applies: there is no 'dbFailOnError' option for
RunSQL. Also, you may have to deal with popup messages when you use RunSQL
as opposed to Execute. But if everything works, great.

Carl Rapson