• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Slow form in MS Access

I have a form in Access that is slow to open and the response it also slow when moving from record to record and even when going between lines on a subform withing the form.
I suspect it may be because of the number of combo boxes in the form and also in fields on the subform.
I really don't want to do away with these combo boxes because they make data entry much easier.
My question is, would it help to redo some of these combo boxes and use static data entered into the combo box control rather than having the combo box based upon a table or query?
  • 4
  • 3
1 Solution
Jeffrey CoachmanCommented:
<I suspect it may be because of the number of combo boxes in the form and also in fields on the subform.>
Shall this number forever remain a secret, or can you share it with us...?

Certainly the number of control like comboboxes will affect performance.
But there are *many* more factors that could contribute to "Slowness"
(Recordset complexity, Coding, number of records, Linked tables, Images ...etc)

So can you provide a bit more about the design and purpose of this form?

Finally can you quantify "Slow" in real chronological terms...

dsoderstromAuthor Commented:
This is a form for entering fairly complex Engineering Change Orders.
The form has three tabs.
There are three subforms.
There are 4 combo boxes on the man form.
There are 12 combo boxes on fields in subforms.
The form takes about 10 seconds to open.
Clicking or arrowing between records on the subform with the majority of the combo boxes takes about 8 seconds.
Jeffrey CoachmanCommented:
Nothing there would explain a 8 second delay...

Are these forms Unbound (no recordsource, records are loaded via recordset)

What is the recordsource for the main form and the subform?
Do the recordsources contain any linked tables?
Do these comboboxes combined, display more than 10,000 records total for all of them? (The default limit for one combobox is 1,000)
Any code on the Open/load event of either form?
How about the current event?
is the database split?
Are you compacting the DB and compiling the code on a regular basis?
How Big is the DB before and after compacting?
Is the DB split?
Are you running this DB over a standard network?/VPN?/Citrix?/Wireless?

So, again, as you can see, there are a lot of possible causes, so you really cant say "Slow" without explaining the details of the database...

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

dsoderstromAuthor Commented:
The recordsource for the main form is  an Engineering Change Order (ECO) header table.  It has a unique key which is the ECO number.
The recordsource for the subform containing most of the combo boxes is an ECO detail table.  It is linked to the main form via the ECO number.  
The ECO header and detail tables are linked access tables.
The combo boxes do not display more than 10000 recordes combined.
I do have some code on both the open and current events that enables or disables controls on the form based upon a status field in the ECO header table.  But this code is not causing the delay.  Using debug I have set a toggle breakpoint on the very first line of this code and the delay occurs before it reaches this point.
I do run compact and repair on a regular basis.  The size of the database after compacting is around 4800 KB.  
Most of the tables in the database are linked tables but there are a few small work tables that are local.
I am running the database on a standard network.
I realize that there could be various reasons that the form is slow.  I'm trying to eliminate these causes one at a time.  My original question was "would it help to redo some of these combo boxes and use static data entered into the combo box control rather than having the combo box based upon a table or query?"  
Jeffrey CoachmanCommented:
""would it help to redo some of these combo boxes and use static data entered into the combo box control rather than having the combo box based upon a table or query?"  
No way to know *exactly* without the actual DB in front of me...

What OS?
What Service Pack..?
Is this for all users?
All Machines
...we could go on forever...

You will have to eliminate one issue at a time....

Which brings up the question:
Did this form always run slow?, or is this a new occurrence?
If this is sudden, then what changed?
If this has been gradual, then what is increasing?

If you create a simple form (based on the same recordsource) with less controls, is it just as slow?
If you create a simple form, with less records is it just as slow?
If you create the same form *without* the comboboxes, is it just as slow?
If you just used the main form, ...is it just as slow?

This is basic troubleshooting, ...you will have to eliminate one aspect at a time until the performance reaches an acceptable level.

How much memory is installed? How much is actually *Available* (from the task manager)?

Finally, remember that sometimes the Form (and all of it's aspects) will just end up being "slow"...
(Put 8 people in a VW Bug, and it will be Slow, no matter what you do)

Perhaps I am missing something here, but again, without me being in your shoes and knowing *everything* about the DB, it is impossible to pin down the exact cause of the "Slowness"


dsoderstromAuthor Commented:
didn't really solve the problem
Jeffrey CoachmanCommented:
I understand...

I am sorry that I could not do more to help with this specific issue.

Just bear in mind that, we can't really "Solve" a question like this without having Access to the exact environment.

"Slow" is a very generic term, and as I have demonstrated, ...it depends on a great many factors.

If you follow the troubleshooting steps outlined above, you will be able to eventually get to the heart of the issue.

Here are the top links to resources that address performance issue in Access databases:



Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now