How to prompt for duplicate on a form?

D

DMarieD

Hi -

I have a form linked to a table that has 6 specific fields that I do not
allow duplicates. Is there a way to have the form field tell me when a
dulpicate entry is made before the record is complete and submitted?

Right now it lets me complete the form then says the record can't be added
because of a duplicate then I have to search each field to see where the
duplicate data is.

Thanks,
DD
 
J

John Spencer

Is the duplicate any one field or is it the combination of all six fields?

If it is one field, you can use the after update event of eachcontrol to
determine if the value is a duplicate of an existing value.

You would need code something like the following for each control's after
update event

Select Case DCount("TheFieldName","TheTableName")
Case 0
'Not in use so no message needed
Case > 1
MsgBox "Value Already exists"
Case 1
IF Me.NewRecord then
MsgBox "Value already exists"
ElseIf Me.TheControl.Oldvalue <> Me.TheControl.Value then
MsgBox "Value already exists"
End Select


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
D

DMarieD

No not dupe on all - any one can be a dupe. So for clarification: in each of
the "Field Properties" I would copy and paste your code in the Event
Procedure on After Update and just replace ("TheFieldName","TheTableName")
with my info. Correct?

Thanks,
DD
 
D

DMarieD

Hi,

Can anyone help? I copied and pasted the code below and I keep getting an
error on this line:
ElseIf Me.TheControl.Oldvalue <> Me.TheControl.Value then

It highlights ".TheControl"
The error box says -
Compile error:
Method or data member not found

Am I supposed to replace "TheControl" with something?
Thanks,
DD
 
Top