Finding duplicate records using VBA

Hi All,

I am new to MS Access VBA, but not to VBA or to MS Access.

Thanks in advance for whatever help you can give me.

I am trying to verify whether or not duplicate records exist in a table (General Flying & Ownership Expenses) using VBA. What is the best way to proceed? I designed a query that counts duplicate records (see below). A duplicate record would be two records having the same "Type" and the same "Year"


[General Flying & Ownership Expenses].Year,
[General Flying & Ownership Expenses].Type,
Count([General Flying & Ownership Expenses].Type)

AS CountOfType

FROM [General Flying & Ownership Expenses]


[General Flying & Ownership Expenses].Year,
[General Flying & Ownership Expenses].Type


(((Count([General Flying & Ownership Expenses].Type))>1));

What I want is if the results of this query for CountOfType are greater than 1 (i.e. there are duplicates), a message box will pop up indicating the table where the error is coming from and for the Sub to halt (see pseudo-code below).


Sub Duplicate_Tester

Duplicate Test Process
if (results from Duplicate Test Process) > 1 then
msgbox(“Duplicate records in General Flying and Ownership Expenses”)
end if

End Sub

How to do this? Should I be using a query at all? I tried using the MS Access help and found it baffling.

Thank you!

- Kevin

Post your answer or comment

comments powered by Disqus
Hi all,

I have inherited a database with a table with plenty of duplicate records. In some cases the a record may appear more than once.

I have kindly been given the following code but somehow I cannot get it to work. I am getting a runcode error.

In my example database I have three fields a, b and c
and I have duplicate records:-

Please note I am using A2003....I have attached word doc with code for anyone's perusal.

Just amended my post to include my sample database. I am also providing, again, some background to my problem:-

I've inherited this database with a table which has accidentally been populated with records that already exist.

I have set up a sample database that more or less mimics the real one. I have omitted PKs and other database rules because that is what I have. I "simply" require a coherent piece of VBA code that will save me the trouble of sorting the records and then deleting the duplicate records that I see.

Obviously I would be working on a copy to start of with so that I would first understand the code before I embark on the real thing.

I completely understand the concerns that maybe the database is structured improperly or whether additional fields have to be included, but my main concern is deleting the duplicates.

The code I am supplying has been kindly offered to me but is not working. I believe its almost there, and I am sure it just requires some tweaking.

Basically what I need is code which will sort the table and then compare the entire contents of one record with the one below it. If it is the same, ie. all the fields are the same, then one of the duplicates should be deleted.

Thanks and any advice on the code grately appreciated.

Hi! Anyone here knows how to filter a table record using VBA?

For example, I have a list of customers. I want to filter them to those living in the East only. How do i do it using VBA code?

Please reply.. Really need help here. Thanks!

Is it possible to do a find and replace using VBA or SQL.
I would like to be able to replace fields (the attendee's name) in my attendance_table.

Each attendee's name appears multiple times in the attendance_table, so I am thinking that a loop would be the answer but I do not how to do a loop.

For example:

If a person changes their name (by marriage for example), I would like to be able to find all instances where the previous name appears in the attendance table and replace it with their new married name.

I hope this is possible.

Thank you for your time.

IS there a way to find Duplicate Records from two different tables? The Duplicate Query Wizard only allows for one table. I have a talbe of "Users" and a table of "Users who got new Laptops" I want to compare the two tables and find duplicate records so that I can find out if anyone on my "User" list has received a new Laptop.

Thank You,

What is the best way to find duplicate records in a table with 25 columns!?

I know there is a built in find duplicate query but it only allows a max of 10 columns to be compared.

Edit: I actually want to find duplicates records between two tables both the same layout and columns

Any help welcome
Kind regards,


Sorry if this thread gets posted twice, there was an error when I first tried to post it.


Is there some way I can select a record using VBA, as if I clicked on the record selector at the left side of a form? (the record selector is marked black)


I want to find duplicate records of one field in a table and delete all but one of the records using VBA.

I was thinking of using: CurrentDb.Execute "DELETE *
but I'm not sure how to put what I want after that.

I did use the Query Wizard and was able to get the duplicate SQL needed:

SELECT First(tblUserID.[UserID]) AS [UserID Field], Count(tblUserID.[UserID]) AS NumberOfDups
FROM tblUserID
GROUP BY tblUserID.[UserID]
HAVING (((Count(tblUserID.[UserID]))>1));

Any help would be much appreciated! Thanks.

I would like to remove all duplicate records from a table I have setup. I have created a find duplicates query using the wizard which works great. What would I put in for a statement to add to the query that would delete the duplicates. It doesn't matter which record gets deleted as long as there's no duplicate. Here is my setup:

My table name is "BCES" and the field I will be using to remove duplicates from is called "BC"


Hi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.

Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb

ID Trading Name Location No Location Street Location Suburb
41350 Arabica Café Grey St
263 Arabica Cafe` 1 GREY STREET
41294 Arashi Japanese Resturant 41 Courtenay PL
150 Arashi Kushi Yaki Bar 41 COURTENARY PLACE

From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.

SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];

Hi there,

I used Access quite a lot a few years ago, but as I no longer use it I am now very rusty, so I have a question which I hope somebody can help with!

Basically I have a table with one field called 'email'. I'd like to create a query which finds duplicate records (there could be two records, or there may be more) and then deletes both/all of them, so that there is no trace of that email address left anywhere in the table at the end.

I would be very grateful if somebody could help me out with this, and meanwhile send thanks for your time.


I have imported an excel table into access succcefully. The records contain the following fields: NIIN, FSC, SD, NET QTY, NRTS QTY, Credits, Net, and Noun. Each record has a very unique NIIN. I have written a query to find all duplicate NIIN records and sum up fields NET QTY, NRTS QTY, Credits, Net respectively then append the summed up records to a different table. The query works fine at first.

After analyzing the results, I noticed that there are some NIIN records in which all the data is a duplicate except for the Noun field. Based on the NIIN, the Noun fields should be identical, but due to typos when entered into excel (data received from external source), they are not and thus those records arenot considered duplicates. In reality they are duplicate records.

Is there a way, by using a query, to locate duplicate records by the NIIN field (but not include the Noun field), sum up fields NET QTY, NRTS QTY, Credits, Net and append the summed up record to a new table that does includes the Noun field?

I have a continuous form, and I would like to programatically find and select one of the records with VBA if it meets certain conditions. How would I do this? Basically trying to control the record selector arrow with VBA.

If the form I am using already has a record using a specific number, I want the user to be aware before they enter all the info. I found this code and it works except when it redirects me to the existing record, it doesn't. It redirects me to record #1. The field booking number is not a PK or AutoNumber field. It is a number field and as you can imagine, each is very specific to each person. Using that number as the PK field just won't work for this application. What I would like to accomplish is to be redirected to an existing booking number if it exists, otherwise the user will just fill in the form as needed. Here's what I have"

[Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.BookingNumber.Value
stLinkCriteria = [BookingNumber] = " & " '" & SID & "'"
'Check tblClient table for duplicate BookingNumber
If DCount("*", "tblClientInfo", "[BookingNumber]=" & Me.BookingNumber) > 0 Then
'Undo duplicate entry
'Message box warning of duplication
MsgBox "Warning Booking Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Booking Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub]

Any help will be greatly appreciated

What is the recommended method for deleting records using a VBA module? Currently I am using an SQL method that specifies the table name and the upper time stamp date from which values should be deleted.

My database is continually being updated with new files from this same VBA module. I would like to delete old records based on a rolling window (say 30 days). Would this be the best way to go? It seems a bit slow...but then again my database contains 1 million + records.


With my form open, I want a button that when clicked will create a new record using VBA and save it in my table. Then I want it to GoTo the record (or last record in the table) in the current form.

The following code does all of the above, EXCEPT it goes to the last record of the form, rather than the last record of the table.

Here's my code.

Dim dbTransportationPlanning As DAO.Database
Dim rstCurrent_Shipments As DAO.Recordset
Dim Shipment_num As String
Set dbTransportationPlanning = CurrentDb
Set rstCurrent_Shipments = dbTransportationPlanning.OpenRecordset("Current_Sh ipments")
rstCurrent_Shipments("Product").Value = "ONE"
rstCurrent_Shipments("TNProject").Value = "NEW"
rstCurrent_Shipments("Shipment_Num").Value = "NEW"
rstCurrent_Shipments("Departure_Date").Value = "1/1/1900"
rstCurrent_Shipments("Shipper").Value = "NEW."
rstCurrent_Shipments("Consignee").Value = "NEW"
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acLast
DoCmd.OpenQuery ("Sharepoint_Append")
DoCmd.SetWarnings True

Hi all,
I have this database to keep track of our company's licenses. It works fine. We have about 250 records, each record contains licensee's info and also has an ole field linked to a folder that gets updated directly from the form.

All the linked files are pdf files.

Whenever we have a new license or one that needs to get updated we do (right click ----Insert Object ---etc).

The problem I am having is..

If the user is updating a license or replacing an expired one with a new one, the user needs to access the folder, move the expired License to a new folder and name the new license so it gets linked to the database.

Is there a way to do this process using vba like a button to update or replace the existing pdf file.

the linked files are under a folder like this:

R:operLicensesFolder(usualy a state name)Filename.pdf

Thanks in advance

I am creating a small Access application that will allow me to update my Call handling system's site table.
It uses an mdb file, and I wish to add new sites (from new contracts) to the SCSite table.

I recieve regular updates of new sites from existing customers, so want to create a simple macro that will

1: import (from an excel file) the new sites and add these to the existing SCSite table
2: Check for duplicate records using the Site_Num primary key
3: Merge non duplicate records into existing SCSite table.

Is this possible, and if so - how? VBA? Macro builder? Query?

A Swift response would be great, I have had a look through previous posts - but to no avail!

I have a table on which I cannot set a primary key because there are duplicate records in the table. How can I use a query to show the records that are duplicated?


Need help and know no to very little code.

Have a table DONORS that has assigned Soundex for finding possible duplicate donors. The fields I need to compare are Soundex,Lname.Fname,DOB,DonorId so that when I create a report from the query I can group possible duplicates in pairs to be researched to determine if they are actual duplicates are not.
I tried using the find duplicate records wizard to no avail.

I think I should be looking at using an Alias for the Donors table when the designing query. Need something like If Soundex and Lname and or DOB are same then pair these two where DonorId is not =.

Will be willing to e-mail table to anyone that might be able to help.



Hi, basically what i want to do is append a record to a table using vba. I would like to take two values from my form (productID, supplierID) and insert them into a table (supplierProductsTBL) under the same headings. I've constructed an sql statement but am getting the following error:

runtime error '3346'

number of query values and destination fields are not the same'

I think this happens because the table also contains an autonumber field (supplierProductID) and i'm not referencing this in my sql statement (below):

mySQL = "INSERT INTO SupplierProductsTBL ( SupplierID, ProductID ) VALUES ('" & SupplierID & "'), ('" & ProductID & "')"

i wuld just get rid of the autonumber field but i need this.

Any help on this would be appreciated, cheers mark.


I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.


Dave, England


I am relatively new to using VBA in Access an would appreciated a little help!

I am trying to add a new record via VBA. Actually a series of records. I want the new records to be partially auto-completed by the information on the existing form and subform. The goal is to take the number of weeks in a school year and set up the record when the child is enrolled in the program on the History Report. I want it to loop until the number of weeks in the program equals the number of records generated. My Problem is the number of weeks in a program is stored in another table. I keep hitting a wall trying to pull that information. Below is my latest attempt. I added a subform with the data I needed. Now I am trying to call the data from the subform to fill new records. There are several fields not auto-completed that will be throughout the program.

Any help would be appreciated! I hope this makes sense my brain is fried. Thanks!

Private Sub SetPaymentHistory_Click()

Dim Week
Dim Counter
Dim WeekID

WeekID = 10
Counter = 0

Do Until WeekID = Counter
Set rs = CurrentDb.OpenRecordset("Select * From PaymentHistory")
Week = Counter + 1

With rs
![ChildID] = [Forms]![SessionEnrollmentForm]![ChildID]
![SessionID] = [Forms]![SessionEnrollmentForm]![SessionID]
![ProgramID] = [Forms]![SessionEnrollmentForm]![Program Type]
![WeekID] = [subForms]![SessionDefine-subform]![WeekID] this was my idea but I keep getting an error message here.
![Cost of Program] = [Forms]![SessionEnrollmentForm]![Payment Amount]
![Sliding Scale] = [Forms]![SessionEnrollmentForm]![Sliding Scale]
![Sliding Amount] = [Forms]![SessionEnrollmentForm]![Sliding Amount]
![CCDF] = [Forms]![SessionEnrollmentForm]![CCDF]
![CCDF amount] = [Forms]![SessionEnrollmentForm]![CCDF amount]
End With

Counter = Week


Set rs = Nothing

DoCmd.OpenForm "PaymentHistoryForm", acFormDS, , "ChildID=" & Me.ChildID

End Sub


I am trying to write a procedure that will loop through the records in a table and compare them on one field to check for duplicates. If they are duplicates, I need the value from the Quantity fields for the two adjacent records (the table is sorted) to be added and assigned to the first record. Then, I would like to have the code delete the second (duplicate) record. For some reason, when I invoke rst.MoveFirst, it doesn't go to the first record in the table (I checked this by doing an assignment statement to try to modify the Quantity field in the first record). Here's what I've got so far:

Private Sub CombineDups()
Dim prevRecVal As String
Dim thisRecVal As String
Dim Quantity As String

Dim dbs As Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Shopping List", dbOpenTable)
With rst
End With

thisRecVal = rst![Ingredient Num]

Do Until rst.EOF
prevRecVal = thisRecVal
thisRecVal = rst![Ingredient Num]
Quantity = rst!Quantity
If thisRecVal = prevRecVal Then
With rst
!Quantity = rst!Quantity + Quantity
End With
End If

With rst
End With
End Sub

I am stumped. Can anyone please help me out?

Hello People.
I need some help coding with an application.

I have a table that includes forecast of different parameters for different products. So when ever new forecast data is added the already existing data for same product and parameter is not deleted, but there is a field called revision, so old data record is marked as 0 and new is marked as 1 to indicated the revision 1 indicates new forecast. It can go like 2, 3....

What I want is to comapre the latest records for different products and their parameters from the table.

So I can do a duplicate record query, that will give me all the duplicates for certain product and parameter. The other records remaining in the table now are only single, so they can be latest. And from the duplicate query I need to take highest revision no. for the latest record.

After that I need to merge all those, highest revision no. records from duplicate query and the single records (The records in the table other than the duplicate query records)

Need some logical and code help with this.

but my data is kind of strange.

The result merged table...

Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name

M1_p BI time .1 .2 .4 .6 Jim rev0
M1_p test time .2 .5 .3 .7 Ted rev0
m2_p Oven pop .3 .4 .3 .5 Mary rev0
M1_p BI time .4 .1 .1 .6 Jim rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1

Here rev 0 is old forecast for some product and a particular parameter, if there comes new forecast for same combination then it will be rev 1. It can be rev 2, rev 3 for different product or parameters.

So What I want is to generate a latest report out of this.

if I run duplicate query on product name and parameter name both then I will get a table with 1 ,3 ,4 ,5 records from previous table.

Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name

M1_p BI time .1 .2 .4 .6 Jim rev0
m2_p Oven pop .3 .4 .3 .5 Mary rev0
M1_p BI time .4 .1 .1 .6 Jim rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1

From this table I need to select latest records based on revision field. Means for product name and parameter name combination I need to check for highest rev no. in that group and take that record out. And the remaining 2 record that was in original table but it was not having any update so it should be there in final report. Like this..

Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name
M1_p test time .2 .5 .3 .7 Ted rev0
M1_p BI time .4 .1 .1 .6 J im rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1

Please help with this.

Not finding an answer? Try a Google search.