For loop, method is not available

[EJaques]

I have a number of tables that I'd like to audit. The tables have numeric data, strings, and dates.

Before I call this function a copy of the data in all the fields is written to a temp table called TableAudit, the table I'm comparing with is TableSource.

I can't enumerate the names of these fields because they change depending on which table I am editing.

My problem is in the FOR loop below. I get and error saying that the method is not available for recAudit.fld.Va lue.

I don't know why.

Any help would be greatly appreciated.

Function VerifierChamps(ByVal NomTableSource As String, ByVal NomTableAudit As String, sKeyField As String, lngKeyValue As Long)
    
On Error GoTo errhandler

   Dim db As DAO.Database
   Dim recTableSource As DAO.Recordset 'The table being audited
   Dim recAudit As DAO.Recordset 'The temporary audit table
   Dim fld As DAO.Field
   Dim intCompare As Integer 'Supposed to be different than zero if matches are found
   Dim blnCmp As Boolean
    
   Set db = CurrentDb
  
   Set recTableSource = db.OpenRecordset("SELECT * FROM " & NomTableSource & " WHERE " & sKeyField & " = " & lngKeyValue)
   Set recAudit = db.OpenRecordset("SELECT * FROM " & NomTableAudit & " WHERE " & sKeyField & " = " & lngKeyValue)
   intCompare = 0
   blnCmp = False
  
  'All the fields in the Source Table and in the Audit Table have exactly the same names
   For Each fldS In recTableSource.Fields
    If (fld.Value = recAudit.fld.Value) Then
    
    intCompare = intCompare + 1
    Else
    intCompare = 0
    End If
    
    Next
  
   
    If intCompare <> 0 Then
      blnCmp = True
    Else
      blnCmp = False
    End If
    
    VerifierChamps = blnCmp
   
'If no errors
ExitHere:

   Set fldA = Nothing
   Set fldS = Nothing
   Set tdf = Nothing
   Set db = Nothing
   Set recTableSource = Nothing
   Set recAudit = Nothing

   'Notify the user the process is complete.
   'MsgBox "Record Print Complete"
   Exit Function

errhandler:
   'There is an error return as null

   With Err
    
      MsgBox "Error in VerfierChamps " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "GetFields"
   End With

   Resume ExitHere

End Function

[ADezii]

I know your Tables have exactly the same Field Names, and from what you indicate they have exactly the same number of Records. To compare each Field in every Record for the two Tables, you could do something like this:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim rst_2 As DAO.Recordset
Dim intFldCtr As Integer
Dim fld As DAO.Field
Dim lngRecNum As Long

lngRecNum = 0

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
Set rst_2 = MyDB.OpenRecordset("Table2", dbOpenSnapshot)

With rst
  Do While Not .EOF
   lngRecNum = lngRecNum + 1
    For intFldCtr = 0 To .Fields.Count - 1
      If .Fields(intFldCtr) = rst_2.Fields(intFldCtr) Then
        Debug.Print "Match on Field " & CStr(intFldCtr + 1) & " in Record Number " & CStr(lngRecNum)
      End If
    Next
      .MoveNext
       rst_2.MoveNext
  Loop
End With

rst.Close
rst_2.Close
Set rst = Nothing
Set rst_2 = Nothing

[EJaques]

want to erase this post but can't

[EJaques]

Thank you very much. It seems to be working well.

Just curious. Can you tell me why the method was't available in the FOR loop of the code I posted above ?

It was for the recordset of the TableSource but not for the recordset of the AuditTable.