Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Questio on beforeupdate event to prevent duplicate record

Posted on 2009-12-30
Medium Priority
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
  • 2
LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 26144016
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

peter57r earned 2000 total points
ID: 26144019
That would be in the form_beforeupdate event procedure.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 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