?
Solved

Prevent duplicate records

Posted on 2014-02-25
2
Medium Priority
?
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

752 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