Connecting Tech Pros Worldwide Help | Site Map

Sum in a Continuous Form

  #1  
Old June 21st, 2009, 05:44 PM
Member
 
Join Date: Mar 2008
Location: Doylestown, PA
Posts: 44
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
  #2  
Old June 21st, 2009, 09:52 PM
missinglinq's Avatar
Moderator
 
Join Date: Nov 2006
Location: Richmond, Virginia USA
Posts: 2,973
Provided Answers: 11

re: Sum in a Continuous Form


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)>
  #3  
Old June 22nd, 2009, 03:19 AM
Member
 
Join Date: Jun 2009
Posts: 33

re: Sum in a Continuous Form


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? : )
  #4  
Old June 22nd, 2009, 03:30 PM
NeoPa's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Posts: 15,473
Provided Answers: 57

re: Sum in a Continuous Form


Quote:
Originally Posted by GLEberts View Post
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?
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.
  #5  
Old June 22nd, 2009, 10:03 PM
Member
 
Join Date: Mar 2008
Location: Doylestown, PA
Posts: 44

re: Sum in a Continuous Form


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
  #6  
Old June 22nd, 2009, 11:14 PM
Member
 
Join Date: Jun 2009
Posts: 33

re: Sum in a Continuous Form


I'm going to assume that the control name that has qty in it is called "Text1"

try this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. Dim MyValue As Integer
  4. DoCmd.GoToRecord , , acFirst
  5. MyValue = Me.Text1.Value
  6. Dim i As Integer
  7. For i = 1 To Me.Form.RecordsetClone.RecordCount - 1
  8.  
  9. DoCmd.GoToRecord , , acNext
  10.  
  11. MyValue = MyValue + Me.Text1.Value
  12.     Next
  13. Debug.Print MyValue
  14. End Sub
  15.  
  #7  
Old July 5th, 2009, 03:07 PM
Member
 
Join Date: Mar 2008
Location: Doylestown, PA
Posts: 44

re: Sum in a Continuous Form


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
  #8  
Old July 5th, 2009, 09:43 PM
Member
 
Join Date: Jun 2009
Posts: 33

re: Sum in a Continuous Form


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
  1.  
  2. Private Sub Command0_Click() 
  3.  
  4. DoCmd.GoToRecord , , acFirst 
  5.  
  6. Dim i As Integer 
  7. For i = 1 To Me.Form.RecordsetClone.RecordCount - 1  
  8. Me.Total.Value = Me.Qty.Value * Costeach.Value
  9. DoCmd.GoToRecord , , acNext 
  10.  
  11.     Next 
  12.  
  13. End Sub 
  14.  
  #9  
Old July 6th, 2009, 05:12 AM
Member
 
Join Date: Mar 2008
Location: Doylestown, PA
Posts: 44

re: Sum in a Continuous Form


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
  #10  
Old July 6th, 2009, 01:41 PM
Member
 
Join Date: Jun 2009
Posts: 33

re: Sum in a Continuous Form


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
  #11  
Old July 6th, 2009, 09:49 PM
Member
 
Join Date: Mar 2008
Location: Doylestown, PA
Posts: 44

re: Sum in a Continuous Form


Quote:
Originally Posted by yarbrough40 View Post
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
Thanks for the advice got it up and running after a few tries. works great.
Thanks for your time again.
Gary
  #12  
Old July 22nd, 2009, 03:24 PM
Newbie
 
Join Date: Jul 2009
Posts: 1

re: Sum in a Continuous Form


thanks for this thread
  #13  
Old August 3rd, 2009, 03:06 PM
NeoPa's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Posts: 15,473
Provided Answers: 57

re: Sum in a Continuous Form


It's always good to see that others get benefit from these threads as well as the original posters. Thanks for posting :)
Reply


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum the value on a continuous form csolomon answers 4 December 3rd, 2008 03:53 PM
Running Sum In Continuous Form In Ms Access 2003 balancetotal answers 6 March 21st, 2007 08:50 PM
Testing Data after save on continuous form David answers 1 January 4th, 2007 06:55 PM
continuous form, how to sum unbound control in footer ? Roger answers 1 November 13th, 2005 04:08 AM
Sum Unbound Control on Continuous Form Footer? Pat answers 3 November 12th, 2005 07:50 PM