Problem filtering

[markmarko]

Hello,

So, I've got a form that's linked to many tables. I'm trying to make a
couple filtering controls.

So far, I've got a combo box that lists the possible filter methods (by
account#, Customer name, etc). Depending on which selection is made, it shows
a textbox(es) to enter filter criteria. For this question, let's assume user
selects Filter By Account #.

The Account# control on the form is called AccountNumber. The text box that
the user types into for filtering is called Filter-Account#.

Then there's a "Apply Filter" button. On Click, this calls a macro which
first ShowsAllRecords (to remove previous filters (not sure I need this, but
it seems so)). Then it applies a filter, depending on what has been selected
in the combo box, in this case Account#.

As a start, let's assume Filter by Account # is chosen. The macro then
performs Apply Filter, with WHERE
[AccountNumber]=[Forms]![Entry-SalesOrderTabbed]![Filter-Account#]

When I test it, it asks for the parameter Filter-Account#. I can't
understand why it can't see it. It's copy/pasted, so know it's not mistyped.

I've also tried it using a query instead of the WHERE, that seems to sort
the first time, but any subsequent filters use the orignal Filter-Account#,
regardless of what is currently typed into it.

Any advice is greatly appreciated!

 

[markmarko]

Whoops, sorry. The problem with the filter using the previous enter criteria
happens with the WHERE ApplyFilter. When I use the query, it returns no
records.

 

[markmarko]

Whoops again... Sorry, my brain is bamboozled.

The problem with it showing the orignal filter results on any subsequent
filter uses a temp variable (to get around the problem of it not seeing the
Filter-Account# control. I'd rather not have to use a temp variable, it's not
elegant, but I can't get it to work otherwise.

 

[Jeanette Cunningham]

Hi Markmarko,
There is an excellent example of how to use form filters with all the code
on Allen Browne's website. It is the article with the Search form.
www.allenbrowne.com

Jeanette Cunningham

 

[Guest]

Hello,

So, I've got a form that's linked to many tables. I'm trying to make a
couple filtering controls.

So far, I've got a combo box that lists the possible filter methods (by
account#, Customer name, etc). Depending on which selection is made, it shows
a textbox(es) to enter filter criteria. For this question, let's assume user
selects Filter By Account #.

The Account# control on the form is called AccountNumber. The text box that
the user types into for filtering is called Filter-Account#.

Then there's a "Apply Filter" button. On Click, this calls a macro which
first ShowsAllRecords (to remove previous filters (not sure I need this, but
it seems so)). Then it applies a filter, depending on what has been selected
in the combo box, in this case Account#.

As a start, let's assume Filter by Account # is chosen. The macro then
performs Apply Filter, with WHERE
[AccountNumber]=[Forms]![Entry-SalesOrderTabbed]![Filter-Account#]

When I test it, it asks for the parameter Filter-Account#. I can't
understand why it can't see it. It's copy/pasted, so know it's not mistyped.

I've also tried it using a query instead of the WHERE, that seems to sort
the first time, but any subsequent filters use the orignal Filter-Account#,
regardless of what is currently typed into it.

Any advice is greatly appreciated!

 

[markmarko]

Jeanette,

Thanks for the link! There's some great info there!

As for this problem, I was hoping to do it with built in 2007 macros, as
opposed to VBA, both cause I'm not used to VBA as it applies to Access and
don't really have time to get up to speed on it. Also, I don't particularly
want to mess with allowing code on client machines. It looks like that might
be easier in 2007, but VBA security has caused me endless headaches in the
past (Excel).

I really can't understand this problem. The I can tell that the input field
is getting the correct data, it's just that any time I run the filter with
other data in that field, it filters on the original data. Even after
ShowAllRecords, and also after setting the forms filter property to nothing.