do the linkchild/master subform properties filter the underlying data or apply criteria? (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2013
Messages
16,553
The reason I'm asking is that I've was told many years ago when first setting out on the Access learning curve that they apply a filter and reviewing a number of posts on this forum, many others think the same thing. Typically 'as a demonstration remove these properties and all the records are shown, ergo, a filter was applied'

However something I've been working on would indicate that they actually apply a criteria.

For example:-
for a main form called frmCustomers and subform called sfOrders
linkchild=customerFK
linkmaster=customerPK
subform recordsource="Orders" (i.e. the whole table)

result - as expected you only see orders relating to the relevant customer

however if you look at the sql for the query Access uses it is clearly applying a criteria

Code:
PARAMETERS __CustomerPK Value; 
SELECT DISTINCTROW * 
FROM Orders  AS frmCustomers 
WHERE ([__CustomerPK]  = CustomerFK);

so the subform recordsource is aliased as the mainform name, and the linkmaster value is passed as a parameter, identified with a prefix of two underscores.

If you want to see this query, you need to first find it - look in msysobjects for a record where the type=5 and the name looks like this
~sq_cfrmCustomers~sq_csfOrders

it is structured as ~sq_c{mainformname}~sq_c(subformname}

then use a bit of vba code to see the sql

debug.print currentdb.querydefs("~sq_cfrmCustomers~sq_csfOrders").sql

I can't think of a way of confirming whether this is then reinterpreted as a filter or applied as a criteria. Any suggestions gratefully recieved.

So the other question is that a subform load event fires before the mainform is open - so if that sql is executed before the main form is open, the parameter is not available so are no records returned or all of them? My assumption would be no records.

I may have been operating under a misunderstanding all these years since the terms 'filter' and 'criteria' are often mixed up (the openform 'where' parameter for example applies a filter, not a criteria)

Can anyone provide any further light on this subject?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 19, 2002
Messages
42,981
I never examined the SQL being sent to the server for a subform. I just assumed it would include criteria so that the server would return only the set of records needed by the form. Retrieving the entire set of rows from the table would defeat the purpose of using a RDBMS and letting it do the heavy lifting.

When people who use Access techniques like form filters convert their BE to SQL Server, they are flabbergasted by how slow the app becomes when they expected it to be faster. Once I started using RDBMS' with Access 25+ years ago, I stopped using form filters for this very reason. Even the apps I create that will probably never be upsized, use good client/server techniques because they work equally well with Access and I don't have to make major mods should the app ever need to be converted and I can use only one technique which is easier on me.

One app that I actually sell to the public has the option of ACE or SQL Server. The app has only one paragraph where there needed to be differences and that was unique to this app. The app comes with a set of test data that is connected to various word and excel documents. So, if the user wants to restore the test data to its original state, I delete the data for the test company and then reload it. In order to make everything connect, I need to reload the original autonumbers. To do that requires a special command when the BE is SQL Server to allow identity column values to be appended. Access just lets you run append queries that include autonumber values as long as no duplicates are caused.

Interesting regarding the Where clause of the OpenForm/OpenReport. Even though there are two options - filter and where, the where applies a filter. Even worse, puting the criteria in the Filter option, doesn't filter at all. Looks like a bug to me.

Everyone who is interested, please try this and use the Feedback feature to report this.
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 04:19
Joined
Apr 3, 2020
Messages
1,003
good question, @CJ_London

To me, it makes sense that the subform is only pulling records that it needs. An application I wrote in Access, in a short time, took a lot longer to speed up when the BE moved to Azure. In the end, the mainform was just pulling one record, and the RecordSource of the subforms didn't need to be changed to make it fast.

Interesting to see the SQL you posted. Even though subforms load first, when the mainform record is changed, they'd be quick to get different records that way.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2013
Messages
16,553
@Pat Hartman - the openform/report actually requires the name of a query for the filtername parameter. Never found a use for it myself :). I always recommend users set a form to return no records (ie. a criteria WHERE False or WHERE 1=0) in the recordsource then pass the criteria in the openargs parameter and in the form load event, replace "WHERE False" with "WHERE " & openargs.

@strive4peace agree it makes sense - it's just that newbies are told it 'filters'. To me, there is a whole world of difference between a filter and a criteria. And it's not clear whether the entire recordset is loaded initially only to be disposed of when a criteria can be applied - which I guess would be on the main form current event.

Think I'll need to get a very large dataset which takes a measurable time to load and try to get some timings
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 19, 2002
Messages
42,981
And it's not clear whether the entire recordset is loaded initially only to be disposed of when a criteria can be applied
If the entire recordset is loaded initially, you would see the query being sent. If you didn't see it, Access didn't send it.

I probably should have mentioned that the reason I didn't know that the Open method used a filter instead of actually modifying the where clause, is because I don't use this method. I use a where clause in my RecordSource that references the form that opened it to filter records.

This can be awkward if you open the same form/report from multiple places. I had one app where that became a problem so I settled on a hidden form that opened at startup. When I had arguments for forms/reports, I put them as controls on this "hidden" form and all the queries referenced the hidden form. You'd be amazed at how few arguments most applications have for standard filtering of forms/reports. It is only my special search forms that use the exotic attributes and those forms write the query using VBA and pass the whole string to the form/report that gets opened.
 

Users who are viewing this thread

Top Bottom