Compare two recordsets to check for duplicates
I am trying to basically compare two types of information. I import about 10 tables that need to be appended to a master table. but before I can append these tables to the master table, I need to check the individual tables to see if they have already been appended to the master table. (the fields i use to check are customer name and period end date...i added customer name because for example for april, 10 customers will have the period end date of 4/30/04) If the tables have already been appended, I want the message box to say 'already in the database'. If the tables have NOT been appended, then I go to the AppendImports module to run that. I am using Access 97.
Thanks in advance for your help.
Below is the code:
Dim db As Database
Set db = CurrentDb()
Dim rst1, rst2 As recordset
Set rst1 = db.OpenRecordset("tblDateCheck")
Set rst2 = db.OpenRecordset("tblFilePath1")
Do Until rst2.EOF()
If rst1.Fields(0).Value = rst2.Fields(3).Value & ".Period_End_Date" _
And rst1.Fields(1).Value = rst2.Fields(3).Value & ".Company_Number" Then
MsgBox ("The requested data is already in the database")
DoCmd.OpenQuery "Append Imports", acViewNormal