DCount problem redux

  • Thread starter Thread starter scottyboyb
  • Start date Start date
S

scottyboyb

Greetings, a week or so ago I tagged a question onto another thread about a
DCount problem and I got an answer that I am having a problem with. I had to
go out of town and so I have left this for a while. Now I am back for a few
days and I am trying to make the answer work.

I have a form (testForm ased on one field from a table) and a subform
(testSubform based on a SQL statement). Here is the SQL:
SELECT ContributorsPledgesQuery.Name, ContributorsPledgesQuery.Campaign_ID,
ContributorsPledgesQuery.DatePledged, ContributorsPledgesQuery.AmountPledged,
ContributorsPledgesQuery.Paid, ContributorsPledgesQuery.PaymentDate
FROM ContributorsPledgesQuery;

The DCount needs to give me a count of records showing on the subform for
each record showing on the main form.

So here is what I have:
=DCount("[AmountPledged]","Pledges","[CampaignName] = '" &
[Forms]![testForm]![txtCampaignName] & "'")

=DCount("[field i am counting records of on subform]","table subform field
is in","[criteria field on main form] = '" & [Forms]![main form
name]![criteria fieldname on main form] & "'")

This gives me the total number of records in the Pledges table, not the
number of records showing on the subform. Does anyone see what is wrong with
this?

Best,
Scott
 
To find out how many records are being displayed on a subform, use

Me![NameOfSubformControl].Forms.RecordsetClone.RecordCount

or

Forms![NameOfParentForm]![NameOfSubformControl].Forms.RecordsetClone.RecordCount

(that's supposed to be all on one line...)

Note that the name of the subform control on the parent form may be
different than the name of the form being used as a subform.
 
I guess I am not sure what you mean by "Note that the name of the subform
control on the parent form may be different than the name of the form being
used as a subform."

The field I think you are talking about's name in the table is AmountPledged
and the field's name in the subform is curAmountPledged. The parent form is
testForm. The subform's name is testSubform. I do not know of any other name
it could have. Both of those give me a name error in both of your examples.

Table: Pledges
Field: AmountPledged
Field Type: Currency

Form: testForm
Subform: testSubform
Subform Field: curAmountPledged

Unbound text box: txtPledgeCount

Any thoughts?

Best,
Scott

Douglas J. Steele said:
To find out how many records are being displayed on a subform, use

Me![NameOfSubformControl].Forms.RecordsetClone.RecordCount

or

Forms![NameOfParentForm]![NameOfSubformControl].Forms.RecordsetClone.RecordCount

(that's supposed to be all on one line...)

Note that the name of the subform control on the parent form may be
different than the name of the form being used as a subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scottyboyb said:
Greetings, a week or so ago I tagged a question onto another thread about
a
DCount problem and I got an answer that I am having a problem with. I had
to
go out of town and so I have left this for a while. Now I am back for a
few
days and I am trying to make the answer work.

I have a form (testForm ased on one field from a table) and a subform
(testSubform based on a SQL statement). Here is the SQL:
SELECT ContributorsPledgesQuery.Name,
ContributorsPledgesQuery.Campaign_ID,
ContributorsPledgesQuery.DatePledged,
ContributorsPledgesQuery.AmountPledged,
ContributorsPledgesQuery.Paid, ContributorsPledgesQuery.PaymentDate
FROM ContributorsPledgesQuery;

The DCount needs to give me a count of records showing on the subform for
each record showing on the main form.

So here is what I have:
=DCount("[AmountPledged]","Pledges","[CampaignName] = '" &
[Forms]![testForm]![txtCampaignName] & "'")

=DCount("[field i am counting records of on subform]","table subform field
is in","[criteria field on main form] = '" & [Forms]![main form
name]![criteria fieldname on main form] & "'")

This gives me the total number of records in the Pledges table, not the
number of records showing on the subform. Does anyone see what is wrong
with
this?

Best,
Scott
Click to expand...


.
Click to expand...
 
What Doug is saying is that when you want to refer to a control on a subform
in the code module of the main form, you address the name of the control on
the form, not the name of the form being used as a subform.

When you create a subform control on a form, the control object has a name.
Then in the property dialog of the subform control is a property named
Source Object. The Source Object property identifies the form object that
will be displayed in the subform control. If you use the wizard, it will
use the same name for the subform control and the form it will present, but
this is not a really good practice.

The syntax to refer to a control on a subform is:

Forms!MainFormName!SubformControlName.Form!ControlName

scottyboyb said:
I guess I am not sure what you mean by "Note that the name of the subform
control on the parent form may be different than the name of the form
being
used as a subform."

The field I think you are talking about's name in the table is
AmountPledged
and the field's name in the subform is curAmountPledged. The parent form
is
testForm. The subform's name is testSubform. I do not know of any other
name
it could have. Both of those give me a name error in both of your
examples.

Table: Pledges
Field: AmountPledged
Field Type: Currency

Form: testForm
Subform: testSubform
Subform Field: curAmountPledged

Unbound text box: txtPledgeCount

Any thoughts?

Best,
Scott

Douglas J. Steele said:
To find out how many records are being displayed on a subform, use

Me![NameOfSubformControl].Forms.RecordsetClone.RecordCount

or

Forms![NameOfParentForm]![NameOfSubformControl].Forms.RecordsetClone.RecordCount

(that's supposed to be all on one line...)

Note that the name of the subform control on the parent form may be
different than the name of the form being used as a subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scottyboyb said:
Greetings, a week or so ago I tagged a question onto another thread
about
a
DCount problem and I got an answer that I am having a problem with. I
had
to
go out of town and so I have left this for a while. Now I am back for a
few
days and I am trying to make the answer work.

I have a form (testForm ased on one field from a table) and a subform
(testSubform based on a SQL statement). Here is the SQL:
SELECT ContributorsPledgesQuery.Name,
ContributorsPledgesQuery.Campaign_ID,
ContributorsPledgesQuery.DatePledged,
ContributorsPledgesQuery.AmountPledged,
ContributorsPledgesQuery.Paid, ContributorsPledgesQuery.PaymentDate
FROM ContributorsPledgesQuery;

The DCount needs to give me a count of records showing on the subform
for
each record showing on the main form.

So here is what I have:
=DCount("[AmountPledged]","Pledges","[CampaignName] = '" &
[Forms]![testForm]![txtCampaignName] & "'")

=DCount("[field i am counting records of on subform]","table subform
field
is in","[criteria field on main form] = '" & [Forms]![main form
name]![criteria fieldname on main form] & "'")

This gives me the total number of records in the Pledges table, not the
number of records showing on the subform. Does anyone see what is wrong
with
this?

Best,
Scott
Click to expand...


.
Click to expand...
Click to expand...
 
I'm not talking about fields in tables, and if you look closely at what I
posted, you'll see I'm not talking about controls on subforms anywhere.

When you add a form as a subform on another form, a subform control is added
to the parent form. That control has
properties such as the name of the form being used as a subform.

Try:

Forms!testForm!testSubform.Form.RecordsetClone.RecordCount




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scottyboyb said:
I guess I am not sure what you mean by "Note that the name of the subform
control on the parent form may be different than the name of the form
being
used as a subform."

The field I think you are talking about's name in the table is
AmountPledged
and the field's name in the subform is curAmountPledged. The parent form
is
testForm. The subform's name is testSubform. I do not know of any other
name
it could have. Both of those give me a name error in both of your
examples.

Table: Pledges
Field: AmountPledged
Field Type: Currency

Form: testForm
Subform: testSubform
Subform Field: curAmountPledged

Unbound text box: txtPledgeCount

Any thoughts?

Best,
Scott

Douglas J. Steele said:
To find out how many records are being displayed on a subform, use

Me![NameOfSubformControl].Forms.RecordsetClone.RecordCount

or

Forms![NameOfParentForm]![NameOfSubformControl].Forms.RecordsetClone.RecordCount

(that's supposed to be all on one line...)

Note that the name of the subform control on the parent form may be
different than the name of the form being used as a subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scottyboyb said:
Greetings, a week or so ago I tagged a question onto another thread
about
a
DCount problem and I got an answer that I am having a problem with. I
had
to
go out of town and so I have left this for a while. Now I am back for a
few
days and I am trying to make the answer work.

I have a form (testForm ased on one field from a table) and a subform
(testSubform based on a SQL statement). Here is the SQL:
SELECT ContributorsPledgesQuery.Name,
ContributorsPledgesQuery.Campaign_ID,
ContributorsPledgesQuery.DatePledged,
ContributorsPledgesQuery.AmountPledged,
ContributorsPledgesQuery.Paid, ContributorsPledgesQuery.PaymentDate
FROM ContributorsPledgesQuery;

The DCount needs to give me a count of records showing on the subform
for
each record showing on the main form.

So here is what I have:
=DCount("[AmountPledged]","Pledges","[CampaignName] = '" &
[Forms]![testForm]![txtCampaignName] & "'")

=DCount("[field i am counting records of on subform]","table subform
field
is in","[criteria field on main form] = '" & [Forms]![main form
name]![criteria fieldname on main form] & "'")

This gives me the total number of records in the Pledges table, not the
number of records showing on the subform. Does anyone see what is wrong
with
this?

Best,
Scott
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top