Total of subform on a form

  • Thread starter Thread starter Robert Raley
  • Start date Start date
R

Robert Raley

Ok this is my what I have on the sub form I have a field [seconds] I am
doing the following in the footer in a text box.

=Round(Avg([seconds]/60),2)

text box name is pvt_avg. On the main form I have created a text box with
this in the control.

=[frm_wkly_pvt Subform_subform2]![pvt_avg]

I have tested the subform with a query and all works fine. However when I
use the main form I receive an # error message.

Both forms are in datasheet veiw.

Just to compicate things the main form is also a subform of another form.
All works great except this (&R^*&^(&)(&^&^%^%* formula.

Any help will be appreciated.

Thanks

Bob
 
As long as the textbox in the subform is showing the correct value, the
problem should be getting the correct "path" to the subform. A subform is
held on the main form in a subform control. You need to refer to the name of
this control, not the name of the form in the subform control.

Example:
=[NameOfSubformControl].[Form]![NameofControlOnSubform]

To get the name of the subform control, open the main form in design mode,
open the Properties sheet, and click on the subform ONE time. The properties
sheet should show the name of the subform control. If you click more than
once, you'll be in the subform and will be showing it's properties, not
those of the control holding it.
 
Thanks Wayne for the help.

I however do not understand what you mean by subform control. The name of
the subform or what. I can not find anything that says subform control.

The name of the subform is frm_wkly_pvt Subform_subform2 the name of the
text box in the footer is pvt_avg

Thanks Bob


Wayne Morgan said:
As long as the textbox in the subform is showing the correct value, the
problem should be getting the correct "path" to the subform. A subform is
held on the main form in a subform control. You need to refer to the name of
this control, not the name of the form in the subform control.

Example:
=[NameOfSubformControl].[Form]![NameofControlOnSubform]

To get the name of the subform control, open the main form in design mode,
open the Properties sheet, and click on the subform ONE time. The properties
sheet should show the name of the subform control. If you click more than
once, you'll be in the subform and will be showing it's properties, not
those of the control holding it.

--
Wayne Morgan
Microsoft Access MVP


Robert Raley said:
Ok this is my what I have on the sub form I have a field [seconds] I am
doing the following in the footer in a text box.

=Round(Avg([seconds]/60),2)

text box name is pvt_avg. On the main form I have created a text box with
this in the control.

=[frm_wkly_pvt Subform_subform2]![pvt_avg]

I have tested the subform with a query and all works fine. However when I
use the main form I receive an # error message.

Both forms are in datasheet veiw.

Just to compicate things the main form is also a subform of another form.
All works great except this (&R^*&^(&)(&^&^%^%* formula.

Any help will be appreciated.

Thanks

Bob
Click to expand...
Click to expand...
 
There is a control on the main form. Its type of control, not its name, is a
subform control. It is actually possible to use code and swap which form is
the subform while you have the main form open. Also, on main forms that have
several subforms, sometimes this is used to open the main form quicker. To
do so, you don't put a form in the subform control in design mode, you do so
in code after the main form is open. Subreports work the same way, they are
actually in a subreport control on the main report. With the form opened in
design mode, if you run you mouse over the toolbox you will find the subform
control. The tool tip should say "subform/subreport". Depending on HOW you
added the subform to the main form, the name of the control and the name of
the subform may be the same. Access will give the subform control the same
name as the subform if you add the subform by dragging and dropping it onto
the main form. However, if you use the control from the toolbox to create
the subform control then add the subform later, the default name (at least
in Access 2003) is Child0.

Assuming you dragged and dropped the subform onto the main form and that the
subform control has the same name as the subform it holds then the syntax
using the names you gave would be:

=[frm_wkly_pvt Subform_subform2].[Form].[pvt_avg]

If the value of the control of the subform may change without moving to a
different record on the main form, in whatever event fires when that change
occurs, you will also need to refresh the control on the main form to update
it to the new value. Doing a Recalc on the main form should do it, but may
actually cause the form to flash for a few seconds. Instead, you can just
requery the control on the main form to update it.

Example:
Me.Parent.txtTextbox.Requery
 
Wayne, Thanks again for your help. I have been doing exactly what you
suggested. Stilldoes not work. I changed both main form and subform to
form view and it works fine. Any explanation why it will not work in
DataSheet View.

Thanks

Wayne.


Wayne Morgan said:
There is a control on the main form. Its type of control, not its name, is a
subform control. It is actually possible to use code and swap which form is
the subform while you have the main form open. Also, on main forms that have
several subforms, sometimes this is used to open the main form quicker. To
do so, you don't put a form in the subform control in design mode, you do so
in code after the main form is open. Subreports work the same way, they are
actually in a subreport control on the main report. With the form opened in
design mode, if you run you mouse over the toolbox you will find the subform
control. The tool tip should say "subform/subreport". Depending on HOW you
added the subform to the main form, the name of the control and the name of
the subform may be the same. Access will give the subform control the same
name as the subform if you add the subform by dragging and dropping it onto
the main form. However, if you use the control from the toolbox to create
the subform control then add the subform later, the default name (at least
in Access 2003) is Child0.

Assuming you dragged and dropped the subform onto the main form and that the
subform control has the same name as the subform it holds then the syntax
using the names you gave would be:

=[frm_wkly_pvt Subform_subform2].[Form].[pvt_avg]

If the value of the control of the subform may change without moving to a
different record on the main form, in whatever event fires when that change
occurs, you will also need to refresh the control on the main form to update
it to the new value. Doing a Recalc on the main form should do it, but may
actually cause the form to flash for a few seconds. Instead, you can just
requery the control on the main form to update it.

Example:
Me.Parent.txtTextbox.Requery

--
Wayne Morgan
MS Access MVP


Robert Raley said:
Thanks Wayne for the help.

I however do not understand what you mean by subform control. The name of
the subform or what. I can not find anything that says subform control.

The name of the subform is frm_wkly_pvt Subform_subform2 the name of the
text box in the footer is pvt_avg
Click to expand...
Click to expand...
 
Do you have both forms in datasheet view? If so, the main form won't see the
data on the subform until it has been expanded. If I remember correctly from
previous testing on this, once it has been expanded it can be collapsed
again. In the Activate event of the main form try

DoCmd.Echo False
Me.SubdatasheetExpanded = True
Me.SubdatasheetExpanded = False
DoCmd.Echo True

and see if that helps.
 
check the txtBox names, if one has a name of a field used in a
calculation you can get all sort of errors and miscalculations.

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Back
Top