Form Value to a Report 1

[Shirley]

Dose anyone know how to insert a calculated field on a form to a report. I have tried many way to accomplish this but nothing yet.

The problem is that the calculated filed is a combination of 3 other fields on the form, but some of the calculation value are not in the query. The many calculation value if oneway, which comsisted of 3 subforms linked by destination, junction and school. On the report there are not links. Could you please give me an ideal of which way to go.

 

[Amiel]

Shirley, I will attempt to give you a solution to this but I must first be sure that I understand the question.

I believe that you wish to create a calcualted value on a report, patterned after a calcualated value that appears on a form that you already have created.

If this intrepretation of your question is correct then, I offer the following:

There are two distinct ways to accomplish this. If the Report will be launched from the form containing the calculated value then you can create a variable in a Code Module to hold that value (you will assign the value to the variable declared in the code module using the Forms 'Current()' event procedure). You will then use appropriate Event procedure in the Report to place the value of that variable on/in the report. You follow ?

1. Create a code module
2. 'Dim' a global variable (declared outside of a Function or Sub) in the Module, call it gReportGlobalforCalculatedValue, or what ever.

2. In the Forms 'Current()' Event procedure add the line
gReportGlobalforCalculatedValue = Val(Me![FieldofInterest1]) + Val(Me![FieldofInterest2]) + Val(Me![FieldofInterest3]). (using 'Val' here to assure that we are getting numeric values, this function will not generate an error if non-numeric values are used so be careful)

3. Add a text box on the report to hold the calculated value. Place it in the detail section.

4. In the 'Format' event of the detail section add the code:
Me![textboxIadded] = gReportGlobalforCalculatedValue.

5. Save the report (and the form).


Note the calculated field on the report depends on the Form to create the calculated value. If launched outside of the form that value will allways be 'Zero'.

The second method is to place all of the fields that you will need for the calculation on the report, setting the visible property to false for each of the three fields, add a text box to hold the calculated value, then add the calculation function to the 'Format' event of the detail section in the report. As in :

Sub Detail_Format ()
Me!{txtFieldIAddedManually] = Val(Me![FieldofInterest1]) + (Val(Me![FieldofInterest2]) * Val(Me![FieldofInterest3])).
'or whatever calculation you need to use.
End Sub


Somwhere in this is your answer. Let me know how it goes.

 

[Shirley]

Amiel

I will try step 1 but step 2 would not work, because the value used in the calculation are from 3 subforms that a linked by 3 fields. This makes it very hard to do the calculation on the report, because there are no linking on a report. Thanks for the suggestion.