Calculated Field on form (beginner question)

[Williams485]

Hello Access Forum,



I'm a newcomer to this forum and I'm a beginner with Access, so apologies for any questions that seem naive or ignorant!

I have a calculated field that shows the sum of two other fields on the same form. I've got as far as making the expression work but it doesn't quite do what I want. I need the calculated field to update when any one of the reference fields are updated (i.e. currently it only updates once all the fields in the expression are updated).

How to I change the form so that the calculated field updates based on current values (including any fields that contain no value)?

Many thanks

[RuralGuy]

This link should help: http://allenbrowne.com/casu-14.html

[qa12dx]

thanks RG. i didn't know about the calc field in access 2010. ess

re thread q - like the link says, calculated field bad. be sure to read why. it will save u a LOT of heartburn
in the form, put in an unbound textbox and just like u made the expression in the table, u'll do the same only u'll put a "equal to" sign.

place a unbound textbox in form. if property box not visible, right click textbox and properties. under Data tab - Control Source put in ur expression, like below;

=[qty]*[price]

just remember basic math, BODMAS when u write these expressions. any terminology u r not fimiliar with google it

[RuralGuy]

If you put the calculation in the query then you can bind a TextBox control to the AnswerField and the answer updates every time you change the input fields.

[Williams485]

Hello and thanks for your replies.
I've picked this up again having looked at the resource by Allen Browne and seem to be doing what it suggests. However, when I test my form and query I get "#NAME?" in the text box that contains the calculated field.
The things I've done so far are:
1. Enter expression into Query Design grid - the expression is exactly

TtlLGDays: [FTSP LG Days] + [SA LG Days]

2. On the form, select control source of the text box and open the Expression Builder dialogue. Select the new query - this enters

[TtlLGDays]![TtlLGDays]

into the expression box.

When I then test the form, I get the error. From what I've read the error is usually an incorrect reference to the expression, field name, or something like this. However, I can't see any issue (perhaps I'm looking too hard!).

Any suggestions welcome!

Thanks

[RuralGuy]

How about naming your calculated field something different from the name of the query?

[Williams485]

Thanks, RuralGuy! Changed the name of the query so now reads:

=[TtlLGDays]![qryLGDays]

but the same error still occurs. Is there a tool in Access that will list all names (of fields, controls, queries, etc) so that I can glance through to ensure there's no more ambiguity?
Is there anything else that could be causing the error?

[RuralGuy]

Is this value in the DropDown list of the ControlSource of that control?

[Williams485]

It's the value shown in the property list but I didn't select it from the DropDown - I had to select it from the Expression Builder dialogue (...). It doesn't appear in the DropDown at all.

[RuralGuy]

remove the RecordSource ofn the form and close the form after saving. Then open the form and set the RecordSource to the query again. Your field should be listed in the DropDown selection for the ControlSource of the TextBox and you should be able to select it. If not, then something is wrong.

[Williams485]

I appreciate your continued help on this one... I've followed your instruction but the field doesn't appear on the list. It does, however, appear in the Record Source DropDown for the form itself? Does this give you any indication as to what is wrong?
many thanks

[RuralGuy]

The RecordSource dropdown displays queries and tables, not fields in them. Does that suggest something?

[RuralGuy]

Did I loose you on that one?

[Williams485]

Good morning, RuralGuy. It seems I hadn't actually posted my last reply of the day - but you did lose me a little.

It tells me that my query is stored as a query (because it is showing in the Record Source drop down) but that my calculated field isn't, as it should be, in the Control Source drop down, which would suggest that there's something wrong with the field, not the query. This could be something wrong with the way I'm trying to set up my calculated field, or it could be something fundamentally wrong with my understanding of this situation!

Any more clues??

[RuralGuy]

How about posting the SQL for the query so we can see it?