VBA使用另一个表/记录集中的值使用循环更新Access中的表/记录集吗? [英] VBA Update table/recordset in Access using Loop with values from another table/recordset?

问题描述

我需要一些用于Access的VBA的帮助.

我有一个表"Client_Table" ，其中包含100行数据.我有另一个表"SalesRep_Table" ，其中有10个不同的销售代表ID号(例如: AA1111 ，依此类推).

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

我通过创建两个记录集并尝试通过SQL Update进行循环来解决此问题.但是，我最后得到的所有100条记录仅包含最后一次重复销售100次的销售代表ID.您可以看一下我的代码，然后告诉我需要在哪里修复吗?

 公共子Command01_Click()昏暗的strSQL昏暗的ClientsTableQuery，SalesRepListDim DataB作为数据库Dim ClientQD作为QueryDef昏暗的SalesQD作为QueryDefDim rstClient作为记录集Dim rstSalesRep 作为记录集ClientTableQuery =客户端"SalesTableQuery ="SalesRepList"'创建一个包含100个名为客户"的客户记录的记录集strSQL =从Client_Table选择*"设置DataB = CurrentDB()设置ClientQD.CreateQueryDef(ClientTableQuery，strSQL)设置rstClient = DataB.OpenRecordset(ClientTableQuery)'使用10个销售代表记录创建一个记录集，这些记录称为"SalesRepList"strSQL =从SalesRep_Table中选择SalesRepID"设置DataB = CurrentDB()设置SalesQD.CreateQueryDef(SalesTableQuery，strSQL)设置 rstSalesRep = DataB.OpenRecordset(SalesTableQuery)rstSalesRep.MoveFirstrstClient.MoveFirst直到rstSalesRep.EOF = True为止'SQL查询以更新已分配销售代表ID"列中的前10个单元格具有SalesRepList记录集中的销售代表ID的客户记录集strSQL =更新客户端，SalesRepList SET Clients.AssignedSalesRepID =SalesRepList.SalesRepID，其中Clients.ClientIDNumber在(选择前10个Clients.ClientIDNumber FROM Clents，其中Clients.AssignedSalesRepID为Null)DoCmd.RunSQL(strSQL)rstSalesRep.MoveNext环形MsgBox完成循环"第一销售代表关闭结束子 

解决方案

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

SQL解决方案的步骤:

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

I need some help with some 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).

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_Table gets inserted into the next 10 cells in the Clients table, 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.

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?

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

解决方案

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).

Steps for SQL solution:

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

这篇关于VBA使用另一个表/记录集中的值使用循环更新Access中的表/记录集吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！