|
Home > Archive > MS Access database support > August 2005 > Calculated total on subform will not display on main form
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Calculated total on subform will not display on main form
|
|
| Shannan Casteel via AccessMonster.com 2005-08-24, 8:26 pm |
| I have a subform named "sbfrmParts" with a list of parts along with the
quantity and price. I have used a text box in the subform's footer and set
the control source to "=Sum([NET PRICE]*[Quantity])". I set the format to
display currency. This text box is called "SubformTotal", and is visible
property is set to "No". On the main form I have made another text box and
set its control source to "=[sbfrmParts].Form!SubformTotal".
When I enter some parts everything works perfectly until I look at the text
box on the main form. It only displays "#Error".
What is wrong?
Any suggestions?
Shannan Casteel
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Wayne Morgan 2005-08-24, 8:26 pm |
| A calculated control will update itself when you change the other controls
it is based on if those controls are on the same form. When they are on a
different form, such as a subform, then you need to tell the calculated
control to update. To have it update after you add each new record to the
subform, in the subform's Current event try:
Me.Parent. NameOfControlOnMainF
orm.Requery
--
Wayne Morgan
MS Access MVP
"Shannan Casteel via webservertalk.com" <forum@webservertalk.com> wrote in
message news:535573BAD0681@w
ebservertalk.com...
>I have a subform named "sbfrmParts" with a list of parts along with the
> quantity and price. I have used a text box in the subform's footer and
> set
> the control source to "=Sum([NET PRICE]*[Quantity])". I set the format to
> display currency. This text box is called "SubformTotal", and is visible
> property is set to "No". On the main form I have made another text box
> and
> set its control source to "=[sbfrmParts].Form!SubformTotal".
>
> When I enter some parts everything works perfectly until I look at the
> text
> box on the main form. It only displays "#Error".
>
> What is wrong?
>
> Any suggestions?
>
> Shannan Casteel
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-24, 8:26 pm |
| Wayne,
Thanks for your reply. I tried entering "Me.Parent.MainFormTotal.Requery" in
the OnCurrent event of the subform, but it still returns "#Error" in the
MainFormTotal text box on the main form. My subform is based on a query.
Don't know if that matters, just thought I'd tell you.
Thanks for the help.
Wayne Morgan wrote:[color=darkred
]
>A calculated control will update itself when you change the other controls
>it is based on if those controls are on the same form. When they are on a
>different form, such as a subform, then you need to tell the calculated
>control to update. To have it update after you add each new record to the
>subform, in the subform's Current event try:
>
>Me.Parent. NameOfControlOnMainF
orm.Requery
>
>[quoted text clipped - 14 lines]
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Wayne Morgan 2005-08-24, 8:26 pm |
| Going back to your original statement, the brackets should be as follows:
=sbfrmParts.Form!SubformTotal
On my main form textbox which refers to a control on the subform, Access
tried adding brackets but it worked after I removed them. It usually adds
what it needs, but not always and sometimes too much. Also, sbfrmParts
should be the name of the subform control on the main form, not the name of
the subform itself; however, they may both be the same.
--
Wayne Morgan
MS Access MVP
"Shannan Casteel via webservertalk.com" <forum@webservertalk.com> wrote in
message news:53560C3C2C809@w
ebservertalk.com...
> Wayne,
>
> Thanks for your reply. I tried entering "Me.Parent.MainFormTotal.Requery"
> in
> the OnCurrent event of the subform, but it still returns "#Error" in the
> MainFormTotal text box on the main form. My subform is based on a query.
> Don't know if that matters, just thought I'd tell you.
| |
| Bob Quintal 2005-08-24, 8:26 pm |
| "Shannan Casteel via webservertalk.com"
<forum@webservertalk.com> wrote in
news:53560C3C2C809@w
ebservertalk.com:
> Wayne,
>
> Thanks for your reply. I tried entering
> "Me.Parent.MainFormTotal.Requery" in the OnCurrent event of
> the subform, but it still returns "#Error" in the
> MainFormTotal text box on the main form. My subform is based
> on a query. Don't know if that matters, just thought I'd tell
> you.
>
> Thanks for the help.
>
An alternate approach is to leave the textbox on the main form
unbound, and put code in the subform's AfterUpdate Event to set the
textbox on the mainform's value
e.g. parent!subformTotal = me.subformTotal
--
Bob Quintal
PA is y I've altered my email address.
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 9:25 am |
| I tried deleting the brackets around the formula, but Access just keeps
adding them back in. How'd you do that.
Shannan
Bob Quintal wrote:
>[quoted text clipped - 6 lines]
>
>An alternate approach is to leave the textbox on the main form
>unbound, and put code in the subform's AfterUpdate Event to set the
>textbox on the mainform's value
>
>e.g. parent!subformTotal = me.subformTotal
>
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 9:25 am |
| I also tried Bob's approach, but it didn't seem to do anything. When I went
back to the main form the text box was blank.
Shannan
Bob Quintal wrote:
>[quoted text clipped - 6 lines]
>
>An alternate approach is to leave the textbox on the main form
>unbound, and put code in the subform's AfterUpdate Event to set the
>textbox on the mainform's value
>
>e.g. parent!subformTotal = me.subformTotal
>
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 9:25 am |
| Guys,
I went to the Northwind sample database and checked all the syntax and it was
exactly the same as mine. I don't know what is wrong. Something in
properties maybe? This is driving me nuts.
Shannan
Shannan Casteel wrote:[color=darkred
]
>I also tried Bob's approach, but it didn't seem to do anything. When I went
>back to the main form the text box was blank.
>
>Shannan
>
>[quoted text clipped - 7 lines]
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Wayne Morgan 2005-08-25, 9:25 am |
| If both methods aren't working, then I would suspect that one of the
controls' or forms' name is wrong.
--
Wayne Morgan
MS Access MVP
"Shannan Casteel via webservertalk.com" <forum@webservertalk.com> wrote in
message news:535F88C4684C4@w
ebservertalk.com...
> Guys,
>
> I went to the Northwind sample database and checked all the syntax and it
> was
> exactly the same as mine. I don't know what is wrong. Something in
> properties maybe? This is driving me nuts.
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 11:25 am |
| Wayne,
Here is how it is setup. I went and double checked just to be sure.
Subform Name: sbfrmParts
Main Form Name: frmClaim
Subform text box name: SubformTotal
Main Form text box name: MainFormTotal
Main Form text box control source: = [sbfrmParts].Form!SubformTotal
I could not get those last set of brackets to go away. The text box on the
main form still displays "#Error".
What's wrong?
Wayne Morgan wrote:[color=darkred
]
>If both methods aren't working, then I would suspect that one of the
>controls' or forms' name is wrong.
>
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Wayne Morgan 2005-08-25, 11:25 am |
| I probably misspoke on the brackets, although on mine they aren't there.
There is one control you've left off, what is the name of the subform
control on the main form? This control holds the subform and its name may or
may not be the same as the subform itself. This control is where you set the
Parent/Child Link fields.
--
Wayne Morgan
MS Access MVP
"Shannan Casteel via webservertalk.com" <forum@webservertalk.com> wrote in
message news:535FC0A132530@w
ebservertalk.com...
> Wayne,
>
> Here is how it is setup. I went and double checked just to be sure.
>
> Subform Name: sbfrmParts
> Main Form Name: frmClaim
> Subform text box name: SubformTotal
> Main Form text box name: MainFormTotal
> Main Form text box control source: = [sbfrmParts].Form!SubformTotal
>
> I could not get those last set of brackets to go away. The text box on
> the
> main form still displays "#Error".
>
> What's wrong?
| |
| Tim Marshall 2005-08-25, 11:25 am |
| Shannan Casteel via webservertalk.com wrote:
> Subform Name: sbfrmParts
> Main Form Name: frmClaim
> Subform text box name: SubformTotal
> Main Form text box name: MainFormTotal
> Main Form text box control source: = [sbfrmParts].Form!SubformTotal
>
> I could not get those last set of brackets to go away. The text box on the
> main form still displays "#Error".
Probably something you've checked already, but is SubformTotal a bound
control? If so, what is it's control source? If it is also
SubformTotal (from a query or the SQL behind the subform) this will be
your error source.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| Tim Marshall 2005-08-25, 11:25 am |
| Tim Marshall wrote:
> Shannan Casteel via webservertalk.com wrote:
>
>
>
> Probably something you've checked already, but is SubformTotal a bound
> control? If so, what is it's control source? If it is also
> SubformTotal (from a query or the SQL behind the subform) this will be
> your error source.
I just looked at your original post.
Following the theme of my quoted suggestion, above, have you tried
making the SubformTotal text box in the subform visible and checked to
see if you are also getting an error there? If you are, confusion in
naming will be the likely culprit. Check to see if the controlsource of
SubformTotal, which is =Sum([NET PRICE]*[Quantity]) is getting confused.
In other words, check the text boxes that have NET PRICE and Quantity
as names or controlsources and see if name=controlsource. If so, you'll
need to rename the controls.
Another thing - you mention that you have this SubformTotal text box in
the "subform's footer". Are you sure it's not in the subform's page
footer? A location there might also throw an error.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| The name of the control that holds the subform is also sbfrmParts.
Shannan
Wayne Morgan wrote:[color=darkred
]
>I probably misspoke on the brackets, although on mine they aren't there.
>There is one control you've left off, what is the name of the subform
>control on the main form? This control holds the subform and its name may or
>may not be the same as the subform itself. This control is where you set the
>Parent/Child Link fields.
>
>[quoted text clipped - 11 lines]
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| The control source for SubformTotal, which is the text box on the subform's
footer, is "=Sum([NET PRICE]*[Quantity]). This text box works fine. It
returns the correct value.
Shannan
Tim Marshall wrote:
>[quoted text clipped - 4 lines]
>
>Probably something you've checked already, but is SubformTotal a bound
>control? If so, what is it's control source? If it is also
>SubformTotal (from a query or the SQL behind the subform) this will be
>your error source.
>
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| May be a dumb question, but what's the difference between the subform footer
and the subform page footer?
Shannan
Tim Marshall wrote:
>[quoted text clipped - 10 lines]
>
>I just looked at your original post.
>
>Following the theme of my quoted suggestion, above, have you tried
>making the SubformTotal text box in the subform visible and checked to
>see if you are also getting an error there? If you are, confusion in
>naming will be the likely culprit. Check to see if the controlsource of
>SubformTotal, which is =Sum([NET PRICE]*[Quantity]) is getting confused.
> In other words, check the text boxes that have NET PRICE and Quantity
>as names or controlsources and see if name=controlsource. If so, you'll
>need to rename the controls.
>
>Another thing - you mention that you have this SubformTotal text box in
>the "subform's footer". Are you sure it's not in the subform's page
>footer? A location there might also throw an error.
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Tim Marshall 2005-08-25, 8:25 pm |
| Shannan Casteel via webservertalk.com wrote:
> The name of the control that holds the subform is also sbfrmParts.
That could be your problem, Access is probably confused with two names
the same.
Try renaming the subform something like subParts, then reassign the
problem text box this controlsource:
subParts.form.SubformTotal
Hopefully that will work...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| Tim,
I checked the [NET PRICE] and [Quantity] controls and name did equal control
source, but I changed the names, which automatically updated my formula, and
it had no effect.
Shannan
Tim Marshall wrote:
>[quoted text clipped - 10 lines]
>
>I just looked at your original post.
>
>Following the theme of my quoted suggestion, above, have you tried
>making the SubformTotal text box in the subform visible and checked to
>see if you are also getting an error there? If you are, confusion in
>naming will be the likely culprit. Check to see if the controlsource of
>SubformTotal, which is =Sum([NET PRICE]*[Quantity]) is getting confused.
> In other words, check the text boxes that have NET PRICE and Quantity
>as names or controlsources and see if name=controlsource. If so, you'll
>need to rename the controls.
>
>Another thing - you mention that you have this SubformTotal text box in
>the "subform's footer". Are you sure it's not in the subform's page
>footer? A location there might also throw an error.
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| Nevermind I know what the page footer is, and no the text box is on the form
footer.
Shannan
Shannan Casteel wrote:[color=darkred
]
>May be a dumb question, but what's the difference between the subform footer
>and the subform page footer?
>
>Shannan
>
>[quoted text clipped - 16 lines]
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| Tim,
Now I get an error that reads "#Name".
Shannan
Tim Marshall wrote:
>
>That could be your problem, Access is probably confused with two names
>the same.
>
>Try renaming the subform something like subParts, then reassign the
>problem text box this controlsource:
>
>subParts.form.SubformTotal
>
>Hopefully that will work...
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| Tim,
The subform is based on a query, by the way. Don't know if that matters.
Shouldn't they all be based on queries...Maybe not.
Shannan
Shannan Casteel wrote:[color=darkred
]
>Tim,
>
>Now I get an error that reads "#Name".
>
>Shannan
>
>[quoted text clipped - 7 lines]
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Tim Marshall 2005-08-25, 8:25 pm |
| Shannan Casteel via webservertalk.com wrote:
> Tim,
>
> The subform is based on a query, by the way. Don't know if that matters.
> Shouldn't they all be based on queries...Maybe not.
You can take the SQL from the SQL view of the query designer and plop
that into the record source, instead of a query name. I prefer this
whenever possible as it cuts down on the number of queries that need to
be managed. Indeed, if you're distributing an application, mde or mdb,
that is not secured, users can go into the queries tab and mess up your
queries (deliberately for some usually valid purpose or accidentally
like the sort of people who can't do anything with a software except
find ways to accidentally mess things up! 8) ) so I find putting the
SQL ino the recordsource preferable, unless I manipulate the SQL of a
saved query every time a form opens.
And of course, you can also have what are called "unbound" forms, ie,
not "bound" to any SQL statement or query/table.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| Tim Marshall 2005-08-25, 8:25 pm |
| Shannan Casteel via webservertalk.com wrote:
> Tim,
>
> Now I get an error that reads "#Name".
Without seeing your app, there's definitely something there that seems
to be playing havoc with your desired results. I'm still leaning
towards naming of controls as the bad thing.
How big is your mdb when you compact it? I could have a peek at it if
you can email it - my address is tucked away (and munged - you need to
remove "scumspammer") in my web site below (don't mind the silliness of
the site - it's not a computer site) on the contact information link.
Let me know here or email me first as to the size of your file BEFORE
YOU SEND ANYTHING and I need ample warning - there's only a particular
time of day I can accept large (ie, greater than a few kb, yes, kb)
files at work - on home I'm on dial up that never gets faster than 26
kps. And I can't guarantee I'll find the problem - I'll only look for
the obvious areas I suspect.
Of course, you also realize, I'll probably rip into you mercilessly via
email about your structure if your other thread I've responded to is any
indication, Tee hee. 8) 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| Shannan Casteel via AccessMonster.com 2005-08-25, 8:25 pm |
| Tim,
My application is 35 Mb.
Shannan
Tim Marshall wrote:
>
>Without seeing your app, there's definitely something there that seems
>to be playing havoc with your desired results. I'm still leaning
>towards naming of controls as the bad thing.
>
>How big is your mdb when you compact it? I could have a peek at it if
>you can email it - my address is tucked away (and munged - you need to
>remove "scumspammer") in my web site below (don't mind the silliness of
>the site - it's not a computer site) on the contact information link.
>
>Let me know here or email me first as to the size of your file BEFORE
>YOU SEND ANYTHING and I need ample warning - there's only a particular
>time of day I can accept large (ie, greater than a few kb, yes, kb)
>files at work - on home I'm on dial up that never gets faster than 26
>kps. And I can't guarantee I'll find the problem - I'll only look for
>the obvious areas I suspect.
>
>Of course, you also realize, I'll probably rip into you mercilessly via
>email about your structure if your other thread I've responded to is any
>indication, Tee hee. 8) 8)
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Jeff Smith 2005-08-25, 8:25 pm |
| Go to Tool on the menu bar choose Database Utilities and then Compact and
Repair database. This will compact the database to a smaller size. Then use
a zip compression utility (ie winzip) to compress it more and send that file
off to Tim.
"Shannan Casteel via webservertalk.com" <forum@webservertalk.com> wrote in
message news:5362EA1494F24@w
ebservertalk.com...
> Tim,
>
> My application is 35 Mb.
>
> Shannan
>
| |
| Wayne Morgan 2005-08-25, 8:25 pm |
| Shannan,
Create a new file and import the form, tables, and queries needed to make
this form work. Zip it and send it to me at comprev@hotmail.com and I'll
take a look at it. If the data in the table(s) is sensitive, just import the
table structure, not the data.
--
Wayne Morgan
MS Access MVP
"Shannan Casteel via webservertalk.com" <forum@webservertalk.com> wrote in
message news:53621181697F0@w
ebservertalk.com...
> The name of the control that holds the subform is also sbfrmParts.
>
> Shannan
>
> Wayne Morgan wrote:
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Tim Marshall 2005-08-26, 7:25 am |
| Jeff Smith wrote:
> Go to Tool on the menu bar choose Database Utilities and then Compact and
> Repair database. This will compact the database to a smaller size. Then use
> a zip compression utility (ie winzip) to compress it more and send that file
> off to Tim.
Thanks Jeff,
Yes, 35 megs is a bit big for email! The other thing you could try to
do after doing the compact and repair Jeff describes is if the file is
still over 10megs (probably the limit I'd want to download here at
work), try deleting the tables that aren't needed to display this
particular issue and/or deleting a pile of records. Then compact &
repair once again...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| Shannan Casteel via AccessMonster.com 2005-08-26, 9:24 am |
| Hey guys,
I sent the file to both of you. I hope it's small enough. If it's not let
me know and I'll try to fix it. Once again, thanks for your help.
Shannan
Wayne Morgan wrote:[color=darkred
]
>Shannan,
>
>Create a new file and import the form, tables, and queries needed to make
>this form work. Zip it and send it to me at comprev@hotmail.com and I'll
>take a look at it. If the data in the table(s) is sensitive, just import the
>table structure, not the data.
>
>[quoted text clipped - 13 lines]
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Shannan Casteel via AccessMonster.com 2005-08-26, 8:24 pm |
| Hey,
I GOT IT TO WORK FINALLY!!!!!!
I was reading in the developer's handbook that you can take a form and drag
it on top of another to create a subform. Previously I had used the tool box
to create the form, and for some reason it didn't like that. Everythings
good now. Thanks for everyone's help.
Shannan
Shannan Casteel wrote:[color=darkred
]
>Hey guys,
>
>I sent the file to both of you. I hope it's small enough. If it's not let
>me know and I'll try to fix it. Once again, thanks for your help.
>
>Shannan
>
>[quoted text clipped - 8 lines]
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200508/1
| |
| Wayne Morgan 2005-08-26, 8:24 pm |
| When you drag and drop the form onto the other form to create the subform,
the subform CONTROL on the main form gets the same name as the subform. When
you create the control by yourself, it does NOT. So the problem has probably
been the wrong name for the subform control in the code.
--
Wayne Morgan
MS Access MVP
"Shannan Casteel via webservertalk.com" <forum@webservertalk.com> wrote in
message news:536E8ADB4C614@w
ebservertalk.com...
> Hey,
>
> I GOT IT TO WORK FINALLY!!!!!!
>
> I was reading in the developer's handbook that you can take a form and
> drag
> it on top of another to create a subform. Previously I had used the tool
> box
> to create the form, and for some reason it didn't like that. Everythings
> good now. Thanks for everyone's help.
| |
| Wayne Morgan 2005-08-27, 9:24 am |
| I took a look at the database you sent. The control source of the textbox in
question is "=subParts.Form!SubformTotal". The name of the subform is
subParts, but the name of the subform CONTROL is sbfrmParts. When you
dragged and dropped the subform onto the main form you would have gotten a
subform CONTROL with the same name as your subform. Your control source then
matched the name of the new subform control and started working. Had you
changed your control source to "=sbfrmParts.Form!SubformTotal" it would also
have worked.
--
Wayne Morgan
MS Access MVP
|
|
|
|
|