do the linkchild/master subform properties filter the underlying data or apply criteria?

[CJ_London]

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

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?

 

[strive4peace]

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]

@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