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

Question

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

Add `Option Explicit` to the Declarations second of that code module. Then compile your code by selecting Debug->Compile from the VBE editor's main menu. Fix anything the compiler complains about and compile again. Repeat until no more compile errors. You would be wise to include `Option Explicit` in every module. — HansUp, Sep 12 '18 at 19:13

score 1 · Accepted Answer · answered Sep 12 '18 at 18:03

1

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.

answered Sep 12 '18 at 18:03

HansUp

95,961
11
77
135

I'm still having difficulty navigating errors. I've tried a lot of googling and guess/check but I'm confused on how to set up the index so that it states 'code'StrSQL2.Seek "=", 27...'code' the value '27' or 'external_nmad_id' is the only column in the StrSQL2 recordset I've made via the query. Here is what that section looks like as of now... ''StrSQL2.CreateIndex( 'Set StrSQL2.Fields("ID") =' 'StrSQL2.Column(0).Value StrSQL2.Index = "ID" StrSQL2.Seek "ID =",' '!external_nmad_id'' – rgorowsky Sep 12 '18 at 20:40
Sorry, I can't even format my code right in the comments - let alone get it to run – rgorowsky Sep 12 '18 at 20:42
yes, StrSQL2 is now a recordset. I'm now returning the Run-time error '3251' operation is not supported for this type of object. – rgorowsky Sep 12 '18 at 20:59
I agree. Thanks for your help @Hans, I think it's best to restart this thread: I've made a new question at this link [link](https://stackoverflow.com/questions/52303535/access-vba-how-can-i-seek-a-value-in-a-recordset-error-code-3251-operation-i) – rgorowsky Sep 12 '18 at 21:11

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

1 Answers1