Cant get sum to work on a form (1 Viewer)

Nick.NZ

New member
Local time
Tomorrow, 11:51
Joined
Nov 14, 2021
Messages
15
Hey guys,

So I've got a very basic problem, but i can't understand for the life of me how to solve it.

1. I have a main form
2. I have a sub form.

In the sub form it contains the Unit price, and quantity supplied. I added a text box into the sub form which is the product total. The product total is simply the quantity supplied * the unit price. This works fine. All i want to do is have a "product total option. on the main form, can be in the footer or the body of the main form, doesn't matter.

The problem is every time i use the Sum function in the text box ,to reference the sub form field of "product total" it just displays a #Error in the order total text box.

I've read some posts that say you can't use a calculated control to do a sum. So i tested this out on the same of quantity supplied (which is not a calculated field) and it gives and error as well.

I have been careful to reference the correct field in the sub form.

Just to note a few things. The only fields in the query were Unit price, and Quantity supplied. Product total was a added text box. Order total was an added text box into the form.

I just cannot get the sum function to work on the main form for anything. Any suggestions would be great.


Thanks,
NIck
 

Nick.NZ

New member
Local time
Tomorrow, 11:51
Joined
Nov 14, 2021
Messages
15
So the name of the product total text box is "producttotal" as you can see from the 4th screenshot.
 

Attachments

  • screen1.JPG
    screen1.JPG
    50.8 KB · Views: 345
  • sceen2.JPG
    sceen2.JPG
    182.2 KB · Views: 352
  • screen 3.JPG
    screen 3.JPG
    161.1 KB · Views: 230
  • screen4.JPG
    screen4.JPG
    152.4 KB · Views: 345

June7

AWF VIP
Local time
Today, 14:51
Joined
Mar 9, 2014
Messages
5,423
Have to do the Sum within the subform header or footer. Even if subform is Datasheet view and the textbox does not show. Then textbox on main form references subform textbox.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:51
Joined
May 7, 2009
Messages
19,169
you can't have a Sum like that. you can only Sum on Bound Fields.
but you can also add code to your Subform's Current Event:

Code:
private sub form_current
dim dblTotal as Double
with me.recordsetclone
    if not (.bof and .eof) then
        .movefirst
    end if
    do until .eof
        dblTotal = dblTotal + (CDec(Nz(![QuantitySupplied, 0))* CDec(Nz(![UnitPrice], 0)))
        .MoveNext
    Loop
End With
Me.Parent![Text28] = dblTotal
End Sub
 

Nick.NZ

New member
Local time
Tomorrow, 11:51
Joined
Nov 14, 2021
Messages
15
Have to do the Sum within the subform header or footer. Even if subform is Datasheet view and the textbox does not show. Then textbox on main form references subform textbox.

Okay so the view that i would like is datasheet" because this displays both products on a single page. The issue is for some reason it doesn't show the header or footer if i were to put a text box in there.

If i change the view to continuous, i can see the header or footer, but it'll show 1 product on each page, rather than 2 products on the same page.

Even on the continuous view the sum doesn't work.

It does work how ever if i do =Sum( [QuantitySupplied] * [UnitPrice] )

but again that will not work on the main form. Only on the subform, and will only show on Continuous view

Sorry for my newbieness, i dont know anything about visual basic. Purely just using the access interface.
 

Attachments

  • screen5.JPG
    screen5.JPG
    200.1 KB · Views: 343
  • screen6.JPG
    screen6.JPG
    52.7 KB · Views: 346
  • screen7.JPG
    screen7.JPG
    57 KB · Views: 245

Nick.NZ

New member
Local time
Tomorrow, 11:51
Joined
Nov 14, 2021
Messages
15
you can't have a Sum like that. you can only Sum on Bound Fields.
but you can also add code to your Subform's Current Event:

Code:
private sub form_current
dim dblTotal as Double
with me.recordsetclone
    if not (.bof and .eof) then
        .movefirst
    end if
    do until .eof
        dblTotal = dblTotal + (CDec(Nz(![QuantitySupplied, 0))* CDec(Nz(![UnitPrice], 0)))
        .MoveNext
    Loop
End With
Me.Parent![Text28] = dblTotal
End Sub
Thanks for replying.

Sorry i dont know anything about visual basic, i'm just using the access interface. Thanks for typing up that code. Is there no way around this without writing code?! seems like it should be such a simple thing to do
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:51
Joined
May 7, 2009
Messages
19,169
see this demo and see the Controlsource of Text28.
 

Attachments

  • test_test.accdb
    488 KB · Views: 368

Nick.NZ

New member
Local time
Tomorrow, 11:51
Joined
Nov 14, 2021
Messages
15
Alright i managed to sort it out.

In the sub form footer text box (text28 its called in this case) i put =Sum([QuantitySupplied]*[UnitPrice])

then i put another text box in the main form and just put =[test2 subform].[Form]![Text28]

and its all working. Thanks for taking the time to reply guys.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:51
Joined
Sep 21, 2011
Messages
14,048
Alright i managed to sort it out.

In the sub form footer text box (text28 its called in this case) i put =Sum([QuantitySupplied]*[UnitPrice])

then i put another text box in the main form and just put =[test2 subform].[Form]![Text28]

and its all working. Thanks for taking the time to reply guys.
Might want to start giving meaningful names to the controls?
If you come back to make changes after 6 months, text28 is not going to mean much? :(
Good habit to get into. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:51
Joined
Feb 19, 2002
Messages
42,976
I'm with Gasman. You would be doing yourself a HUGE favor if you stopped being sloppy about control names. Whenever you add a control to a form or report, the next thing you should do is to give it a rational, meaningful name. THEN you can add code or reference it elsewhere. You can clean up the names after the fact but it is much more trouble.
 

Nick.NZ

New member
Local time
Tomorrow, 11:51
Joined
Nov 14, 2021
Messages
15
Yeah will do in future. It was just trying to get the thing working, so i ended up deleting and adding text boxes multiple times. But yeah i realize its good practice to properly name things.

Thanks for the feedback.
 

Users who are viewing this thread

Top Bottom