(Solved) – MS Access 2019: How do I check for duplicate records before update, then run a specific procedure if duplicates are found?


I am very new to both StackOverflow and to doing any sort of advanced programming in MS Access. I created a database to catalog my trading card collection (Excel just wasn’t cutting it since we’re talking about over 2000 unique cards). At first it was just a simple table of records, but now it’s turned into a full-fledged database that I have search forms for and queries and everything.

What I’m trying to do right now is streamline my process a bit, and there is something very specific that I want to make Access do. I’m almost certain that whatever I want to do will have to be done in VBA, and I’m just not familiar enough with it to do what I want.

What I want it to do is this: Any time a new record is entered, I want it to check the record before it saves the record into the DB (I’m fairly confident that I need to use the “Before Update” event for this) and make sure that the “Sort ID” field (an auto-calculated field I’ve created) contains no duplicates (I know I’ll most likely have to use queries for this since auto-calculated fields can’t be indexed). If the program detects a duplicate, I want it to produce a message box saying that I’m trying to enter a duplicate record and ask me if I want to update the “number owned” field of the existing record instead of creating a new one, and then take me to the record in question on an affirmative response.

What I currently have is a validation rule that uses an index (comprised of the fields that generate the Sort ID), which generates a custom error message by using the following VBA code in the “On Error” event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conErrRequiredData = 3022

If DataErr = conErrRequiredData Then
     MsgBox ("Duplicate Sort ID. Please update the 'number owned' field on the existing record instead.")
     Response = acDataErrContinue
     Response = acDataErrDisplay
End If

End Sub

This code works exactly as it should, but I want more than a pop-up error that I can’t do anything with. I have a query entitled “CheckDuplicateSortID” that I created using the Query Wizard, and it checks the “Sort ID” field for duplicates, but that’s as far as I’ve managed to get. The example on This site is about the closest I’ve managed to find to what I’m looking for, but the code sample given is very difficult for me to understand because there’s very little explanation with it; I’m not familiar enough with Access VBA to know which parts are important code and which parts are his specific field names and other variables; I haven’t gotten any error messages because I’m stumped on even trying to figure out what needs to be changed from that sample code and what it needs to be changed to.

Leave a Reply

Your email address will not be published. Required fields are marked *