Filter property or sql Where? OrderBy property or sql Order by? (1 Viewer)

[amorosik]

Which is the best way to select rows in the current recordset?
How do you filter the recordset to display on a form/report?
Are there any specific reasons for using the first or second method?
Personally, I almost always tend to use SQL string selections, which are then passed in their entirety as the data source
But sometimes I'm tempted by Filter/OrderBy.

 

[CJ_London]

Which way really depends on the circumstances. To reduce network loads and to limit what the user can see, use the sql string with criteria and sorts. Allow filters and sort if relevant to the task.

Personally I never use the where parameter of docmd since it is actually a filter

You can use the filtername parameter to select a different query with different criteria as the recordsource but it would still need to return the same fields. However I've never found a use for it that beats modifying the sql. It used to be that you could save a filter object within the form or report object to be called by this parameter but it is deprecated in later versions, probably went out with 2003.

Depending on the requirements I either modify the sql source before opening the form or pass a criteria as an openarg to add to the recordsource in the open event. Obviously this isn’t appropriate if users are allowed to open forms directly from the navigation window

 

[sonic8]

Personally I never use the where parameter of docmd since it is actually a filter

And what do you feel is the problem with that?

 

[Ken Sheridan]

In the majority of cases i will reference controls in a form as parameters in the query's WHERE clause. The following is a simple example in which the parameters are three unbound correlated combo boxes in the bound form's header section:

SELECT [FirstName] & " " & [LastName] AS FullName, Address,
City, Region, Country
FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)
INNER JOIN (Cities INNER JOIN Contacts ON Cities.CityID = Contacts.CityID)
ON Regions.RegionID = Cities.RegionID
WHERE Countries.CountryID = Forms!frmCorrelatedCombos!cboFindCountry
AND (Regions.RegionID = Forms!frmCorrelatedCombos!cboFindRegion
    OR  Forms!frmCorrelatedCombos!cboFindRegion IS NULL)
AND (Cities.CityID = Forms!frmCorrelatedCombos!cboFindCity
    OR  Forms!frmCorrelatedCombos!cboFindCity IS NULL)
ORDER BY Contacts.LastName, Contacts.FirstName;

Note how in this case each parameter bar the first is optional by virtue of the parenthesized 'OR parameter IS NULL' operations. This allows the user to drill down from country to city via region, with the form's recordset being reloaded at each stage by calling the form's Requery method.

 

[CJ_London]

And what do you feel is the problem with that?

It loads the entire recordset but prioritises the records to display. Click the filter button at the bottom of the form to show all records

Not good if you are a sales person reviewing their sales based on their id as a filter - click the filter button and they can see everyone’s sales

 

[sonic8]

It loads the entire recordset

It doesn't.

Not good if you are a sales person reviewing their sales based on their id as a filter - click the filter button and they can see everyone’s sales

That is indeed a problem in such a scenario.
However, if it is about security/permissions, I would integrate restricting the records in the underlying query, preferably on a database server.

 

[CJ_London]

doesn't.

Does - try it

r, if it is about security/permissions, I would integrate restricting the records in the underlying query,

That is what I was saying

 

[The_Doc_Man]

I had a case where I had maybe about six possible filters but once I ran data through the filters, it was always the same form. So I allowed the users to specify via combo boxes (or not, as appropriate) and then pick the form they wanted to see. Before I launched that form, I used the form properties for filtering and sorting. Since we had something like 80 distinct projects (actually, Navy departments) hosted at our site, the unfiltered form would be a little sluggish. Not to mention more likely to have "collisions" with other users.

Reports didn't work the same way because, as many folks know, reports tend to ignore your WHERE clauses and seek their own style of filtering nad sorting.

 

[Josef P.]

Personally, I almost always tend to use SQL string selections, which are then passed in their entirety as the data source
But sometimes I'm tempted by Filter/OrderBy.

If you look at the Jet showplan (or execution plan from SQL Server), you will see that using Form.Filter generates the same plan as modifying the SQL statement for the form's data source.
But: It's not possible to use Form.Filter and Form.OrderBy so that only one data query is executed. With the change to the SQL statement, this is never a problem.