Adding a calculated field to a Form

[floorboy]

Hi, I'm sure you've all had this one and are probably sick of explaining it but I just can't seem to get it to work.

Access 2007, Windows XP sp3

I have created a form based on a table, to enter order details. I want to add a field that calculates the cost of a roll of carpet, [width] * [Length] * [Price], so that when new orders are entered the total cost of the roll is calculated & displayed and as each record is browsed the cost is calculated (later I'll add the vat & the full cost)

I've looked online and realise I have to create a query which I have done by entering [width] * [Length] * [Price] in the first line first column and the query is duly created but now when I open that query I get a dialogue box asking for all the perameters. I'm a bit lost, any help would be much appreciated.

I did read something about bound boxes and bound table but got lost.

Why can't I see the queries in the list of available fields to add to the current form in design view, again, lost

Just a thought, am I better off to use a report to calculate the cost of all rolls outstanding and grouping them by manufacturer, hmmm I could tag each roll as delvered or outstanding and do it that way.

Regards

Mike

 

[missinglinq]

It sounds as if you don't have the fields from the Table in the Query, hence the Query asks for them when opening. What you should be doing is

1. Create a Query based on the table
2. Include the appropriate fields, including Length, Width and Price
3. Include a calculated field such as QuotedPrice:[Length] * [Width] * [Price]

Now base your form on the Query, not on the underlying table.

Linq ;0>

The field QuotedPrice will now appear in the Field lsit and the calculation will be done after the data isentered.

 

[DCrake]

As a footnote, do not use words such as Length and Width as field names as they are Access Reserved words.

Also use BODMAS where possible in your calculations.

 

[SteveSchapel]

Mike,

As an alternative to Linq's suggestion, instead of doing the calculation in a query, you can also just add a textbox to your form, and in its COntrol Source property, enter your calculation expression in there (preceded by a = sign)...
=[Length]*[Width]*[Price]

Agree also with David's comments about the naming of the Width field.

 

[missinglinq]

I thought the same thing, but in point of fact, Length and Width are not listed as Reserved Words in Access 97 thru 2003 in any list I can find! Can't say for 2007/2010, but I seriously doubt that it's changed.

Also, if you built your form by either using the Form Wizard or pulling the fields from the Field List, Access will give the resulting Controls the same names as the fields.

If you don't change the Control names, changing Price to txtPrice, for instance, using Steve's suggestion will result in errors in the calculation. If I remember correctly, the reason for this is that having both the field and control named the same, in this kind of thing, causes a circular reference.

 

[SteveSchapel]

Linq,

I thought the same thing, but in point of fact, Length and Width are not listed as Reserved Words in Access 97 thru 2003 in any list I can find! Can't say for 2007/2010, but I seriously doubt that it's changed.

Thanks for pointing that out. You are correct, they are not shown in the "official" list, and I am surprised about that.

If you don't change the Control names, changing Price to txtPrice, for instance, using Steve's suggestion will result in errors in the calculation.

No, sorry, this is not correct. If the field names and the control names are the same, then it will work fine. If you do, for some reason, change the control names, such that the control names are different from the names of the fields to which they are bound, then calculated control also seem to work fine, regardless of whether you use the field name or the control name.

As a matter of interest, I believe that it is slightly more efficient/quicker to do the calculations in the controls on a form or report itself, rather than in the underlying query. But of course, in practice, this is not a real consideration, and no reason not to do it in the query.

I think what you are thinking of is the stipulation that you should never have a control named the same as a field in the form's Record Source, if the control is not bound to that field. But that's a different story.

 

[missinglinq]

I would never challenge an MVP's answer without testing my response first, Steve! Maybe you can explain this.

I set up a table with the fields

Length Long Int
Width Long Int
Price Currency

I then created a bound form based on this table.

Using the same name for fields and controls and for the calculated field's Control Source using

=[Length]*[Width]*[Price]

I get these results

10 * 15 * 3.00 = 191700

10 * 10 * 4.00 = 170400

20 * 30 * 10.00 = 1278000

Changing the Control names by using the txt prefix the answers are correct.

10 * 15 * 3.00 = 450

10 * 10 * 4.00 = 400

20 * 30 * 10.00 = 6000

I ran this thru before my second post and have run it both ways, three times since your last post, with the same results each time.

The part I was fuzzy about was when I referred to a "circular reference" error. Actually the error is usually called a "Control has a reference to itself" error. I knew I had this archived somewhere, but my memory's not what it used to be, and when I transferred my code archive files to a new machine last week I was amazed to find out that had just shy of 2000 of them.

Don't remember where I got this from, but I trusted the site at the time or I wouldn't have archived it:

Another common error is "Control has a reference to itself." This is when the bound field has a text box of the same name and the control's Control Source Property uses this name to assign a value."

BTW, I was serious when I said "Maybe you can explain this!" I'd really like to know why I'm getting these results if the "same name" thing is not supposed to make a difference.

Linq ;0)>

BTW, Using Access 2003 under (sigh, don't ask!) Windows Vista.

 

[The_Doc_Man]

I'll just add my 0.02 worth.

I have run into the issue of naming the control to match the underlying field. It works when you want to populate the control, and in fact a form wizard will match the control to its field name. But if a field's value computation includes the name of the control itself, there is an ambiguity between the field and the control. Access chooses which one to use - and somehow defies the odds by always choosing the wrong one even when you might think it is a 50-50 proposition.

As to length and width, they are properties of controls. Those funky numbers that were way too big looked like Access picked up a control width in units of twips.

As a general rule, when you get a dialog box asking for something that you thought was the name of a field or other potential data source, you spelled something wrong OR failed to provide proper linkage to that data source. Therefore, Access thinks this must be a parameter query and wants you to enter the parameter.

As to the price, width, and length of your carpet sample, just be sure when you store the invoice that you remember the unit price at that time, since unit price does not have to be constant over time. (Prices do fluctuate, you know.) To regenerate the computation for future review, you must store everything you need; not just the measurement but the price or discounted price as well. Also, if your taxes fluctuate, you should remember to record the tax rate at the time of purchase. Anything that has a time variance in it should be stored somewhere so you can compute the values you used at the time.

 

[SteveSchapel]

Linq,

This is very interesting. Thank you very much for pursuing this, and I am already asking questions from others about this, in particular why it is that Width is not classified as a Reserved Word. Because, you see, this strange behaviour you are seeing is the result of a confusion over the use of Width as a field name, and thus is an example of a quite specific and unusual circumstance. It is really not related at all to the other question we were discussing regarding the naming of controls the same as fields, nor the issue of doing calculations on the form vs in the query.

To illustrate, go back to the design of your test form, and change the width of the form, and then look at the results returned by your calculated control using:
=[Length]*[Width]*[Price]
Still wrong? Yes. The same as before? No. That is because the expression is reading the value of the Width property of the form, and not the value of the Width field.

 

[SteveSchapel]

Doc Man,

... if a field's value computation includes the name of the control itself

This appears to be an example of the problem I referred to in an earlier reply to Linq, i.e. "the stipulation that you should never have a control named the same as a field in the form's Record Source, if the control is not bound to that field."

For the record, I always, always name bound controls the same as the name of the field they are bound to. And I never, ever name an unbound control the same as a field in the Record Source table/query. As a result, I never have experienced any related problems.

 

[SteveSchapel]

By the way, "width" is included on Allen Browne's list of problem words at http://allenbrowne.com/AppIssueBadWord.html#W

 

[floorboy]

Thanks for the response my friends, I'm still working on it and also getting to grips with Dreamweaver, I was a confirmed Frontpage man but have to change so apologies for the delayed reply, I'm further developing the database I have but still struggle. My sincere thanks to those that replied I will try to put your suggestions into practice and let you knoiw where I get to

Many thanks

 

[SteveSchapel]

Best wishes for the continued success of your project, Floorboy.