Finding duplicate records using VBA

[Carvel]

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"


SELECT

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

AS CountOfType

FROM [General Flying & Ownership Expenses]

GROUP BY

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

HAVING

(((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).



Pseudo-Code

Sub Duplicate_Tester

Duplicate Test Process
if (results from Duplicate Test Process) > 1 then
msgbox(“Duplicate records in General Flying and Ownership Expenses”)
End
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

 

[john471]

Did you consider cleaning up your data just the once, then adding a unique index to the table, comprised of the two fields in question, such that it is impossible to add further "duplicate" entries ?

 

[Carvel]

Hi John,

Thanks for your response. The table in question is populated by data entered by users. They can either enter the data via a form, or they can import the data from Excel or from another data source. Because of this, there needs to be a check to look for duplicates, as this has caused problems in the past.

Thanks again.

- Kevin

 

[john471]

Kevin,

I think you missed the whole point. If you add an appropriate unique index to the table, it won't matter how they try to get the records in there, attempted additions or changes that violate the index will be rejected. Saves you having to do regular checks and cleanups.

Otherwise, what if bad data gets in there and someone pulls a report on it before the next cleanup is run ???