"On Format" event not working in report

[rantaljo]

In a report, I want the labels and fields in the detail section of the report to be invisible if there is no data to display. I thought I could do this in the ‘OnFormat’ event for the Detail section of the report.

I’ve tried various iterations of this VBA code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!lblFindingDescription.Visible = (Me!FindingDescription <> Null)
Me!txtFindingDescription.Visible = (Me!FindingDescription <> Null)
End Sub

Where FindingDescription is the name of the underlying field name that I want to evaluate for data, lbtFindingDescription is the label “Finding Description” on the attached screen shot, and “txtFindingDescription” is the control.

What am I doing wrong?

Julie

[HansV]

Instead of

(Me!FindingDescription <> Null)

use

Not IsNull(Me!FindingDescription)

[rantaljo]

Hi Hans,

Thanks for trying. I tried that, and after much angst, figured out that the event wasn't triggering. Based on other posts, it seems that some attributes -- such as visible -- can't be changed using the 'On Format' event.

Do you have any thoughts on how I can 'hide' fields and labels if they are null?

Julie

[HansV]

It should be possible to change the Visible property of controls in the On Format event of the section that contains them - I have done this quite often.

Could you create a stripped-down copy of the database without sensitive information, zip it and attach the zip file to a reply? Thanks in advance!

[rantaljo]

Hi Hans,

Thanks for looking at this. I've attached the report, query and several tables. The report is kind of a mess, since I was trying different things -- but you get the idea.

Julie

[HansV]

The code works fine, but you have to switch to either Report View or (preferably) Print Preview to see the effect. If you switch to Layout View, the code won't run.

S270.png

If it doesn't work that way for you, make sure that you have enabled macros/VBA.

[rantaljo]

Thank you. That seems to work!

[rantaljo]

On a separate note, do you know why neither of the Iif statements in the header part of the report are working? I can't figure it out :(

[HansV]

The text box at the top of the InspectionsID header is named NameShort, but its control source refers to NameShort too. This causes a circular reference.
Rename this text box to - for example - txtNameShort, and use IsNull(...) instead of ... Is Null:

=IIf(IsNull([Location Detail]),[NameShort],[NameShort] & " " & [Location Detail])

or to the somewhat shorter

=[NameShort] & " "+[Location Detail]

Explanation: if you use + to concatenate strings, Null on either side of the + will cause the result to be Null.
"something" & Null = "something"
"something"+Null = Null

The control source of the other text box can be changed to either of the above too.

[rantaljo]

Aah -- if only for coding knowledge. I appreciate you sharing yours. Thanks so much!

Julie

[rantaljo]

And one more problem...

I launch the report we've been discussing from a form, called "Inspections" (design view attached), by clicking on the 'Inspection Report' button. It sets a temporary variable to filter the report by the ID for the inspection that was on the Inspections Form.

The macro behind the button, 'on Click' is:
Action ---> Arguments
SetTempVar --> inspectionRec, [Forms]![Inspections]![InspectionsID]
OpenReport --> InspectionIndividualReport, Report,,[TempVars]![inspectionRec]=[QryRptInspectionsFindings]![InspectionsID]
Close --> Form, Inspections, Prompt

The report opens and filters correctly.

To close the report and return to the same record on the Inspections screen, I tried the following macro behind a close button on the Report (design view attached), 'on Click':

Action --> Arguments
Close --> Report, InspectionIndividualReport
OpenForm --> Inspections,Form,,,,Normal
GoToControl --> InspectionsID
FindRecord --> [TempVars]![inspectionRec], Whole Field, No, All, No, Yes

This does not bring me back to the record I started on. What is wrong? What approach should I be taking?

Julie

[HansV]

Why don't you leave the form open in the background? The user then automatically returns to the same record when they close the report.

[rantaljo]

Well, that's just too easy :)

But, a problem going back to the beginning of the day, I think.

The 'On click' command for 'Print Report' button generates a 'rendering' error and will not run the print command. If I take out the VBA that hides the fields in the detail section of the report, it will run. Also, if I print the report from the menu command, it completes the rendering and prints.

I use the buttons in the database because I hide the toolbar and menus from the users -- so I use the buttons with macros to allow users some limited functionality.

Attached are the VBA code for hiding the fields, and a screen shot of the report with the error message.

Julie

[HansV]

I'm afraid I don't know why you get an error. I opened the report with the code to hide the two controls from your sample code and a filter on InspectionsID, then printed it to PDF without error messages. I'm using Foxit PDF though, not Adobe Acrobat.

S0664.png

[rantaljo]

From what I could tell, it didn't like that I was triggering the report (from the Inspection form) into 'Report' view, then running the VBA, and then trying to print from the Report. When I changed the report format to 'Print Preview' and then triggering the 'Print' command from the form's button (instead of the report's button), I was able to get it to print without a hitch.

So that's a day's work.

Thanks for your help.

Julie

[HansV]

Glad you were able to solve it. Perhaps it's a difference between Adobe Acrobat and Foxit PDF - I could use your Print Report button from Report View without error.