Refresh a control on a form that performs a calculation

[kcdoell]

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:

Private Sub Binding_Percentage_AfterUpdate()
'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
'on the quick reference table on the Forecast form.  
 
Me.Refresh
 
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:

Private Sub Binding_Percentage_AfterUpdate()
'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
'on the quick reference table on the Forecast form.  
 
 Me.Dirty = False
    Me![SumGWP].Requery
    Me![Sum50GWP].Requery
    Me![SumNWP].Requery
    Me![Sum50NWP].Requery
 
   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.

[RuralGuy]

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?

[kcdoell]

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

=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....

[RuralGuy]

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.

[kcdoell]

Hello:

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

Private Sub Binding_Percentage_AfterUpdate()
'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
'on the quick reference table on the Forecast form.  
 
 Me.Dirty = False
    Me![SumGWP].Requery
    Me![Sum50GWP].Requery
    Me![SumNWP].Requery
    Me![Sum50NWP].Requery
 
   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:

=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:

=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.

[RuralGuy]

Excellent! Glad you got it sorted.