data bye bye

[elmister]

hello everyone.
am just learning access and i have a little problem.
i have a table that has three fields: Price of phone, Tax paid, total price.

in the form i placed all three fields. on the Tax paid control source i entered =[Price of phone]*0.0825 . Price of phone is enterd manually.

Formula works, it gives me a value , works perfect but the only thing is that the result the value of the formula doesnt get put in the table under that field . Under the Tax paid field. only the Price of phone data that i enter manually. its works great in the form. but no data is sent back to the table. What am i doing wrong? please help!

 

[missinglinq]

...the result the value of the formula doesnt get put in the table under that field

Of course it doesn't! The Control Source for the TaxPaid textbox is not a field in the table, it's

=[Price of phone]*0.0825

Change the Control Source for the TaxPaid textbox to the TaxPaid field in the underlying table, then, in the AfterUpdate event for [Price Of Phone], use code to do the calculation:

Me.TaxPaid = Me.[Price of phone]*0.0825

Replace the names I've used with your actual name, of course!

 

[elmister]

missinglinq, thanks for answering.

okay so deleted all three fields on the form and droped them down again.

Price of phone : were users will inter a amount cost of phone wo tax.
Tax Paid :should calculate how much tax will be added to Price of Phone
Total Price : sum of Tax Paid and Price of phone.

now this is were am lost.

on the (Price of phone) field i go to AfterUpdate event and go to code builder and enter:

Me.TaxPaid = Me.[Price of phone]*0.0825 this will place the tax amount in Tax paid automaticlly.

sorry for being a dum a.. am learning. thanks

 

[elmister]

got it got it got it , thank you thank you thank you .

 

[boblarson]

1. I would not hard code the tax rate in. It WILL change and then you need to change code.

2. Use a table to store the tax rate so you can change it without changing code.

3. You then can use a Dlookup to calculate the tax paid and store it using the way that was shown. You do not store the TOTAL because that can be calculated at any time using the existing numbers.

 

[elmister]

i saw that. i was reading about that yesterday. am a happy man right about now!! missinglinq just saved my a.. , this thing was hunting me for a while. thanks for all the input and keep up the good work. thank you thank u tk u.

 

[missinglinq]

Taxes being raised? I'm shocked, Bob, shocked I tell you!

Bob makes a good point! So to recap:

Make a table to hold a single record to store the current tax rate, name it TaxRateTable.

Use this code to calculate the tax paid:

Private Sub Price_of_Phone_AfterUpdate()
 Me.Tax_Paid = Me.Price_of_Phone * DLookup("TaxRate", "TaxRateTable")
End Sub

Then, since you don't need to store the total price (as Bob said, you should simply re-calculate it when needed) use this as the Control Source for Total Price:

=[Price of Phone]+[TaxPaid]

Just as an aside, you really should get into the habit of not having spaces in your object names. When you name a textbox Tax Paid or Price of Phone with spaces in the names, Access, in the VBA code module behind the form, refers to them by replacing the spaces with Underscores, which can be confusing, especially if you're entering code freehand, rather than letting AutoComplete an object name.

TaxPaid or PriceOfPhone is just as readable as Tax Paid or Price of Phone.

 

[ChrisO]

Just as an aside…

Many years ago on another site a thread, now deleted, was running about storing certain calculated values.

Naturally, the general thrust was not to store calculated values at any time.
Then the question arose about storing calculated tax values.
There persisted the notion that at no time store a calculated value, even tax values.

This makes sense with regards to database normalization most of the time.

(Without mentioning names, no one here though.)
However, a person stated that within certain states of the US, and remember we don’t know where the OP is from, that re-calculating tax amounts (on the fly) could lead to a discussion with the local District Attorney.

I don’t remember the reason given, or the states involved, but I think the statement was made by a CPA from Texas.

I mention this to draw attention to what may suit database normalization most of the time may also be illegal some of the time, and that talking to the accountant may save some embarrassing moments.

 

[elmister]

ChrisO, wow! that was deep. illegal is not good. will ck into it. Thank.

missinglinq,
boblarson had mentioned that aswell. I do want to store the Total Price.
and am going to see if i could use the same code to do that.


I somewhere read that it’s not a good programmer ethic to keep calculated values in a field
. its better to calculated on the fly. but this is what a customer wants. what can i do.


guys thank you. is good to know there is good people out there that are willing to help. Here is just job security. noone helps anyone. so that you guys for doing what you do.