contact management DB

[kkrev]

In the contact table, I want to be able to put in a unique number identifying a client and have the record come up automatically if it is already inputted. Right now I save it to see if it will be rejected to know if it is already has been inputted. What's an easier way?

 

[JerryDennison]

In the BeforeUpdate event procedure for the control test to see if the value already exists. If so, then display a message and undo the change.

...
Dim vReply As Variant
If Me.ActiveControl = DLookUp("[FieldName]", "TableName", "[FieldName] = " & Me.ActiveControl Then
vReply = MsgBox ("Value Already Exists", vbOKOnly)
DoCmd.CancelEvent
Me.Undo
End If
...

 

[kkrev]

Thanks for commenting, I am a nurse trying to keep track of my patients so this is new for me. where does this command line go in the design? On the table or on the form? and in which property in the design?