Form footer calculation error

[davidbodhi]

I have a simple tabular form with the following:

cboItem_Name
txtItem_Cost
txtQuantity
txtExtended_Price

The AfterUpdate for txtQuantity = Me!txtExtended_Price = Me!txtItem_Cost*Me!txtQuantity, which puts the extended price in the control.

In the FOOTER of the form, I have txtSub_Total. It's Control Source is:
=SUM([txtExtended_Price]).

As far as I can tell from reading, when placed in the form footer and used in 'continuous form' layout, this ought to give me the total cost of ordered items.

Instead, I am getting #error. Can anyone help me with this?

 

[ScottGem]

I have a simple tabular form with the following:

cboItem_Name
txtItem_Cost
txtQuantity
txtExtended_Price

The AfterUpdate for txtQuantity = Me!txtExtended_Price = Me!txtItem_Cost*Me!txtQuantity, which puts the extended price in the control.

In the FOOTER of the form, I have txtSub_Total. It's Control Source is:
=SUM([txtExtended_Price]).

As far as I can tell from reading, when placed in the form footer and used in 'continuous form' layout, this ought to give me the total cost of ordered items.

Instead, I am getting #error. Can anyone help me with this?

Use:
=SUM(Me!txtItem_Cost*Me!txtQuantity)

Also, no need for the After Update. Just set the controlsource for Me!txtExtended_Price as:

=[txtItem_Cost]*[txtQuantity]

 

[davidbodhi]

Scott - Thanks (again) for assistance.

I've made the exact changes you suggested (via copy and paste), including going back to having Control Source for txtExtended_Price being the calculation instead of using the AfterUpdate event, but am still getting #error in the sub total box. Access "fixed" the syntax slightly, putting square brackets around each "Me" and fieldname, suggesting it likes the syntax, but still nada.

This code is so simple it doesn't even deserve the term "code" but it's still not right.

I just created a junk form that adds two fields and sums the result. Using =SUM([result]) does not work, but using =SUM([field1]+[field2]) does.
WHY?

And, more relevantly, why doesn't similar code in my real form work?
To put it technically, ARGH!!!......

I do have each field format set as 'currency'. That ought to be ok, right?
This makes SO little sense to me..........

 

[ScottGem]

Try The Sum without the Me.

=Sum(txtitem_Cost*txtQuantity)

 

[davidbodhi]

OK, tried that, too, Scott....

=Sum(Me!txtItem_Cost*Me!txtQuantity)
and
=Sum([txtItem_Cost]*[txtQuantity])

both return #error.

Access puts the square brackets in, itself.

I'm stumped, not that that's a big surprise.... it OUGHT to work, right?

It's the same syntax I used in the junk form, adding number fields. Is it posible that it HAS to be general number instead of currency? Nope. Just tried that. Curses!@#$%#^#

 

[ScottGem]

Currency is only a format. It doesn't affect the underlying value. It is possible that there is a null anywhere? A record that has no quantity or unit cost? That would cause an error.

If that's not it, can you post a stripped down version of your database?

 

[davidbodhi]

Can't be a record with a problem, Scott, since I'm just creating a few on-the-fly as I'm testing, and deleting them again.

I'll post the database, thanks. The table and form I'm dealing with are tblPurchased_Items frmPurchased_Items. My intention is to use frmPurchased_Items as a subform.

Hope you can find my error.

 

[ScottGem]

Try it with the field names instead of the control names:

=Sum([Quantity]*[Item_Cost])

 

[davidbodhi]

That worked, Scott! Now, being the kind of person I am, I want to know why THAT worked and not any of the other syntaxes we tried. Do you know? Creating my bogus form the other day, other syntax worked.

Is it my version of Access? Is it the context? Is it the operating system I'm developing in? Is it the weather? Is there some rhyme or reason why this works and that doesn't, or is it just "on account of because"? Does one just have to keep trying different ways of saying the same thing till one just happens to work? It's sure be great to know ahead of time.

Also, while the sub total field now totals, the number doesn't update until after the last item's record loses focus. I'm sure I need to tell it to refresh itself, but am not sure where that command, ideally, should go. Any suggestions?

One other little thing, not very important, really. When this form is placed in it's parent form, each time an item is added, the 'new record' blank moves lower in it's window until you either have to use the scroll bars or click the new record asterisk button. Do you know how to make it stay visible automagically?

Also, I want to say I *really* appreciate your assistance and PERsistence in helping me through these basic, simple and annoying problems.

 

[ScottGem]

The Sum function works on the field names not the control names. Why it works that way I don't know.

You can probably put Me.Repaint or Me.Refresh in the After Update of the control where you want the totals to update.

Don't think there is anything you can do about the new record going at the bottom.

 

[davidbodhi]

Now I feel like I'm really imposing on you.

Whenever my subform has any records, whether created standalone or within the parent form, and I close the main form and then reopen it, I get the following big ol' error and a white blank spot on the main form, where the subform ought to be:

"This expression is typed incorrectly, or it is too complex to be evaluated. For Example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Without changing any code, if I delete the records in the subform's table, the main form opens fine.

Do you know what this is about? Ever seen the problem?

 

[ScottGem]

If I didn't want to be imposed on I would do this. The message indicates there is some expression in the form or query behind the form, which is causing a problem. You have to root out that expression.

 

[davidbodhi]

OK, I see now that it wasn't so ugly as I'd thought. It wasn't a calculation error or bad syntax in my queries or whatever awful thing I thought.

It was my poor naming scheme. In the main form. Something was looping because some form text boxes had the same name as table fields. Changing the control names on the main form to the conventional style eliminated the error.

Now I'll go back , edit my VBA sybtax to reflect the corrected control names and see if the error comes back. If not, it was nothing more than that. If it does, I'll be back at looking for bad syntax, etc.

 

[kindlysoul]

Hi, I know this isn't solution to your problem, but my googling lead me to this page and I was having the same problem. I was trying to get the count of records in the report and kept inexplicably getting #error. All that was needs was to move the text box from the Page Footer to the REPORT Footer!