Re: Form Filter

On Feb 9, 7:41 am, "zuckerm...@xxxxxxxxx" <zuckerm...@xxxxxxxxx>
wrote:
I just noticed the following....

In my form load event, I have the following code:

Private Sub Form_Load()
   Me.Filter = "Status='Active'"
   Me.FilterOn = True
End Sub

The form loads correctly and it shows only 60 records, which is
correct. However, I notice that the "apply filter" button on the
toolbar does not appear depressed, and at the bottom of the screen the
word "(filtered)" does not appear.

If I click the "A-Z" button, the records sort and the record count at
the bottom of the screen changes to 112 records, which is the full
table count.

Why does the filter not seem to fully execute during the load event?

Thanks,
Fred

Hi,

Button on toolbar and status bar text "Filtered" are not set as Access
"don't know" did you set filter to data during form load event or not.
Once when you press "A-Z" button form filter and sorting order are re-
set so you lose the filter.

You will need to create custom buttons with code to sort data; I will
rather use different SQL's in record source of form and change the
record source of the form. Unfortunately you cannot use both
approaches.

If this helps - make a form have labels in header and use the label
OnClick event to sort data. Add new module and copy/paste this sub
there:

Public Sub SortByColumn(frmThisForm As Form, strFieldName As String)
On Error GoTo Err_SortByColumn

frmThisForm.OrderByOn = True
If StrComp(frmThisForm.OrderBy, strFieldName) = 0 Then
frmThisForm.OrderBy = strFieldName & " DESC"
Else
frmThisForm.OrderBy = strFieldName
End If

Exit_SortByColumn:
On Error GoTo 0
Exit Sub

Err_SortByColumn:
MsgBox Err.Description
Resume Exit_SortByColumn

End Sub

Now use OnClick event of the label(s) and add this command for each
label:

SortByColumn Me, "[LabelName]"

First click will sort data in ASC order, second in DESC order.

Regards,
Branislav Mihaljev
Microsoft Access MVP
.