In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
subform / main form not recalculating with SQL Server
Posted on 2014-11-10
I have an access XP app that is being converted to 2013 with SQL Server. There is a typical subform / main form with a subform with Quantity, Retail, Days and calculated control Amount (controlsource==[Quantity]
On the older version working since 2.0, when Quantity was updated, Amount would update, When that line was updated, the subtotal on the main form would update. Now Neither update happens.
I have been able to force the Amount control in the line to update with an Me.Amount.Requery in the afterupdate of Quantity, Retail, and Days. That is nice and clean.
Subform Detail Controls:
Quantity, Retail, Day, Amount (controlsource = =[Quantity]*[Retail]*[Days
Subform Footer Controls:
Subtotal (controlsource = =Sum([Quantity]*[Retail]*[
Main Form Controls:
Subtotal =IIf(IsNull(Forms!frmJobs!
I have tried many variations to get the subtotal to update WITHOUT a requery or recalc. When i do that it sends the cursor back to top.
I have the subform visible and can see that the subform total does not update when you move off the detail record to and detail record.
Here is the Subform After Update and some of what i have tried. Non work with a whole refresh or requery.
Me.Amount.Requery
Me.Subtotal.Requery
Me.Refresh
Me.Parent.Subtotal.Requery
Me.Parent.Refresh ' This will make it update, but moves cursor to top
'Me.Recalc ' this will work but moves cursor to top
Is there a way to resolve this behavior? I would rather not have to whole form refresh since the way it repaints is not that nice, and the cursor goes to top.
On the older version working since 2.0, when Quantity was updated, Amount would update, When that line was updated, the subtotal on the main form would update. Now Neither update happens.
I have been able to force the Amount control in the line to update with an Me.Amount.Requery in the afterupdate of Quantity, Retail, and Days. That is nice and clean.
Subform Detail Controls:
Quantity, Retail, Day, Amount (controlsource = =[Quantity]*[Retail]*[Days
Subform Footer Controls:
Subtotal (controlsource = =Sum([Quantity]*[Retail]*[
Main Form Controls:
Subtotal =IIf(IsNull(Forms!frmJobs!
I have tried many variations to get the subtotal to update WITHOUT a requery or recalc. When i do that it sends the cursor back to top.
I have the subform visible and can see that the subform total does not update when you move off the detail record to and detail record.
Here is the Subform After Update and some of what i have tried. Non work with a whole refresh or requery.
Me.Amount.Requery
Me.Subtotal.Requery
Me.Refresh
Me.Parent.Subtotal.Requery
Me.Parent.Refresh ' This will make it update, but moves cursor to top
'Me.Recalc ' this will work but moves cursor to top
Is there a way to resolve this behavior? I would rather not have to whole form refresh since the way it repaints is not that nice, and the cursor goes to top.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
4 Comments
Have a look at the solution regarding a similar question on this page: http://www.experts-exchange.com/Database/MS_Access/Q_28551520.html
Basically, I would refresh the data, and then use DoCmd.GoToRecord to go back to the record where you were.
Regarding the refresh not looking nice, I would suggest using
or
Basically, I would refresh the data, and then use DoCmd.GoToRecord to go back to the record where you were.
Regarding the refresh not looking nice, I would suggest using
me.painting = false
(Do the refresh, then the DoCmd.GoToRecord)
me.painting = true
or
Application.Screenupdating = False
(Do the refresh, then the DoCmd.GoToRecord)
Application.Screenupdating = True
Active 4 days ago
I will try this, but interested to know why the behavior is different than before.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 19 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.