By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,651 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,651 IT Pros & Developers. It's quick & easy.

For loop, method is not available

EJaques
P: 8
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.Value.

I don't know why.

Any help would be greatly appreciated.

Expand|Select|Wrap|Line Numbers
  1. Function VerifierChamps(ByVal NomTableSource As String, ByVal NomTableAudit As String, sKeyField As String, lngKeyValue As Long)
  2.  
  3. On Error GoTo errhandler
  4.  
  5.    Dim db As DAO.Database
  6.    Dim recTableSource As DAO.Recordset 'The table being audited
  7.    Dim recAudit As DAO.Recordset 'The temporary audit table
  8.    Dim fld As DAO.Field
  9.    Dim intCompare As Integer 'Supposed to be different than zero if matches are found
  10.    Dim blnCmp As Boolean
  11.  
  12.    Set db = CurrentDb
  13.  
  14.    Set recTableSource = db.OpenRecordset("SELECT * FROM " & NomTableSource & " WHERE " & sKeyField & " = " & lngKeyValue)
  15.    Set recAudit = db.OpenRecordset("SELECT * FROM " & NomTableAudit & " WHERE " & sKeyField & " = " & lngKeyValue)
  16.    intCompare = 0
  17.    blnCmp = False
  18.  
  19.   'All the fields in the Source Table and in the Audit Table have exactly the same names
  20.    For Each fldS In recTableSource.Fields
  21.     If (fld.Value = recAudit.fld.Value) Then
  22.  
  23.     intCompare = intCompare + 1
  24.     Else
  25.     intCompare = 0
  26.     End If
  27.  
  28.     Next
  29.  
  30.  
  31.     If intCompare <> 0 Then
  32.       blnCmp = True
  33.     Else
  34.       blnCmp = False
  35.     End If
  36.  
  37.     VerifierChamps = blnCmp
  38.  
  39. 'If no errors
  40. ExitHere:
  41.  
  42.    Set fldA = Nothing
  43.    Set fldS = Nothing
  44.    Set tdf = Nothing
  45.    Set db = Nothing
  46.    Set recTableSource = Nothing
  47.    Set recAudit = Nothing
  48.  
  49.    'Notify the user the process is complete.
  50.    'MsgBox "Record Print Complete"
  51.    Exit Function
  52.  
  53. errhandler:
  54.    'There is an error return as null
  55.  
  56.    With Err
  57.  
  58.       MsgBox "Error in VerfierChamps " & .Number & vbCrLf & .Description, _
  59.             vbOKOnly Or vbCritical, "GetFields"
  60.    End With
  61.  
  62.    Resume ExitHere
  63.  
  64. End Function
  65.  
Mar 3 '10 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,607
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim rst_2 As DAO.Recordset
  4. Dim intFldCtr As Integer
  5. Dim fld As DAO.Field
  6. Dim lngRecNum As Long
  7.  
  8. lngRecNum = 0
  9.  
  10. Set MyDB = CurrentDb
  11. Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
  12. Set rst_2 = MyDB.OpenRecordset("Table2", dbOpenSnapshot)
  13.  
  14. With rst
  15.   Do While Not .EOF
  16.    lngRecNum = lngRecNum + 1
  17.     For intFldCtr = 0 To .Fields.Count - 1
  18.       If .Fields(intFldCtr) = rst_2.Fields(intFldCtr) Then
  19.         Debug.Print "Match on Field " & CStr(intFldCtr + 1) & " in Record Number " & CStr(lngRecNum)
  20.       End If
  21.     Next
  22.       .MoveNext
  23.        rst_2.MoveNext
  24.   Loop
  25. End With
  26.  
  27. rst.Close
  28. rst_2.Close
  29. Set rst = Nothing
  30. Set rst_2 = Nothing
Mar 3 '10 #2

EJaques
P: 8
want to erase this post but can't
Mar 3 '10 #3

EJaques
P: 8
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.
Mar 3 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.