Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    87

    Unanswered: VBA: Sum of field in unbound textbox

    I have form and subform in it. Subform has continuous records and one field at the top (e.g. ProductName or Price or Quantity). If i want to have a totalsum of particular field, i create unbound textbox, put it in footer, and write " =Sum([Quantity]) " in it.

    Can i do the same thing (Total sum for particular field) for this subform, but with VBA? Rather than in Design View...

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Not really, well not with any modicum of efficiency anyways. Why would you possibly want to do this?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'll say a qualified "yes", you can. Having said that, I'll explain.

    1- To use the same SQL sum() function like you would in a bound textbox in design view does not exist.

    Of course, if you want to open the RecordSource seperately in VBA and run a SQL statement such as "SELECT Sum(FieldName) As SumOfFieldName FROM" etc., and then assign the unbound textbox to the value of SumOfFieldName, you can do that, but I can't imagine why you would want to do it that way.

    2- DIM a global variable, say SumFields as some numeric type. In the report's OnOpen event you can initialze SumFields to 0. In the Detail OnPrint event, you can have the code SumFields = SumFields + Me.FieldName. Then in the Footer's OnPrint event you can have the code Me.UnboundControlName = SumFields.

    As you see, this is a painful way to go about it, and I can't imagine doing it in any other way but the sum() of a bound control in the footer, but you asked...

    HTH,

    Sam

  4. #4
    Join Date
    Oct 2005
    Posts
    87
    Quote Originally Posted by Sam Landy
    I'll say a qualified "yes", you can. Having said that, I'll explain.

    1- To use the same SQL sum() function like you would in a bound textbox in design view does not exist.

    Of course, if you want to open the RecordSource seperately in VBA and run a SQL statement such as "SELECT Sum(FieldName) As SumOfFieldName FROM" etc., and then assign the unbound textbox to the value of SumOfFieldName, you can do that, but I can't imagine why you would want to do it that way.

    2- DIM a global variable, say SumFields as some numeric type. In the report's OnOpen event you can initialze SumFields to 0. In the Detail OnPrint event, you can have the code SumFields = SumFields + Me.FieldName. Then in the Footer's OnPrint event you can have the code Me.UnboundControlName = SumFields.

    As you see, this is a painful way to go about it, and I can't imagine doing it in any other way but the sum() of a bound control in the footer, but you asked...

    HTH,

    Sam

    Thanks for reply, just to clarify, you mean it is immpossible to have =sum() in unbound textbox or in bound textbox on my form? (Without VBA)

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No, he means it is impossible to DUPLICATE =sum() in a textbox without vba.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by YZF
    Thanks for reply, just to clarify, you mean it is immpossible to have =sum() in unbound textbox or in bound textbox on my form? (Without VBA)
    I could take the easy way out and agree with Teddy. However, I'd be lying, because I really don't understand the question.

    Essentially, I think you're confusing the meaning of an unbound control vs a bound control. When you type FieldName as the ControlSource, that makes it, of course, a bound textbox. When you type in a formula, such as =Sum(FieldName), you are still using a bound textbox, because the textbox information is "bound" to the data in the form's Record Source. All you're doing with the formula is having SQL do some action on the data, but it still reports on the data.

    An unbound control is a control that has no entry at all in the Control Source property. In almost all cases, it's used by the program - read VBA - to store temporary data or, in any case, data that will be used in a manner transparent to the user. In such cases, the control has its Visible property set to No.

    Even when you want to display the control, like in your case, what makes it unbound is the fact that there's no entry in the Control Source property.

    To solve for your problem, all you need is another textbox, in the ReportFooter section, with the same formula, =Sum(Quantity) to give you a total for the field irrespective of ProductName.

    HTH,

    Sam

Posting Permissions

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