Custom NavControls are conflicting with custom filters. Any idea why? (1 Viewer)

gojets1721

Registered User.
Local time
Today, 07:46
Joined
Jun 11, 2019
Messages
429
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 #:

Code:
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:

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

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:46
Joined
Aug 30, 2003
Messages
36,118
Test the recordset for EOF

If Not rst.EOF Then
 

gojets1721

Registered User.
Local time
Today, 07:46
Joined
Jun 11, 2019
Messages
429
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

You've got your good things, and you've got mine.
Local time
Today, 10:46
Joined
May 21, 2018
Messages
8,463
Code:
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.
 

gojets1721

Registered User.
Local time
Today, 07:46
Joined
Jun 11, 2019
Messages
429
Code:
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!!
 

Users who are viewing this thread

Top Bottom