Referencing control in subform from subform's subform

K

Kat

OK, this is a bit complicated so I will try to simplify as best as
possible and try to provide a visual below:

Main Form contains one subform: Subform1
Subform1 contains numerous subforms, Subform2 - 5

|-Subform2 (control I want to
populate)
Main Form ----|-Subform1---|-Subform3
|-Subform4
|-Subform5 (calculated value I
want)

I can reference the controls in Subform5 in Subform2 using the
following:
=Forms!MainForm!Subform1!Subform5.Form!txtControl

Only when Subform5 txtControl is a calculated field such as
Sum(txtValue) it comes back as a #Error.

When I use this format in Subform1, it works fine, It is only a problem
in Subform2. Perhaps this is not possible? The only way I can get it
to work is by putting a bogus SUM(txtxxx) control in Subform 2 that
#Errors out, but for now I have hidden the field. But, this is messy
and I would like a fix.

Any ideas?
 
A

Allen Browne

You missed the .Form bit for the first level subform.

Try:
=Forms!MainForm!Subform1.Form!Subform5.Form!txtControl
Explanation:
http://allenbrowne.com/casu-04.html

BTW, if txtControl has an expression in its Control Source, e.g.:
=[Quantity] * [UnitPrice]
you cannot sum it, i.e. you cannot put this in the Form Footer:
=Sum([txtControl])
You need to repeat the expression to get the total, i.e.:
=Sum([Quantity] * [UnitPrice])
 
K

Kat

OK, here is the original yes/no field on SubForm5: txtVPNAcct, the
calculated field on SubForm5 was txtVPNCount =Abs(Sum(txtVPNAcct))

I moved the calculation to SubForm2 (repeating the expression as you
stated) and added the .Form that was missing:

=Sum(Forms!MainForm!Subform1.Form!Subform5.Form!txtVPNAcct)

It still errors.

Allen said:
You missed the .Form bit for the first level subform.

Try:
=Forms!MainForm!Subform1.Form!Subform5.Form!txtControl
Explanation:
http://allenbrowne.com/casu-04.html

BTW, if txtControl has an expression in its Control Source, e.g.:
=[Quantity] * [UnitPrice]
you cannot sum it, i.e. you cannot put this in the Form Footer:
=Sum([txtControl])
You need to repeat the expression to get the total, i.e.:
=Sum([Quantity] * [UnitPrice])


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kat said:
OK, this is a bit complicated so I will try to simplify as best as
possible and try to provide a visual below:

Main Form contains one subform: Subform1
Subform1 contains numerous subforms, Subform2 - 5

|-Subform2 (control I want to
populate)
Main Form ----|-Subform1---|-Subform3
|-Subform4
|-Subform5 (calculated value I
want)

I can reference the controls in Subform5 in Subform2 using the
following:
=Forms!MainForm!Subform1!Subform5.Form!txtControl

Only when Subform5 txtControl is a calculated field such as
Sum(txtValue) it comes back as a #Error.

When I use this format in Subform1, it works fine, It is only a problem
in Subform2. Perhaps this is not possible? The only way I can get it
to work is by putting a bogus SUM(txtxxx) control in Subform 2 that
#Errors out, but for now I have hidden the field. But, this is messy
and I would like a fix.

Any ideas?
Click to expand...
Click to expand...
 
K

Kat

Kat said:
OK, here is the original yes/no field on SubForm5: txtVPNAcct, the
calculated field on SubForm5 was txtVPNCount =Abs(Sum(txtVPNAcct))

I moved the calculation to SubForm2 (repeating the expression as you
stated) and added the .Form that was missing:

=Sum(Forms!MainForm!Subform1.Form!Subform5.Form!txtVPNAcct)

It still errors.

Another tidbit: =Forms!MainForm!Subform1.Form!Subform5.Form!txtVPNAcct does return the value in the first record. As soon as I slap on the Sum(), it errors.

Allen said:
You missed the .Form bit for the first level subform.

Try:
=Forms!MainForm!Subform1.Form!Subform5.Form!txtControl
Explanation:
http://allenbrowne.com/casu-04.html

BTW, if txtControl has an expression in its Control Source, e.g.:
=[Quantity] * [UnitPrice]
you cannot sum it, i.e. you cannot put this in the Form Footer:
=Sum([txtControl])
You need to repeat the expression to get the total, i.e.:
=Sum([Quantity] * [UnitPrice])


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kat said:
OK, this is a bit complicated so I will try to simplify as best as
possible and try to provide a visual below:

Main Form contains one subform: Subform1
Subform1 contains numerous subforms, Subform2 - 5

|-Subform2 (control I want to
populate)
Main Form ----|-Subform1---|-Subform3
|-Subform4
|-Subform5 (calculated value I
want)

I can reference the controls in Subform5 in Subform2 using the
following:
=Forms!MainForm!Subform1!Subform5.Form!txtControl

Only when Subform5 txtControl is a calculated field such as
Sum(txtValue) it comes back as a #Error.

When I use this format in Subform1, it works fine, It is only a problem
in Subform2. Perhaps this is not possible? The only way I can get it
to work is by putting a bogus SUM(txtxxx) control in Subform 2 that
#Errors out, but for now I have hidden the field. But, this is messy
and I would like a fix.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

The text box that collects the sum should go in the Form Footer section of
*that* form.

You can transfer it to another form once you have the total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kat said:
OK, here is the original yes/no field on SubForm5: txtVPNAcct, the
calculated field on SubForm5 was txtVPNCount =Abs(Sum(txtVPNAcct))

I moved the calculation to SubForm2 (repeating the expression as you
stated) and added the .Form that was missing:

=Sum(Forms!MainForm!Subform1.Form!Subform5.Form!txtVPNAcct)

It still errors.

Allen said:
You missed the .Form bit for the first level subform.

Try:
=Forms!MainForm!Subform1.Form!Subform5.Form!txtControl
Explanation:
http://allenbrowne.com/casu-04.html

BTW, if txtControl has an expression in its Control Source, e.g.:
=[Quantity] * [UnitPrice]
you cannot sum it, i.e. you cannot put this in the Form Footer:
=Sum([txtControl])
You need to repeat the expression to get the total, i.e.:
=Sum([Quantity] * [UnitPrice])


Kat said:
OK, this is a bit complicated so I will try to simplify as best as
possible and try to provide a visual below:

Main Form contains one subform: Subform1
Subform1 contains numerous subforms, Subform2 - 5

|-Subform2 (control I want to
populate)
Main Form ----|-Subform1---|-Subform3
|-Subform4
|-Subform5 (calculated value I
want)

I can reference the controls in Subform5 in Subform2 using the
following:
=Forms!MainForm!Subform1!Subform5.Form!txtControl

Only when Subform5 txtControl is a calculated field such as
Sum(txtValue) it comes back as a #Error.

When I use this format in Subform1, it works fine, It is only a problem
in Subform2. Perhaps this is not possible? The only way I can get it
to work is by putting a bogus SUM(txtxxx) control in Subform 2 that
#Errors out, but for now I have hidden the field. But, this is messy
and I would like a fix.
Click to expand...
Click to expand...
Click to expand...
 
K

Kat

Thanks for the help. I tried all of the suggestions - it just would
not work. Instead I created yet another subform using a queary that
summed up the numbers I wanted. Eliminating the calculations on the
form, and it worked that way.

Allen said:
The text box that collects the sum should go in the Form Footer section of
*that* form.

You can transfer it to another form once you have the total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kat said:
OK, here is the original yes/no field on SubForm5: txtVPNAcct, the
calculated field on SubForm5 was txtVPNCount =Abs(Sum(txtVPNAcct))

I moved the calculation to SubForm2 (repeating the expression as you
stated) and added the .Form that was missing:

=Sum(Forms!MainForm!Subform1.Form!Subform5.Form!txtVPNAcct)

It still errors.

Allen said:
You missed the .Form bit for the first level subform.

Try:
=Forms!MainForm!Subform1.Form!Subform5.Form!txtControl
Explanation:
http://allenbrowne.com/casu-04.html

BTW, if txtControl has an expression in its Control Source, e.g.:
=[Quantity] * [UnitPrice]
you cannot sum it, i.e. you cannot put this in the Form Footer:
=Sum([txtControl])
You need to repeat the expression to get the total, i.e.:
=Sum([Quantity] * [UnitPrice])


OK, this is a bit complicated so I will try to simplify as best as
possible and try to provide a visual below:

Main Form contains one subform: Subform1
Subform1 contains numerous subforms, Subform2 - 5

|-Subform2 (control I want to
populate)
Main Form ----|-Subform1---|-Subform3
|-Subform4
|-Subform5 (calculated value I
want)

I can reference the controls in Subform5 in Subform2 using the
following:
=Forms!MainForm!Subform1!Subform5.Form!txtControl

Only when Subform5 txtControl is a calculated field such as
Sum(txtValue) it comes back as a #Error.

When I use this format in Subform1, it works fine, It is only a problem
in Subform2. Perhaps this is not possible? The only way I can get it
to work is by putting a bogus SUM(txtxxx) control in Subform 2 that
#Errors out, but for now I have hidden the field. But, this is messy
and I would like a fix.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding a new record to a subform 1
Adding new records to subforms 3
Subform RecordSet 1
How to always get the top level form reference, when calling from any subform/subform... 0
Cancel the changing to a different record in a subform 1
Referrring to SubForm Controls 5
Run Code on one subform from another subform 2
Eliminate prompt in requery of subdatasheet in vba 6

Top