Questio on beforeupdate event to prevent duplicate record

Posted on 2009-12-30
Last Modified: 2012-05-08
I have a form that has 5 data fields: field 1, ... 5 (the form is bound to an external table). I added a module to prevent duplicate records being saved to database by checking three critical fields (field 1,...3) at UI level, i.e.:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim updtRecs As Recordset

sql = "SELECT field1, field2, field3 FROM table ... WHERE ..."

Set updtRecs = CurrentDb().OpenRecordset(sql)
  If updtRecs.RecordCount <> 0 Then
    MsgBox "Warning: Possible duplicate entry."
    Cancel = True
  End If

This works good for new records that contain changes on the three critical fields (feild 1, 2, 3). The problem is when users made change on another non-critical fields (field 4, field 5) on current existent record, the error message will still popup becuase updtRecs.RecordCount is not 0 in this case (since those three critical fields are not changed and is still in current table).

Any good quick fix on this issue so that when users edit and save current non-critical fields on existent records on the form, the validation will be skipped so that there is no such warning (of course, for the three critical fields, the validation is still there)?

I got advice that I could put the code in the before update events of the three fields instead of the form before update event. Is this the best solution? the problem is I have a long duplicate checking module (open and close a recordset, etc.), do I need to call this module everytime a critical field is changed? Thanks for the advice.
Question by:heyday2004
    LVL 77

    Accepted Solution

    For just 3 known fields you could do..

    if me.field1<> me.field1.oldvalue or me.field2<> me.field2.oldvalue or me.field3<> me.field3.oldvalue then
    ' check for duplicates here

    ' don't bother
    end if
    LVL 77

    Assisted Solution

    That would be in the form_beforeupdate event procedure.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    761 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

    7 Experts available now in Live!

    Get 1:1 Help Now