How do I calculate and store results?

[Joe Leon]

I have a Form with a QTY field and a field AMOUNT_TAKEN.

I need to have the AMOUNT_TAKEN subtracted from QTY when I click on a
Calculate Button and also have the AMOUNT_TAKEN field cleared.

How do I do the update of the QTY with a query and store it for further use?

I have a Table with both QTY and AMOUNT_TAKEN fields

Thanks!

 

[Daryl S]

Joe Leon -

Bring up the Properties for the form. Set the RecordSource to be the table
that contains your data.
For each of the two fields on the form, make sure the Control Source
property is the associated field from the table. This will take care of both
displaying the information in the table on the form and updating the
information in the table when it has been changed on the form.

Then to update the information when a AMOUNT_TAKEN has been entered, you
need to put the following in the AfterUpdate event of the AMOUNT_TAKEN field:

Me.QTY = Me.QTY - Me.AMOUNT_TAKEN
Me.AMOUNT_TAKEN = 0

That said, I am not sure you have your database design set up correctly. I
assume you have other fields in your table, including a unique field (unless
this will only ever be one record you are storing). Also recognize that the
AMOUNT_TAKEN field will always be zero in this table, so it seems silly to
store this. You will also have no records of how much was taken or the
original quantity. Maybe you are just playing with Access to learn how it
works?

 

[John W. Vinson]

How do I do the update of the QTY with a query and store it for further use?

You don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.


You may want to read up a bit on how to use Access: I'm afraid you're somewhat
on the wrong track! Check out some of these resources, especially the
tutorials.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[eduardo freitas]

I have a Form with a QTY field and a field AMOUNT_TAKEN.

I need to have the AMOUNT_TAKEN subtracted from QTY when I click on a
Calculate Button and also have the AMOUNT_TAKEN field cleared.

How do I do the update of the QTY with a query and store it for further
use?

I have a Table with both QTY and AMOUNT_TAKEN fields

Thanks!