Critique this search method

[munkee]

Hi all,

I am using a controls placed on my main form to build up search criteria to populate my subform.

The basic selections create a dynamic WHERE for my sql statement, dependent on selections made.

Now what I want to see critiqued is my final lines implementing the code which are:

Me.subfrmSearch.SourceObject = "subfrmSearch_OnCase"
        Me.subfrmSearch.Form.RecordSource = "Select * FROM qrySearch_OnCase WHERE " & strwhere

The subfrmSearch can take on 2 different stylings for the output hence why I am setting a specific sourobject at run time. This is purely for aesthetics and the fact I am using a tab control on my main form to differentiate between two types of searches that can be performed.


Anyway, more importantly the second line of code where I set the subform recordsource to be a sql string of:

Select * FROM qrySearch_OnCase WHERE " & strwhere

strwhere is the dynamic string for the where clause. But have I used the most optimal method here for returning results?

I have read a couple of responses to similar questions with the "always make sure you only return what is needed". My code follows this due to the WHERE clause returning only the ROWS which are required, I have filtered out any un-needed columns in my compiled query qrySearch_onCas e and for me that is a nicer format to be adding in any extra columns I may require from my original table.

Once again though is this the best way? Or should I just base my subform record source on the compiled query and filter THE FORM, not run a sql statement?

[NeoPa]

So, the options are really :

1. Use a predefined QueryDef which is designed to filter the data against the values in controls on your open form.
2. Create a more precise filter string and apply that as SQL to the RecordSource of the form.

The first is somewhat clumsy, but benefits from precompilation and optimisation of the QueryDef (Assuming it was always saved after any relevant changes to design were made to the db).

The second is more precise and tidy, but needs the code to be optimised (automatically) before executing.

I'd choose the third option!

The most straightforward option for me, would be to leave the RecordSource pointing directly to the table itself, and change only the .Filter parameter. Your code would need to change only very little, and it would be more logical (as you are changing the .Filter property to change the filtering) as well as leaving the Record Source itself alone.

I would expect the optimisation factor of using SQL in such a relatively straightforward manner to be minimal. This would apply to both #2 and #3 of course.

[munkee]

Strangely enough I had started coding this by just applying the where clause to the forms filter event. However I thought during loading the form or infact if I put off the loading of the recordset in to the subforms sourceobject I would be retrieving everything and then filtering. Surely on what possibly could be large datasets this could be a slow process?

Or have I got the wrong end of the stick and the filter occurs and brings only the filtered records back over to the subform.

[NeoPa]

No, you have it about right I'd say. However, if that becomes a problem you could design the form to start with to have a filter of simply "(FALSE)". That would ensure no records whatever are loaded, but whatever preamble is required to open the data set is run before your code runs to apply any filter. Does that make sense?

[munkee]

Thanks for this NeoPa it has been good to go through. I think most of the time as soon as I start thinking about doing any partial retrieval from a table I jump straight in to the "I need a query" mode. Whereas as discussed a well set up form filter can do just the same.

I think it has also shown a lack of understanding on my part by creating a querydef for what I want and then re-querying a query.. which is just silly it seems.

[NeoPa]

Every time you give it more thought and consider the process, you get more experience and understanding. This will make it easier to consider and decide in future. All good :-)