Prevent Duplicates in a field

K

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
 
J

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
Click to expand...

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]
 
Top