The object doesn't contain the Automation object

[rgkimball]

I am trying to run the following code from a macro but keep getting this error: "The object doesn't contain the Automation object 'Compare2Rows'"

Option Explicit

Public Function Compare2Rows()

Dim rstCurrentData As DAO.Recordset
Dim rstCurrentData2 As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim strTableName As DAO.Database
' sets up loop and time variables

Dim strSQL As String
Dim DT As Double
Dim mtype As String
Dim sid As String
Dim wid As String


    
    'Name if table that you wish to modify

    Set strTableName = CurrentDb
    
    'The field that you wnat to use as a base time
    strSQL = "SELECT * FROM " & "tblMaster" & " ORDER BY CollectT"


    '
    ' Open the first Recordset
    '
    
    Set rstCurrentData = CurrentDb.OpenRecordset("tblMaster", dbOpenSnapshot)
    
    Set rstNew = CurrentDb.OpenRecordset("tblMaster", dbOpenTable)
    

    '
    ' Open the recordset using the Clone Method  of the first one.
    '
    
    Set rstCurrentData2 = rstCurrentData.Clone

    '
    ' Move rst1 pointer to the of the rows to campare
    '
    
    rstCurrentData.MoveFirst


    '
    ' Now set the current record of rst2 = current record of rst1.
    ' (assigning the bookmark of a recordset to another one is only
    ' possible because the second recordset was obtained using the
    ' Clone method of the first one. In such a case both recordsets
    ' share the same bookmarks). 
    
            DT = 1
            mtype = "None"
            sid = "00"
            wid = "A"
    
    rstCurrentData2.Bookmark = rstCurrentData.Bookmark

    '
    ' Move rst2 to the next record.
    '
        rstCurrentData2.MoveNext
   '
    ' Now the current record of rst1 is Row(n) while
    ' the current record of rst2 is Row(n+1).
    ' We can perform the comparison(s) between both
    ' sets of fields.
  ' If you have run out of current data to look at then just add the new record

Do Until rstCurrentData2.EOF

        If Not rstCurrentData2.EOF Then
        
            If (rstCurrentData!CollectT + 1) < rstCurrentData2!CollectT Then
                 
                 'Do Until (rstCurrentData!CollectT + 1) >= rstCurrentData2!CollectT
                        With rstNew
                            rstNew.AddNew
                            rstNew!CollectT = (DT + rstCurrentData!CollectT)
                            rstNew!MsgType = mtype
                            rstNew!SenderID = sid
                            rstNew!WordID = wid
                            rstNew.Update
                            
                            rstCurrentData.MoveNext
                        End With
                            'Loop
            Else
                If (rstCurrentData!CollectT + 1) >= rstCurrentData2!CollectT Then rstCurrentData.MoveNext
                End If
                            
            'End If
            End If
        rstCurrentData2.MoveNext

    Loop

    '
    ' Clean up when done.
    '

    rstCurrentData2.Close
    rstCurrentData.Close
    Set rstCurrentData = Nothing
    Set rstCurrentData2 = Nothing


End Function

The table that it is working with has 4 field the main one being "CollectT" which is a number the other 3 are just text.

Can anyone help me with this error; the code runs fine from the VBA window. I really need it to run in Access.

Thanks

[si_the_geek]

Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)

[westconn1]

at which point do you get the error?

[rgkimball]

I get the err
or when I try to run it from a macro. I can run it all day long from the vb editor.

[westconn1]

at which point do you get the error?
on what line?

or when I try to run it from a macro

how are you calling from a mzcro?

[rgkimball]

After getting the code to work I created a macro in Access to "Run Code" the error occurs when I am trying to run the macro as soon as I click run. All the marco does it call teh saved function "Compare2Rows"; when I created the marco I was able to select the "Compare2Rows" function.

I have no idea why it will not run, as I said I can run it all day from the VB Editor, I only get this error whenI try to call "Compare2Rows" from a macro.

Thanks for trying to help its driving me crazy.