Summing up a continuous form (1 Viewer)

[Chrism2]

Hello everyone, it's been a while!

I'm adding to a DB which has been working well for a while, but I'm stumped on something which seems a bit silly.

I have a continuous form which has a query behind it. The query takes two values: (For example)

[LineCost] and [LineQTY]

Inside the query, we use the expression TLC: [LineCost] * [LineQTY]

I need to sum this so a Sale with several lines gives me the total sell and cost value.


If you look at the sfrmEditQuote - you'll see the exact same method - working. sfrmEditSale is direct copy of this form, with some changes here and there. But can I get past the dreaded #error? Nope.

I've tried renaming the fields and text boxes, checking the query, but I keep getting #error on the sfrmEditSale form footer.

I have attached the DBs in old and new format. Can someone point me right here?

Thanks!

 

[bob fitz]

Perhaps, in the forms footer, instead of using Sum([TLC])
try something like Sum( [LineCost] * [LineQTY])

 

[Chrism2]

Thanks for the reply. I had considered and tried that - it again returns #error. The big puzzle is why on one form I have a working version and on the other, with few changes, it consisently returns #error.

Perhaps, in the forms footer, instead of using Sum([TLC])
try something like Sum( [LineCost] * [LineQTY])

 

[bob fitz]

Do you have text boxes on the form named [LineCost] and [LineQTY]

 

[Chrism2]

No, I had read Access can confused fields and text boxes, so [LineCost] = "SaleCost". and [LineQTY] = "SalesQuantity".

Do you have text boxes on the form named [LineCost] and [LineQTY]

 

[bob fitz]

So perhaps it needs to be: Sum([SaleCost] * [SalesQuantity])

 

[Chrism2]

Still no joy.

So perhaps it needs to be: Sum([SaleCost] * [SalesQuantity])

 

[bob fitz]

I can't see any text boxes named [SaleCost] or [SalesQuantity]

EDIT:
Ok I've found [SaleCost] but not [SalesQuantity]

 

[Chrism2]

In sfrmSaleLines, next to the label "Cost:" this Text box houses the field "linecost", but the text box control is called SaleCost. Same with "lineqty" - click on it and you'll see it is called something else.

Although the other, working form, field and text box control have the same name anyway.

If I create a new continuous form, based on the exact same underlying query, and simply create a text field in the Form Footer and use the expression =Sum([TLC]) it works. And in that continuous form, TLC is both the underlying field and control name.

Why this is broken is a mystery...

 

[bob fitz]

Same with "lineqty" - click on it and you'll see it is called something else.

So what is it called

 

[Chrism2]

SalesQuantity

 

[vbaInet]

Corrupt form, rebuild it. Have a look at the attached db and look at frmSaleLines.

I'm dubious about the way you're sharing the application. How are your users connecting to it?

 

[Chrism2]

Cheers, I'll have a look at that later and try a rebuild.

That DB is a segment of a much larger DB where the tables are sharepoint lists. I've ripped out only the relevant tables and forms to the problem (and converted the tables to local ones). The extra fields in the tables are put there by sharepoint.

I've encountered corrupt forms before, is the only solution to start again, or is there a way to clear things up?

 

[Chrism2]

You are not wrong, the corruption appears to be in the field which adds up the number of Items (=sum([lineqty]) , removing it and replacing it fixes the summing up.

:banghead:

Thanks all for steering me to the solution.

Corrupt form, rebuild it. Have a look at the attached db and look at frmSaleLines.

I'm dubious about the way you're sharing the application. How are your users connecting to it?

 

[vbaInet]

I would not just replace that control, redo the entire form.

Is your db split? And does each user have their own front end?

 

[Chrism2]

I have indeed re done the entire form, it was useful to know where the issue was though.

Yes, each user is deployed their own copy of the front end when they log into a PC, they must be signed into Sharepoint and this in turn lets them connect to the lists stored in the back end.

It's worked rather well for a couple of years. The best things about it are remote access and when people delete records accidentally, Sharepoint recovers from this very well. I've been asked to expand its capability to include sales tracking, so I'm working from there.

 

[vbaInet]

Strange how that probably crept in!