Prevent Duplicates in a field

[Ken]

Hi

(Access 2003) Is there any way to check that the data typed into a field
(e.g. 201-150-500) is not duplicated as I leave the field, not after
exiting after all of the form is filled in as happens now. I am after a
simple message to just say, "Data already exists"
I am not sure how to write VBA code, although I am familiar with properties
and where to look at the variuos VBA codes for each field in a form. 99% of
my database was created with wizards within the database. I have entered
code written by other people before.

Thanks in advance
Ken

 

[John W. Vinson]

Hi

(Access 2003) Is there any way to check that the data typed into a field
(e.g. 201-150-500) is not duplicated as I leave the field, not after
exiting after all of the form is filled in as happens now. I am after a
simple message to just say, "Data already exists"
I am not sure how to write VBA code, although I am familiar with properties
and where to look at the variuos VBA codes for each field in a form. 99% of
my database was created with wizards within the database. I have entered
code written by other people before.

Thanks in advance
Ken

You can indeed check in the control's BeforeUpdate event. You would use code
resembling

Private Sub txtMyTextbox_BeforeUpdate(Cancel as Integer)
If Not IsNull(Me!txtMyTextbox) Then
If Not IsNull(DLookUp("[yourfieldname]", "[yourtablename]", _
"[yourfieldname] = '" & Me!txtMyTextbox & "'") Then
MsgBox "this value already exists", vbOKOnly
Cancel = True
End If
End If
End Sub

You can get fancier - offering the ability to choose between canceling the
field, cancelling the entire record, or jumping to the found value if you
want.

John W. Vinson [MVP]