Form Calculation breaks on restart of Access 2007

  • Thread starter Thread starter morejunk
  • Start date Start date
M

morejunk

I'm pretty new to Access so maybe I'm just missing something (just
recently switched from Filemaker Pro).

I have two controls on a form (Names: "txtTotalEaten" and
"txtCalAllow"). I have a third control where the control source is
the expression below:

=[txtTotalEaten].[Value]-[txtCalAllow].[Value]

This worked until I shut down Access and relauched the app. I then
get a #Name error. Retype it. #Name

If I change the expression to:
=txtTotalEaten-txtCalAllow

Again. It works once. Shut down and relaunch... #Name.

I wish I could just call a value from a query but I can't seem to make
that happen either.


Very frustrating.


Thanks,
Adrian.
 
It's a complicated situation. You don't tell us if this form is bound or
not and if it is, if these two controls are themselves bounded or not to the
record source of the form. In the case of a bounded form, you also don't
tell us if you are playing with the record source of the form when it's
opening or after that.

Also, in the case of a bounded control (on a bounded form, of course), you
don't tell us if the name [txtTotalEaten] refer to the name of the other
control or if it refers to the name of the field in the record source (or if
it refers to both of them). As you see, there is a lot of possibility here.

Usually, when something like « =txtTotalEaten-txtCalAllow », the names
txtTotalEaten and txtCalAllow should refer to the names of the fields of the
record source behind the form and not to the names of controls.
 
The simplest method would be to set the control source of your 3rd control to

=(Nz([txtTotalEaten],0)-Nz([txtCalAllow],0))

Or - in a query - create a calculated column

Result: Nz([TableName]![Yourfield],0)-Nz([TableName]![SomeOtherField],0)

Of course change the name of the table and the field to what they really are
 
this is a good solution. it takes into concideration null values eminating
from the text box. still don't know about validating the text boxes too but
that's another issue.

the nz function returns another value from the source if the source is null.
great function.

Wayne-I-M said:
The simplest method would be to set the control source of your 3rd control to

=(Nz([txtTotalEaten],0)-Nz([txtCalAllow],0))

Or - in a query - create a calculated column

Result: Nz([TableName]![Yourfield],0)-Nz([TableName]![SomeOtherField],0)

Of course change the name of the table and the field to what they really are






--
Wayne
Manchester, England.



morejunk said:
I'm pretty new to Access so maybe I'm just missing something (just
recently switched from Filemaker Pro).

I have two controls on a form (Names: "txtTotalEaten" and
"txtCalAllow"). I have a third control where the control source is
the expression below:

=[txtTotalEaten].[Value]-[txtCalAllow].[Value]

This worked until I shut down Access and relauched the app. I then
get a #Name error. Retype it. #Name

If I change the expression to:
=txtTotalEaten-txtCalAllow

Again. It works once. Shut down and relaunch... #Name.

I wish I could just call a value from a query but I can't seem to make
that happen either.


Very frustrating.


Thanks,
Adrian.
Click to expand...
Click to expand...
 
still don't know about validating the text boxes

There was no mention of validation in the OP.

Maybe they will ask another question regarding this - am sure someone will
be able to offer advice

--
Wayne
Manchester, England.



vbasean said:
this is a good solution. it takes into concideration null values eminating
from the text box. still don't know about validating the text boxes too but
that's another issue.

the nz function returns another value from the source if the source is null.
great function.

Wayne-I-M said:
The simplest method would be to set the control source of your 3rd control to

=(Nz([txtTotalEaten],0)-Nz([txtCalAllow],0))

Or - in a query - create a calculated column

Result: Nz([TableName]![Yourfield],0)-Nz([TableName]![SomeOtherField],0)

Of course change the name of the table and the field to what they really are






--
Wayne
Manchester, England.



morejunk said:
I'm pretty new to Access so maybe I'm just missing something (just
recently switched from Filemaker Pro).

I have two controls on a form (Names: "txtTotalEaten" and
"txtCalAllow"). I have a third control where the control source is
the expression below:

=[txtTotalEaten].[Value]-[txtCalAllow].[Value]

This worked until I shut down Access and relauched the app. I then
get a #Name error. Retype it. #Name

If I change the expression to:
=txtTotalEaten-txtCalAllow

Again. It works once. Shut down and relaunch... #Name.

I wish I could just call a value from a query but I can't seem to make
that happen either.


Very frustrating.


Thanks,
Adrian.
Click to expand...
Click to expand...
Click to expand...
 
Sorry about that.

The form is unbounded (it's a main form where I have various subforms
showing summaries and charts). All of the controls are unbounded as
well. The control source for both of them are calculations(shown
below):

[txtTotalEaten]=DSum("[qryEatingCalc]![Total
Calories]","qryEatingCalc","[tblEating]![dateEaten]=Date()")
[txtCalAllow]=Round(DLookUp("[qryTotalDailyCalorieBurn]!
[TotalDailyCalorieBurn]","qryTotalDailyCalorieBurn"),0)



There are no validation rules for any of the controls.
 
Back
Top