string to return textbox sum

[Jaye7]

I have a textbox on a subform and I want to get the sum in a string as follows.

Dim s1 As String
 s1 = Sum(Forms!CountItem!CountItemLastCount.Form!ThisCount)
 MsgBox s1

When I use the above I get an error message saying - Sub or function not defined and it highlights the sum part of the equation.

I have been using the sum criteria in a textbox but if the user doesn't tab of the box then it doesn't see it as being updated.

I have tried me.dirty and everything else that usually works like send keys tab event, requery form and controls, a left mouse click but nothing is helping, therefore I thought that code to actually update the textbox may work.

 

[spikepl]

Forms!CountItem!CountItemLastCount.Form!ThisCount

Refers to one value: ThisCount on the current record of the subform. No point summing 1 value.

Make the sum on the subform in a textbox, read
/ refer to textbox in parent form.

And if in doubt, as always google: access sum subform

 

[Jaye7]

I already have that happening, it doesn't work and is the whole reason for this thread.

The textbox that sums thiscount is not updating when I click my commandbutton, to process data unless they tab off it, that's why I am attempting to use a string instead.

It isn't summing 1 value as it is a continuous form and therefore there are thousands of records in that field/textbox.

 

[MarkK]

This code is VBA . . .

Dim s1 As String
s1 = Sum(Forms!CountItem!CountItemLastCount.Form!ThisCount)
MsgBox s1

. . . and there is no public member of the normally referenced libraries (like VBA) that expose a public Sum() function. Do you mean DSum(), which is provided by Access?

Note that the calculation context when you type an expression into the ControlSource property of a TextBox is very different from VBA.

 

[vbaInet]

More questions...

1. What expression do you have in ThisCount?
2. What exactly is the problem? ThisCount doesn't return an updated value?

 

[Jaye7]

The ThisCount textbox (in subform) is bound to a field.

When they add a value in the ThisCount box (continuous) it then sums the values into a textbox named txtAdjust which has the formula.

=Sum([ThisCount])

Another textbox contains the original sum value, which I then subtract the txtAdjust value from.

The problem is that it isn't requerying the txtAdjust and the box that calculates the original sum less the txtadjust.

What happens is they click on a button which then performs a calculation to make sure that the original sum less txtadjust doesn't equal zero and other calculations.

If you tab off the textbox it requeries fine, but if they don't then it doesn't requery, even using code in the button to tab off, change from dirty etc... doesn't work, therefore I always get my popup which says "This is a zero count", regardless of what is in the textbox.

I used a left mouse click to avoid this previously but it just keeps clicking the button in this instance and therefore I tried to move the mouse with code but that doesn't work either.

 

[vbaInet]

Sum() works off data stored in the table or query. If you want instant calculations as you type, then you will need to rethink your strategy.

 

[Jaye7]

what do you suggest? as I can't use dsum as the textbox would not show as updated unless they tab off.

 

[vbaInet]

You will need to do some calculations in code and it should be done in the Change event of the ThisCount textbox. It will include values from the Sum() textbox, ThisCount and the Text property of ThisCount. Have a think!