Sum in a Continuous Form

[GLEberts]

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

[missinglinq]

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)>

[yarbrough40]

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? : )

[NeoPa]

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.

[GLEberts]

Explaniation of what I am trying to do

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 "Descriptio n"
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

[yarbrough40]

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

try this:

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

[GLEberts]

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

[yarbrough40]

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

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

[GLEberts]

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

[yarbrough40]

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

[GLEberts]

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

[pjv50]

thanks for this thread

[NeoPa]

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