Prevent duplicate records

SteveL13
SteveL13 used Ask the Experts™
on
Am expert had helped me with the following code to prevent duplicate entries in a table and I suppose it works but if a form is opened to review a current record and then the form is closed they get a message indicating that the record already exists.  I have this code in the onclose event of the form.

How can I fix this problem?  We definitely don't want duplicate records but we do want to be able to review an existing record.

Here's the code:

    If DCount("*", "tblProducts", "ProdName & SizeID & FormatID & LocationID = " & Chr(34) & Me.cboProdName & Me.cboSizeID & Me.cboFormatID & Me.cboLocationID & Chr(34)) > 0 Then
        MsgBox "This product already exists.  Please correct your entry."
        Me.cmdCloseForm.SetFocus
        Cancel = True
    End If

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try moving the code to the Before Update event of the form... which only runs if the record has changed.
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
Alternatively, since you are using a custom close button... move the code to the Click Event of cmdClose, but add a check to only run the code if the form is dirty:

If Me.Dirty = True then
    If DCount("*", "tblProducts", "ProdName & SizeID & FormatID & LocationID = " & Chr(34) & Me.cboProdName & Me.cboSizeID & Me.cboFormatID & Me.cboLocationID & Chr(34)) > 0 Then
        MsgBox "This product already exists.  Please correct your entry."
        Me.cmdCloseForm.SetFocus
        'Cancel = True  <<--- needed in the Before Update event, not in a command button click event
    End If
End if

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial