Cancel Save if data validation fails

[sra2786]

I have a "New Record" button to allow entry of new records. In the Form_BeforeUpdate I validate the data the user entered. If they try to enter a duplicate record, I want to Cancel the Save, display a message that they are trying to enter a duplicate record.

How do I select the existing record so the user can change the existing record or how do I cancel adding a New Record.changes.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intDCount As Integer
Dim blnResponse As Boolean


On Error GoTo ErrTrap


intDCount = DCount("*", "tblOrders", "[strOrderID] = '" & Me.cboOrder & "' AND [dteArriveDate] = #" & Me.txtArriveDate & "#")


If intDCount > 0 Then
MsgBox "Attempted Duplicate Process. Please fix data"
Cancel = True


Else

Thanks.
Sandy

[orange]

Please show all code.
You can also review the Similar Threads at the bottom of the page for ideas.
Good luck.

[moke123]

You could use DlookUp instead of dcount

lngPK = nz(DLookUp("YourPKField", "tblOrders", "[strOrderID] = '" & Me.cboOrder & "' AND [dteArriveDate] = #" & Me.txtArriveDate & "#"),0)

This would return 0 for no record due to the NZ() or the PK of the record assuming there will only be one.
Undo the new record and set the form to the PK you looked up.
I've never tried it but looks like it should work.

[orange]

If your records have a unique composite index (multi-field unique index), you can check for pending duplicates in the Before Update event.
You can also trap error 3022 and dismiss the attempted update with your own message.

Same approach to potentially duplicate PK.