total amount calculation in access 03

[executiverocker]

im building a database that will take customer orders, but whatever i do i take one step forward and 12 steps back!!!!!!!!!!

ive got a transaction form with transaction ID, date and time, then a subform that contains Item ID, description, quantity, price and line total.

Ive been trying queries, control source etc but to no avail

All i want is to be able to work out the cost of each line then get a total! I either get a #name?, #error?, or not even that far as i get frustrated with it and have to walk away before i smash my laptop

Can anyone help? im at my wits end

 

[Scottyk537]

In the control source of [line total] write,
=[quantity]*[price]
if those are the names of the textboxes.
To get a grand total, use a form footer. Create a new text box with its control source
=sum([line total])

If still not working, you'll have to post your DB.

 

[executiverocker]

ive tried that, it works it out in the form, but if i goto the table, there are no values stored in the "line total" column

 

[Scottyk537]

Why would you want to store a calculation in the table?
Run the calculation on the form or in a query at runtime. There is no reason the store this value in a table! No wonder your driving yourself crazy.
You can store this value using VB coding, but I do not recomend it.

 

[executiverocker]

well when i run the sum([linetotal]) it keeps giving me #error. the table shows linetotal as 0.00 for every line. So i thought it may be something to do with that? The line totals are correct when the form is run though

 

[boblarson]

the control source for the text box should be:
=Sum([linetotal])

But your text box cannot be named linetotal and the text box needs to be on the SUBFORM Footer, not the main form.

 

[executiverocker]

ok ive put it inside the form footer of the subform, but it doesnt appear. Is this because the subform is a datasheet?

 

[boblarson]

Yes, that would be why. I THINK you can reference it from a control on the main form though. Try putting a text box on the main form and set it's recordsource to:

=[Forms]![YourMainFormNameHere].[Form].[YourSubformContainerNameHere].[Form].[YourSubformTextBoxNameHere]

 

[executiverocker]

=Forms!Transaction.Form.transactionpart.Form.linetotal

throws up a #name? error. the text box is called totalcost so no conflict

 

[boblarson]

Make sure that transactionpart is truly the name of the CONTAINER that houses the subform on the main form. The container name is what needs to be referenced. If you added the subform with the wizard then it's likely the same name as the subform, but it isn't necessarily.

Also, if your text box is named totalcost then the control source on the main form should be:
=[transactionpart].[Form]![totalcost]

(I made an initial error in the syntax. I created a test and determined that)

 

[executiverocker]

the sub form is called "transactionpart subform" on the list, ive tried that formula and again ii get a #name? error

the text box on the main form is called mainformtotal

the text box on the subform is called total cost

the box on the subform with the information i need is called linetotal

 

[boblarson]

Any chance you can post your db?

 

[executiverocker]

http://public.box.net/executiverocker72993 you should be able to access it from there

 

[boblarson]

I can't access that site from work - it's a restricted site. Can you run Compact and Repair (Tools > Database Tools > Compact and Repair) and then zip it into a zip file and send to me via email? I'll PM you my email address.

 

[Rich]

In the control source of [line total] write,
=[quantity]*[price]
if those are the names of the textboxes.
To get a grand total, use a form footer. Create a new text box with its control source
=sum([line total])

If still not working, you'll have to post your DB.

You cannot Sum calculated fields, the calculation has to be repeated
ie =Sum(Quantity*Price)

 

[executiverocker]

I can't access that site from work - it's a restricted site. Can you run Compact and Repair (Tools > Database Tools > Compact and Repair) and then zip it into a zip file and send to me via email? I'll PM you my email address.

ok thanks ill zip it up

 

[boblarson]

Good point Rich- I must be slightly brain dead today

 

[boblarson]

You could have posted it here if you zipped it. You can't post a straight mdb file here anyway. But compacting it first and the zipping it I got 61Kb which, the last time I checked is less than 393Kb (the limit).

Anyway, fixed it. Rich's thing was just what we needed and I probably had the syntax slightly off. Take a look at both the footer text box and the other on the main form to see what I did.

 

[executiverocker]

You could have posted it here if you zipped it. You can't post a straight mdb file here anyway. But compacting it first and the zipping it I got 61Kb which, the last time I checked is less than 393Kb (the limit).

Anyway, fixed it. Rich's thing was just what we needed and I probably had the syntax slightly off. Take a look at both the footer text box and the other on the main form to see what I did.

thanks!! it was making me go crazy! yeah it was almost there, ill have to keep this handy! Ill know for next time, thanks