Calculated Controlls in form don't update to table

  • Thread starter Thread starter bonnie.porter
  • Start date Start date
B

bonnie.porter

urg..

I have a table of oyster orders and a form to imput the data into the
table. I have fields in the form that need to be caclulated. When I
build the formulas in the expression builder and then try to use them
in the form the table is not updated with the result. It displays the
result in the correct box on the form but then the answer is not put
into the table. Here are the formulas.


= [quantity ordered in dozens] * 12
= [Number of bags received] * 12
= [Number of dozens in bags] * [Number of bags received]
= [total number of oysters reveived] - [quantity ordered in total
number of oysters]

help and guesses are greatly appreciated!

- Bonnie Porter, MCP
 
urg..

I have a table of oyster orders and a form to imput the data into the
table. I have fields in the form that need to be caclulated. When I
build the formulas in the expression builder and then try to use them
in the form the table is not updated with the result. It displays the
result in the correct box on the form but then the answer is not put
into the table. Here are the formulas.


= [quantity ordered in dozens] * 12
= [Number of bags received] * 12
= [Number of dozens in bags] * [Number of bags received]
= [total number of oysters reveived] - [quantity ordered in total
number of oysters]

help and guesses are greatly appreciated!
Click to expand...

The bad news is, calculated controls can never be bound to a field,
because they are bound to the expression that calculates their values.
The good news is, it's seldom necessary or desirable to store a
calculated value -- not if that value depends entirely on values that
*are* stored.

For example, if you're storing [quantity ordered in dozens], why would
you ever want to store the unit quantity, which can always be calculated
as [quantity ordered in dozens] * 12? On the other hand, if you want to
store the unit quantity, you don't need to store the number of dozens,
which can always be calculated by dividing by 12. If you store them
both, you (a) take up unnecessary space in your database, (b) take extra
time to retrieve the redundant data from the disk, and (c) run the risk
that at some point, one of those values will be updated without
recalculating the other, leaving you with contradictory fields in the
table.

If you want the user to enter [dozens], but store only [units], you can
do that by binding the [units] text box to a field in the table, leaving
the [dozens] text box unbound, and performing the calculation using VBA
code in the AfterUpdate event of the [dozens] text box, assigning the
result to the [units] text box. Like this:

Private Sub txtDozens_AfterUpdate()

Me!txtUnits = Me!txtDozens * 12

End Sub
 
Back
Top