locked
Continuous Form Calculated Control Sum RRS feed

  • Question

  • Hi,

    I have a need to place a text box in the footer of a continous form dispalying the sum of a calculated control from my form's detail area. I've read plenty of threads saying you have to put the calculation in the sum formula instead of just summing the field (ex. instead of Sum([ExtendedAmount]) you have to use Sum([Price]*[Quantity]) or put the calculation in the underlying query and then you can Sum it. The problem is the calculated control I need to sum references other calculated controls which, reference other calculated controls, and so on. I can't put it in the query because I get "Query too complex". I was trying to put all of the calculations into the SUM formula, but reached a stopping point. Ok so a litle background info will probably be helpful.

    I have material items (over 1000) and for each material item I am trying to basically run MRP (material requirements planning). So for each item I have a demand, a supply, and a forecast. Each of these are split into weeks (1-52). To acheive this I had to run each of those through a crosstab query. So what I ended up with is 52 columns of demand, 52 for supply, and 52 for forecast. I also have on hand inventory (just one column). So for each week I calculate a net available. For week 1 it is (On Hand Inventory + Week1Supply)-(Week1Demand+Week1Forecast). Week 2 takes (Week1Net+Week2Supply)-(Week2demand+Week2Forecast) , and so on. When I get a few weeks into it I get the error "Query too complex". So what I did was did the calculations at the form level.

    So my continuous form is setup similar to a crosstab query, where I have weeks accross the top (1-52) and then for each material i have each of those cateogries falling under each week. So when a week net available goes negative, I have a "Planned Receipt" text box which gives me the inverse of that negative number.

    So for week 2 :  2PR= IIF([Week2Net]<0,-(Week2Net),0).

    Now, comes the tricky part (or what i considered the tricky part). When I have a planned receipt, I needed to create a "Planned Order" for the material, but the planned order needed to show up in the weeks before the planned receipt, based on the lead time of the item. So if my material has a lead time of 2 weeks, and I show a planned receipt for week 10, I need to have a planned order for week 8. So what I did here was for each week I put a text box which contained this formula:

    =[Forms]![MRP](Format((10+[LTWeeks]),0) & "PR"))

    [LTWeeks] is my lead time, in weeks, for the material. So basically this takes the week number plus the lead time in weeks and references that weeks planned receipt field. So now for every week I have a "Planned Order" control text box. Then, from that, I multiplied my planned order number by the price of the material to get a dollarized version. What I ultimately need is a dollarized SUM of all materail planned orders for each week. But, as we all know you cant Sum a calculated control.

    Please help! I've been beating my head against the wall all day trying to figure this out. I consider myself a beginner with VBA, but can usually manage to follow instructions with it, so if VBA is required thats ok. How can I get the Sum of my Planned Order fields for each week?

    Sorry about the long post, but I think it was necessary to explain my situation and why I can't use the usually suggested methods to get to a summed total Let me know if any additional info is needed.

    Thanks in advance.

    Wednesday, March 7, 2012 8:42 PM

Answers

  • No, I don't think I can say much beyond what's in VBA Help without going into exact details for each of your calculations.  In general, you need to change each text box's expression to a function call like  =calcsomething(fieldA,  fieldB, ...) where fieldA, fieldB, etc. are the names of record source fields, not control names.

    Each individual function would use the text box's expression in its code:

       Public Function calcsomething(fA,  fB, ...)
          Me.thetextbox = {the text box expression you had before}
       End Function

    Replace any references to another text box in the expression with a call to the other text box's corresponding function.

    This may be tedious coding, but it should not be difficult.

    • Marked as answer by Bruce Song Wednesday, April 4, 2012 3:19 AM
    Saturday, March 10, 2012 9:21 PM

All replies

  • Well, I did not follow all that, but I think maybe you should try creating Public VBA function(s?) in a standard module to do the calculations (again, not using calculated control values).  Eventually you may be able to get to the point where you can use the function(s?) in the query or in the Sum function.  If you can do that, it is important that the function has no arguments that are control references and does have at least one argument that is a field in the query.
    Wednesday, March 7, 2012 10:14 PM
  • Tell me if I understood you correctly:

    You have a continuous form with calculated fields (lets call them txtCalculated1....n). For each txCalculated you want have a total at the bottom in the footer section. You say that calculated fields cannot be summed.

    Imran

    Wednesday, March 7, 2012 10:27 PM
  • So I thought it might be helpful to post the design view of my continous form to give a better idea of what I've got going.

    I need to SUM the planned order $$ for each week (only have it setup for week 10 right now.) I havent created the planned order $$ for subsequent weeks because I want to make sure i can total it first. If i can't total it, it is useless.

    I'm not very familiar with creaitng public vba functions. Can you give me an example of what code i would need to put in it? Sorry, I'm prettty new to VBA (and relatively new to Access for that matter..)

    Any ideas?

    Wednesday, March 7, 2012 10:28 PM
  • Correct. The fields are not calculated in the query, though. They are calculated in the form's text box. Since Access doesn't "store" these values, they can't be summed with a Sum() function....
    Wednesday, March 7, 2012 10:36 PM
  • Is there a way to create a temporary table from the calculated controls, and then have the SUM control total the table fields?
    Thursday, March 8, 2012 2:06 PM
  • No, I don't think I can say much beyond what's in VBA Help without going into exact details for each of your calculations.  In general, you need to change each text box's expression to a function call like  =calcsomething(fieldA,  fieldB, ...) where fieldA, fieldB, etc. are the names of record source fields, not control names.

    Each individual function would use the text box's expression in its code:

       Public Function calcsomething(fA,  fB, ...)
          Me.thetextbox = {the text box expression you had before}
       End Function

    Replace any references to another text box in the expression with a call to the other text box's corresponding function.

    This may be tedious coding, but it should not be difficult.

    • Marked as answer by Bruce Song Wednesday, April 4, 2012 3:19 AM
    Saturday, March 10, 2012 9:21 PM
  • No, you would have to go through pretty much the same process of calculating the values and writing them to a recordset on the temp table.
    Saturday, March 10, 2012 9:24 PM