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"
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).
Duplicate Test Process
if (results from Duplicate Test Process) > 1 then
msgbox(“Duplicate records in General Flying and Ownership Expenses”)
How to do this? Should I be using a query at all? I tried using the MS Access help and found it baffling.
1. Deleting Duplicate records using VBA
2. filter a table record using VBA?
3. Find and replace using VBA?
4. Find Duplicate Records from TWO different tables
5. Find duplicate records
6. Select record using VBA
7. Find and Delete (all but 1) Duplicate Records from Table with VBA
8. Remove duplicate records without using append query and make a table
9. How to find duplicated records?
10. Find duplicates, and remove BOTH records?
11. Query to find duplicate records
12. Find and Select Continuous Form record using VBA
13. Trying to find a specific Record using VBA
14. Deleting Records using VBA Module
15. GoTo last record in Table rather than last record in Form using VBA
16. Link PDF file to a record using vba
17. Import Excel File into exisiting table... ignoring duplicate records
18. How to find duplicate records in a table?
19. pairing duplicate records in a table
I have inherited a database with a table with plenty of duplicate records. In some cases the a record may appear more than
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
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.
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.
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.
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.
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]
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];
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
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
Me.Bookmark = rsc.Bookmark
Set rsc = Nothing
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.
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
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!
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.