Calculated total on subform will not display on main form

[Shannan Casteel via AccessMonster.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 AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Wayne Morgan]

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.NameOfControlOnMainForm.Requery

--
Wayne Morgan
MS Access MVP
"Shannan Casteel via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.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 AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.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.

Wayne Morgan wrote:

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.NameOfControlOnMainForm.Requery

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

[quoted text clipped - 14 lines]

Shannan Casteel

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Wayne Morgan]

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 AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.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]

"Shannan Casteel via AccessMonster.com"
<fo***@AccessMonster.com> wrote in
news:53***********@AccessMonster.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]

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:

Wayne,

[quoted text clipped - 6 lines]

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

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:

Wayne,

[quoted text clipped - 6 lines]

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.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

Shannan Casteel wrote:

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

Wayne,

[quoted text clipped - 7 lines]

e.g. parent!subformTotal = me.subformTotal

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Wayne Morgan]

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 AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.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]

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:

If both methods aren't working, then I would suspect that one of the
controls' or forms' name is wrong.

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Wayne Morgan]

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 AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.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]

Shannan Casteel via AccessMonster.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]

Tim Marshall wrote:

Shannan Casteel via AccessMonster.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.

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]

The name of the control that holds the subform is also sbfrmParts.

Shannan

Wayne Morgan wrote:

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,

[quoted text clipped - 11 lines]

What's wrong?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

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:

Subform Name: sbfrmParts
Main Form Name: frmClaim

[quoted text clipped - 4 lines]

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

May be a dumb question, but what's the difference between the subform footer
and the subform page footer?

Shannan

Tim Marshall wrote:

Subform Name: sbfrmParts
Main Form Name: frmClaim

[quoted text clipped - 10 lines]

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Tim Marshall]

Shannan Casteel via AccessMonster.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]

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:

Subform Name: sbfrmParts
Main Form Name: frmClaim

[quoted text clipped - 10 lines]

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

Nevermind I know what the page footer is, and no the text box is on the form
footer.

Shannan

Shannan Casteel wrote:

May be a dumb question, but what's the difference between the subform footer
and the subform page footer?

Shannan

Subform Name: sbfrmParts
Main Form Name: frmClaim

[quoted text clipped - 16 lines]

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 AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

Tim,

Now I get an error that reads "#Name".

Shannan

Tim Marshall 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...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.com]

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:

Tim,

Now I get an error that reads "#Name".

Shannan

The name of the control that holds the subform is also sbfrmParts.

[quoted text clipped - 7 lines]

Hopefully that will work...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Tim Marshall]

Shannan Casteel via AccessMonster.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]

Shannan Casteel via AccessMonster.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]

Tim,

My application is 35 Mb.

Shannan

Tim Marshall 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)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Jeff Smith]

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 AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.com...

Tim,

My application is 35 Mb.

Shannan

[Wayne Morgan]

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 co*****@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 AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.com...

The name of the control that holds the subform is also sbfrmParts.

Shannan

Wayne Morgan wrote:

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,

[quoted text clipped - 11 lines]

What's wrong?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Tim Marshall]

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]

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:

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 co*****@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.

The name of the control that holds the subform is also sbfrmParts.

[quoted text clipped - 13 lines]

What's wrong?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Shannan Casteel via AccessMonster.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.

Shannan

Shannan Casteel wrote:

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

Shannan,

[quoted text clipped - 8 lines]

>
> What's wrong?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

[Wayne Morgan]

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 AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:53***********@AccessMonster.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]

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