By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Refresh a control on a form that performs a calculation

kcdoell
100+
P: 230
Hello:

I thought I was done with this one but a user who is testing my DB just pointed out a problem.

I used the following in the afterupdate event:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Binding_Percentage_AfterUpdate()
  2. 'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
  3. 'on the quick reference table on the Forecast form.  
  4.  
  5. Me.Refresh
  6.  
  7. End Sub 

Basically, once I change the Binding_Percentage and tab into another field on the same row my numbers (My unbound controls that are performing a calculation) change appropriately but the minute I click on to another row (using the mouse or tab) on my table the numbers change. It looks like the numbers change to the old settings. When I click back to the row I changed, the numbers go back to what they are supposed to reflect. I changed the code to the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Binding_Percentage_AfterUpdate()
  2. 'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
  3. 'on the quick reference table on the Forecast form.  
  4.  
  5.  Me.Dirty = False
  6.     Me![SumGWP].Requery
  7.     Me![Sum50GWP].Requery
  8.     Me![SumNWP].Requery
  9.     Me![Sum50NWP].Requery
  10.  
  11.    End Sub
But the same strange thing happens......

I read that if a Control is unbound, the many instances of this Control on the Continuous Form will display the same value in all instances. Therefore I would need to somehow bind the Control on the Continuous Form if I need to display different values in its instances.

Does that make sense to anybody out there, so far I am not getting it..

Any ideas would be great,

Keith.
May 20 '08 #1
Share this Question
Share on Google+
5 Replies


RuralGuy
Expert 100+
P: 375
Does it help if I say that in order for a control on a continuous form to display a unique value it needs to be bound to a field in the underlying RecordSource?
May 20 '08 #2

kcdoell
100+
P: 230
Does it help if I say that in order for a control on a continuous form to display a unique value it needs to be bound to a field in the underlying RecordSource?
Not really, In the case of my [SumGWP] I have the control source set to
Expand|Select|Wrap|Line Numbers
  1. =Nz(DSum("[GWP]","ReQryForecast","[Binding_Percentage] >= 75"),0)
[GWP] & [Binding_Percentage] are controls on my form and the control source of the form is set to a query (ReQryForecast). Did I not bind [SumGWP]??????

If you can help me understand better or maybe it is bound already after my explination....
May 20 '08 #3

RuralGuy
Expert 100+
P: 375
Putting a formula or function such as you have in the ControlSource of a control is not binding the control. Binding the control would be setting the ControlSource of the control to point to a field in the RecordSource of the form.
May 20 '08 #4

kcdoell
100+
P: 230
Hello:

Okay, problem solved! I tell you what I did but don’t totally understand it. I set my AfterUpdate to the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Binding_Percentage_AfterUpdate()
  2. 'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
  3. 'on the quick reference table on the Forecast form.  
  4.  
  5.  Me.Dirty = False
  6.     Me![SumGWP].Requery
  7.     Me![Sum50GWP].Requery
  8.     Me![SumNWP].Requery
  9.     Me![Sum50NWP].Requery
  10.  
  11.    End Sub
Then in the unbound controls themselves I was looking at my control source formula. In the case of my [SumGWP] I had the control source set to:
Expand|Select|Wrap|Line Numbers
  1. =Nz(DSum("[GWP]","ReQryForecast","[Binding_Percentage] >= 75"),0)
and did not see why I needed to refer to the control source of my form [ReQryForecast] to sum my [GWP]. So I took it out of the expression:

Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf([Binding_Percentage]>=75,[GWP],0))
And bang the problem went away……………………….. Too bad I spent a ton of time building queries to perform the calculation with the Continuous form idea I was running with when the solution was easier than that…..
Thanks for the help.

Keith.
May 20 '08 #5

RuralGuy
Expert 100+
P: 375
Excellent! Glad you got it sorted.
May 20 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.