Slow form in MS Access

Posted on 2011-10-06
Last Modified: 2012-06-27
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?
Question by:dsoderstrom
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <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...


    Author Comment

    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.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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...


    Author Comment

    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?"  
    LVL 74

    Accepted Solution

    ""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, it just as slow?

    This is basic troubleshooting, 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"



    Author Closing Comment

    didn't really solve the problem
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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, 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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now