If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DCount problem redux
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 |
#2
|
|||
|
|||
DCount problem redux
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 http://I.Am/DougSteele (no e-mails, please!) "scottyboyb" wrote in message ... 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 |
#3
|
|||
|
|||
DCount problem redux
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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "scottyboyb" wrote in message ... 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 . |
#4
|
|||
|
|||
DCount problem redux
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!Control Name "scottyboyb" wrote in message ... 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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "scottyboyb" wrote in message ... 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 . |
#5
|
|||
|
|||
DCount problem redux
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.Rec ordCount -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "scottyboyb" wrote in message ... 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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "scottyboyb" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|