Multiple Form Instances in Northwind2 and Query Criteria

[GK in the UK]

I open form instances with a somewhat clunky way of getting a single record PK into the recordsource query, so I was interested to see how it's done in Northwind2. To my surprise one of the two forms that are opened as instances has an unqualified query as recordsource. In Form_Load, there's a line Me.RecordSource = "select * from qryOrder where OrderID = " & dict("OrderID")

I applied similar code to my application, by taking the parameter out of the form recordsource query and having that (similar) line of code which I think of as a nested query. I fully expected the Recordset.Recordcount to be the unqualified query (7000+ records) until the code line. But no, it's 1, albeit it's the first row in the table. Once the line has executed, it's still 1 but the correct record.

But - if I put a Me.Recordset.MoveLast in Form_Load BEFORE I qualify the query, the recordcount is the number of records the query returns - sort of what I expected.

So it looks like only the speed of Access' execution is preventing the load of the entire query. Doesn't this compromise the Northwind2 method of qualifying the query, as in, Access is for a moment requesting the entire query and populating the recordset? In repeated testing in my application I did see a recordcount of 2 before I applied the criteria. Also, in a Sql Server scenario, does the server return the unqualified query result to Access before it applies the criteria in code?

 

[theDBguy]

Hi. Just checking, what do you mean by qualified and unqualified query? Were you referring to applying a criteria when you said you "qualify a query?"

 

[GK in the UK]

Yes, sorry, by unqualified I mean with no criteria or parameter, just returns all the records. But in Form_Load, criteria is applied and I'm suggesting it's too late, isn't it?

 

[Gasman]

Well if the record source is being set in form load, I woud expect no reason to set the form source in the form properties?

 

[Pat Hartman]

So it looks like only the speed of Access' execution is preventing the load of the entire query. Doesn't this compromise the Northwind2 method of qualifying the query, as in, Access is for a moment requesting the entire query and populating the recordset? In repeated testing in my application I did see a recordcount of 2 before I applied the criteria. Also, in a Sql Server scenario, does the server return the unqualified query result to Access before it applies the criteria in code?

Yes, it would. I'm not sure that Northwind was trying to show good client/server technique specifically. Forms should be bound to queries with criteria to limit the rows returned from the server. Rather than replacing the RecordSource at load, I would use the query with the criteria as the bound RecordSource. I think the developer was intending to remove the RecordSource before distributing so the form loads unbound. Then the RecordSource is added later. I would just bind initially to the query with the criteria.