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:


[code=vb]Private Sub Binding_Percent age_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 [/code]


Basically, once I change the Binding_Percent age 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:

[code=vb]Private Sub Binding_Percent age_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[/Code]

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 [code=vb]=Nz(DSum("[GWP]","ReQryForecas t","[Binding_Percent age] >= 75"),0)[/code] [GWP] & [Binding_Percent age] 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:

[code=vb]Private Sub Binding_Percent age_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[/Code]

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: [code=vb]=Nz(DSum("[GWP]","ReQryForecas t","[Binding_Percent age] >= 75"),0)[/code] 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:

[code=vb]=Sum(IIf([Binding_Percent age]>=75,[GWP],0))[/code]

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.