Duplicate HelperData - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Duplicate HelperData

John Rutter

2 years ago

I am trying to prevent the users from putting a duplicate into the HelperData field on the HelperF form.  I have watched the duplicate TechHelp Extended which uses the DCOUNT function, this utilized 2 IDs to look for a duplicate.  I only have 1 ID (HelperTypeID) and so trying to use the text value put into a new record field by the user.  Is there a way of doing this to trigger the count to a 1 and then prevent the duplicate?

I have tried the following:
    ' Define the WHERE condition based on HelperID and HelperData
    criteria = "HelperID = " & HelperIDValue & " AND HelperData = '" & HelperDataValue & "'"
    C = DCount("*", "HelperT", criteria)
    If C > 0 Then
        'Entry already exists in the table

But criteria returns a Null.

Any help would be welcomed.

John Campbell @Reply

2 years ago

You may want to put a message box in after your criteria. If it is returning a null or nothing, then your dcount won't work. I use the below to check for dup names. Put all of this on the afterupdate of the field in doubt, in this case the 2nd field.

DuplicateChecker = DCount("[CustomerID]", "[Tbl_Customers]", "[CustomerFirstName]='" & Forms![FrmWorkOrdersbyCust]![CustomerFirstName] & "' AND [CustomerLastName] ='" & Forms![FrmWorkOrdersbyCust]![CustomerLastName] & "'").

If DuplicateChecker >= 1 Then

DuplicateResponse = MsgBox("There is a " & [CustomerFirstName] & " " & [CustomerLastName] & "" & " already in the database. Do you want to cancel what you've done and try looking them up first? Select Yes to remove the current customer you are adding. Select No to continue adding the current customer.", 36, "Duplicate")

	Richard Rost @Reply 2 years ago
An easier method might be to set up a Composite Key.

	John Rutter OP @Reply 2 years ago
Hi John Thank you for this, all working now.

John Rutter OP @Reply

2 years ago

Hi Rich
Thanks for the help, I tried a composite key first, but this wanted 2 IDs. If a duplicate was entered, the HelperID was an Autonumber and so generated a unique number and wasn't seen as a duplicate. I couldn't see away of using the HelperData value to check for the duplicate in the composite key wizard.
Also as mentioned in your extended cut I was able to use a custom msgbox to tell the user they had entered a duplicate and then delete the duplicate entry.
Kind regards John

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View