should I use query or filter in my search form? 2

[NeilT123]

After much googling I have found 2 ways to create a dynamic search form which works for me.

In the first method the search box on the main form updates a hidden text box on the main form which is then used in a parameter query to fill the sub form. In the second method the code behind the search box is used to create a filter and then the sub form is filtered.

Both methods work as I want them to but I wondered if there was any advantage or disadvantage to either method?

Thank you

 

[MajP]

I think you may get some different opinions based on personal preference, but I have never really see a discussion comparing the efficiency of a filter versus creating a new query or using a parameter query. And unless the result set is so large and complex that efficiency is an issue there is really nothing to worry about since both work. I prefer to do it in code using a filter or creating a query string. It is easier for me to debug and see what is going on and I find it more flexible. If I am debugging someone else's code that uses a lot of parameter queries it takes me a long time to figure out how all the pieces come together. I personally never ever use parameter queries because I find them cumbersome to write, hard to debug, and requires fipping between vba and the query builder to debug. But some people love them. I am a way better coder than sql writer other people are reverse. So I think either is fine depending on what you prefer.

 

[MajP]

Forgot to mention. One of my favorite "tricks" in Access is that you can link a subform to an unbound control. Most people think you can only link it to a bound field on the parent form. So you can have a combo box or textbox and link to that. Without any code when you select a value in the unbound combobox it will filter the subform.

 

[strongm]

It can depend on the backend. If your backend is Access itself, then it is pretty much a matter of personal preference as MajP suggests.

However if the backend is on a remote server (e.g the corporate SQL Server), then a query is generally more efficient, since a filter requires the entire source recordset to be downloaded to the local machine whilst the query just downloads the required subset.

 

[NeilT123]

Thank you for the responses.

I can understand that a query reduces the record set and also that using code is easier to test but as my DB is not huge and I have not seen any discernible performance difference between the two methods I think I will stick with the query route as it is a bit easier for me.

MajP, how does your “trick” work please?

 

[MajP]

Assume you have a combobo box on the main form and it has a list of values. The subform has records with a field that contains matching values. Normally you would link mainform to subform by field names. But you can instead link by control name, and it figures it out. So something like this in the subform control properties:

Link Master Field: [cmboEmployeeID]
Link Child Field: [employeeID]

Where employeeID is a field in the subform and cmboEmployeeID is a combobox that holds employeeIDs. If I pick employeeID M1234 then all records in the subform matching this will filter. No code necessary. Does not work in the case where you want to show all values if not value is selected in the combobox. Then you would need some extra code.

 

[1DMF]

As others have said, it really depends on what data you are retrieving and where it is.

Strongm & MajP have covered this, but as you haven't stated where your data is, there is another massive caveat if using MS SQL as a back-end.

If you have large tables with joins in your query, you need to create views in SQL or SP's / TVF's as Access will retrieve all records in all tables in the join then perform the Cartesian join and then apply your where clause.

I improved performance 100 fold when I changed some of my queries to SQL views and linked to the views in Access instead.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[NeilT123]

Hi 1DMF, both front end and back end are in Access 2007

 

[1DMF]

Then what I said doesn't affect you

I do things slightly differently, as I have MS SQL as a back-end.

My search form uses :- A SQL SP, a cloned recordset via a class object I built, and uses 'filtering' to narrow down displayed results as the user types in the search boxes provided.

The data-set is 20,396 records with 10 columns displayed on a datasheet view form.

It's not the best search GUI in the world, but boy is it fast!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music