Code for If/Then After Report Opens

[jt463]

My report runs several calculations when it opens and then displays the data. Just before the report is previewed or printed, I would like to insert code that will look at one of the calculated fields and analyze it to see if the value is greater than or less than 0.

If it is less than zero, I want the field value to be "None". If it is greater than zero, I want it to display the actual value.

I believe this would be the code:

If Me.InitialPayment.Value < 0 Then
    Me.InitialPayment.Value = "None"
Else
End If

However, when I place it in the 'Open' Procedure, I don't believe the calculations have processed. (I get a VB Error that says 'You have entered an expression that has no value'.) So, I am trying to insert it AFTER the calculations process but BEFORE the report is displayed.

Any help would be greatly appreciated!

 

[kellbell]

How are your calculations working? If the calculations are based off of controls in reports or forms in your database, you can use the if statement in the expression builder instead of in vba code.

 

[jt463]

Thanks kellbell. The calculations are based on controls in the report. I have never tried the if statement in the expression builder. I will have to give that a try.

I ended up creating a label ('None') and making it visible or not visible depending on the value of the InitialPayment control. I placed that code with 'onActivate', and it worked like a charm.

Perhaps a bit archaic, so I will have to look in to your suggestion.

Thanks for your help!

 

[jjlogan]

JT-
1. I think you can set up a control on the report (textbox) with the logic of determining "none". Don't think you need to use label and change visible property. When the report opens, this new control does its calculation and enter either None or 0 or whatever.
2. Reports and forms are different animals in Access in that Access has a report engine that starts with its set of records and moves from beginning to end of report presenting the data; whereas forms are user interactive and can respond to values in controls (e.g. at an event like like clicking a command button or entering / exiting a text box). So, you can do calculations in the Open event of report (but would need to create an ADO recordset in the Open event and then do calc's and then set up the SQL record set you want and assign it to the Recordsource property of Me (the report)) or you can use a control on the report to hold an expression as suggested above. Another way to get None or 0 in a control is to do the calculation (IIF) in the query design and use an Alias name (e.g. a field in query like Initial:IIF([InitialPayment]>=0,[InitialPayment],"None").
If this is old hat, forgive me.
jeff

 

[Hakala]

You can also use some code in the Detail section of reports. For instance, this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Difference > 45 Then
Me.Section(0).BackColor = &HC0C0C0
Else
Me.Section(0).BackColor = &HFFFFFF
End If
End Sub

Changes the background color of each line of the detail section depending on the value in the [Difference] field. Perhaps you can tweak it to do what you need?




Michelle Hakala

http://www.winebird.com/

 

[danvlas]

You should place your code in the format event of the section that contains the control InitialPayment.
Open event is not good for control manipulation.


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html