Filter a form using a combo box and a macro

  • Thread starter Thread starter John Taylor
  • Start date Start date
J

John Taylor

I have a form with a combo box called "KeyIndicator" with four selections
("All"; "Key Deliverable"; "Work Plan"; "Other") - simple enough stuff - I
use this combo box to categorise records in my form.

I want to set a second combo box that will allow me to select one of the
same four criteria but will filter the records on the form so that I can
scroll through one category only (does that make sense?)

I set up an unbound combo box and named it "KeyFilter" with the same four
selections (i.e. "All"; "Key Deliverable"; "Work Plan"; "Other"). I then
wrote a macro called "OpenKeyFilter" with the folowing argument (used "Key
Deliverable" as an example): -

Macro Name OpenKeyFilter
Condition [Forms]![Issues]![KeyFilter]="Key Deliverable"
Action ApplyFilter
Where Condition ([Forms]![Issues]![KeyIndicator]="Key Deliverable")

The macro is set to run on "AfterUpdate" in the "KeyFilter" combo box.

This doesn't appear to work and I suspect I'm making a silly mistake but I
can't figure it out, the filter action looks like it works but it doesn't
filter on the "Key Deliverable" selection.

The filter "All" was easy as I simply used the "ShowAllRecords" Action and
it works OK.

Any help would be appreciated and thanks in advance.

John Taylor
 
There are a lot of ways to do this...one that might be the simplest:
(But you may have to do some tweaking)
First the assumption:
The recordsource for your form contains a field named KeyIndicator, and
presumably you are using the "first" combo box as a bound combo box to make
the entry, into each record, of one of your 4 values.
If so...

Base your form on a query.
Using the expression builder, put a criteria on the query on your
KeyIndicator field that refers to the combobox you wish to use as a filter.
This will allow you to set the criteria for the form recordsource by simply
picking one of the choices in your combo box.

Next, you need to create a macro (or VBA) for the AfterUpdate event of your
"filtering combo box". This macro needs to requery the form. (This will cause
the form to be filtered for only the records which contain the value you
picked in your combo box.)

A wrinkle: If you want the form to be able to display all of the records,
you may need make a couple of modifications:
Modify the recordsource of the combo box to
("*"; "Key Deliverable"; "Work Plan"; "Other")
or
("*";"All"; "Key Deliverable"; "Work Plan"; "Other")

Also, you would want the criteria expression in the query to include the
like operator so that it would look something like
Like [Forms]![Issues]![KeyFilter]

I haven't tested this, but have used this technique numerous times.

--
Frank H
Rockford, IL


John Taylor said:
I have a form with a combo box called "KeyIndicator" with four selections
("All"; "Key Deliverable"; "Work Plan"; "Other") - simple enough stuff - I
use this combo box to categorise records in my form.

I want to set a second combo box that will allow me to select one of the
same four criteria but will filter the records on the form so that I can
scroll through one category only (does that make sense?)

I set up an unbound combo box and named it "KeyFilter" with the same four
selections (i.e. "All"; "Key Deliverable"; "Work Plan"; "Other"). I then
wrote a macro called "OpenKeyFilter" with the folowing argument (used "Key
Deliverable" as an example): -

Macro Name OpenKeyFilter
Condition [Forms]![Issues]![KeyFilter]="Key Deliverable"
Action ApplyFilter
Where Condition ([Forms]![Issues]![KeyIndicator]="Key Deliverable")

The macro is set to run on "AfterUpdate" in the "KeyFilter" combo box.

This doesn't appear to work and I suspect I'm making a silly mistake but I
can't figure it out, the filter action looks like it works but it doesn't
filter on the "Key Deliverable" selection.

The filter "All" was easy as I simply used the "ShowAllRecords" Action and
it works OK.

Any help would be appreciated and thanks in advance.

John Taylor
Click to expand...
 
Thanks for your help - tried this but couldn't get it to work, however;
figured out my error in the first place and it works a treat now.

John Taylor

Frank H said:
There are a lot of ways to do this...one that might be the simplest:
(But you may have to do some tweaking)
First the assumption:
The recordsource for your form contains a field named KeyIndicator, and
presumably you are using the "first" combo box as a bound combo box to
make
the entry, into each record, of one of your 4 values.
If so...

Base your form on a query.
Using the expression builder, put a criteria on the query on your
KeyIndicator field that refers to the combobox you wish to use as a
filter.
This will allow you to set the criteria for the form recordsource by
simply
picking one of the choices in your combo box.

Next, you need to create a macro (or VBA) for the AfterUpdate event of
your
"filtering combo box". This macro needs to requery the form. (This will
cause
the form to be filtered for only the records which contain the value you
picked in your combo box.)

A wrinkle: If you want the form to be able to display all of the records,
you may need make a couple of modifications:
Modify the recordsource of the combo box to
("*"; "Key Deliverable"; "Work Plan"; "Other")
or
("*";"All"; "Key Deliverable"; "Work Plan"; "Other")

Also, you would want the criteria expression in the query to include the
like operator so that it would look something like
Like [Forms]![Issues]![KeyFilter]

I haven't tested this, but have used this technique numerous times.

--
Frank H
Rockford, IL


John Taylor said:
I have a form with a combo box called "KeyIndicator" with four selections
("All"; "Key Deliverable"; "Work Plan"; "Other") - simple enough stuff -
I
use this combo box to categorise records in my form.

I want to set a second combo box that will allow me to select one of the
same four criteria but will filter the records on the form so that I can
scroll through one category only (does that make sense?)

I set up an unbound combo box and named it "KeyFilter" with the same four
selections (i.e. "All"; "Key Deliverable"; "Work Plan"; "Other"). I then
wrote a macro called "OpenKeyFilter" with the folowing argument (used
"Key
Deliverable" as an example): -

Macro Name OpenKeyFilter
Condition [Forms]![Issues]![KeyFilter]="Key Deliverable"
Action ApplyFilter
Where Condition ([Forms]![Issues]![KeyIndicator]="Key
Deliverable")

The macro is set to run on "AfterUpdate" in the "KeyFilter" combo box.

This doesn't appear to work and I suspect I'm making a silly mistake but
I
can't figure it out, the filter action looks like it works but it doesn't
filter on the "Key Deliverable" selection.

The filter "All" was easy as I simply used the "ShowAllRecords" Action
and
it works OK.

Any help would be appreciated and thanks in advance.

John Taylor
Click to expand...
Click to expand...
 
Back
Top