Search from Command Buttons

[Paul Avery]

I have a form in which I put command buttons in the header for search so that
I can display the form for the user without displaying any tool bars. The
first button, titled "Define Search," calls a FilterByForm macro. The second
button, titled "Apply Search" calls an ApplyFilter macro. The third, titled
"Reset" calls a RemoveFilter macro. When I click the "Define Search" button
the form sets for entry of the search values but disables all the command
buttons at the same time so that after entering the search values I cannot
activate the "Apply Search" button. I have to go to the ApplyFilter button in
the tool bar to continue the search process. After that, the "Reset" button
works fine.

How can I ApplyFilter from a command button in the form?

 

[Allen Browne]

Filter By Form is a special view, and none of the events work in this view.
This means you cannot use a command button on the form, because its Click
event will not work.

Use a toolbar button instead. If that is unacceptable, then don't use FBF.
It does not work in the runtime versions of Access at all, so you might want
to come up with your own way of filtering instead.

 

[Paul Avery]

Allen,

Thanks for the response. I was afraid that was the answer. Could you give me
an example of how I might develop my own way of filtering?

Paul Avery

 

[Allen Browne]

In any given form, there are typically from 0 to 6 fields that the user
could potentially want to filter on. What we do is to place unbound controls
in the Form Header section, so the user can enter filter values for those
fields. To the right of these unbound controls are 2 command buttons for
Apply Filter and Remove Filter.

The Apply Filter command button visits each of the unbound controls and
builds up a filter string from the ones where the user entered something:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save first.

If Not IsNull(Me.txtFilterSurname) Then 'Text field example
strWhere = strWhere & "([Surname] = """ & Me.txtFilterSurname & """)
AND "
End If

If Not IsNull(Me.txtFilterAmount) Then 'Number field example
strWhere = strWhere & "([Amount] = " & Me.txtFilterAmount & ") AND "
End If

If Not IsNull(Me.txtFilterDOB) Then 'Date field example
strWhere = strWhere & "[DOB] = " & Format(Me.txtFilterDOB,
"\#mm\/dd\/yyyy\#") & ") AND "
End If

'etc for other boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FitlerOn = True
End If
End Sub