Forms and Record Data Question

[OldWolf]

Greetings, this is my first post here.

I have a form that calculates a total by taking price times quantity. I would like the form to not only display the results of this calculation, but store it in a field in a table.

How would one go about doing this?

I'm still a relatvie newbie with Access 97 db dev, and I'm much more familiar with a mySQL/PHP setup.

Any help would be greatly appreciated.

Thanks.

-Old_Wolf

 

[Rohdem]

It is generally not a good idea to store a value in a table that can be derived from other values......however, if you absolutely must do this, you can create a 'total' text box on the form that is bound to the field in the table that you want to store the value in. On the 'after update' event of the price and quantity fields, recalculate the value.

me.total = me.price * me.quantity Mike Rohde
rohdem@marshallengines.com

 

[LarryDeLaruelle]

OldWolf:

The simple way to do this would be to create a field in the table to hold the results of the calculation. In an After_Update event for last field entered (Price or Quantity) enter the code: FieldName = Price * Quatnity. Of course you will need to use your own field names.

Generally speaking, however, it is usually considered bad form to store the results of a calculation when the original data are already stored in the Db. It is a simple matter to recreate the calculation for any form or report displays.

If you change your mind about storing the result, then all you need to do is put an unbound text box on the form (call it txtDisplay for example). In the same event used above, simply make the code statement to be txtDisplay = Price * Quantity. The same technique can be used on a report but you would put the code in the Format Event of the details section.

Hope this helps.
Larry De Laruelle
larry1de@yahoo.com

 

[LarryDeLaruelle]

Mike:

You beat me to it.

Glad to see we agree on not storing results.

Larry De Laruelle
larry1de@yahoo.com

 

[OldWolf]

Thanks for the replies.

I suppose this my "just in case" approach to databasing.
If anything, for whatever reason, should happen to the forms, the user could still get to the order totals quickly, since they would be in the database. And having the DB perform the calculations would save the user alot of time.

An Excel spreadsheet would have been easier to create as an order form template, but the boss wanted the orders to be databased for searching. Hence, Access seemed the way to go. The PO officer is really the only person who will be using this, in our small org.

Any other methods that you could suggest would be most appreciated. It would be nice if a field could be set up to use this calculation on the data as it was entered into the table.

Thanks.

-Old_Wolf

 

[OldWolf]

I should read replies a bit more closely before posting.
Let me expound on my situation a bit more.

I have a table and it contains (among other things) a Qty, UnitPrc, and Total field.
The form for the table will multiply Qty by UnitPrc and display the result in an unbound text box without complaint.

However, I would also like the results of that calculation stored in the Total field that already exists in the table. This is what I meant by "just in case". The result is stored in the table, not just displayed on a form.

I hope this makes things a bit clearer.
Or perhaps I've just made it more confusing.

I tried your suggestions, but they returned an error. Probably has to do with my table references in the After_Update event.

Thanks.

-Old_Wolf

 

[Rohdem]

You really aren't referencing the table, you are refering to controls on the form. Check to see what the names of your text boxes are (note that this is different from the control source). Your text boxes might be called something like text1 or text5. If they are, change these names to something that makes a little more sense like quantity or price. Once you have the three textboxes on the form and named correctly (quantity, price, total), then it just takes one line on the after update event of the quantity and price text boxes.

me.total = me.quantity * me.price

(The 'me' refers to the current form.)

If you still get an error, post the error and the line of code that it errors on. Mike Rohde
rohdem@marshallengines.com

 

[OldWolf]

Ok, the problem was sorted out.
I misspelled one of the control names. UntPrce instead of UntPrc. *sigh* It's always the simple errors that catch you.
As I was just leaving work after posting, I was not able to check the script until today.

Many thanks for the help.

-Old_Wolf