Duplicate a form and subform using an autonumber (1 Viewer)

[paul young]

I am trying to duplicate a form and subform. Each record in the form is identiefied by a BookingID which is an autonumber. When I run the following duplicate command, a message appears saying 'You tried to assign the Null value to a variable that is not a Variant data type.' It is a Runtime error 3162. This form is a booking form and the subform contains food items for each booking.


Private Sub DuplicateARecord_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!BookingID = Me.BookingID
!SerialNo = Me.SerialNo
!NoOfBookingPerRequistionNo2 = Me.NoOfBookingPerRequistionNo2
!NameOfHost = Me.NameOfHost
!TrustOrNonTrust = Me.TrustOrNonTrust
!Department = Me.Department
!ContactTelephone = Me.ContactTelephone
!ContactMobile = Me.ContactMobile
!ContactFax = Me.ContactFax
!DateOfRequest = Me.DateOfRequest
!DateOfFunction = Me.DateOfFunction
!TimeRequiredStart = Me.TimeRequiredStart
!TimeRequiredEnd = Me.TimeRequiredEnd
!Venue = Me.Venue
!ReasonForBooking = Me.ReasonForBooking
!NoOfGuests = Me.NoOfGuests
!StandingOrder = Me.StandingOrder
!Comments = Me.Comments
!BookingReceivedBy = Me.BookingReceivedBy
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !BookingID

'Duplicate the related records: append query.
If Me.[MenuSelection].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [MenuSelected] ( BookingID, ItemID, Itemname, NumberofitemID, [Trust:Itemcost], SubTrustcost, [NonTrust:Itemcost], SubNonTrustcost, Itemcomment, PriceComment ) " & _
"SELECT " & lngID & " As NewID, ItemID, Itemname, NumberofitemID, [Trust:Itemcost], SubTrustcost, [NonTrust:Itemcost], SubNonTrustcost, Itemcomment, PriceComment ) " & _
"FROM [MenuSelected] WHERE BookingID = " & Me.BookingID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

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

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler

End Sub

I am no programmer as I found the coding on internet and substituted the necessary fields. Is there a way round this autonumber issue?

Thanks.

Paul

 

[WayneRyan]

Paul,

Move this line to be before the .Update

.Bookmark = .LastModified

For some weird reason, the AutoNumber field's value is only valid
between the .AddNew and the .Update.

Wayne

 

[paul young]

Duplicate form and subform

Wayne

Thanks for your reply. I did what you advised and the same message appeared. When I debugged it, the following line was highlighted:

!BookingID = Me.BookingID

which suggested that the problem lies on that line before it even gets to the .AddNew / .Bookmark = .LastModified / .Update lines.

Any suggestion?

Thanks.

Paul