Access Reports - Hiding Fields, Labels, and other elements based on data

Question

I've been trying to format a report to provide a listing for prospectives vendors that shows a piece of equipment's model number, serial number, range, asset number, and calibration frequency. The concept is based on that this proposal, if accepted, would later become a purchase order - so fields such as if the work is to be performed in house are provided, as well as when the proposal was created, when do we propose to send the items out, and lastly, if there is a valid purchase order number already assigned to the whole mess yet.

I've been trying to have a field that changes value based on if the value of a Yes/No checkbox in the underlying query is True or False. However, I cannot get any of the standard property changes to work in a report - it doesn't throw an error, it just does nothing. I've tried inserting my code into the On Format event, as well as On Load event on the respective form - but the code will simply not run.

Does these kinds of property manipulations based on underlying query field values not work on reports like they do on forms?

Answer 1

When formating a report, you probably want to put your code on the "onPrint" event. It is triggered when "printed" to your screen.

Answer 2

Make an unbound textbox, in the Control Source property put in a formula something like this:

=IIF([BooleanField],"Value if True","Value if false")

Answer 3

I think it would be best to assign the value(s) in the query, rather than the report.

A little sample code:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me.YN Then
    ''Label in the detail section
    Me.Label_YN.Visible = False
    ''Unbound field
    Me.txtField1 = "abc"
Else
    Me.Label_YN.Visible = True
    Me.txtField1 = "def"
End If
End Sub

Answer 4

This goes a bit beyond your specific question but may be a useful alternative approach from a report design viewpoint.

You may wish to put the "in-house" data into a separate sub-report that is linked to the main report (say, on a master work order number). The data for the sub-report comes from a new table that holds the in-house data including the master work order number). You then connect the master table data to the new table with the master work order number.

If the master report has no link to the sub-report there should be no displayed data (there shouldn't be any code needed). As needed, you can set the master report detail and the sub-report "can shrink" properties to yes if you want close up the white space for empty sub-report.

The advantage of this approach is that the new table could have multiple records for a given master work order number, say for different executing departments. Also, the sub-report design can be modified separately without affecting the master report (aside from size).