=Sum(A_TextBox)

[ghudson]

Brain freeze going on here for I have spent too much time trying to figure out how to total an unbound (calculated) text box on a form. I must be so simple that I am overlooking the obvious. I have tried different scenarios with the DSum and Sum function without success. I have a unbound (calculated) text box named [tbNegativeQuanity] that I want to total in my form. The value of the [tbNegativeQuanity] text boxes will either be 0 or -1. If the total of the [tbNegativeQuanity] text boxes is less than zero then I know I have a quantity error and I want to display a message box alerting that fact.

How can I get a total of my text box [tbNegativeQuanity]? I am going to use the total in the OnOpen event of the form but the total could go in the forms footer if needed. I am using Access 97. Thanks in advance for your help!

 

[ghudson]

Thanks for answering Pat. That has me almost there. I added the calculation for the [NegativeQuantity] field in the forms record source SQL. The [tbNegativetbQuantitySum] TextBox in the forms footer is correctly displaying "True" with this as the record source...
=IIf(Sum([NegativeQuantity])<0,"True","False")

But, I can not test the value of the text box in the forms OnOpen [or any other On??? event] with this..
If Sum([NegativeQuantity]) < 0 Then
MsgBox "One or more of the available materials has a negative inventory quantity!", vbCritical, "Negative Quantity Error"
End If

Can I not use the Sum() function in VBA? I get the error Sub or Function not defined.

When I test the value of the TextBox [tbNegativetbQuantitySum] with a message box in the OnOpen event I get a False reading then the forms displays a True. Somehow the actual calculation is slower than the form since I can not accurately test the value in the OnOpen, OnLoad or OnCurrent event.

Any suggestions?

 

[Mitch_____W]

Why are you putting the "Sum()" in the IF? Why not leave it as:
If [tbNegativetbQuantitySum] = "True" Then
MsgBox "One or more of the available materials has a negative inventory quantity!", vbCritical, "Negative Quantity Error"
End If

 

[Rich]

Have you tried the Timer event?

 

[ghudson]

Actually I had already tried that.

MsgBox "tbNegativetbQuantitySum = " & tbNegativetbQuantitySum
If tbNegativetbQuantitySum = "True" Then
'If Sum([NegativeQuantity]) < 0 Then
MsgBox "One or more of the available materials has a negative inventory quantity!", vbCritical, "Negative Quantity Error"
End If

The message box when called in the OnOpen event displays "False" yet the value in the [tbNegativetbQuantitySum] text box displays "True".

Why?!?!

 

[ghudson]

Rich,

The timer worked. I am still not understanding why my previous attempts to test the value of the calculated text box would not work. I guess that there is a slight moment when the form opens, the text box does the calculation and yet the code in the forms OnOpen event is faster than the calculation in the text box since the tested trigger is never tripped.

 

[stormin_norm]

Can you post the timer code or sample of this.
I have the SAME issue with a DSUM of a subform/recordset. I want to total ALL records in the underlying subform and AS SOON as a user changes a value on the subform the main form should recalc the total (like a spreadsheet does).
Problem is the DSUM I am using picks up the stale data in the db.
It appears the text field is executing from the after update event before the data is stored.
This timer may do it

 

[Rich]

Why are you using DSum?

 

[stormin_norm]

Sorry, I should clarify a bit further-
The main form is college number, lets say 1..3 colleges. The subform displays the credit details of each college with a subtotal for that college.
The main form is a total of ALL credits for ALL colleges. As soon as the user changes a value in the subform, I need to recalc the total for all colleges.

A regular sum is just picking up what is on the subform and not picking up the other transfer college values.

Stepping through a record set is okay, still needs a requery to pick up what changed on the subform.

The only thing which came close is the dsum.
And now I have to try an on_current plus the after_update...And the after update doesn't fire until you leave the subform "row".

If you have a solution, I would love it.

 

[Rich]

Save the record then Requery the main form textbox if neccessary, Don't put Requery in the Form_Current event

 

[stormin_norm]

Rich-
Thanks, but same thing.
I can see the mainform perform the main form requery, then the subform SUM([ENG hours]) fires.
I may be able to make a test db this afternoon or tonight.
----------
subform:
Private Sub ENG_hours_AfterUpdate()
On Error GoTo Err_HoursUpdate
DoCmd.Save
Me.Parent.ENGtotal.Requery
Exit_HoursUpdate:
Exit Sub
Err_HoursUpdate:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_HoursUpdate
End Sub

 

[Rich]

It's DoCmd.RunCommand acCmdSaveRecord
not DoCmd.Save

 

[stormin_norm]

Sorry I had brain crosslink...I am converting data and had recordset .update on my mind and .save jumped in there. Not enough sleep last night

Thanks Rich that worked perfect.
yup yup yup...Update and then requery.
Would have been nice to work without the update...but it works and I am happy.
THANK YOU for the tips!