Perform calculation through button on report

[luzz]

Hi all, I would like to ask how to have difference calculated value appear on report through button? I am able to click on the button on my report to perform calculation, however, all the calculated value are the same. How can i have difference calculated value for each line?

Only the first line of the calculation is correct in the image I attached.

below is my coding:

'Declare variable
Dim Lbs As Integer
'set variable to textbox
Lbs = Me.SumOfLbs
Dim GrossWeight As Integer
Dim NettWeight As Integer
NettWeight = Me.TotalNettWeight

'Lbs <130
If Me.SumOfLbs <= 130 Then
Me.TotalGrossWeight = (Me.TotalNettWeight + 20)
'Lbs <200
ElseIf Me.SumOfLbs >= 131 And Me.SumOfLbs <= 200 Then
Me.TotalGrossWeight = ((Me.TotalNettWeight * 1.15) + 15)
'Lbs <500
ElseIf Me.SumOfLbs >= 201 And Me.SumOfLbs <= 500 Then
Me.TotalGrossWeight = ((Me.TotalNettWeight * 1.1) + 10)
'Lbs <999
ElseIf Me.SumOfLbs >= 501 And Me.SumOfLbs <= 999 Then
Me.TotalGrossWeight = (Me.TotalNettWeight * 1.15)

 

[The_Doc_Man]

First, you normally don't include active controls like command buttons on a report. Not that you can't, but it is rare. Usually, if you want a computation, you just include it in the .Controlsource definition and always do it. Is there ever a time when you would NOT want that computation performed?

Second, the image you posted does not clearly illustrate the problem you reported. You said all the calculated values are the same - but that is not the case for the image you showed us.

I'm also going to presume you merely truncated that computation ladder because you have no trailing END IF. You can simplify the way this IF-ladder reads by using a SELECT CASE statement. It has the same effect but is easier to read, I think.

SELECT CASE Me.SumOfLbs
    CASE 0 TO 130
        Me.TotatGrossWeight = Me.TotalNetWeight + 20
    CASE 131 TO 200
        Me.TotalGrossWeight = ((Me.TotalNetWeight * 1.15 ) + 15)
    CASE 201 TO 500
    etc. etc.
END SELECT

 

[luzz]

First, you normally don't include active controls like command buttons on a report. Not that you can't, but it is rare. Usually, if you want a computation, you just include it in the .Controlsource definition and always do it. Is there ever a time when you would NOT want that computation performed?

Second, the image you posted does not clearly illustrate the problem you reported. You said all the calculated values are the same - but that is not the case for the image you showed us.

I'm also going to presume you merely truncated that computation ladder because you have no trailing END IF. You can simplify the way this IF-ladder reads by using a SELECT CASE statement. It has the same effect but is easier to read, I think.

SELECT CASE Me.SumOfLbs
    CASE 0 TO 130
        Me.TotatGrossWeight = Me.TotalNetWeight + 20
    CASE 131 TO 200
        Me.TotalGrossWeight = ((Me.TotalNetWeight * 1.15 ) + 15)
    CASE 201 TO 500
    etc. etc.
END SELECT

I include the button for computation is because i want to computate the grossweight based on the total amount for nettweight.

The image i attached shows that the grossweight value is the same despite having difference nettweight. By right difference nettweight will give a difference grossweight

 

[Minty]

You can do all this in the report without the button.
Make the data available in your underlying query , then have a calculated control on the report based on that data.
Or better still do the calculations in the query.

 

[luzz]

You can do all this in the report without the button.
Make the data available in your underlying query , then have a calculated control on the report based on that data.
Or better still do the calculations in the query.

I actually do the calculation part on my form, however, the data are calculated line by line so when i sum the data in the report, the grossweight does not tally with my nettweight

 

[Minty]

Move that calculation from the report to the query, then you can sum it on the report, you don't need to display it - have it as a hidden control if it get you the result you want.

 

[luzz]

In order to have active controls on a report, you must use ReportView rather than PrintPreview. ReportView is "live" and PrintPreview is "static". A live report works more like a form where unbound controls always contain the same value. Also certain events don't run and so not all your code will be executed and page totals will not be displayed.

So far, everyone, has told you to not use a button. I hate to go along with the herd but that is my advice also. Do NOT use PrintPreview. Do the calculation in the query which will allow the control to be bound and therefore allow you to sum it.

Using button in the report is my last resort. I am using a query to sum the calculation. However, if i do a combine PO, the sum will not telly with my manual calculation. So i am thinking if there is a way where i can just perform the calculation after summing up the different weightage

 

[luzz]

Did you try performing the calculation in the query as I suggested?

yes, i use query to sum up the calculation