Link to home
Create AccountLog in
Avatar of Marilyn Messineo
Marilyn MessineoFlag for United States of America

asked on

Check for duplicates in Microsoft Access VBA

I import a CSV file into a temporary table ("Import") in VBA.  The file may contain duplicate records (product number), I don't want to import the duplicate records I want to display a message showing for the user to select 1 of the duplicate records to keep (fields to display:  Account Name, Account Number, Opportunity Id, Product Number, price, quantity, total).  The record they do not choose should be deleted and the process should continue

Here is my code for the import:

Public Function Import_Quote_Test()
Dim strFilter As String
Dim strImportFileName As String
Dim DB As DAO.Database
Dim strSQL As String
Dim strQuoteNo As String
Dim rstImport As DAO.Recordset
Dim strResponse As String
Dim ctl As Control

On Error GoTo Import_Quote_Test_Err

Set DB = CurrentDb

'Prompt user to select Import File

strFilter = ahtAddFilterItem(strFilter, "Import Files", "*.csv")
strCreateFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an import file...", _
                Flags:=ahtOFN_HIDEREADONLY)

'Make sure the Import File Name is not blank
If strCreateFileName = "" Then
    MsgBox ("Import file not selected, please try again.")
    GoTo Import_Quote_Test_Exit
End If

DoCmd.OpenQuery "qdel_Create_Quote_Import_Temp"
DoCmd.OpenQuery "qdel_Create_Quote_Import"

DoCmd.TransferText , TableName:="Import", FileName:=strCreateFileName, HasFieldNames:=True

I WANT TO ADD THE DUPLICATE LOGIC HERE BEFORE THE QUERY BELOW RUNS

DoCmd.OpenQuery "qapp_create_quote_import_Temp"
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

create a duplicates query using the Query Wizard

CREATE ribbon then Query Wizard

A human will need to look at the results.
Avatar of Marilyn Messineo

ASKER

I want the duplicate records to display in a pop-up window where the user can check one of the duplicates to keep.
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account