Looping through recordset with VBA
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
Sesame
Updated on April 06, 2020Comments
-
Sesame about 4 years
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?
-
Sesame about 14 yearsOops... Customers and Salespeople are not the actual entities I am using in the application; I am just using them as examples and apparently missed that in the conversion of my actual code! Thanks for catching it anyways. I'll edit my OP.
-
user3001801 about 14 yearsGotcha. Edited my answer to add a solution to your core issue.