vba 使用带有另一个表/记录集的值的循环在 Access 中更新表/记录集？

VBA Update table/recordset in Access using Loop with values from another table/recordset?

提问by KD9920

I need some help with some VBA for Access.

我需要一些 VBA for Access 的帮助。

I have a table "Client_Table"with 100 rows of data. I have another table "SalesRep_Table"where I have 10 distinct Sales Rep ID numbers (such as: AA1111, and so on).

我有一个"Client_Table"包含 100 行数据的表。我有另一个表"SalesRep_Table"，其中有 10 个不同的销售代表 ID 号（例如：AA1111，等等）。

My goal is to run a procedure that takes the first ID record "AA1111"and places it in the appropriate column on the Clients table named "AssignedSalesRepID"for the first 10 rows, then the next ID number in the SalesRep_Tablegets inserted into the next 10 cells in the Clientstable, and the process repeats through a loop until all 10 IDs are now in 10 rows each to fill the 100 rows of data in the Clients table.

我的目标是运行一个过程，该过程获取第一个 ID 记录"AA1111"并将其放置在以前"AssignedSalesRepID"10 行命名的 Clients 表上的相应列中，然后将下一个 ID 号SalesRep_Table插入Clients表中的下一个 10 个单元格中，并且该过程通过循环重复，直到所有 10 个 ID 现在都在 10 行中，以填充 Clients 表中的 100 行数据。

I went about it by creating two recordsets and trying a loop through SQL Update. However I end up with all 100 records containing just the last Sales Rep ID 100 times repeating. Can you take a look at my code and let me know where it needs to be fixed?

我通过创建两个记录集并尝试通过 SQL 更新进行循环来解决这个问题。但是，我最终得到的所有 100 条记录只包含最后一个销售代表 ID 重复 100 次。你能看看我的代码，让我知道哪里需要修复吗？

Public Sub Command01_Click()

Dim strSQL
Dim ClientsTableQuery, SalesRepList
Dim DataB as Database
Dim ClientQD as QueryDef
Dim SalesQD as QueryDef
Dim rstClient as Recordset
Dim rstSalesRep as Recordset

ClientTableQuery = "Clients"
SalesTableQuery = "SalesRepList"

'Creates a recordset with 100 client records named "Clients" 
strSQL = "Select * from Client_Table"
Set DataB = CurrentDB()
Set ClientQD.CreateQueryDef(ClientTableQuery, strSQL)
Set rstClient = DataB.OpenRecordset(ClientTableQuery)

'Creates a recordset with 10 sales rep records named "SalesRepList"
strSQL = "Select SalesRepID from SalesRep_Table"
Set DataB = CurrentDB()
Set SalesQD.CreateQueryDef(SalesTableQuery, strSQL)
Set rstSalesRep = DataB.OpenRecordset(SalesTableQuery)


rstSalesRep.MoveFirst
rstClient.MoveFirst

Do Until rstSalesRep.EOF = True

'SQL Query to update the top 10 cells in the "Assigned Sales Rep ID" column in the  
Clients recordset with the Sales Rep ID from the SalesRepList recordset

strSQL = "Update Clients, SalesRepList SET Clients.AssignedSalesRepID =   
SalesRepList.SalesRepID where Clients.ClientIDNumber in (Select Top 10     
Clients.ClientIDNumber FROM Clents where Clients.AssignedSalesRepID is Null)"

DoCmd.RunSQL (strSQL)
rstSalesRep.MoveNext

Loop
MsgBox "Finished Looping"
rstSalesRep.Close


End Sub

回答by bf2020

I hate to be the one to tell you this, but you should reconsider using SQL to do this update. I see that you have already written a lot of code and might feel like if you switch back to SQL that you will then have wasted all this vb code. I have felt like that myself in times past. But you can solve this problem with SQL with an order of magnitude less code(or nearly so).

我不想成为告诉您这一点的人，但您应该重新考虑使用 SQL 进行此更新。我看到您已经编写了很多代码，并且可能会觉得如果您切换回 SQL，您将浪费所有这些 vb 代码。过去我自己也有这种感觉。但是您可以使用 SQL 以少一个数量级的代码（或几乎如此）来解决这个问题。

Steps for SQL solution:

SQL解决步骤：

1. Sequence rows in both sets
2. mod A set sequence by B set sequence max
3. update A set on mod = b seq

1. 两组中的序列行
2. mod A set sequence by B set sequence max
3. 在 mod = b seq 上更新 A 集

回答by parakmiakos

You are making a Join call in your query, without defining how those 2 tables are being joined. You are not mentioning anywhere, which record of the rstSalesRep recordset you wish to set the assignedSalesRepId to.

您正在查询中进行 Join 调用，而没有定义这 2 个表的联接方式。您没有在任何地方提及您希望将assignedSalesRepId 设置为rstSalesRep 记录集的哪个记录。

Also I would reduce all your code down to the following:

此外，我会将您的所有代码缩减为以下内容：

Dim strSQL
Dim DataB As Database
Dim rstSalesRep As Recordset

Set DataB = CurrentDb()

Set rstSalesRep = DataB.OpenRecordset("Select SalesRepID from  SalesRep_Table  ")
Do Until rstSalesRep.EOF = True

    strSQL = "Update Client_Table, SalesRep_Table SET Client_Table.AssignedSalesRepID = SalesRep_Table.SalesRepID " & _
    "where Client_Table.ClientIDNumber in (Select Top 2 Client_Table.ClientIDNumber FROM Client_Table where Client_Table.AssignedSalesRepID is Null)" & _
    " and SalesRep_Table.SalesRepID = '" & rstSalesRep("SalesRepID") & "'"

    DoCmd.RunSQL (strSQL)
    rstSalesRep.MoveNext

Loop
MsgBox "Finished Looping"
rstSalesRep.Close