?
Solved

Prevent Subform Refresh

Posted on 2011-10-26
6
Medium Priority
?
325 Views
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?
0
Comment
Question by:dwcummings
  • 4
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37032066
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.
0
 
LVL 74

Expert Comment

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

EX:
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...


Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37033235
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, ...in all situations?

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

;-)

JeffCoachman
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Author Comment

by:dwcummings
ID: 37033561
Jeff,

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.

Doug
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 37033638
<However, your comments triggered a thought.>
That was the whole purpose of my post!
;-)
...to 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"

;-)

Jeff
0
 
LVL 74

Expert Comment

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

809 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