By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,483 Members | 1,585 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
12 Replies


missinglinq
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

yarbrough40
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

NeoPa
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

yarbrough40
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
  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.  
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

yarbrough40
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
  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.  
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

yarbrough40
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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.