Form Loads slowly caused by faulty data???

[harterc1]

I have a form containing a listbox that when a line is selected in it, it opens another form containing data based on the selection (using a query that is dynamically altered for the new form's recordsource)

ANYWAYS, the form takes about 30 seconds to load.

The tables involved in the recordsource query total about 12,000 records which isn't nearly enough to slow the process down this much.

I set breakpoints and traced the code to the point where the program attempts to open the new form using the DoCmd.Open "formName" command.

At that point is when the program freezes for 30 seconds.

The new form's load and open events aren't even touched during that time period. They are executed once the 30 seconds are up.

. I also noticed that if i take all the contraints off the tables that are involved in the query, the load time is 3-5 seconds!

let me know of anything that may help me diagnose this problem please. maybe commenting on process flow or something.

[puppydogbuddy]

I have a form containing a listbox that when a line is selected in it, it opens another form containing data based on the selection (using a query that is dynamically altered for the new form's recordsource)

ANYWAYS, the form takes about 30 seconds to load.

The tables involved in the recordsource query total about 12,000 records which isn't nearly enough to slow the process down this much.

I set breakpoints and traced the code to the point where the program attempts to open the new form using the DoCmd.Open "formName" command.

At that point is when the program freezes for 30 seconds.

The new form's load and open events aren't even touched during that time period. They are executed once the 30 seconds are up.

. I also noticed that if i take all the contraints off the tables that are involved in the query, the load time is 3-5 seconds!

let me know of anything that may help me diagnose this problem please. maybe commenting on process flow or something.

Here are some great tips and references to check out.

1. Turn off problem properties:
http://allenbrowne.com/bug-09.html

2. Visit Tony Toews’ Web Site for Performance Tips
http://www.granite.ab.ca/access/performancefaq.htm

3. Tips to Speed up your Access 2000 Forms

From the tips page of aadconsulting:
A tip from Mark Plumpton of www.customdata.co.nz

I have a form that took 85 seconds to save. After applying the technique below it reduced the save time to just a couple of seconds. It also reduced my compacted database size from 5mb to 4mb after applying this technique.

This is the tip: Load the form, subform, combobox and listbox record sources at run-time. That's it. You will achieve dramatic performance improvement and reduced database size.

Here's the technique: Delete the SQL from the RecordSource and RowSource properties of the form, subforms, comboboxes and listboxes. Now in the Form_Load event load the appropriate SQL as follows ...

Private Sub Form_Load()
Me.RecordSource = "qryLargeTable"
Me.txtSomeField.RowSource = _
"SELECT SomeField " & _
"FROM qryLargeTable " & _
"GROUP BY SomeField " & _
"ORDER BY SomeField;"
End Sub

It also pays to clear the record sources in the Unload event as sometime these get saved with the form in Access 2000.

Private Sub Form_Unload(Cancel As Integer)
Me.RecordSource = ""
Me.cboFindRecord.RowSource = ""
End Sub

4. Tips to Improve Subform Performance
Also from the tips page of aadconsulting:
http://www.aadconsulting.com/aadtips.html

Base subforms on queries rather than tables. Include only required fields from the record source.
Index all the fields on the subform that are linked to the main form. Indexes speed up the matching of subform records.
Index any fields used for criteria such as where a subform is based on a parameter query.
If you are linking on multiple fields, add a calculated field to the main form that concatenates the fields. Then, create a calculated column in the subform's RecordSource property query with the same expression.

For example, to link to the subform on an Employee ID field and an Order ID field, add a text box to the main form with the following properties:

Name: EmployeeIDOrderID
ControlSource: =[EmployeeID] & [OrderID]

Next, add the following field to the query that the subform is based on:

EmployeeIDOrderID: [Employee ID] & [Order ID]

Then, link the main form and the subform on the concatenated field rather than on the two individual fields. The subform properties might look as follows:

LinkChildFields: EmployeeIDOrderID
LinkMasterFields: EmployeeIDOrderID

Because Access only has to compare one criteria to return the subform's recordset, the subform's performance should improve. Set the subform's DefaultEditing property to Read-Only if the records in the subform are not going to be edited.
If your subform is a continuous form and contains combo boxes, explicitly justify the combo box in the subform's form Design view. This prevents Access from determining the proper justification of the combo box values for each record and thus speeds the display of subform records which have combo boxes.
5. Dynamically load Subform on Page Change of Tab Control
http://www.databasejournal.com/featu...le.php/3599781