访问VBA如何在记录集中查找值，此类对象不支持错误代码“ 3251”操作 [英] Access VBA how can I .Seek a value in a recordset, error code '3251' operation is not supported for this type of object

问题描述

我很难编写VBA来实现几个目标：
1.循环访问地址表（ SunstarAccountsInWebir_SarahTest）以区分有效地址和无效地址。如果无效-导出到其他表。
2.如果有效，则将地址值匹配到第二个表。如果匹配，则将地址值插入第二个表。

3.如果不匹配，则导出到另一个不同的表

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

我遇到的问题是导出工作，但是我没有看到对条件2 ID在第二个表中的有效地址有任何操作。我正在尝试使用seek方法来查看第一张表中的ID是否在第二张表中-并使其更新次数尽可能多（两个表中的ID都不唯一）。而且我不能使用两个嵌套循环，因为表太大了。下面是到目前为止的代码。我返回的错误提示：StrSQL1.Index = blablavalue

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

推荐答案

感谢@Hansup提供指导，最终的有效代码如下所示：

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

这篇关于访问VBA如何在记录集中查找值，此类对象不支持错误代码“ 3251”操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！