validation rule for not allowing duplicate entries

[guggd2868]

Hi yall

I am trying to set a form to enter data into a table, but I want to be
able to set up a validation rule that will stop users if they enter in
a value that has already been input. The form is for putting in
document ID numbers and names. Right now, if I enter a doc number
that already exists, Access just ignores the entire entry and doesn't
add anything to the table. Obviously, this is a problem. Is there a
way to add a validation rule that refers back to other entries in the
same field?

Thanks

D Gugg

 

[guggd2868]

Forgot to mention, I'm working in Access 03.

Thanks

D Gugg

 

[Klatuu]

Use the form's Before Update event and check the table using a DLookup to
see if the value already exists in the field:

If Not IsNull(DLookup("DocumentID", "DocumentTable", "DocumentID = " &
Me.txtDocumentID)) Then
MsgBox "Document Number " & Me.txtDocumentID & " Already Exists
Cancel = True
Me.txtDocumentID.Undo
End If

The above code assumes DocumentID is a numeric field in your table.
Change the names to use your actual names.

 

[tedmi]

Look up the BeforeUpdate Event (not proerty) in VB help. The example there
does exactly what you need. Use the event of the form, not any of the fields
on it.