Access VBA how can I .Seek a value in a recordset, error code '3251' operation is not supported for this type of object

Question

I'm having difficulty writing VBA to accomplish a couple goals: 1. loop through address table ("SunstarAccountsInWebir_SarahTest") to separate out valid from invalid addresses. if invalid - export to different table. 2. If valid, match the address values to a 2nd table. If it matches, then insert the address values into the 2nd table.
3. If it doesn't match, then export to another different table

The issue I'm getting is the exporting works, but I'm not seeing any action on condition 2 "valid address where it's ID is in the 2nd table". I'm trying to use seek method to see if the ID from the first table is in the 2nd table - and have it update as many times as it occurs (ID's are not unique in both tables). And I can't use two nested loops because the tables are too large. Below is the code as of now. I'm returning an error where it says: StrSQL1.Index = "blablavalue"

Public Sub EditFinalOutput2()

'set variables
Dim i As Long
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim StrSQL1 As DAO.Recordset
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")

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 StrSQL1 = db.OpenRecordset("SELECT RIGHT(IRSfileFormatKey, 10) As blablavalue FROM 1042s_FinalOutput_7;", dbOpenDynaset)
    Set ss = db.OpenRecordset("1042s_FinalOutput_7")
    StrSQL1.Index = "blablavalue"
    StrSQL1.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

You can't use `Seek` on a recordset based on a query. Sorry I didn't notice that in your previous question. — HansUp, Sep 12 '18 at 21:37
The query is only to get the filtered version of the id. the ID in table 2 is a concatenation of different ID types within the database. In this case the ID from table 1 would match to the last 10 digits of the ID in table 2. Is it possible to just seek the table itself - but specify matching the last 10 digits? Or If I did a separate query before this, so that Table 2 had a column of the ID that matched the style of table 1, could it be seek in that way? I feel like seek is the best to use since the only matching value are those IDs, but they aren't Distinct in either table — rgorowsky, Sep 12 '18 at 21:56
Why not just filter the query used in "Set StrSQL1..." to look for the key you need, then if no record returned, do 'something', else do 'something else'? — Wayne G. Dunn, Sep 12 '18 at 22:18
@rgorowsky Wayne's suggestion sounds promising to me. If that doesn't fit in as you wish, suggest you look into [Recordset.FindFirst Method (DAO)](https://msdn.microsoft.com/en-us/library/office/ff194787.aspx). That one is not so restrictive as `Seek` --- it can be used with a recordset based on a query. — HansUp, Sep 12 '18 at 22:33
@HansUp Thanks Hans! It's sooooo close. The errors are gone and it appears to be parsing through - with one exception. The line giving me issue is 'StrSQL1.FindFirst ([PrimaryKey] = qs.Fields("external_nmad_id"))' As it loops through there is a value in the "external_nmad_id" object, but [PrimaryKey] whos up in the hover-over as '[PrimaryKey] = "" ' Primary key is set in the query dynaset. When I loop through there are 'external_nmad_id's that match the query, and there are no errors, but the address is not parsed in... — rgorowsky, Sep 13 '18 at 16:32
Set StrSQL1 = db.OpenRecordset("SELECT RIGHT(IRSfileFormatKey, 10) As PrimaryKey FROM 1042s_FinalOutput_7;", dbOpenDynaset) StrSQL1.FindFirst ([PrimaryKey] = qs.Fields("external_nmad_id")) — rgorowsky, Sep 13 '18 at 16:33
@rgorowsky Please revise the question to reflect the current state of the problem. The update may attract additional helper eyes. — HansUp, Sep 13 '18 at 16:39
@HansUp I was not referencing the field that would be amending in StrSQL1 - but now it works! Looks good now, now I just gotta see if it'll loop through 150,000 records without crashing — rgorowsky, Sep 13 '18 at 17:04

score 1 · Answer 1 · answered Sep 13 '18 at 17:10

Thanks to @Hansup for providing guidance, the final "working" code looks like this:

Public Sub EditFinalOutput2()

'set variables
Dim i As Long
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim StrSQL1 As DAO.Recordset
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
Dim PrimaryKey As String
Dim box13c_Address As String

'open reference set
Set db = CurrentDb
Set qs = db.OpenRecordset("SunstarAccountsInWebir_SarahTest")

'turn popup messages off
DoCmd.SetWarnings False

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 StrSQL1 = db.OpenRecordset("SELECT RIGHT([1042s_FinalOutput_7].IRSfileFormatKey,  10) As PrimaryKey, box13c_Address FROM 1042s_FinalOutput_7;", dbOpenDynaset)
    StrSQL1.FindFirst ([PrimaryKey] = qs.Fields("external_nmad_id"))

        If StrSQL1.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:
                StrSQL1.Edit
                StrSQL1.Fields("box13c_Address") = qs.Fields("nmad_address_1") & qs.Fields("nmad_address_2") & qs.Fields("nmad_address_3")
                StrSQL1.Update

        End If

End If

qs.MoveNext
Next i

End With

'turn popup messages back on
DoCmd.SetWarnings True

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

End Sub

Access VBA how can I .Seek a value in a recordset, error code '3251' operation is not supported for this type of object

1 Answers1

Linked