Macro not getting latest info



I am trying to add a combo box and button to a form which
will allow a user to select a value and then press the
button to filter on that value.

I have a macro which does a ...
ApplyFilter [myTable]![Status]=[Forms]![myForm]!

....where filterStatus is my dropdown selction (populated
from a support table).

It works the first time, but subsequent attempts to run it
always return whatever the first selected value was.

Pressing the "unfilter" button makes no difference - it
still refilters to the dropdowns first selected value
HOWEVER doing a right-click Remove Filter/Sort anywhere on
the form DOES work and the next value selected OR the
current value selected will successfully apply that choice
for the filter.

In none of these instances does the dropdown show anything
other than the expected (most recently selected) value.

That new value then gets locked in and changes to the
combo value get ignored until I do another right click
Remove filter, or close the form, or update the macro, or
update the form.

I've tried adding a requery of the combo box in various
places and it doesn't make a difference.

A colleague tried the same functionality on the change
event of the combo (of a different form on a different
database) so that it wasn't using a seperate button to
trigger the functionality and got exactly the same
results - the new value was ignored and only the first
selected value was used in the ApplyFilter command.

I can live without this function - but it seems pretty
fundamental to the way this is supposed to work so I would
really like to know, not just a way round the problem but
also what the hell's going on and why something so
seemingly straightforward doesn't work the way it appears
that it's supposed to.

To recap...

Open form; shows all records.
Select statusX; press btnFilter; shows statusX records
Select statusY; press btnfilter; shows statusX records
Press remove filter; shows all records;
Select statusANY; press btnFilter; shows statusX records
Right click Remove Filter/Sort; shows all records
Select statusANY[optional if already selected];
.... press btnFilter; shows statusANY records




Further debugging shows this problem is not in the Macro
or the dropdown but in whatever's behind the ...

I'll cross post this to the VBA

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question