Check for duplicate and error message

[casper]

I have a form - see attached image.
The first text box is called Job_No. When a number is entered here how can I check that the number doesn't already exist when the user tabs to the next box. Then if it does exist display a custom message to the effect "This already exists" and not the Access default duplicate error message.

Thanks

Michael

 

[Matt Greatorex]

In the field's OnLoseFocus or AfterUpdate event, or in the form's BeforeUpdate event, you could use the DCount() function to see if there is at least one record present. e.g. something like

If DCount("JobNumber","JobTable","JobNumber = " & [Forms]![[I]formname[/I]]![Job_No]) Then
     Msgbox "That Job Number already exists"
     [Forms]![[I]formname[/I]]![Job_No] = ""
     [Forms]![[I]formname[/I]]![Job_No].SetFocus
End If