Looping through recordset with VBA

Question

I am trying to assign salespeople (rsSalespeople) to customers (rsCustomers) in a round-robin fashion in the following manner:

1. Navigate to first Customer, assign the first SalesPerson to the Customer.
2. 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.
3. 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?

Answer 1

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