Calculated Fields in Forms

Topic

New Reply

I am using Access 97-SR2.
I have a question that is probably pretty basic, but I have to ask anyway. I am trying to develop a registration form that calculates registration fees based on various conditions; full-time, part-time, dinner, etc. I have used the “control” property to put in conditional calculations to compute part time fees and in the sub-total and total fields. The problem is that the fields calculate properly on screen but do not get posted in the data field when the form is complete. How do I get the record to post properly to the table with the calculated fields in the right position?

I am sure that the answer is fairly simple (which is why I probably missed it), but this is real frustrating.

Brian Bonner

Reply | Quote

Replies

You have to understand that calculated controls on a form are bound to the calculation, not to an underlying field. There isn’t any way to bind a calculated control to a table field. And normally you don’t store the result of calculations in a table because you can recreate them based on stored values.

In this case, however, I assume that it’s only the calculated value you want to store, not the various items that go into the calculation, right? If that’s so, then you need to use a little bit of code to insert the calculated value into the table. The easiest way for you would be to add a textbox to the form and bind it to the field you want to populate with the calculated value. You can set the field’s visible property to false so that it doesn’t actually appear on the form. Then you simply use code to set the value of the TextBox equal to the value of the calculated control.

You didn’t explain whether your calculations were based on values being entered, so I can’t be too specific about the answer. However, if you have several textboxes that have to be filled in, you can use the AfterUpdate event of the last one (assuming they’re all required) to write the value of the calculated control into the non-visible textbox, which will write it to the table. The formula is as simple as this:

Me!txtNonVisible = Me!txtCalculated

Reply | Quote

Thank you for this answer. I want to be able to store the value because there is a roll-up report that is also needed. The values in the fields depend on yes/no answers. For example, if a person is attending all 4 days, the fee is $75. If the person is attending “pert-time” the fee is $50. There are fields for full-time fee and part-time fee, so the iif() function works just fine.

Again thank you.

Reply | Quote

I know that this is an old post, but this explanation helped me alot to get the form correct.
I am working on getting the quoting program that we use in Excel into Access so that recording the quotes will be easier.
However I am having trouble getting the calculated field (now copied to a textbox) to :
a. “stick” to the data entry table
b. show up in the report that must get printed
The two of them are probably related. I have the calculated field named “Price1” (thru “Price10”) and the textbox which copies the value as “P1” (thru “P10”) I have the control source of the textbox as “=Price1” which works fine for the form. Do I need something (macro?) to have (make?) the value “stick” to the data entry table? I think that once I get this solved the value will appear in the report.

Any help would be appreciated.

Thanks,
Mark

Reply | Quote

I think that you are right that problem a causes problem b.
If you want a value to stick, then the control source of the control on the form must be the field name in the table, not another control on the form or a calculation expression.
When you do this, you then need to use code to get the calculated value into the control.
Presumably you enter values into several other textboxes on the screen, and after the last one is entered, you want the price calculated. Put the calculation in the afterupdate event for the last control, and then set the value of the bound control to the result of this calculation.

In practice, you usually need to be able to go back to any of the data entry controls and make changes and have the result updated, so you really need the calculation in the afterupdate event for all of the controls that take data. This requires your calculation to have some way of dealing with empty controls.

To take an example: if price = quantity * unit price, then I would have three controls on the screen. I would enter data into quantity and unitprice.
In the after update event for each I could have
me![price] = me![unit price] * me![quantity]

This would throw an error if either unit price or quantity were Null, so it would better to have:

me![price] = nz(me![unit price],0) * Nz(me![quantity]],0)

The nz function substitutes 0 for Null values.

Reply | Quote

Thanks for your help.
I am able to get the price to show up on the report, but only when I do something (click or try to enter a number) in the P1 text box.
I have the calculated field and the textbox keyed so that if there is no quantity there will be no price shown (if then else).

How would I be able to tie it all together?
This is my statement:
=IIf(IsNull([q1]),” “,(([material cost]+([Labor1]+[Labor1]*4.4)/[q1])/[Margin]*100))
changing the q #’s and Labor #’s up to 10.

Thanks for your help.

Reply | Quote

I can’t tell from this what the problem is. What is q1?
Where is the expression =IIf(IsNull([q1]),” “,(([material cost]+([Labor1]+[Labor1]*4.4)/[q1])/[Margin]*100)) ? It can’t be in an event . Do you have the after update event procedures?

Can you post a cut down version? ie. a new db with just the bare minimum of tables, and queries needed for the form and the one form. No need for the report. The data will show in the report if it is put into the table (assuming of course that the report gets the data from the table)

Reply | Quote