Query over network takes forever - lets try a filter

[twildt]

Right now I have a query that returns records based on input of a textbox of a form.



I dragged this query onto the form so it is a subform view of the query. datasheet style.

When the user enters the account number into the text box I have a control button that runs a "requery". The query is setup in the criteria section to use the account number entered on the form so it automatically returns the data they need to see. Works great. Returns multiple values if they exist.

Problem is it's stored on the network. The query ALWAYS runs when you first open the form because it's dragged onto the form as a subform. Well there isn't any data inside the textbox yet because your just now opening the form! So the form takes a good 1-2 minutes to open that first time.

I noticed when I take the criteria [form]![myform]![textbox] out of the query the form opens immediately with all the data shown in the subform. I could then just filter the data I need instead of using criteria in the query to ask for it from the textbox.

Well my question is how can I use that textbox(after update event) to filter my subform automatically for the user?

Or is there a way to fix my subform query to not run the query at form startup?

Thanks in advance.

[Bob Fitz]

You could try deleting the Record Source of the form used in the subform control and setting it again in the After Update event of the text box. Code would be something like:

Me.[NameOfSubformCONTROL].Form.RecordSource = "NameOfQuery"

Be aware though, that [NameOfSubformCONTROL] may not be the same as the name of the form as it is displayed in the database window.

[twildt]

That worked great!

Thanks. Solved.

[Bob Fitz]

Glad to help. How long does the form take to open now. Just curious.

[twildt]

It's instant. Even though it shows all the data, not having to run the query(with null value to lookup) made all the difference.

Edit: Sorry, it doesn't show any data at startup. But it's still instant. Works perfect.

[Bob Fitz]

I assume that instant will be fast enough.