Notify of duplicate value in field before going to next form

[ustar]

I have a field that is text based (numbers and letters) that does not allow duplicates but does not tell me until I get ready to save the form that it won't allow because it is a duplicate. I would like it to notify me once I leave the field, since by the time I have filled in the rest of the form it is quite lengthy. I have tried Microsoft help what they had did not work.

 

[Nebuchednezzar]

you could put some code in the 'on exit' event for that field that queries the table in question for that value...
if it exists, display a message box and clear the field or something.

 

[Rohdem]

Save the record on the 'after update' event of the text box. Access will error if it is a duplicate value. If you need to customize the error message, use

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If (DataErr = 3022) Then
MsgBox "Some Error Message"
Response = acDataErrContinue
End If
End Sub Mike Rohde
rohdem@marshallengines.com

 

[petermeachem]

What you could do is to open a recordset in the beforeupdate event of the text box and search for duplicates. Are you ok on that? If it is a duplicate then set cancel = true. The cursor will be stuck on that field until you type something new. Peter Meachem
peter@accuflight.com