How to Check for Duplicate entry?

[ITProg]

I have a combobox on my form that allows you to select a name. When I add a new record, I would like to check the name to make sure a record does not already exist in the table. I think I would use the before update property, but I'm not sure how to write the code to check if the name exists.

 

[p27br]

you could add a unique index on the field you want to be unique.

 

[ITProg]

If add a unique index on that field, the other text boxes on my form will not fill with the corresponding data for that person.

 

[ITProg]

I would like to check for a duplicate entry on the beforeupdate field of the combobox. If I set the unique index, I can complete data entry on the form, then try to move on before it tells me that I have a duplicate record. Also, if I set a unique index, it will not fill in some name text boxes that I have at the top of the form unless I specify in the afterupdate event the values to fill in those text boxes.

 

[PHV]

Take 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 FAQ222-2244

 

[ITProg]

I have the following DLookup function:

DLookUp("[CNO]","tblReviews",[Forms]![frmReviews]![cboCNO].[column](0))

I put it in a macro. I want the before update field of my combobox to check for an existing CNO in the tblReviews before any data entry to add a record. The Dlookup is supposed to compare the CNO which is in column 0 of the combobox to the CNO field in tblReviews. The macro has the DLookUp function as the condition. The action is to display a message box, then I would like to cancel the event. When I run try to add a record and select the combobox, my msg box appears whether the record is a duplicate or not. Then it keeps appearing whenever I hit any control on the form. I have to end task to get it to stop. Any suggestions?

 

[PHV]

DLookUp("[CNO]","tblReviews","[CNO]='" &[Forms]![frmReviews]![cboCNO].[column](0) & "'")
If CNO 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 FAQ222-2244

 

[ITProg]

The extra &"" at the end of my DLookup statement fixed my msgbox problem. But, the message box does not appear at all. I have changed the condition of the DLookUp to Is Null, but that did not work either. I also get a validation error when the information goes into my bound text boxes at the top of my form.