Need to Speed up Form on Opening, Add Filter?

[Jlacey91]

Hi I'm relatively new to building forms.



I've created a form with a sub-form that forms a fairly large query.

In the form I have a combo-box the is used to select a unique identifier within the sub-form which works great.

However on opening the form it loads the details for every unique ID which is very slow.

I need a way to stop the form from loading the information for every Field in the query, either using a filter to load one only? Or stopping the sub-form from loading until the combo box has been populated?

Or is there something else?

Many thanks for any help.

Jamie

[RuralGuy]

A great link for improving performance: http://www.granite.ab.ca/access/performancefaq.htm

[ItsMe]

In Access, there is a property of Forms and Reports called the Filter property. Using this property affects what the User will see. However, the form and or report will retrieve a larger recordset from the table(s). In other words, a query that retrieves every record can be used as a Form's Recordsource. Then, you can use the Filter property to 'Display' certain records and 'Hide' other records.

If you know in advance what records you wish to view, you can use WHERE criteria within a query. If a form's recordsource is based on a query that uses a WHERE clause, the form will not retrieves all of the records in the table(s). The WHERE clause will be evaluated before the dataset becomes part of the Form's Recordset.

You can practice this idea by building your own queries via the Query Designer. By adding a table to the Design Surface of a new Query Object, you will be able to add fields to the Grid at the bottom of the Query Designer Window by double clicking or dragging the name(s) of a field(s) from within the table. Once you have collumns defined for your query, you can use the Criteria field in the Grid to type expressions. These expressions will be converted to a WHERE clause in your new query.

Two examples of expressions you could type might be ...

1. > 20
2. Not Like "Garbage"