Calculating fields

[Hope]

I need to calculate fields so they show in the table.

for example I have these fields: a weight, b weight, c weight and d weight. I need to take a weight minus b wieght and have that answer go in c weight, then I need to take a weight divided by the answer of c weight and have that answer go to d weight.

I have the calucations in the form and it is calculating but c weight and d weight are not showing up in my table.

 

[boblarson]

Principle of Normalization: You shouldn't be storing any calculated fields if you have the fields available to recreate the value at any time.

Second, you definitely shouldn't store them if users have direct access to the table since you won't be able to force an update to the calculated field in the table. So, if the user changes the values in the table, the calculated value would not be accurate.

 

[Hope]

If I can't store the calculated fields to the tables how can I store the information?

 

[boblarson]

What are you going to be using the information for? You wouldn't store the information you would do the calculation in a query and use that query as the basis for reports, etc.

 

[Hope]

These calculations figure out the weighs and ranges for our parts so we need the information store permanent so we can go back and look at our progess, I created database about 4 years ago so I need to be reminded what to do here.

Thanks

 

[boblarson]

To store something permanent from a form:

1. Add a hidden text box for the item you want to store. Bind it to the field you want stored.

2. In the visible calculated control's AfterUpdate (in code window) event put:
Me.YourHiddenBoxName = Me.YourCalculatedBoxName

 

[Hope]

One confused girl

In my Form in design view I have the calcalation in the field, and looking at the form it does the calculation correctly for me, the problem is it's not saving in the table, are you saying it won't save in the table and I need a query?

 

[rainman89]

is the fields controlsource set to the field in the table? if not it will need to be

 

[boblarson]

That's why the hidden box has the controlsource set to the field in the table and the code updates that field when the calculated field changes.

 

[Hope]

In my form I have weight, sub weight, del weight and density, I need to take weight minus sub weight and have the answer in del weight, so in my form in the control source I have =[weight]-[sub weight] and it does not show in my table

 

[The_Doc_Man]

I think we have to return to boblarson's original statement. ANY FIELD that you can compute "on the fly" in a query should never be updated by any type of computation in a update query. I only see three weights in your table that need storing... A, B, and the weight you divide by C. NO OTHER WEIGHT SHOULD BE STORED.

One thing you should always remember is that for most forms and reports, a query is the better recordsource if any computations are involved anyway.

Another thing to consider is whether these fields are time variant or time invariant. Because if they are time variant, you should not update ANYTHING. You should create a newer record - AND you should consider that your structure is incorrect.

You used the word "progress." That is why I hit upon the "time variation" issue. If you are looking at progress, you need something like

partnumber - part of compound prime key
sampledate - other part of compound prime key
weight

And you need a lot of those samples to show you the history. (This is for purposes of discussion only.) This table should be a child of the table that lists partnumbers and descriptions of parts.

Then, when you want to show progress by time, you can use this sample table grouped by part number and ordered by sample date to learn what you wanted to know.

I hope you can excuse us for having trouble understanding what you want to do so that we can advise you better. Your description is not entirely clear and it at least prima facie appears that you might have a non-normalized table in-hand. We have trouble enough working with normalized data sometimes. Working with improperly formed tables is that much harder.

 

[boblarson]

Okay, let's try this again.

1. put a NEW text box on your form

2. Set it's visible property to NO

3. Set it's controlsource to the field that you have in your table where you want to save the calculated value

4. Click on the current text box that has =[weight]-[sub weight] in it and click on the events tab in the properties dialog

5. Find the event named After Update and click on the text area next to that property so that a dropdown appears.

6. Within that dropdown select EVENT PROCEDURE

7. Click the elipsis (...) that appears all the way to the right when you have selected EVENT PROCEDURE and click on that (...) to open the code editing window.

8. Type in
Me.YourHiddenTextBoxNameHere = Me.YourCalculatedTextBoxNameHere
(replacing YourHiddenTextBoxNameHere and YourCalculatedTextBoxNameHere with the actual names of your two text boxes)

It should look something like this when complete:

Private Sub YourCalculatedTextBoxNameHere_AfterUpdate()
Me.YourHiddenTextBoxNameHere = Me.YourCalculatedTextBoxNameHere
End Sub

 

[boblarson]

I hope you can excuse us for having trouble understanding what you want to do so that we can advise you better. Your description is not entirely clear and it at least prima facie appears that you might have a non-normalized table in-hand. We have trouble enough working with normalized data sometimes. Working with improperly formed tables is that much harder.

And, if we have failed to convince you to do it the way Doc suggests, then my step by step will store it, even if you shouldn't. Sometimes I know that you just want to do it the way you want to do it and either don't have the desire or time available to try to redesign it so it is properly normalized.