Notifying when entering duplicate records in form

[tmpark]

I posted this in the Access Forms section but didn't get any responses. Hopefully someone here can help.

I've searched the threads and have come up with good advice but I'm having trouble implementing the solution. Any help you can provide is most appreciated.

I have a form, frmPROPERTY, with a field, PROP_ID, from the table tblPROPERTY. I need to display a message whenever someone enters a PROP_ID that is already in a previous record. I found the following script and tried it in After Update but nothing seems to be happening when I enter two of the same Ids.

Private Sub PROP_ID_AfterUpdate()

If DCount("*", "tblPROPERTY", "[PROP_ID]=" & Me.PROP_ID) > 0 Then
MsgBox "This property has apparently been involved in a previous project"
Me.PROP_ID = Null
End If

End Sub

I may be missing something very obvious. PROP_ID is a text field with IDs such as 000-0000 or 000-0000-0000 and no validation rules applied. There are also null values for this field in the table.

Thanks for your help.

I'm also interested in general naming convention for objects and fields if you have an extra second. Thanks.

Trent Park

 

[Lightning]

Open the table in Design view and highlight the Prop_ID field. In the Field Properties grid, set the Indexed property to YES - No Duplicates. Save the Table.

Now, whenever a duplicate value occurs in this field, a system error message will pop up saying that the new record cannot be saved because a duplicate index would be created.

HTH
Lightning

 

[tmpark]

Thanks Lightning. I should clarify that I want to be able to have duplicates in the table only I want a message notifying users of this. Thanks.