CODEWITHSUNDEEP
eventsms-accessreportvbams-access-2010
prayingmantes
prayingmantes

Reputation: 155

What event will fire each time a report is previewed/printed?

I would like to evauluate the value of a textbox report control and hide or display it based on its value, which I can achieve easily with VBA:

If Me.Fixed.Value = 0 Then
    Me.Fixed.Visible = False
End If

That works fine, but the query I am using as the report's record source allows a range of records to be printed all at once (1 per page/report), and I want the above code to run for each page/report. I am unsure of where to put the code so that each record will play by the rules. Currently, if I choose a range of 8 records, only the first one does what I want, and as I navigate through the other records in the print preview screen the format of the report is remaining unchanged when it should be changing.

I have tried the following events:

Report:

Section:

Where can I put my VBA so that each time I scroll through/navigate the range of records returned on that report my code runs?

Upvotes: 2

Views: 6358

Answers (2)

AdamsTips
AdamsTips

Reputation: 1776

Note that the Visible property is not available in the Detail_Paint() event, which is the event you need to use to have the conditional formatting apply in Report View. (Which might be required if you are trying to do something fancy such as simulated hyperlinks for a drill-down effect.)

A workaround is to set the ForeColor of the text box to equal the BackColor. Although the text is technically still there, it does not "show" on the displayed report, thus simulating a hidden field.

Private Sub Detail_Paint()
    ' Check for even numbers
    If (txtID Mod 2 = 0) Then
        txtID.ForeColor = vbBlack
    Else
        ' Set to back color to simulate hidden or transparent.
        ' (Assuming we are using a Normal BackStyle)
        txtID.ForeColor = txtID.BackColor
    End If
End Sub

Example Output:

Note hidden even numbers

Upvotes: 1

Andy G
Andy G

Reputation: 19367

You need to set the Visible property back to True as well, otherwise it will remain invisible.

I'm using the Format event of the Details section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Fixed = 0 Then
        Me.Fixed.Visible = False
    Else
        Me.Fixed.Visible = True
    End If
End Sub

This works in Print Preview but not in Report View. There is probably a way to get this to work with the Report View, but I never use this view.

The statement can be simplified:

Me.Fixed.Visible = Not (Me.Fixed = 0)

Upvotes: 4

Related Questions