Hide Part of Report Based on Field Value

[raweber]

Is it possible to toggle the .visible status of an object in a Report based on the value of one of the fields in the record?

I have a report called rptDrillDown which gives the details of a record after being called by an On Click event from the summary report. So far, so good. It's calling up just fine. However, each record has a check box indicating whether the study has to be implemented. When the box is checked, a whole bunch of fields show up with dates to track the implementation. When the box is unchecked, the fields are invisible.

This works great on my data entry form, but not so much for my report. The crazy thing is I thought it was working yesterday. Today, not so much.

Here's a sample of the code:

Private Sub Report_Load()
If Me.[IM] = -1 Then
    Me.Label75.Visible = True
    Else
    Me.Label75.Visible = False
    End If
End Sub

I deleted out all the other objects - none of them work. The behavior I'm getting is that whether IM (the check box) is checked (-1) or not checked (0), none of the fields show up.

I'm guessing either I can't do this in a report, or the answer is actually pretty simple.

Thanks, Rob

 

[Mr. B]

Try using the OnFormat event of the section of the report where the control is located.

 

[raweber]

I don't see an On Format event on the list.

I guess I should have said Access 2007.

 

[Mr. B]

With your report in design view, click on the desired section bar of the report and then look in the Events tab. If you have the "report" selected there is no On Format event, but there is one for each section of the report.

 

[raweber]

Ahh, there it is.

Hmm, now the fields show whether IM is checked or not, rather than not showing whether IM is checked.

Sure I can do this in a report?

 

[Mr. B]

Yes, you can do this in a report but after looking at your code; You are referring to a label not a control. If you want to read the value of a field then you would be checking the value of some control in your report.

 

[raweber]

[IM] is a field in the table for which the record is being called up. When I click through from the Summary Report the source for the detailed report is the master table with a filter set by the On Click event from the summary report.

I want to turn off a number of items, of which Me.Label75 is just one, based on the value of [IM]. But, [IM] is definitely a field in the underlying table.

Thanks for taking all this time with me, Rob

 

[Mr. B]

You have to refer to the name of the control that has the value of the [IM] field in your table.

 

[raweber]

In my table, the column, or field, that I want to use as the determination of what is displayed is called "IM" and it is a "Yes/No" data type.

In the detailed report, the check box that is tied to this field is called [chkIM]. When I change the On Format event to [chkIM], I get the same behavior.

If I can't make it happen, I'll just leave all the controls visible - it won't hurt anything, it just won't be as slick...

Thanks, Rob