Stop command button disabling with filter by form

[OssieMac]

With an open form, any command buttons on the form are disabled when the
Filter by Form is called whether by clicking on the toolbar 'Filter by Form'
button or using a command button on the form to run the following code:-
DoCmd.RunCommand acCmdFilterByForm

I would like to have a button on the form where I can toggle it's caption
from 'Filter by Form' to 'Apply Filter' and then to 'Remove Filter'.

However, my problem is that immediately Filter by Form is applied, all the
command buttons on the form are disabled. Is there any way of keeping a
button enabled while in the 'Filter by Form' mode? (I have tried enabling it
but to no avail.)

Thanks in advance for all replies.

 

[Allen Browne]

Filter By Form (FBF) mode is not the same as Form mode. There is no way to
make command buttons (or any other events) operational in this mode. You can
use the Toolbar buttons of course.

FBF is of limited use. Doesn't work in runtime/mde either. If you have
outgrown it, you might be ready to start building filtering into your own
forms. Here's an example of how:
Search form - Handle many optional criteria. Sample database
at:
http://allenbrowne.com/ser-62.html

 

[Tom van Stiphout]

On Sat, 12 Jan 2008 19:40:01 -0800, OssieMac

No.
That's why there is a toolbar or a ribbon for those buttons.
-Tom.

 

[OssieMac]

Thanks Allen and Tom for taking the time to answer. Much appreciated. You
have told me what I suspected 'that I can't do what I wanted to'.

 

[steve]

Well, YOU CAN in fact do what you wanted. I have come up with
something akin to the Filter By Form feature of Access that requires
far less pain than that awful Microsoft example qbf2k.mdb, and as an
alternative to Allen's excellent search form:

1. Open the form you want the filter on (e.g. frmCustomers)
2. Create a button called cmdFind, set to OpenForm frmCustomersSearch
(to be created)
3. Create another button called cmdApplyFilter, set to Enabled.....No
(optional)
4. Click Save As and save a copy of the form, call it
frmCustomersSearch
5. Open the frmCustomersSearch form
6. Set each field Unbound (i.e. set the Control Source for each field
empty)
7. Set cmdApplyFilter to Enabled......Yes, with the following code On
Click:

Private Sub cmdApplyFilter_Click()
Forms!frmCustomers.SetFocus
DoCmd.ApplyFilter , "CustomerName like '*" & Me.CustomerName &
"*'"
DoCmd.Close acForm, "frmCustomersSearch"
End Sub

8. Now try it! Click on the Find button on the frmCustomers form, type
something into the CustomerName field, click on the ApplyFilter
button.

Obviously, we are only searching on CustomerName here but this can now
be easily expanded to include any of the unbound fields (say, any
field that is not null) by adding a little bit of code to the
cmdApplyFilter_Click() routine, and some AfterUpdate event on the
search form to make Enter key the trigger.

Steve

 

[OssieMac]

Thanks for the info Steve. I'll try it and see how it works out. For my
original problem I created my own toolbar and it works very well. When
setting a custom toolbar in the form properties, it turns off the other
toolbars and only leaves the user with a choice of what you want them to be
able to use. The toolbar buttons are as good as having command buttons.

 

[steve]

Yep, I'm with you, another reasonable solution. The problem with my
customer is that they are used to the Filter By Form paradigm because
they have mostly used DataEase - which has that FBF feature as the
main searching method. So I had to come up with an Access version of
that. I will be working on the solution a little more and will post
here when complete.

Steve

 

[steve]

OK, I have come up with the following code for the cmdApplyFilter
button.

I have also set Enabled......No on all the other buttons on the page,
except Cancel. This makes it obvious to the user that they are on the
search form.

Private Sub cmdApplyFilter_Click()
For Each Control In Me.Form.Controls
If Control.ControlType = acTextBox Then
ctlVal = Control.Value
If Not IsNull(ctlVal) Then
Forms!frmSalesOrders.SetFocus
DoCmd.ApplyFilter , "[" & Control.Name & "]" & " like
'*" & ctlVal & "*'"
DoCmd.Close acForm, "frmSalesOrdersSearch", acSaveNo
Exit Sub
End If
End If
Next Control
End Sub

 

[Allen Browne]

Jenny, you have posted a couple of new threads, which are not follow-ups to
an existing one. Consequently we can't tell what you are referring to here.

I assume you are creating a form with unbound controls where the user can
enter search criteria, and then you filter the form to display the matching
results. If so, you are probably building a string from the text boxes and
multi-select list boxes, and then applying it as the filter for the form.

You will need to create this code yourself, but a couple of examples might
help you do it. Here's one that shows how to build the filter string based
on a multi-select list box, by looping through the ItemsSelected:
http://allenbrowne.com/ser-50.html
Although the example is applied to a report, it's exactly the same to filter
a form.

This example shows how to combine the values from several text boxes and
combos to create a filter string:
http://allenbrowne.com/ser-62.html
It is designed to make it easy to add more search boxes as needed. You can
concatenate the filter string from the list box example and from this
example.