Hi Richard/Alex

I have set up a field within my WorkOrderF called OrderNumber which is indexed and set to no duplicates.

I want to prevent users from entering a duplicate number but if they do I want them to be taken to the original record.

I have the following code in the before update event:

Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.OrderNumber.Value
    stLinkCriteria = "OrderNumber =" & "'" & SID & "'"

    'Check WorkOrderT for duplicate OrderNumber
    If DCount("OrderNumber", "WorkOrderT", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Order Number " _
             & SID & " Already Exists." _
             & vbCr & vbCr & "You will now be taken to that record.", _
               vbInformation, "Warning: Duplicate Information"
        'Go to record of original Order Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
End If

    Set rsc = Nothing
    
End Sub

It works fine until it reaches Me.Bookmark = rsc.Bookmark

then it breaks. Can you show me what I'm doing wrong. I am using Access 2010.

Thanks
Anne

---

Reply from Alex Hedley:

I'm not a fan of RecordsetClone and Bookmarks

Why not set it to:
Set rsc = CurrentDb.OpenRecordset("TABLE/QUERY", dbOpenDynaset)

Or you could say
Set rsc = Me.Recordset.Clone

Also it might not be the Bookmark that is the issue, sometimes Access highlights the next line.
Is OrderNumber a String or Integer
stLinkCriteria = "OrderNumber =" & "'" & SID & "'"
If Integer change to:
stLinkCriteria = "OrderNumber=" & SID