Large table causing slow opening form

[JPritch]

I have a database with a table that contains 360,000 rows. I built a form with four boxes where a user can specify values to limit the result set. And instead of having a new window open with the results, I built a subform and placed it on the main form to display the results.

Here is how it flows:
Main Form -> user enters search criteria
Search Criteria -> feed as criteria in query
Query Results -> display on subform
Subform -> shows on main form

The query is setup to take the values from the main form and either use it if it's not null, or return all values if the field is null.

The problem I am having is that on opening the main form, Access is taking the four null values from the main form search fields, feeding them to the query, which is then feeding the subform. So 10 minutes later when the main form finally opens, I have 360K records displaying in my subform.

What I would like is to be able to open the main form instantly, specify my search criteria, then run the query, then have the query results populate the subform.

What do I need to do?

 

[JPritch]

Found a similar thread where a poster had two subforms on his main form, but he didn't want the subforms to load until after the main form opened and he pushed a button because it was slowing his form down.

Ruralguy suggested the following:

1)You should be able to leave the RecordSource of the SubForms empty and assign them on the click of your button.

2)You could also play with the Visible property of the SubFormControl if you like.


I tried hiding the subforms but the main form is still slow to open. Any idea on if suggestion #1 will work, and if so, how I would go about doing it?

 

[Jon K]

1)You should be able to leave the RecordSource of the SubForms empty and assign them on the click of your button......

Any idea on if suggestion #1 will work, and if so, how I would go about doing it?

You can place a Select SQL statement with a Where False clause in the subform's RecordSource e.g.

SELECT .......
FROM [TableName]
WHERE FALSE

Then in the code of the button, set the subform's RecordSource property to the original query:-
Me.SubformName.Form.RecordSource = "QueryName"


The WHERE FALSE criteria in the statement should suppress the loading of records when the form starts up.


However, for 360K records, Access still takes time to run the query and load the search results when the button is clicked.

You can speed up the search by building a SQL statement on the fly when the button is clicked to include only those criteria specified (using If ... End If), and assign the SQL statement to the subform's RecordSource property.

Me.SubformName.Form.RecordSource = strSQL
.

 

[JPritch]

Thanks Jon K!

Everything works the way I wanted it to now!