return to first record when last record reached

[stevekos07]

Hi all. I am at the beginning of a challenging project for a database I administer at work. I am still learning VBA, so I will need quite a bit of assistance with this as I go I am sure. The task is to develop a call-center type application that will rotate through clients who need to be called based on call time and whether they have already been called. The query filters on a field [Answered] = false, and sorts on CallTime ascending.

I have developed a very simple database to test the concepts, and I have some elements working ok so far. I have a "Next Call" command button which contains the following on-click code:

Private Sub cmdNextCall_Click()

If Me.chkAnswered.Value = "-1" Then
DoCmd.Requery
Else
DoCmd.GoToRecord , "", acNext
End If
End Sub

This works fine until I get to the end of the recordset, but I need to insert some code that will navigate to the first available record again, and repeat this process until all clients have been successfully called (ie. [Answered].value = "-1").

Then if the available recordset is zero, I want a messagebox that says something like "All Clients Called".

So it's just the code that will loop through and requery over and over until all the clients have been called.

I hope this makes sense.

 

[static]

Using Find on the recordset would probably be more efficient than constant refreshing.

Private Sub cmdNextCall_Click()
    With Me.Recordset
        .FindNext "chkAnswered=false"
        If .NoMatch Then
            .FindFirst "chkAnswered=false"
            If .NoMatch Then
                MsgBox "All Clients Called"
            End If
        End If
    End With
End Sub

 

[jdraw]

As static has responded, here is more info, syntax and example on FindFirst from M$oft.