How do I calculate and store results?

[Joe Leon]

I have two fields in a form. The first field displays a QTY and the second
field is used to enter the number of items, NUMI, that are to be taken from
the QTY.

What I would like to do is to use the NUMI to subtract from QTY when I click
on a calculate button. Then when the calculation is complete, the QTY
displays the updated value which is stored as the new QTY and the NUMI is
cleared.

How can I do this with queries? I don't want to store the results in the
table.

Thanks!
Joe...

 

[Arvin Meyer [MVP]]

When you have both elements of a calculation, you never need to store the
result, unless you are using some sort of non-relational database. Use a
query to calculate the difference. Example:

Total: Sum([Qty]- Sum[NUMI])

 

[Dale Fye]

Are either of these fields bound to a table? If so, changing the values will
change the information stored in the table.

On to form references. Generally, when we talk about forms, we talk about
controls as opposed to fields. Controls can be bound, in which case they
have control sources that refer to a field in the underlying recordset. Or,
they can be unbound. Another generally accepted technique is to change the
name of your controls so that the name reflect the type of controls
(txt_Field1, cbo_Field2, ...). It is beneficial when you post your message
if you are clear about the types of controls you are dealing with.

To use a command button to recompute your quantity, you would use the
command buttons click event, something like:

Private Sub cmd_Calc_Click

if len(me.txt_Numi & "") > 0 Then
if isnumeric(me.txt_Numi) then
me.txt_Qty = me.txt_Qty - me.txt_Numi
me.txt_Numi = NULL
else
msgbox "Value in 'Numi' must be numeric!"
me.txt_Numi.setfocus
endif
else
msgbox "Enter a value in 'Numi'!"
me.txt_Numi.setfocus
end if

End SUb

 

[Joe Leon]

Dale, Thanks!

Yes, these fields are in a table. These are numerical fields and I need the
QTY to be updated and stored for further use.

 

[Joe Leon]

Arvin, thanks!

I see and understand what you say, but how do I store the Qty for further use?
I need the NUMI to clear upon clicking the calculate button.

When you have both elements of a calculation, you never need to store the
result, unless you are using some sort of non-relational database. Use a
query to calculate the difference. Example:

Total: Sum([Qty]- Sum[NUMI])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have two fields in a form. The first field displays a QTY and the second
field is used to enter the number of items, NUMI, that are to be taken
from
the QTY.

What I would like to do is to use the NUMI to subtract from QTY when I
click
on a calculate button. Then when the calculation is complete, the QTY
displays the updated value which is stored as the new QTY and the NUMI is
cleared.

How can I do this with queries? I don't want to store the results in the
table.

Thanks!
Joe...

.

 

[Arvin Meyer [MVP]]

Store Qty and store NUMI. They are the elements of the calculation. You can
use those 2 elements for all calculations in the future, but those
calculations should either be done in a query (usually) or sometimes
directly in a form or report. For instance, QtyOnHand will always be the
formula provided earlier, unless some are damaged or stolen, in which case
make s correction entry for the missing items.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin, thanks!

I see and understand what you say, but how do I store the Qty for further
use?
I need the NUMI to clear upon clicking the calculate button.

When you have both elements of a calculation, you never need to store the
result, unless you are using some sort of non-relational database. Use a
query to calculate the difference. Example:

Total: Sum([Qty]- Sum[NUMI])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have two fields in a form. The first field displays a QTY and the
second
field is used to enter the number of items, NUMI, that are to be taken
from
the QTY.

What I would like to do is to use the NUMI to subtract from QTY when I
click
on a calculate button. Then when the calculation is complete, the QTY
displays the updated value which is stored as the new QTY and the NUMI
is
cleared.

How can I do this with queries? I don't want to store the results in
the
table.

Thanks!
Joe...

.