Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184

    Unhappy Unanswered: Subform Control has no Value

    I have a form with 6 subforms. I need to sum the total dollar amounts of all the subforms and post this value to a control on the main form. I'm using a button that does the computations (along with some other things).

    This works fine when the subforms have values, but there will be many instances when at least one subform has no records. I get a "subform control has no value" error.

    How can I get around this problem? I've tried the Nz() function in both the main and subforms, but it doesn't work. I was thinking of trying something in the subforms such as IIf(recordcount=0,0,sum(nz(DollarAmounts))) for the control with the total amount, but I don't know if that's possible, or if it's the smartest/fastest way to go about it.

  2. #2
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    I am not sure if you are accounting for the syntax, but nz() inlcudes an optional value if it is null:

    nz([Fieldname], Valueifnull)

    If you set your nulls to zero, it might sum then.

  3. #3
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    Thanks, Brian, I learn something new every day....

    However, it won't work for me. My problem is not that the fields have null values, but that there are no records in the subform. I need to either force the totals in the subform to zero when there are no records, or to check for records when summing at the main form level.

  4. #4
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    I had a sinking suspicion that was the case.

    You said that you had this as part of a button click? Could you post some of your pertinant code to give me an idea of how you are calculating it?

    I was thinking a loop of some sort where you check the recordcount of each of your subforms and skip those with none.

  5. #5
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    So far I haven't written all the code because I wanted to make sure the first one worked before going through them all.

    It's something like this:

    Me.curTotalStillDue= (Nz(Me.SUB2003.Form!cur2003Total, 0) + Nz(Me.SUB2004.Form!cur2004Total, 0)+Nz(Me.SUB2005.Form!cur2005Total, 0)+Nz(Me.SUB2006.Form!cur2006Total, 0))- Nz(Me.SUBCredits.Form!curTotalAlreadyPaid, 0)


    Any of these subforms could have no records.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a method I use:

    http://www.mvps.org/access/forms/frm0022.htm

    Some people use the IsNumeric on it's own, but I find the function easier.
    Paul

  7. #7
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184

    Smile

    Thanks all, the IsNumeric() function worked great. I guess I was trying to make it a complicated problem.

    Now for part 2-- When someone changes one of the subforms and I hit the Ccompute button, I have to hit it twice to get the calculation to work. It has to do with moving the focus back to the main form and saving the changes in the subform, I think. I'm requerying the subforms before doing the calculation and that fixes it, but is there a more intelligent way?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •