Prevent duplicate records but allow edits

[demogorgan]

Hi,

So I have a database that users enter order details into - but I do not wish to have duplicate records.
I have prevented that by the following code in the before update portion of the database.

Dim Answer As Variant
        Answer = DLookup("[Order_Number]", "tblOrder_Details", "[Order_Number] = '" & Me.Order_Number & "'")
    If Not IsNull(Answer) Then
        MsgBox "Duplicate Order Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.Order_Number.Undo

This works great, however when I try to edit the record it fires the above code - is there a way of allowing edits but still blocking duplicate values?

Thanks in advance

Demo

 

[pbaldy]

You can add an Else clause and either open up a separate form:

http://www.baldyweb.com/wherecondition.htm

or adapt the bookmark code at the bottom to move the current form to that record.

 

[Mark_]

If [Order_Number] is a number and is AutoNumber, you should have no issues as Access will take care of this for you.

If you are creating your own [Order_Number] in another way, is there a really, really, really good reason to NOT use an AutoNumber?

 

[pbaldy]

It occurred to me I may have misunderstood and that you may be editing a record on the form. If so, you can test NewRecord to know if you're on a new or existing record. Don't test the order number if you're on an existing record.

If Me.NewRecord Then

 

[demogorgan]

It occurred to me I may have misunderstood and that you may be editing a record on the form. If so, you can test NewRecord to know if you're on a new or existing record. Don't test the order number if you're on an existing record.

If Me.NewRecord Then

Hi,

Apologies for the late response - that was what I was missing, I have been scratching my head for days on that one

Thank you

Demo

 

[pbaldy]

Happy to help!