- 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
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?
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?