Howdy. :)

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.

PLEASE HELP!!

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")

rst2.MoveFirst

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")
        rst2.MoveNext
    Else
    End If
Loop
DoCmd.OpenQuery "Append Imports", acViewNormal
End Function

Assuming that all rows get inserted into the table each time you
successfully run an append query, you should be able to check to
see if the first row of the import table exists in the master table.

For a "first row" check only, here's what you can do:

Set rst1 = db.OpenRecordset("tblDateCheck")
Set rst2 = db.OpenRecordset("tblFilePath1")

strCriteria = "[customer name] = & rst2("customer name") & _
              " AND [period end date] = #" & rst2("period end date") & "#"

rst1.FindFirst strCriteria

If rst1.NoMatch = False Then
   MsgBox ("The requested data is already in the database")
End If

rst1.Close
rst2.Close

etc ...


In this case, using a "first row only" check, there is no need to
loop through the table. However, if you want to see if ANY of the
rows from the import table have already been inserted into the
master table, try this:


Set rst1 = db.OpenRecordset("tblDateCheck")
Set rst2 = db.OpenRecordset("tblFilePath1")

rst2.MoveFirst

lngTotalRowsExist = 0
lngTotalsRowsMatched = 0

Do Until rst2.EOF

   lngTotalRowsExist = lngTotalRowsExist + 1

   strCriteria = "[customer name] = & rst2("customer name") & _
                 " AND [period end date] = #" & rst2("period end date") & "#"

   rst1.FindFirst strCriteria

   If rst1.NoMatch = False Then
      lngTotalRowsMatched = lngTotalRowsMatched + 1
   End If

   rst1.MoveNext

Loop

If lngTotalsRowsMatched > 0
   strMsg = lngTotalRowsMatched & " rows of " & _
            lngTotalRowsExist & " total rows in the " & _
            "import file were matched in the master file"
   MsgBox strMsg

End If

rst1.Close
rst2.Close

Just be sure to declare lngTotalRowsExist and lngTotalsRowsMatched
As Long and strMsg As String


Hope that helps.