Subform not calculating

  • Thread starter Thread starter Tiffany Pugh
  • Start date Start date
T

Tiffany Pugh

I have form [frmServiceInfo] in which I have entered a
text box with the following control source:

=DSum("[Service Hours]","[qryServiceInfo]","[EEID]=Forms!
[frmServiceInfo]![EEID] And [Year]<=Forms![frmServiceInfo]!
[Year]")

This is creating a running sum of the service hours for
each employee entered. IT WORKS!!! However when I use
this form as a subform of another form [frmEmplInfo], the
calculation no longer works. I recieve #Error in the text
box results.

The form [frmEmplInfo] has and individual record for each
employee id (the primary key), but the subform
[frmServiceInfo] has multiple records for each employee id
and lists out the service hours for each employee for
every year they worked.

Any ideas on why it works as a free standing form
[frmServiceInfo], but not as a subform [frmServiceInfo].
 
when the form is opened as a subform within a mainform,
it's reference changes to

Forms!MainFormName!NameOfSubformControlOnMainForm!EEID

important: you can't simply reference

Forms!MainFormName!SubFormName!EEID

you have to specifically reference the name of the subform
control on the main form. that may be the same name as the
subform itself, or it may not be; open the main form in
design view to check the subform control's name.

i've never referenced a subform's control in a domain
aggregate function before, but the above references work
in direct form references so hopefully in this situation
also.
 
Because when it is being opened as a "SubForm", it can no longer be
referenced as

Forms![frmServiceInfo]

as used in your DSum (twice).


Firstly, let's make the terminology clear: when people talk about
"SubForm", they can refer to one of the 2 different objects in Access:

* The SubForm *Control*: think of it as the empty rectangle around your
"Subform". This a member of the Controls collection of the MainForm, i.e.
the MainForm is the Parent of it.

* The SourceObject of the SubformControl: This is the Form (and the
Controls) you see inside the SubFormControl. The SourceObject is a Form
whose name can be seen in the Forms tab of the Database Containers window.


A Form being used as the SourceObject is NOT a member of the Forms
collection. Thus, you canNOT use:

Forms![frmServiceInfo]

to refer to it.

The naming used by the SubForm Wizard is quite often misleading: it names
the SubFormControl with the same name as the SourceObject. Personally, I
always use the prefix "sfr" for the SubFormControl and "fsf" for the Form
being used as the SourceObject so that I know which one I refer to.

To refer to a Control on a "SubForm", you need to refer to it through the
hierarchical structure of objects like:

Forms!MainForm!SubformControl.Form!ControlOnSubform

In your case, you need to know the name of the SubformControl (from the
DesignView of the MainForm). It can be the same as "frmServiceInfo" or it
may be something else altogether.

The above is the full reference you need to use in your DSum. However,
since you are referring to the Control in the "context" of the SourceObject
(i.e. the Control with Dsum is on the same data object with the other
referred Controls, I think you may be able to use the *default* reference
as:

=DSum("[Service Hours]","[qryServiceInfo]","[EEID] = [txtEEID]
And [Year] <= [txtYear]")

Note that I also rename the Controls with the prefix "txt" to distinguish it
from the Field names.

--
HTH
Van T. Dinh
MVP (Access)



Tiffany Pugh said:
I have form [frmServiceInfo] in which I have entered a
text box with the following control source:

=DSum("[Service Hours]","[qryServiceInfo]","[EEID]=Forms!
[frmServiceInfo]![EEID] And [Year]<=Forms![frmServiceInfo]!
[Year]")

This is creating a running sum of the service hours for
each employee entered. IT WORKS!!! However when I use
this form as a subform of another form [frmEmplInfo], the
calculation no longer works. I recieve #Error in the text
box results.

The form [frmEmplInfo] has and individual record for each
employee id (the primary key), but the subform
[frmServiceInfo] has multiple records for each employee id
and lists out the service hours for each employee for
every year they worked.

Any ideas on why it works as a free standing form
[frmServiceInfo], but not as a subform [frmServiceInfo].
Click to expand...
 
This Worked! Thanks Tina!

Now I need a little more help with running sums.

I have created another text box on my form that evaluates
the information from my running sum and based on that has
another calculation. Now I need to create a running sum
of that. However, I have been unable to successfully do
that since the information in my origial text box is not
saved. Here is what I have.

=Round(DSum("forms![frmEmplInfo]![frmServiceInfo]!
[Text38]","[qryServiceInfo]","[EEID]=forms![frmEmplInfo]!
[frmServiceInfo]![EEID] And [Year]<=forms![frmEmplInfo]!
[frmServiceInfo]![Year]"),2)

This is producing results, just not the results I wanted.
It is multiplying the number in [text38] by the row number
is is on. Example:

Calculation based on running Sum 2nd Running Sum
10 10
4 8
9 27

I do not understand why it is doing this instead of doing
the running sum. Any suggestions?

This is the results I want:

Calculation based on running Sum 2nd Running Sum
10 10
4 14
9 23

Thank you in advance for any assistance you can provide!

-----Original Message-----
when the form is opened as a subform within a mainform,
it's reference changes to

Forms!MainFormName!NameOfSubformControlOnMainForm!EEID

important: you can't simply reference

Forms!MainFormName!SubFormName!EEID

you have to specifically reference the name of the subform
control on the main form. that may be the same name as the
subform itself, or it may not be; open the main form in
design view to check the subform control's name.

i've never referenced a subform's control in a domain
aggregate function before, but the above references work
in direct form references so hopefully in this situation
also.

-----Original Message-----
I have form [frmServiceInfo] in which I have entered a
text box with the following control source:

=DSum("[Service Hours]","[qryServiceInfo]","[EEID]=Forms!
[frmServiceInfo]![EEID] And [Year]<=Forms! [frmServiceInfo]!
[Year]")

This is creating a running sum of the service hours for
each employee entered. IT WORKS!!! However when I use
this form as a subform of another form [frmEmplInfo], the
calculation no longer works. I recieve #Error in the text
box results.

The form [frmEmplInfo] has and individual record for each
employee id (the primary key), but the subform
[frmServiceInfo] has multiple records for each employee id
and lists out the service hours for each employee for
every year they worked.

Any ideas on why it works as a free standing form
[frmServiceInfo], but not as a subform [frmServiceInfo].
.
Click to expand...
.
Click to expand...
 
Back
Top