preventing duplicate entry - custom message

[crisis2007]

I am trying to find anything that shows how to create a custom message warning when a duplicate entry is being entered on a form. My form is for data entry, based on a query that fills a table. I have looked and it appears that the correct place to start is on the BeforeUpdate of the control. But I have tried and found nothing that has worked yet. I am still using MS Access 97. The table field is indexed (No Duplicates) and is not a key. I am pretty new at learning code but can get through something often by trial and error. Thanks!

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[crisis2007]

Thanks for responding - I think I know what you are trying to direct me to. I am just a little too new to writing code to get started. Can you give an example?

 

[PHV]

A starting point, in the BeforeUpdate event procedure of the control:
If Not IsNull(DLookUp("[field name]", "[table name]", "[field name]='" & Me![control name] & "'")) Then
MsgBox "custom message"
Cancel = True
Exit Sub
End If

If the field is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[crisis2007]

Thank You! You pointed me in the right direction. It took a little bit but I figured it out. I also learned another lesson in correctly naming controls to reduce my confusion. I appreciate your help!