Prevent Subform Refresh

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?
LVL 3
dwcummingsAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<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
 
peter57rCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
dwcummingsAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
What happened to accepting Pete's post as the Accepted solution...?
This would only be fair...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.