dcount expression on subform

[JohnE]

I have a main form that has 2 subforms on it. On SubA (source object is
frmClaimProvision) there is a textbox that is to display the the number of
associated comments there are in SubB (source object is
frmClaimProvisionComment). Both subforms are in continuous style. The way
this works is that SubA is the many to the mainform one, while SubB is the
many to the SubA one. ProvisionID is the PK of tblClaimProvision and the FK
of tblClaimProvisionComment. Here is the expression that is the textbox
control source.

=DCount("ProvisionID","tblClaimProvisionComment",[ProvisionID]=[Forms]![frmClaimProvision]![ProvisionID])

This is not working and I am lost on what is missing or how to proceed from
here so I seek assistance. Can anyone help? Thanks in advance for
responding.

 

[Rick Brandt]

I have a main form that has 2 subforms on it. On SubA (source object is
frmClaimProvision) there is a textbox that is to display the the number of
associated comments there are in SubB (source object is
frmClaimProvisionComment). Both subforms are in continuous style. The way
this works is that SubA is the many to the mainform one, while SubB is the
many to the SubA one. ProvisionID is the PK of tblClaimProvision and the FK
of tblClaimProvisionComment. Here is the expression that is the textbox
control source.

=DCount("ProvisionID","tblClaimProvisionComment",[ProvisionID]=[Forms]![frmClaimProvision]![ProvisionID])

This is not working and I am lost on what is missing or how to proceed from
here so I seek assistance. Can anyone help? Thanks in advance for
responding.

Subforms are not technically "open" and are not found in the Forms Collection.
You have to reference them via the main form and subform control object.

=DCount("ProvisionID","tblClaimProvisionComment",[ProvisionID]=[Forms]!ParentFormName!SubA.Form![ProvisionID])

Note that the name of the subform source object is not used at all. What you
refer to is the Form property of the Subform Control

 

[JohnE]

Mr Brandt, thanks for the prompt response. My test environment number showed
up correctly but appeared in each record on the SubA. The user needs to know
how many comments there are for each record in SubA. There is the
possibility that some of SubA records would not have any comments while
others could have a varying number of comments. What changes do I need to
make to what you sent?
Again, thanks for the response.

I have a main form that has 2 subforms on it. On SubA (source object is
frmClaimProvision) there is a textbox that is to display the the number of
associated comments there are in SubB (source object is
frmClaimProvisionComment). Both subforms are in continuous style. The way
this works is that SubA is the many to the mainform one, while SubB is the
many to the SubA one. ProvisionID is the PK of tblClaimProvision and the FK
of tblClaimProvisionComment. Here is the expression that is the textbox
control source.

=DCount("ProvisionID","tblClaimProvisionComment",[ProvisionID]=[Forms]![frmClaimProvision]![ProvisionID])

This is not working and I am lost on what is missing or how to proceed from
here so I seek assistance. Can anyone help? Thanks in advance for
responding.

Subforms are not technically "open" and are not found in the Forms Collection.
You have to reference them via the main form and subform control object.

=DCount("ProvisionID","tblClaimProvisionComment",[ProvisionID]=[Forms]!ParentFormName!SubA.Form![ProvisionID])

Note that the name of the subform source object is not used at all. What you
refer to is the Form property of the Subform Control

 

[Rick Brandt]

Mr Brandt, thanks for the prompt response. My test environment number showed
up correctly but appeared in each record on the SubA. The user needs to know
how many comments there are for each record in SubA. There is the
possibility that some of SubA records would not have any comments while
others could have a varying number of comments. What changes do I need to
make to what you sent?
Again, thanks for the response.

If you have this expression in a control IN the subform itself then you need to
drop the form reference entirely as that will only see the CURRENT value of the
ProvisionID field (the one on the currently selected record). You need to see
the ProvisionID value per-record. Try this...

=DCount("ProvisionID","tblClaimProvisionComment", "[ProvisionID]=" &
Me!ProvisionID)

That expression (with the Me reference outside the quotes) will pass the
ProvisionID value of each row to the DCount() expression.

However; I should warn you that using DCount() in a continuous form like that
will be very resource intensive. There is a certain amount of overhead
processing associated with every use of a domain aggregate function above and
beyond running the actual internal query. That is negligible when used once or
twice in a single view form or report. When used in a continuous form, query,
or looping code operation though that overhead quickly adds up.

You might be able to use a sub-query in the query used by your subform that
would provide the same information without incurring the overhead that DCount()
will.

 

[JohnE]

Mr. Brandt, thanks. I tweeked it just a bit and it works perfectly. Your
resource part is duly noted.

I have a main form that has 2 subforms on it. On SubA (source object is
frmClaimProvision) there is a textbox that is to display the the number of
associated comments there are in SubB (source object is
frmClaimProvisionComment). Both subforms are in continuous style. The way
this works is that SubA is the many to the mainform one, while SubB is the
many to the SubA one. ProvisionID is the PK of tblClaimProvision and the FK
of tblClaimProvisionComment. Here is the expression that is the textbox
control source.

=DCount("ProvisionID","tblClaimProvisionComment",[ProvisionID]=[Forms]![frmClaimProvision]![ProvisionID])

This is not working and I am lost on what is missing or how to proceed from
here so I seek assistance. Can anyone help? Thanks in advance for
responding.

Subforms are not technically "open" and are not found in the Forms Collection.
You have to reference them via the main form and subform control object.

=DCount("ProvisionID","tblClaimProvisionComment",[ProvisionID]=[Forms]!ParentFormName!SubA.Form![ProvisionID])

Note that the name of the subform source object is not used at all. What you
refer to is the Form property of the Subform Control