Text box value based on sum of another text box

Text box value based on sum of another text box

Post by dominic_br » Sun, 31 Dec 2006 02:14:18


Hi guys,

I am doing a Access project to manage the engineering department
jobs. In a form binded to a table, I am trying to show in a text box
the result of an equation based on the sum of the "value" variable. I
want that equation to update "on change" of the "value" text box. How
would the equation be formulated? Here is what I tried:

--- 1st try ---
Private Sub CommValeur_AfterUpdate()

CommDelaisEntree = DSum("[CommValeur]", "T: CommandeIngenierie",
"[CommandeCompletee] = 'No' AND [CommandeAnnulee] = 'No'") * 0.07 / 42
/ 37.5 / 3.25

End Sub
---------------

I get a runtime '2001' error.

CommValeur : Value variable to be summed
CommDelaisEntree : Variable to be calculated from sum of Value variable
T: CommandeIngenierie : Binded table
CommandeCompletee : states if order is completed, I need to sum only
none completed orders
CommandeAnnulee : states if order was cancelled, I need to sum only
live orders

Any clues?

Thanks guys.

Dominic.
 
 
 

Text box value based on sum of another text box

Post by S2xhdHV » Sun, 31 Dec 2006 03:00:01

Error 2001 can be misleading. It is often caused by a field or table name in
a Domain Aggragate function being named incorrectly; that is, misspelling the
name. What happens is Access creates an SQL statement it passes to Jet. Jet
can't find the references and does not execute the query and passes an error
back to Access which Access interprets as a canceled operation.

I suspect this part of the statement:
"T: CommandeIngenierie"

That is not a valid table name.

 
 
 

Text box value based on sum of another text box

Post by dominic_br » Sun, 31 Dec 2006 03:32:57


I tried to change the table names to remove que ":" and the space.
Here is the last version of the equation, which returns a 3464 runtime.

CommDelaisEntree.Value = DSum("[CommValeur]", "CommandeIngenierie",
"[CommCompletee] = 'No' And [CommAnnulee] = 'No'") * 0.07 / 42 / 37.5 /
3.25

More clues?
 
 
 

Text box value based on sum of another text box

Post by S2xhdHV » Sun, 31 Dec 2006 03:47:01

That error is a data type mismatch. It means the result of your calculation
is not compatible with the data type of the field being used.

CommDelaisEntree.Value should be a text box control on your form. Memory
variables do not have a value property. In fact, you don't need to use the
.Value property, it is the default property of a text box.
The field [CommValeur] needs to be either a Single or Double data type.
Integers and Longs do not accept decimals.