none
Subform Math RRS feed

  • Question

  • Hi.  I'm having a difficult time doing counts on a subform for fields where a certain value is dictated.  For example, I have a field in the subform title MaintenanceItem and the field is a txt field where the values are only "yes" or "no" (and can be in any case--proper, all caps, etc).  I have a txt field called [sfrmMaint] in the subform footer that is invisible and has the following code in the control source:

    =DCount([MaintenanceItem],"sfrmQryRecords","[MaintenanceItem] = 'yes'")

    And the main form has a text box calling that subform text box with this code:

    =[Forms]![Form2]![sfrmQryRecords]![sfrmMaint]

    All that results is an error.  I really thought that I had this buttoned up and it was working fine but that's not the case now.  So I need some help to point out the error of my ways, please!


    Friday, August 18, 2017 2:32 PM

Answers

  • Set the ControlSource property of the control in the subform's footer to:

        =Sum(IIf([MaintenanceItem]="Yes",1,0))

    This will sum the return value of the IIF function calls, which will be 1 if the MaintenanceItem value is 'Yes', zero otherwise, so the result is the count of rows in the subform with a MaintenanceItem value of 'Yes'.

    the syntax in a parent form to reference a control in a subform is:

        =[NameOfSubformControl].Form![NameOfControlInSubform]

    In this expression Form is the Form property of the subform control, NameOfSubformControl is the name of the control in the parent form's Control's collection which houses the subform.  This might or might not be the same as the name of its source from object.  NameOfControlInSubform is the name of the control in the subform whose value you want to return.  So in your case the ControlSource property of the control in the parent from would be:

        =[sfrmQryRecords].Form![sfrmMaint]

    This assumes that sfrmQryRecords is the name of the subform *control* in the parent form's Controls collection.  As I said above, this might or might not be the same as the name of its source form object.

    Ken Sheridan, Stafford, England

    • Marked as answer by R'C Friday, August 18, 2017 6:56 PM
    Friday, August 18, 2017 5:24 PM

All replies

  • Hi,

    If you make the hidden textbox visible, does it show the correct count of records? If so, try changing your main form's textbox control source to this:

    =Forms!Form2.sfrmQryRecords.Form!sfrmMaint

    Hope it helps...

    Friday, August 18, 2017 2:43 PM
  • Set the ControlSource property of the control in the subform's footer to:

        =Sum(IIf([MaintenanceItem]="Yes",1,0))

    This will sum the return value of the IIF function calls, which will be 1 if the MaintenanceItem value is 'Yes', zero otherwise, so the result is the count of rows in the subform with a MaintenanceItem value of 'Yes'.

    the syntax in a parent form to reference a control in a subform is:

        =[NameOfSubformControl].Form![NameOfControlInSubform]

    In this expression Form is the Form property of the subform control, NameOfSubformControl is the name of the control in the parent form's Control's collection which houses the subform.  This might or might not be the same as the name of its source from object.  NameOfControlInSubform is the name of the control in the subform whose value you want to return.  So in your case the ControlSource property of the control in the parent from would be:

        =[sfrmQryRecords].Form![sfrmMaint]

    This assumes that sfrmQryRecords is the name of the subform *control* in the parent form's Controls collection.  As I said above, this might or might not be the same as the name of its source form object.

    Ken Sheridan, Stafford, England

    • Marked as answer by R'C Friday, August 18, 2017 6:56 PM
    Friday, August 18, 2017 5:24 PM
  • =[Forms]![Form2]![sfrmQryRecords]![sfrmMaint]

    Hi R'C,

    I guess that "sfrmQryRecords" is a table/query, and not the name of a subform control.

    I would go for something like:

        =Forms("Form2")!<subformcontrolname>!sfrmMaint

    Imb.

    Friday, August 18, 2017 5:44 PM
  • I got it working though Ken's code arrangement, which is pretty close to what I had, but not close enough I guess.  Thank you to all of you for helping me!  I've used a few forums and this one, without doubt, has proved to be fruitful!  I do wish you could open post attachments, but the workaround helps.

    -R'C

    Friday, August 18, 2017 6:59 PM
  • Hi,

    Glad to hear you got it to work. Good luck with your project.

    Friday, August 18, 2017 7:02 PM