How to prompt for duplicate on a form?

[Guest]

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

 

[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
..

 

[Guest]

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

 

[Guest]

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

 

[John Spencer]

Yes, with the name of the control, that this bit of code is checking.

If your are doing this in the after update event of the control, the
name of the control should be in the first line of the sub.

Private Sub SomeControlName_AfterUpdate()


End Sub

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

 

[Guest]

Hi John - Thank you but now I'm getting another error. The error is
Compile Error:
End Select without Select Case

Can you tell me what I am doing wrong or if additional info is needed in the
code? Here is the full text of what is entered:

Private Sub SUPPLIER_ID_AfterUpdate()
Select Case DCount("Supplier_id", "Vendor Setup Form New")
Case 0
'Not in use so no message needed
Case Is > 1
MsgBox "Value Already exists"
Case 1
If Me.NewRecord Then
MsgBox "Value already exists"
ElseIf Me.SUPPLIER_ID.OldValue <> Me.SUPPLIER_ID.Value Then
MsgBox "Value already exists"
End Select

End Sub

Your expertise is greatly appreciated!
DD

 

[John Spencer]

Private Sub SUPPLIER_ID_AfterUpdate()
Select Case DCount("Supplier_id", "Vendor Setup Form New")
Case 0
'Not in use so no message needed
Case Is > 1
MsgBox "Value Already exists"
Case 1
If Me.NewRecord Then
MsgBox "Value already exists"
ElseIf Me.SUPPLIER_ID.OldValue <> Me.SUPPLIER_ID.Value Then
MsgBox "Value already exists"
End If '<<<<<< THis was missing

End Select

End Sub

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

 

[Guest]

Thanks again John but now it doesn't matter if I have a dupe in the field or
not the "Value Already Exists" message box pops up as soon as the field is
updated.

What could be causing this. Sorry I'm really a novice at this and a little
clueless as to coding.

Thanks again.

 

[John Spencer]

THe problem is the DCount expression. It is returning a count of ALL the
Supplier_ID values in the table. It is not being limited to just the
current value.

DCount("Supplier_id", "Vendor Setup Form New","Supplier_ID=" &
Me.Supplier_ID)

If Supplier_ID is not a number field, but a text field, then you need to
surround the value in the control with quote marks. You can use one of the
following variants
DCount("Supplier_id", "Vendor Setup Form New","Supplier_ID=""" &
Me.Supplier_ID & """")

DCount("Supplier_id", "Vendor Setup Form New","Supplier_ID=" & Chr(34) &
Me.Supplier_ID & Chr(34))

Private Sub SUPPLIER_ID_AfterUpdate()
Select Case DCount("Supplier_id", "Vendor Setup Form New", "Supplier_ID=" &
Me.Supplier_ID)
Case 0
'Not in use so no message needed
Case Is > 1
MsgBox "Value Already exists"
Case 1
If Me.NewRecord Then
MsgBox "Value already exists"
ElseIf Me.SUPPLIER_ID.OldValue <> Me.SUPPLIER_ID.Value Then
MsgBox "Value already exists"
End If '<<<<<< THis was missing

End Select

End Sub

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

 

[Guest]

Please help - below is the coding I've entered and I'm still getting the
MsgBox "Value Already exists" on every entry vs just matching the current
entry if it is a duplicate. What needs to be corrected? Thanks again.

Private Sub SUPPLIER_ID_AfterUpdate()
Select Case DCount("Supplier_id", "Vendor Setup Table", "Supplier_ID=""" &
Me.SUPPLIER_ID & """")

Case 0
'Not in use so no message needed
Case Is > 1
MsgBox "Value Already exists"
Case 1
If Me.NewRecord Then
MsgBox "Value already exists"
ElseIf Me.SUPPLIER_ID.OldValue <> Me.SUPPLIER_ID.Value Then
MsgBox "Value already exists"
End If '

End Select

End Sub
---------------------------------------------------------------------------