Unexpected validation error message

[uncle-lai]

I have a text box in a data entry form bound to an NON-INDEXED field. I have put in codes to check for duplicates and to prevent input of duplicate data:

If (Eval("DLookup(""[TicketNo]"", ""[TS_Collect]"", ""[TicketNo] = FORM![TicketNo]"") Is Not Null")) Then
Beep
MsgBox "This ticket has been entered already. Please check your number." & Chr(10) & Chr(10) _
& "Or press [Esc] key to cancel input.", vbExclamation, "Double entry!"
DoCmd.CancelEvent
DoCmd.RunCommand acCmdUndo
End If

But I have 2 problems:

1. If a duplicate value is entered an MS Access message pops up following my message box says "The value in the field or record violates the validation rule for the record or field." with an example and explanation. Even though I have no validation rule set for the field. I don't know why it comes out and how to get rid of it.

2. After clicking OK to close the message box, I can enter a new value. But if I want to cancel data entry I need to press Esc twice. The first time I press Esc the value I just entered re-appears, I have to press Esc again to cancel the input of a new record. How can I cancel input with only one Esc key press?

Thanks.

Peter

 

[WayneRyan]

Peter,

You can use the form's Before Insert event to do the following:

Dim Response As Integer
If Nz("DLookup("[TicketNo]", "[TS_Collect]", "[TicketNo] = " & Me.TicketNo), "") <> "" Then
   Beep
   Response = MsgBox "This ticket has been entered already. Please check your number." & _     
                     vbCrLf & _
                     "Do you want to retry", vbYesNo, "Double entry!"
   If Response = vbYes Then
      Exit Sub
   Else
     Cancel = True
     Me.Undo
   End If
End If

I haven't tested it, but you can try something like the above.

Wayne

 

[uncle-lai]

Thanks Wayne and Pat,

Pat was right, the TicketNo field is a required field and that's one factor that caused the violation of validation.

After I clicked OK on my own message box it closed, then the "Violation of Validation Rule" message box popped up, which I clicked OK to close. The cursor is still in the CURRENT record. (There was a NEW record row below). I didn't want to enter again so I pressed Esc once, the number I entered before re-appeared; I pressed Esc again to make the CURRENT record row disappear, then the cursor was in the NEW record row.

Now I replaced
Docmd.CancelEvent
Docmd.RunCommand acCmdUndo
with
Me.Undo

When I clicked OK to close my message box the CURRENT record row disappeared and the form returned to normal data entry mode, no more "Violation of Validation Rule" message. And I can keep TicketNo as a required field. I still don't know what exactly the difference between "Docmd.RunCommand acCmdUndo" and "Me.Undo", but "Me.Undo" works great in this case.

Thanks again and wish you all a Happy New Year.

Peter