Difference in selection and ordering on RecordSource compared to using Filter and Order property (1 Viewer)

[Josef P.]

The first query would select potentially thousands of rows but the second selects only 1.

It depends on how the form is opened. Test scenario see #13.
Once it is open, it does not matter whether you select the data records with a changed RecordSource or Filter property. Both variants generate the same retrieval in the SQL server.

Example:
Open form with id = 4, later (e.g. after user action) change to id = 12

a) Form with Recordsource stored query (incl. where):
1. update query: select ... from Table where id = 4
2. doCmd.OpenForm "xyz"

change to id=12
3. update query (or recordsource property): select ... from Table where id = 12

b) Form with recordsource: select ... from Table (without where)
1. docmd.OpenForm "xyz", , , "id = 4" (WhereCondition parameter sets the Filter property)

change to id =12
2. Me.filter = "id = 12"

 

[Pat Hartman]

@Josef P. I can't follow your logic. I can't tell what you are changing. How are you "change to id = 12" for example. Are you modifying the query? Logically, you would never use a hard coded query anyway so maybe a more realistic example would be better. On my forms, the RecordSource query references a control:

Where Somefield = Forms!myform!SomeField

So the form opens empty since Somefield is empty. Then the user enters a search argument and the AfterUpdate event executes a requery to get the recordsource to reload.

 

[Josef P.]

It doesn't matter how the value gets into the query.
Even with your variant with the control reference, the server will only receive the query with the value from the control, as it has no access to the form control.

My statement is only that the query variant and the variant with the use of the Form.Filter property arrive identically in the SQL server.

 

[Pat Hartman]

@Josef P. If you don't load the whole unfiltered recordset, you don't see the values in the filters. I guess, Access figures out that if you type the value, it should send a query with that criteria to the server since there is nothing local to filter.