Stumped Filter Not Working - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Stumped Filter Not Working

Kyle Rapp

2 years ago

Hey Guys, I have a customer table and a boats table, with a one-to-many relationship.  (one customer to many boats)  I have a query that includes both tables and has criteria to limit the query results to certain customers based on both customer and boat criteria.  The purpose of this query is to use it as a filter on the Customer form.  If I run the query manually, it works great.

Next, I created a button on the customer form with the following code in the OnClick event:  DoCmd.SetFilter "MyQueryName"  (I'm positive the query name is correct)

When I open the form and click the button, I do not get an error message but the filter does not work.  It's showing me all records instead of the expected query results.

Any idea why the button fails but running the query manually works fine?

Thanks,
Kyle

Kyle Rapp OP @Reply

2 years ago

Hey Guys, update. I've also noticed that when I click the button to set the filter, it asks for a parameter that I use in a macro for some other command buttons, even though I have no macro assigned to this button. All I have on that button is one line of DBA code. This is so weird. I'm starting to wonder if something is corrupt. By the way, I have 2 other buttons on the same form that use similar code that work perfectly so I'm at a loss.

Thanks again,
Kyle

	Adam Schwanz @Reply 2 years ago
I hardly ever use filter, but I don't think you can just apply a query to it, I think you have to apply the criteria to it. Like `Me.Filter="Number>5" Me.FilterOn=True`

	Kyle Rapp OP @Reply 2 years ago
Hi Adam, I use it with other buttons on the same form and on other forms and it works. Microsoft documentation for both the setfilter and applyfilter methods states that you can use a query to filter a form. In that case, you leave the criteria blank and just reference the query name in double quotes. I'm really stumped on this one.

	Adam Schwanz @Reply 2 years ago
Interesting, so what is the query? The same tables as the recordsource on the form just with some criteria? Could you just type in the criteria like my example? You say you got a parameter popup, was that something you've made yourself like [Enter Your Date] or was it like a field in the query doesn't match the form like [CustomerID]?

	Kevin Yip @Reply 2 years ago
If you run DoCmd.SetFilter "QueryName", it will simply set the filter to be the WHERE condition of the query. If the query has no WHERE condition, the filter will be blank, meaning no filter, and that is probably why you see all records.

Kyle Rapp OP @Reply

2 years ago

Adam, the form's recordsource is the Customer table. The query that I'm using as the filter is based on the customer table and the boats table. I've added fields to the query to display certain fields. I've added the customerstatus field from the customer table and the boatstatus field from the boats table, both with criteria set to "active". I've added the boatname field from the boats table as a count and then the criteria for this count field is >0. So, basically the query result is a list of customer names, filtered by active customers who own at least 1 boat, where the boat is in active status. Again, it works great in the query...just not when I try to setfilter with a button on the form. All I'm doing is docmd.setfilter "queryname".

Kyle Rapp OP @Reply

2 years ago

Hi Kevin, I'm doing the where clause in the query by specifying criteria in certain fields. When I click the button on the form, it should be setting the filter of the loaded form to the query, which is already filtered because of the criteria in the query fields. I even tried using a macro to set the filter (vs VBA) and i had the same results, even though in all cases, running the query manually works perfectly. BTW..thanks guys for the help.

Kyle Rapp OP @Reply

2 years ago

Adam, sorry I forgot to respond to a portion of your question, regarding the parameter. This is a really odd one. I created this button from scratch and all that is behind the button is 1 line of vba code. (docmd.setfilter method)

The parameter that comes up is a parameter I created in a macro for a totally different button. It just says "Enter part of the name or leave blank to see all customer records". That macro is not assigned to this button so I have no idea why its coming up.

	Kevin Yip @Reply 2 years ago
A query that returns the right results doesn't mean it has a WHERE condition. For instance, Query1 and Query2 below both return the same results, but only Query1 has WHERE, so only it will work with SetFilter: Query1: SELECT * FROM T WHERE A = 1 Query2: SELECT * FROM Query1

	Richard Rost @Reply 2 years ago
Instead of setting a filter, you can also change the RecordSource property.

Kyle Rapp OP @Reply

2 years ago

got it thanks guys.  I was doubtful it was going to work correctly so I ended up creating a field in the table for activeboatcount.
Then in the form's beforeupdate event I'm setting the value of that field to a Dcount, where the boat owner equals the customerID on the form and the boat status is active.  Then, i just used the setfilter with a wherecondition using that field, instead of referencing the query.

If I do end up wanting to make the query work, would I jusgt add the where clause to the setfilter line in VBA?  Or do I need to modify the SQL statement in the query?  I'm not real good with SQL so I avoid using it when I can.  (I work in query design view but sometimes will look at the resulting SQL and try to understand what its doing)

Thanks again guys,
Kyle

	Richard Rost @Reply 2 years ago
Just replace the RecordSource with the SQL from the query, or the query name itself.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View