Prevent Subform Refresh

Posted on 2011-10-26
Last Modified: 2012-05-12
I have a rather complicated form with a large number of subforms. Calculations on one subform require that values on the parent form be updated. This occurs after any variable on the child subform is changed. After each update, I refresh the parent form to display the results.

However, most of the subforms contain historical data. And if there is a considerable amount of historical data, i.e., 30,000 records, each refresh becomes painfully slow.

Is there a way to refesh just the parent record without refreshing all the child subforms? Most are static and don't need to be refreshed. I have tried unlinking the subform, disabling the subform, and limiting the recordset to the last 500 records. But none of these options seem to help.

Any thoughts?
Question by:dwcummings
    LVL 77

    Expert Comment

    If you refresh the parent form as a form then I don't thibk there is any way to avoid the subforms refreshing.

    If it is possible, then just  requery specific controls on the main form.
    Provided these are not involved in the main/sub links then nothing else should happen.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Not sure, but you could try creating temp tables out of the subform recordsources...
    Then use those as the source for the subforms...
    You could have a system to create the temp tables on the DB start-up...

    In a broader sense, ...What's the need to display so much historical data in the subforms.
    Try limiting the subform data so that a requery does not take so long.

    Main Form=Customers
    Sub Form=Orders
    The source for the subform could be something like this:
    SELECT *
    FROM Orders
    WHERE Year(OrderDate)=Year(Date())
    ...This should only display only Orders from the current year...

    Just throwing this out there...

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Also note that the design elements of the form itself will contribute to any apparent "Slowness".
    So just having a main form with "a large number of subforms", will make the form "Slow"
    Do you really need to see *all* of the subforms, for each main form record, all situations?

    So try reducing the number of subforms by making specific Main/subform combinations for different purposes (if possible)


    LVL 3

    Author Comment


    I tried limiting the history by selecting the top 500 records. It made a slight improvement, but not enough. I am not sure if a date range would result in a faster search than specifying the number of records to be returned.

    There are a lot of things that are non standard about this form, including the updating of parent fields from child calculations. Unfortunately, the users want more subforms and more information. However, your comments triggered a thought. Most subforms are located on tabs. If I eliminated the tabs and open forms with historical data when the need arises, they won't be refreshed each time the parent record needs to be updated.

    I am not sure how to award points on this issue. I have tried all of your suggestions before I made this post. But, it was your last comments that triggered my thought about getting them off the main form and onto separate forms.

    Thanks for your help.

    LVL 74

    Accepted Solution

    <However, your comments triggered a thought.>
    That was the whole purpose of my post!
    ;-) get you thinking about ways simplify the form to increase performance.

    As far as the points go, a Split is fine with me.
    Accept Pete's post as the "Accepted solution" (as he actually answered your question as posted), ...and mine as the "assisted" solution"


    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    What happened to accepting Pete's post as the Accepted solution...?
    This would only be fair...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    760 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

    13 Experts available now in Live!

    Get 1:1 Help Now