Looping through recordset with VBA
I am trying to assign salespeople (rsSalespeople) to customers (rsCustomers) in a round-robin fashion in the following manner:
- Navigate to first Customer, assign the first SalesPerson to the Customer.
- Move to Next Customer. If rsSalesPersons is not at EOF, move to Next SalesPerson; if rsSalesPersons is at EOF, MoveFirst to loop back to the first SalesPerson. Assign this (current) SalesPerson to the (current) Customer.
- Repeat step 2 until rsCustomers is at EOF (EOF = True, i.e. End-Of-Recordset).
It's been awhile since I dealt with VBA, so I'm a bit rusty, but here is what I have come up with, so far:
Private Sub Command31_Click() 'On Error GoTo ErrHandler Dim intCustomer As Integer Dim intSalesperson As Integer Dim rsCustomers As DAO.Recordset Dim rsSalespeople As DAO.Recordset Dim strSQL As String strSQL = "SELECT CustomerID, SalespersonID FROM Customers WHERE SalespersonID Is Null" Set rsCustomers = CurrentDb.OpenRecordset(strSQL) strSQL = "SELECT SalespersonID FROM Salespeople" Set rsSalespeople = CurrentDb.OpenRecordset(strSQL) rsCustomers.MoveFirst rsSalespeople.MoveFirst Do While Not rsCustomers.EOF intCustomer = rsCustomers!CustomerID intSalesperson = rsSalespeople!SalespersonID strSQL = "UPDATE Customers SET SalespersonID = " & intSalesperson & " WHERE CustomerID = " & intCustomer DoCmd.RunSQL (strSQL) rsCustomers.MoveNext If Not rsSalespeople.EOF Then rsSalespeople.MoveNext Else rsSalespeople.MoveFirst End If Loop ExitHandler: Set rsCustomers = Nothing Set rsSalespeople = Nothing Exit Sub ErrHandler: MsgBox (Err.Description) Resume ExitHandler End Sub
My tables are defined like so:
Customers --CustomerID --Name --SalespersonID Salespeople --SalespersonID --Name
With ten customers and 5 salespeople, my intended result would like like:
CustomerID--Name--SalespersonID 1---A---1 2---B---2 3---C---3 4---D---4 5---E---5 6---F---1 7---G---2 8---H---3 9---I---4 10---J---5
The above code works for the intitial loop through the Salespeople recordset, but errors out when the end of the recordset is found. Regardless of the EOF, it appears it still tries to execute the rsSalespeople.MoveFirst command.
Am I not checking for the rsSalespeople.EOF properly? Any ideas to get this code to work?
rsSalespeople.EOF doesn't indicate when you are on the last row, it indicates when you are PAST the last row.
So when your conditional hits the last salesperson EOF is false so it does a movenext (making EOF true) then the next pass through the loop is operating on the "EOF row" of rsSalespeople which you can't pull values from, hence the error.
Try this instead:
rsSalespeople.MoveNext If (rsSalespeople.EOF) Then rsSalespeople.MoveFirst End If