Placing a Sum in a main form, from another form.

[Guest]

I am a new user to Access, and am surprised at how much difficulty I'm having
getting a simple sum function to work. I must be missing something very
simple. I have a main form called "Project Data". I want to include the
total sqaure footage listed in a table called "Square Footage Table" on this
form. I created another form called "Square Footage Table1" which contained
the field [Total of All Project Sf] which is located in the footer, and is a
sum of the [Total Sf] field. In the main form I identified this other form
this way =[Forms]![Square Footage Table1]![Total of All Project Sf] I
continue to get #name? I checked to see if the actual control names are
correct, and they are. I've also tried this same approach with a Query, but
no luck. I tried bringing in the other firm as a subform, and referring to
it as a subform, but that didn't work either. Any ideas?

 

[Steve Schapel]

Ryn,

No, you can't refer to the value of a control in another form like that.

If you put the [Square Footage Table1] form onto the Project Data form
as a a subform, you would then be able to refer to it in the Control
Source of a textbox on Project Data form, using syntax such as...
=[Square Footage Table1]![Total of All Project Sf]
.... or, as some would have it...
=[Square Footage Table1].Form![Total of All Project Sf]

Another approach, if you don't want tp use the subform, is put this in
the Control Source...
=DSum("[Total Sf]","Square Footage Table")

 

[Steve Schapel]

Ryn,

No, you can't refer to the value of a control in another form like that.

Well, what do you know? After sending this, out of curiosity I gave it
a try, and lo and behold, you can do this after all! I never never had
the need to try anything like this. Apologies for the incorrect
information.

So, back to square 1... The [Square Footage Table1] form is open at the
time that you open the [Project Data] form?

 

[Guest]

Steve,
I really appreciate your help. I tried your method # 1, which is to load the
form as a subform, and then refer to it. It worked. I was thrilled because
I've been struggling with this longer than I should have. I wasn't planning
to use the 2nd form as a subform, but if that's what it takes to make it
work, I will. I tried the "Dsum" approach without luck, but I I need to
study that command a little further. Something odd happened, and this has
happened before, When I opened my database again, the first method you gave
me was no longer working. I don't know why? I hadn't done anything. I
tretraced my steps, recreating the link, to no luck. Any clues? How could
this be? In answer to your reply, I wasn't planning on having the square
footage table open all the time. Does that make a difference?

Ryn,

No, you can't refer to the value of a control in another form like that.

If you put the [Square Footage Table1] form onto the Project Data form
as a a subform, you would then be able to refer to it in the Control
Source of a textbox on Project Data form, using syntax such as...
=[Square Footage Table1]![Total of All Project Sf]
.... or, as some would have it...
=[Square Footage Table1].Form![Total of All Project Sf]

Another approach, if you don't want tp use the subform, is put this in
the Control Source...
=DSum("[Total Sf]","Square Footage Table")

--
Steve Schapel, Microsoft Access MVP

I am a new user to Access, and am surprised at how much difficulty I'm having
getting a simple sum function to work. I must be missing something very
simple. I have a main form called "Project Data". I want to include the
total sqaure footage listed in a table called "Square Footage Table" on this
form. I created another form called "Square Footage Table1" which contained
the field [Total of All Project Sf] which is located in the footer, and is a
sum of the [Total Sf] field. In the main form I identified this other form
this way =[Forms]![Square Footage Table1]![Total of All Project Sf] I
continue to get #name? I checked to see if the actual control names are
correct, and they are. I've also tried this same approach with a Query, but
no luck. I tried bringing in the other firm as a subform, and referring to
it as a subform, but that didn't work either. Any ideas?

 

[Steve Schapel]

Ryn,

If you were going to use this approach...
=[Forms]![Square Footage Table1]![Total of All Project Sf]
.... then the [Square Footage Table1] would have to be open at the time,
otherwise the other form couldn't refer to the value of a control on the
form if it's closed.

I don'e know why the DSum() function didn't work. In a way, this is the
simplest solution. If Square Footage Table is the name of the table,
and Total Sf is the name of the field, then...
=DSum("[Total Sf]","Square Footage Table")
.... should result in the total of the Total Sf entries in the table.

As for the correct behaviour being shown, and then no longer so after
closing the form and opening again, I have no explanation for this. I
assumed that [Square Footage Table1] was the name of the *subform
control* on the main form, which is not necessarily the same as the name
of the form which populates the subform.

 

[Guest]

Steve,
I copied your syntax exactly, and the Dsum function worked perfectly. From
what I can tell, I was not using the quotation marks & brackets correctly. I
really appreciate your help.

Ryn,

If you were going to use this approach...
=[Forms]![Square Footage Table1]![Total of All Project Sf]
.... then the [Square Footage Table1] would have to be open at the time,
otherwise the other form couldn't refer to the value of a control on the
form if it's closed.

I don'e know why the DSum() function didn't work. In a way, this is the
simplest solution. If Square Footage Table is the name of the table,
and Total Sf is the name of the field, then...
=DSum("[Total Sf]","Square Footage Table")
.... should result in the total of the Total Sf entries in the table.

As for the correct behaviour being shown, and then no longer so after
closing the form and opening again, I have no explanation for this. I
assumed that [Square Footage Table1] was the name of the *subform
control* on the main form, which is not necessarily the same as the name
of the form which populates the subform.

--
Steve Schapel, Microsoft Access MVP

Steve,
I really appreciate your help. I tried your method # 1, which is to load the
form as a subform, and then refer to it. It worked. I was thrilled because
I've been struggling with this longer than I should have. I wasn't planning
to use the 2nd form as a subform, but if that's what it takes to make it
work, I will. I tried the "Dsum" approach without luck, but I I need to
study that command a little further. Something odd happened, and this has
happened before, When I opened my database again, the first method you gave
me was no longer working. I don't know why? I hadn't done anything. I
tretraced my steps, recreating the link, to no luck. Any clues? How could
this be? In answer to your reply, I wasn't planning on having the square
footage table open all the time. Does that make a difference?

 

[Steve Schapel]

Ryn,

Not sure if this was it or not, but sometimes if you copy/paste from a
newsgroup post or email, depending on the formatting used, ""s can
sometimes get converted to “”s which messes Access up.