Solved

Prevent duplicate records

Posted on 2014-02-25
2
211 Views
Last Modified: 2014-02-25
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

0
Comment
Question by:SteveL13
  • 2
2 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39886663
Try moving the code to the Before Update event of the form... which only runs if the record has changed.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39886678
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

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now