Copying Records

[Joe Williams]

I have a form/subform set up that lists authorized products for each
SalesPerson. So the form lists sales person john and has a subform that
lists all of his products (a,b,c,d,etc) Table structure is similar to this:
tblSales (SalesPersonID, SalesPersonName), tblSalesProducts(SalesPersonID,
ProductID)

How can i code a button on the main form that will copy all of the subform
records to a new salesperson?

THanks

JOe

 

[Allen Browne]

One approach is to use the RecordsetClone to duplicate the main form record.
This gives you the new value for the autonumber, and you can then use an
Append query statement to copy the subform records also.

The actual code will depend on your field names. This example is for
duplicating an invoice: invoice in main form, with line items in subform:

Private Sub cmdDupe_Click()
Dim sSQL As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
sSQL = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount ) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, tInvoiceDetail.Amount " & _
"FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute sSQL, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records.", vbInformation, "Information"
End If

'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub