text box calculation & data storage

[Rpm1957]

i have a form with 3 text boxes, i would like the user to enters data in box 1 & 2 and then display the result in box 3. However I need to store the result (box 3) in a table and I have read that you shouldn't store calculated fields in a table so what is the best way to go about this?

 

[plog]

Most likely you should store values 1 & 2, and then when you need value 3 you calculate it using a query.

Are you storing values 1 & 2 that the user enters? If not, why?

 

[Rpm1957]

Yes I am storing values 1&2 but how do I get the query to store value three in the table?

 

[boblarson]

Yes I am storing values 1&2 but how do I get the query to store value three in the table?

Why do you need to store the third if you have the first two in the table? You should NOT, I repeat - should NOT do it!!!!

Learn to do things with Best Practices and it will benefit you greatly. It violates the rules of normalization to store redundant data. And it can also be a very high risk of data not being correct. If someone happened to change a value in one of the values directly in the table, the third value (the calculated value) would NOT be correct. This puts the integrity of your data at high risk.

So, when we tell you these things, it isn't to make your life difficult, but to make it EASIER.

Remember, you can use a QUERY in 99% of the places you would use a table so you can have the value concatenated IN THE QUERY when it is needed.

 

[Rpm1957]

This is a calibration sheet, each record in textbox1 and 2 are different every time therefore the calculated value (textbox3) is also unique, so is it best to use a query and how do I store the results?

 

[plog]

Yes it is best to use a query and you do not store the results.

Let's concentrate on the starting and ending points and the route in between the two will become easier to plot. How are you going to use this textbox3 value? In a report? In a form? In another calculation? What's the output? Also, what's your table structure and could you show some actual sample data of the tables?

 

[Rpm1957]

Ok I think I know where your coming from concerning saving the data for text box 3.
I'll try to explain. Text box 1 is a mV reading at a certain stage of calibration.
text box2 is a mV reading at the the next stage.
Text box 3 is the slope between the two readings.
I think what you are saying is if assign a query result to text box 3 the value will be there when i look at the form even though I am not storing the data?

 

[plog]

Essentially correct.

 

[Rpm1957]

So how do I get the query to run after textbox2 data has been entered and display the results in textbox3.
Are there any examples I can follow?

 

[plog]

Its not really a query its a calculation. You would make an 'After Update' event trigger on textbox2 that would do the calculation necessary to compute the value and display it in textbox3.

My form skills are a little rusty, can anyone else following this help with more specific details?

 

[boblarson]

Its not really a query its a calculation. You would make an 'After Update' event trigger on textbox2 that would do the calculation necessary to compute the value and display it in textbox3.

My form skills are a little rusty, can anyone else following this help with more specific details?

If I read the info correctly, it would just be a control source of:

=Nz([Field2NameHere], 0) - Nz([Field1NameHere],0)

no code necessary