Using VBA to add values from multiple controls togeter and putthe sum in another control

[JBryson]

Dear all

Apologies in advance. This may seem so simple. But I am self taught and can just not figure out where I have gone wrong. Looking for assistance - for this (and something else that will post seperately).

So - I have 7 controls on a form:

1. SR06_Datapoints
2. SR10_Datapoints
3. SR15_Datapoints
4. GAP_Datapoints
5. LEGACY_Datapoints
6. NonReg_Datapoints
7. Total_Included_Datapoints

Number 6 should be the total of number 4 and 5 and Number 7 should be the total of items 1 to 5.

I basically want anytime I enter a value in number 1, 2, 3, 4 or 5, for the entries in number 5 (if appropriate) to update and the entry in number 7 to update, as I move to the next control.

I have tried for 7 to create a function as follows:

Option Explicit
Public Function CalculateIncludedDatapoints(SR06 As Variant, SR10 As Variant, SR15 As Variant, GAP As Variant, LEGACY As Variant) As Variant
Dim IncludedDatapoints As Variant
IncludedDatapoints = SR06 + SR10 + SR15 + GAP + LEGACY
CalculateIncludedDatapoints = IncludedDatapoints
End Function

Then in the before update event in the control I have put:

Private Sub Total_Included_Datapoints_BeforeUpdate(Cancel As Integer)
Me.[Total_Included Datapoints] = CalculateIncludedDatapoints(Me.[SR06_Datapoints], Me.[SR10_Datapoints], Me.[SR15_Datapoints], Me.[GAP_Datapoints], Me.[LEGACY_Datapoints])
End Sub

This does not seem to work when I add data in each field. I tried adding the same codse in the above sub into the before update events in controls for items 1 to 5, but didn't work.

I am sure there may bea more elegant bit of code than the "CalculateIncludedDatapoints", either that or I am just calling it wrong. Any assistancew ill be very much appreciated.



Jonathan

[ranman256]

This does work.
you can use: SR06 + SR10 + SR15 + GAP + LEGACY
BUT
if any of these items are null, your result will be null. Prevent it with NZ function
xx= nz(SR06) + nz(SR10) + nz(SR15) + nz(GAP) + nz(LEGACY)

AND you must use the control names, NOT the field names. *(but they could be the same name)

you can do this in VB or in a query.

[JBryson]

Thanks ranman256. This is helpful.

I take it I can just recode the existing function? adding in the NZ bits?

related question. To get this to work after each control is updated, do I need to call the function from each controls event procedure? Or will just having it in the the "total" control work? And is it best for the before update, after update, after edit etc event?

I will start amending and hope to hear back from you.

Thanks so much in advance.

Jonathan

[ranman256]

Yes ,if vb, you'd need to run the function after every control is updated...

txtBox1_afterupdate
txtBox2_afterupdate
....

all would execute the CalculateDatapoints()

[JBryson]

Thanks. Will let you know if it works

[JBryson]

Thanks ranman256. This has worked perfectly.

Is a steep learning curve and just adding those few nz characters in the right place was perfect. Thank you so much. While I have only ever posted on a groyp like this once, I have read lots of posts. it never ceases to amaze me how willing people out there are willing to assist. So thank you so much.

Jonathan