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

1
votes

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
2
loopsms-accessvbarecordset

2 Answers

3
votes

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
1
votes

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.

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