why is query so much faster using value from form as criteria vs using where condition to open form

[tagteam]

First of all my table is in sql server so I know that may affect the answers.



I have a form where I let the user enter a railcar id and then show a list of invoices for that railcar. If I create the query to have a criteria of a value on the form, something like this, [Forms]![NavigationForm]![NavigationSubform].[Form]![RailcarID] it will load in 1-2 seconds.

However, If I create a variable form the value on the form and pass it to the form as a where condition it takes 25 seconds. Something like this

Dim FAvar As String
FAvar = Me.RailCarID
DoCmd.OpenForm "f_AllVendorInvoices-Where", acNormal, , "[RailcarID] = " & Me.RailCarID & ""

Why is there such a big difference in speed and is there a way to speed it up and still just use the where condition?

[kd2017]

Is the server local? How many records are in the invoice table?

Purely a guess but maybe the second method is first getting all the invoice records and then access is filtering them down by the railcareid where as the first method is using standard sql and letting the server on the back end handle the processing.

[Welshgasman]

That is what it does, doesn't it, it just sets a filter when opening the form?
Remove the filter and you should get all the records?

[tagteam]

Yes, the server is local and there are about 600K records on the table. It must be that with the filter criteria in the query access looks at the SQL table and only searches for records with that RailcarID but using the where clause it must try to pull the entire table into access and then look for the railcarid. So like welshgasman says the where condition is loading the entire table, then filtering for the selection. Using the select query i guess it filters on sql server (much faster) then only loads to access the selected records.

[CJ_London]

The where parameter of docmd.openform is poorly named - it is a filter, not a criteria. As WGM says you can remove the filter once opened to show all the records. Instead move your where parameter value to the openargs parameter and in the form open event put something like

Me.recordsource = Me.recordsource & “ where “ & me.openargs

now back on my computer (difficult to write code on a phone) so better to have the form recordsource as

SELECT * FROM myTable WHERE False

so no records are returned

then in the form load event

Me.recordsource = replace(Me.recordsource,"false", me.openargs)