455,483 Members | 1,585 Online
Need help? Post your question and get tips & solutions from a community of 455,483 IT Pros & Developers. It's quick & easy.

# Sum in a Continuous Form

 P: 51 I made an addition to a form I was using and it threw a wrench into the works for my ability to adjust the calculation. I am trying to sum a unbound control calculation in a continous form. question: can you only sum a bound control name? is that why I am getting an error? question: must I use a query to do the calculation for me? if that is true i was trying to locate examples of what i am trying to do and can't locate any - could you give me some direction where to find examples. thanks for your help gary Jun 21 '09 #1
12 Replies

 Expert 2.5K+ P: 3,532 When summing a calculated field you have to use the expression, not the field itself. In other words if FieldC = FieldA + FieldB you cannot use Sum(FieldC) you have to use Sum(FieldA + FieldB) Linq ;0)> Jun 21 '09 #2

 100+ P: 320 can u be a bit more specific as to what you're trying to do? the right approach will depend on where your fields are in the continuous form ( in the same record or in different records) if you are needing to sum the value of textbox1 in each of the records on your form you will need to grab the value from the first record, advance to the next, add the value, advance etc. - and to echo what missinglinq is saying you'll probably have to refer to the calculation as opposed to the value... can u provide an example and your error? : ) Jun 22 '09 #3

 Expert Mod 15k+ P: 31,709 @GLEberts Forget summing these values. Are they even different values to sum? See Why Values in Unbound Form Controls Don't Persist for why I think they will all be the same and summing them a waste of effort. Jun 22 '09 #4

 P: 51 sounds to me that when I developed this form for the use I am using it for that it maybe incorrect. Let me explain what I have done and what I am trying to achieve. Best example to use is something very similair to our accounting program we use "Quickbooks Pro" In Quickbooks Pro you can generate estimates for which ever department of the company you wish. For this example lets use Windows for you home. my existing form in my database I have made into a continious form. the following is what I have existing (control names listed) First column is "Qty" Second column is "Type" Third column is "Description" Fourth column is "Costeach" NOTE: Just added Fifth column is "Total" I knew someday I wanted to make this addition that is why I origianally had the "qty" column. I always just used "1" in "Qty" I before just used "sum([total]) and it worked great. Now however we could use the option to change the "qty" to something other then "1" When my original question begain what I was trying to is: Take the "qty" * "Costeach" and the answer would be in "Total" "Total" is unbound so I could not use the "sum" expression Hope this is clear as what I was working on. Any suggestons would be appreciated if I took the wrong approach and redo the form. Thanks Gary Jun 22 '09 #5

 100+ P: 320 I'm going to assume that the control name that has qty in it is called "Text1" try this: Expand|Select|Wrap|Line Numbers Private Sub Command0_Click()   Dim MyValue As Integer DoCmd.GoToRecord , , acFirst MyValue = Me.Text1.Value Dim i As Integer For i = 1 To Me.Form.RecordsetClone.RecordCount - 1   DoCmd.GoToRecord , , acNext   MyValue = MyValue + Me.Text1.Value     Next Debug.Print MyValue End Sub   Jun 22 '09 #6

 P: 51 thanks for your reply however I am confused as to where to use this in the form to sum in a continious form- Private Sub Command0_Click() thanks Gary Jul 5 '09 #7

 100+ P: 320 sorry you're still having troubles... based on your previous description, I don't believe you are needing to SUM anything but rather multiply one textbox value by another and put that result into a third textbox, and then repeat this for each record on the continuous form - is this correct? If so... then paste this code into the OnClick event for a button on your form. (I've used "Command0" assuming that is the name of your button.) OR - you could place this in the After Render event of the form. That way it will fill in your totals without having to do any extra step. let me know if this solves your problem... if not, then I am still confused as to where these controls appear on your form and what you are trying to do with them Expand|Select|Wrap|Line Numbers   Private Sub Command0_Click()    DoCmd.GoToRecord , , acFirst    Dim i As Integer  For i = 1 To Me.Form.RecordsetClone.RecordCount - 1   Me.Total.Value = Me.Qty.Value * Costeach.Value DoCmd.GoToRecord , , acNext        Next    End Sub    Jul 5 '09 #8

 P: 51 thank you for your reply and explaination you are correct i am trying to "multiply one textbox value by another and put that result into a third textbox, and then repeat this for each record on the continuous form" - is this correct? I have inserted the code as you suggested. it seems to only return a answer for the first record in continious form. if there are 2, 3 or ... more records the [qty]*[costeach] is blank in the others. i will work on it for awhile and see what i can produce thanks for your help. Gary Jul 6 '09 #9

 100+ P: 320 ok - is your form bound directly to a table? or a query? What will work for sure is to create a new column in your table then calculate the field within the sql statement. You then bind that new column to the textbox(total) on your form. There is really no reason to have the total textbox an unbound control. can you post your SQL for the form and I can help you to do this Jul 6 '09 #10

 P: 51 @yarbrough40 Thanks for the advice got it up and running after a few tries. works great. Thanks for your time again. Gary Jul 6 '09 #11

 P: 1 thanks for this thread Jul 22 '09 #12

 Expert Mod 15k+ P: 31,709 It's always good to see that others get benefit from these threads as well as the original posters. Thanks for posting :) Aug 3 '09 #13