Speeding up Unbound Form Behaviour (Access 2002/20

[WebGenii]

I just thought I'd share some changes I've made to a form which really improved its' performance. Plus I'd be interested in your comments...

The form was taking 5-10 seconds to open and then 3-5 seconds to fill in information at each step in the form.

The initial design was an unbound form with two bound subforms, I used DSum and DLookup functions to fill textboxes with text and numeric information (additional information about the customer). Combo boxes allowed the user to select customer accounts/projects. List box controls used the result from the combo boxes to display the results of a query (Numeric information about transactions on the customers' current account/project).

Step 1. I removed the bound subforms, instead the information in the completed fields will be written by an SQL append query in response to the user clicking on a button.
Step 2. I removed all the DSum and DLookup sourced controls
Step 3. (This was the one that really surprised me). The list boxes using a query were also slowing performance (there were a number of them) even though, they were all sourced from the same query. So for both the list boxes and the DSum/DLookup controls I did the following:
I changed the query data source for the original combo box to include the information that had been generated by DSum/DLookup. I added a second (not-visible) combobox whose data source was updated with the numeric transaction information based on the selected account/project.
Using an onchange event allowed me to update each control with the information from the combo box. Once a customer/account/project is selected, the selected record from the combo box provides the information which is displayed by VBA in the form.
What was interesting is that this combo box uses the same query that fed the multiple list boxes previously. I've concluded that each list box ran the query separately, so that the performance improvement must come from the query running once only.

Now the behaviour of the form is much improved. It opens immediately, and information is displayed throughout the form immediately when a customer is selected.

[charlotte]

Yes, as you discovered, each of those controls runs its own copy of the query, and each query creates another connection to the source table. Aggregate functions like DSum and DLookup are very resource intensive and quite slow compared to other ways of retrieving the data. Another way of handling some of this would be to open a recordset in memory and search that recordset to find the appropriate data rather than having a second combobox. You could write data back to that recordset as well once the user had made his selection, eliminating the need for the append query, but that would require VBA code to update the recordset.

[WebGenii]

So, do you have a rule of thumb about when to use a recordset or is it your preferred method?

[charlotte]

It depends on the circumstances. If I am using a truly unbound form (no recordsource and none added later, all the controls unbound as well), I always use a recordset. It's also possible to have a form bound to a recordset, but not bind the controls. Subforms present different situations, since there usually isn't much reason for a truly unbound subform. I use queries to change data in other tables and the recordset to change data in the relevant table.