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.