Custom NavControls are conflicting with custom filters. Any idea why?

[gojets1721]

I've got a custom nav controls in my form (first & last button, current record #, total records) using the following code for the current record #:

Private Sub Form_Current()

    Dim rst As DAO.Recordset
    Dim lngCount As Long

    Set rst = Me.RecordsetClone

    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With

    Me.txtNavControls = Me.CurrentRecord & " of " & lngCount

End sub

It works perfectly except when I use one of my filter buttons. If I press the filter and it filters to 0 records (it constantly flucuates), I get a 3021 error stating no current record. The debugger keys on the ".MoveFirst" line.

Not a huge issue, I'm just OCD about error messages. It'd be nice if that didn't pop up and it just filtered to 0 records. Any idea how to get rid of this error message?

Here's one of my filters' code:

Private Sub btnInProgress_Click()
    Me.Filter = "[Status]= 'In Progress'"
    DoCmd.SetOrderBy "[ComplaintDate] ASC"
    Me.FilterOn = True
End Sub

 

[pbaldy]

Test the recordset for EOF

If Not rst.EOF Then

 

[gojets1721]

Test the recordset for EOF

If Not rst.EOF Then

I'm not sure what that means. Can you be more specific? I'm still very new with VBA

 

[MajP]

If Not rst.EOF Then
  with rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
end with
end if

If your are at EOF, that does not mean you are at the last record but the pointer is beyond any records. Therefore in this case no records exist. You do not want to try to move when there are no records returned.

 

[pbaldy]

I'm not sure what that means. Can you be more specific? I'm still very new with VBA

Looks like I don't have to...

 

[gojets1721]

If Not rst.EOF Then
  with rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
end with
end if

If your are at EOF, that does not mean you are at the last record but the pointer is beyond any records. Therefore in this case no records exist. You do not want to try to move when there are no records returned.

Gotcha. That worked. Thanks guys!!