Calculated controls

[RangerTen]

I have a report that I want to print that lists 4 records per page. I have an unbound text box (that of course repeats for every record = 4 per page) that I want to have a calculated value from several of the records. Here is the dilema. It is easy to have a calculated control where "=[field1]+[field2]" is the control source of the text box. However, my calculations are far more complex as that and have several nested if statements. I attempted to compute my calculations on Report.activate and calculate values that appeared on the form, but it ended up computing the values for the first record and then repeating the same values for all records instead of computing different values for each record. Any ideas on this?? It won't let me do it on report.load. If I could figure out how I could set the controlsource of the textbox in code, it would help, but I continually get the message that I can't change the controlsource when attempting to open for print or print preview. COmputers make life easier, eh? Who's the guy who thought that?

 

[Sohaila Taravati]

Have you tried putting your calculations in your query? If you do that, then it would be a lot easier to do what you want to do.

 

[RangerTen]

My only problem is that my calculations are quite complex and have nested if statements. I can write out the formulas in VBA. If I use the query, how do I write out the formula?? SQL?, and I dont' know how to use the expression builder with if statements, if its possible. Any ideas??

 

[Sohaila Taravati]

Do a search on "Expressions in queries" in the forum, you will probably find what you are looking for

 

[Rich]

If you can do the calculations on a form then you can do them on the report, use the OnFormat or Print events

 

[RangerTen]

I've done the calculations on the report. it even calculates correctly. However, it only calculates the first record and then repeats those answers in all the following records. it doesn't separately calculate all records...let me see if I can illustrate this better

RECORD1-------
textbox1=5
textbox2=5
calculatedControl = 10

RECORD2--------
textbox1=8
textbox2=9
CalculatedControl AGAIN EQUALS 10...NOT 17 like it should.

Both records are on the same page
It would work if I could make the controlsource of the CalculatedControl to =textbox1 + textbox2.

However, I can't make an easy calculated field and therefore have to code the calculation. You mentioned the onformat or print, but those aren't report choices...only choices I could find are Open, Activate, Close, Deactivate, Error, No Data, and Page. It won't let me do it on open, and Activate is the one that only allows me to calculate the first record....Kinda getting frustrated. Thanks for all the suggestions.

 

[Rich]

OnFormat/Print properties are for the detail section, click the little box beside the detail section to open the property sheet for that section

 

[RangerTen]

Rich, you were right on the money with this one. Detal_format did the trick. It now calculates for each and every record. Woo hoo. Thanks again for the help. This world forum is one hell of a resource. Wow.