Show Field if Check Box is Yes (1 Viewer)

[wmix]

I've never had to do this before and my brain is not processing how to get this to work.

I have a query that pulls information based on criteria from several tables. Then I have a simple report that populates a list of information from the query.

Now I have to change the report slightly and only populate specific information based on a new Yes/No check box that was added to a table. So I did a copy of the original report (because I still need the original) and am trying to figure out how to filter for the new report.

I cannot for the life of me wrap my brain around how to get this to work. Is it possible to write something to the way of....

If MachineDPA is True then Show this record on the report.

I was speaking with another Access User that is about as skilled as I am (pretty basic) and they thought I needed to create a brand new query design and use that as my source. The problem is I have a lot of different reports like this that are going to change based on one item or another (not all check boxes). So I was thinking if I could figure out a way to do one of them then I could give a stab at the rest of them.

Thank you for your help.

 

[June7]

Maybe use WHERE condition of OpenReport.

DoCmd.OpenReport "report name", , , "MachineDPA = True"

I don't use dynamic parameters in queries if I can avoid it.

Review http://allenbrowne.com/ser-62.html

 

[MarkK]

In the Format event of the report section in question, you can set the visibility of the entire section. This is a common pattern to selectively show or hide individual records in a report based on criteria you calculate in VBA.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Detail.Visible = Me.MachineDPA
End Sub

In this code example the visibility of the Detail section of the report varies directly with the value of the MachineDPA field.

 

[June7]

Why not just apply filter to only retrieve desired records? This can easily be dynamic with VBA to build filter criteria and apply to OpenReport method.
Making Detail_Format event dynamic for different criteria would be trickier. My guess is it would also perform slower.

 

[wmix]

In the Format event of the report section in question, you can set the visibility of the entire section. This is a common pattern to selectively show or hide individual records in a report based on criteria you calculate in VBA.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Detail.Visible = Me.MachineDPA
End Sub

In this code example the visibility of the Detail section of the report varies directly with the value of the MachineDPA field.

Thank you Markk, this worked perfect.

 

[wmix]

Why not just apply filter to only retrieve desired records? This can easily be dynamic with VBA to build filter criteria and apply to OpenReport method.
Making Detail_Format event dynamic for different criteria would be trickier. My guess is it would also perform slower.

Thank you. I'm just learning how to use forms to filter queries and generate the reports I need. I will certainly look into it.

 

[MarkK]

Why not just apply filter to only retrieve desired records?

Data in a hidden section is still processed by the report. A Sum() function in a footer, for instance, still sums data in hidden Detail sections. Excluding a row from a report has a distinctly different outcome than hiding it. Both are common patterns that produce different results.

 

[Pat Hartman]

Don't confuse hiding a field with hiding a record.

You really need to use the WHERE argument of the OpenReport method to eliminate the records you don't want to show. As @MarkK pointed out, hiding the record doesn't mean that it isn't there. It is still included in sums and counts.

 

[spaLOGICng]

Maybe use WHERE condition of OpenReport.

DoCmd.OpenReport "report name", , , "MachineDPA = True"

I don't use dynamic parameters in queries if I can avoid it.

Review http://allenbrowne.com/ser-62.html

Or apply the True Filter Criteria in the Query itself.