Report On format not working with vba

[tagteam]

have a boolean field (checkbox) in my report detail section for a suspended car. I have this code in the on format section



If Me.Suspended = False Then
Me.CLeaseSuspensionStart.Visible = False
Me.CLeaseSuspensionEnd.Visible = False
Else
Me.CLeaseSuspensionStart.Visible = True
Me.CLeaseSuspensionEnd.Visible = True
End If

however, it still shows all the fields no matter. what. Does this kind of code work in a report?
Thanks

[June7]

Yes, it should, but only for PrintPreview or direct to printer, not ReportView.

Could simplify with:

Me.CLeaseSuspensionStart.Visible = Me!Suspended
Me.CLeaseSuspensionEnd.Visible = Me!Suspended

But the control will still occupy space on form and there will be blank area.

Instead of VBA, could have expression in textbox ControlSource: =IIf(Suspended, CLeaseSuspensionStart, Null)

Then display view won't matter.

Or maybe apply filter so suspended records are not included.

[CJ_London]

depends on the view you have opened for the report (should be report or print preview), but it should work assuming all the fields exist in the current record

Not sure why you need a suspended boolean field - the fact CLeaseSuspensionStart is populated tells you that.

you could try simpler code

Me.CLeaseSuspensionStart.Visible = not isnull(Me.CLeaseSuspensionStart)
Me.CLeaseSuspensionEnd.Visible=Me.CLeaseSuspensionStart.Visible

Ah - June got there first

[tagteam]

Ok, I see. I do need it in Report view because the user pulls it up to review data.

I found a solution that works better for me. Kind of what June said but I created a subquery and tied that to the main query so that only cars that are actually suspended have a suspension record. Then when I run the report I can use the shrink method and have it hide the line when not suspended and show when it was suspended.

So if anyone is views this thread later the answer for me was not to do it in the formatting but to do it in the query and then use shrink on the detail section and those individual field boxes.