CODEWITHSUNDEEP
ms-accessmethodsvba
rgorowsky
rgorowsky

Reputation: 135

Access VBA .seek method, get run-time error '438', or Method or data member not found

I'm trying to use the seek method within a VBA code nested loop. The goal is to use a query to search a table for ID values that match what was identified in the first loop. I'm returning 'Method or data member not found' The error is occurring on the line 'Set StrSQL2.Index = "ID"'. Since "ID" is the only column in that table created by the query I tried commenting it out, but it only shifts the error down to the next line 'StrSQL2.Seek "=", !external_nmad_id

Public Sub EditFinalOutput2()

'set variables
Dim i As Long
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim StrSQL2 As DAO.QueryDef
Dim IRSfileFormatKey As String
Dim external_nmad_id As String
Dim nmad_address_1 As String
Dim nmad_address_2 As String
Dim nmad_address_3 As String
Dim mytestwrite As String

'open reference set
Set db = CurrentDb
Set qs = db.OpenRecordset("SunstarAccountsInWebir_SarahTest")
'Set ss = db.OpenRecordset("1042s_FinalOutput_7")
'Set StrSQL1 = db.OpenRecordset("SELECT RIGHT(IRSfileFormatKey, 10) As ID 
FROM 1042s_FinalOutput_7;")

With qs.Fields
intCount = qs.RecordCount - 1
For i = 0 To intCount

    If (IsNull(!nmad_address_1) Or (!nmad_address_1 = !nmad_city) Or (!nmad_address_1 = !Webir_Country) And IsNull(!nmad_address_2) Or (!nmad_address_2 = !nmad_city) Or (!nmad_address_2 = !Webir_Country) And IsNull(!nmad_address_3) Or (!nmad_address_3 = !nmad_city) Or (!nmad_address_3 = !Webir_Country)) Then
    DoCmd.RunSQL "INSERT INTO Addresses_ToBeReviewed SELECT SunstarAccountsInWebir_SarahTest.* FROM SunstarAccountsInWebir_SarahTest WHERE (((SunstarAccountsInWebir_SarahTest.external_nmad_id)='" & qs!external_nmad_id & "'));"

    Else:
        Set StrSQL2 = CurrentDb.CreateQueryDef("", "SELECT RIGHT(IRSfileFormatKey, 10) As ID FROM 1042s_FinalOutput_7;")
        Set ss = db.OpenRecordset("1042s_FinalOutput_7")
        Set StrSQL2.Index = "ID"
        StrSQL2.Seek "=", !external_nmad_id

            If ss.NoMatch Then
            DoCmd.RunSQL "INSERT INTO Addresses_NotUsed SELECT SunstarAccountsInWebir_SarahTest.* FROM SunstarAccountsInWebir_SarahTest WHERE (((SunstarAccountsInWebir_SarahTest.external_nmad_id)='" & qs!external_nmad_id & "'));"

            Else:   Set ss = db.OpenRecordset("1042s_FinalOutput_7")
                    ss.Edit
                    ss.Fields("box13c_Address") = qs.Fields("nmad_address_1") & qs.Fields("nmad_address_2") & qs.Fields("nmad_address_3")
                    ss.Update

            End If
    End If

qs.MoveNext
Next i

End With

'close reference set
qs.Close
Set qs = Nothing
ss.Close
Set ss = Nothing

End Sub

Upvotes: 1

Views: 271

Answers (1)

HansUp
HansUp

Reputation: 97101

Consider a stripped down version of your code which still recreates the problem you're trying to solve.

Dim StrSQL2 As DAO.QueryDef
Set StrSQL2 = CurrentDb.CreateQueryDef("", "SELECT RIGHT(IRSfileFormatKey, 10) As ID FROM 1042s_FinalOutput_7;")
Set StrSQL2.Index = "ID"

When you attempt to run that code, Access will complain "Object doesn't support this property or method". The reason that happens is because StrSQL2 is a DAO.QueryDef and a QueryDef does not have an Index property. See QueryDef Members (DAO)

So then you disable that problem line and try this ...

StrSQL2.Seek "=", 27 'I substituted an arbitrary number for !external_nmad_id just to keep this simple '

But Access responds with the same complaint again, which is because a QueryDef does not have a Seek method.

Both Index and Seek are object members of a DAO.Recordset, so use them with a Recordset instead of a QueryDef

And if you go that route, designate the name of your controlling index like this ...

YourRecorsetVariable.Index = "ID"

Don't use Set there and make sure that "ID" is the name of the index ... which is not necessarily the name of the column which is indexed.

Upvotes: 1

Related Questions