Subform Total In Main Form (1 Viewer)

M

MartL

Registered User.
Local time
Today, 01:35
Joined
Sep 15, 2012
Messages
14
Hi bit of an Access newbie here looking for a bit of help.

For my carpet shop Ive made a pretty simple database that I have been using for the past year but Im now wanting to spruce it up a little and have been a regular lurker on this forum trying to pick up hints and tips.

The problem I'm having at the moment is getting a total from a subform to appear in my main form. Ive read thread after thread and think that I am following the instructions but I always end up with the result showing #error.

In my subform named Customer Order Items subform I have an expression =Sum([Total Sale Price]) the text box for this is called Order Total. This is working on the sub form and is set to currency.

On the main form I have tried different expressions which all seem to have the same result. I have tried =[Customer Order Item subform]![Order Total] and also =[Customer Order Item subform].[Form]![Order Total]

Any help on this matter would be greatly appreciated as I seem to be totally stuck on the matter at the moment. Also just quickly as well Im just wondering once I have a certain order open how i go about opening that single order on its own say for instance I have the order open and want to click a button saying Invoice Order and have a report open for that record only so i can print off a record. Is that pretty simple to do or am I going down the route of having to write some VBA Code?
 
M

MartL

Registered User.
Local time
Today, 01:35
Joined
Sep 15, 2012
Messages
14
mahenkj2 said:
Just see this if helps.
Click to expand...

Thanks, that was one of the guides I tried to follow but with no luck. Im sure theres something really simple im missing.
 
M

MartL

Registered User.
Local time
Today, 01:35
Joined
Sep 15, 2012
Messages
14
Ive been messing and I can get this to work if I follow the instructions and put the text box in the Form footer but the same thing dosnt work in the Form detail box. Is it not possible to get that result in the detail box?
 
M

MartL

Registered User.
Local time
Today, 01:35
Joined
Sep 15, 2012
Messages
14
I wasnt sure the best way to upload it as it said the original file was too big so I made it to a Zip file but wasnt sure if that was the done thing. The Customer Order Form and the Order Items Subform are the ones that seem to be causing me the trouble.

Thanks for taking a look.
 

Attachments

  • New Cuttin Edge.zip
    344.7 KB · Views: 347
C

Catalina

Registered User.
Local time
Yesterday, 17:35
Joined
Feb 9, 2005
Messages
462
I renamed the forms to: frmOrders and sfrmOrderDetails (no spaces in the names).

The fields in the footer of the sub form are now: fldSum and fldTotal.

The control source of the fields on the main form are set to:

=[Forms]![frmOrders]![sfrmOrderDetails]![fldSum]
=[Forms]![frmOrders]![sfrmOrderDetails]![fldTotal]

It works fine now.

Catalina
 

Attachments

  • New Cuttin Edge.zip
    146.9 KB · Views: 391
M

MartL

Registered User.
Local time
Today, 01:35
Joined
Sep 15, 2012
Messages
14
Thanks for that works perfectly on the one you did however, I still have a problem in that I tried to follow your instructions on mine just as a walk through to get it in my head how to do it properly and it still wont work properly. When I do the above I am now getting the #Name? error. Ive checked spellings and things and from what I can see the way I have done it is exactly the same.

Ive uploaded both Catalina`s file and my file called CuttinEdge1, on mine Ive just tried to do the Total Cost Price on the main form and if someone could take a look and see what Im doing wrong I would be really grateful.
 

Attachments

  • New Cuttin Edge.zip
    532 KB · Views: 369
C

Catalina

Registered User.
Local time
Yesterday, 17:35
Joined
Feb 9, 2005
Messages
462
Open the forms in Design View, select the sub form, go to Properties and
change the Name to: sfrmOrderDetails. Then it will work.

Catalina
 
M

MartL

Registered User.
Local time
Today, 01:35
Joined
Sep 15, 2012
Messages
14
Thats done it thanks very much. Didnt realize about renaming it there.
 
C

Catalina

Registered User.
Local time
Yesterday, 17:35
Joined
Feb 9, 2005
Messages
462
You're welcome, good luck with the database and the business.

Catalina
 
J

JRS23

New member
Local time
Today, 03:35
Joined
Nov 28, 2012
Messages
2
hi there, i'm just new in using MS access and i'm trying to create a simple database for e-visa project. i think i have the same problem as stated above. I tried following the instruction but i cant get the correct result.

Here's the background of my problem.

i have 2 tables tbl_receivedvisa and tbl_issuedvisa.
i put the 2 tables in one form without using any query. tbl_receivedvisa is the main form and tbl_issuedvisa is the subform. below are the contents of each table.

tbl_receivedvisa
1. visa_no
2. visa_title
3. total_receivedvisa
4. utilized_visa
5. remaining_visa

tbl_issuedvisa
1. visa_no
2. visa_title
3. issued_visa

my objective here now is to calculate (get the sum) of all issued visa in the subform.

i've already tried putting unbound text [txttotal] in the form footer =Sum([issued_visa]) and other unbound text in the main form =[sfrmvisa_issued].[Form]![txttotal] but i still didn't get the correct result and i'm getting #error instead.

Hope you can help me with this issue. thanks in advance
 
J

james_liv

New member
Local time
Today, 01:35
Joined
Mar 4, 2020
Messages
25
Hello,

Sorry to dig up a really old query but im struggling with the same issue.

No matter what I do I cannot seem to get the total of the field [S Total] in [tblSalesinv subform] to add up into the field [S Total] in the main [frmSalesSheet].

IF you click menu to start, then click sales and you'll see what I mean.

Can anyone help as ive been trying to around 6 hours and my eyes cant cope!

Thank you so much in advance.
 

Attachments

  • Booking Database_new.zip
    115.5 KB · Views: 75
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:35
Joined
Aug 30, 2003
Messages
36,125
You can't sum a calculated control, only fields in the data. Either move that calculation to the subform's source query or:

=Sum([F Price]*[Qty]-[Discount])

My preference is to use parentheses to make sure the order of calculation is what I want.
 
J

james_liv

New member
Local time
Today, 01:35
Joined
Mar 4, 2020
Messages
25
pbaldy said:
You can't sum a calculated control, only fields in the data. Either move that calculation to the subform's source query or:

=Sum([F Price]*[Qty]-[Discount])

My preference is to use parentheses to make sure the order of calculation is what I want.
Click to expand...
Awsome.
Thank you so much. That worked perfectly.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:35
Joined
Aug 30, 2003
Messages
36,125
Happy to help and welcome to AWF by the way!
 
You must log in or register to reply here.

Similar threads

G
Solved Error #size! on calculated control based on subform value
Replies
2
Views
184
GaP42
G
M
Display subform confrol total to mainform
Replies
18
Views
466
Cotswold
Cotswold
P
Solved Filter subform so total quantity in subform matched the main form quantity
Replies
11
Views
642
Poco_90
P
C
Solved Highlight backcolor in only 1 input on a continuous form
Replies
10
Views
524
riktek
R
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
441
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom