Do not show grouping if no value. (1 Viewer)

[mdnuts]

Here is the source query for my report.
qryFamiliesWEnhancements

Title	Impact	Control_main	Enhancement
Wireless Access	Low, Moderate, High	AC-18
Wireless Access | Authentication And Encryption	Moderate, High	AC-18	(1)
Access Control For Mobile Devices	Low, Moderate, High	AC-19
Access Control For Mobile Devices | Full Device/ Container-based Encryption	Moderate, High	AC-19	(5)
Use Of External Information Systems	Low, Moderate, High	AC-20
Use Of External Information Systems | Limits On Authorized Use	Moderate, High	AC-20	(1)
Use Of External Information Systems | Portable Storage Devices	Moderate, High	AC-20	(2)
Information Sharing	Moderate, High	AC-21
Publicly Accessible Content	Low, Moderate, High	AC-22

I have my Access Report first grouping on Control Main with additional field of Title. By itself, this displays all Control_main fields and their corresponding Title field.

I then need to group all Enhancements under the Control_main. It should look like this.

AC-18	Wireless Access
	Enhancements
	(1)	Wireless Access | Authentication and Encryption
AC-19	Access Control for Mobile Devices
AC-20	Use of External Information Systems
	Enhancements
	(1)	Use of External Information Systems | Limits on Authorized Use
	(2)	Use of External Information Systems | Portable Storage

I can get it to group like I want by secondly grouping on "Enhancement", however in the Access Report it will always show the first line for each Control_main with a blank value for Enhancement, it looks like this.

AC-18	Wireless Access
	Enhancements
		Wireless Access
	(1)	Wireless Access | Authentication and Encryption
AC-19	Access Control for Mobile Devices
	Enhancements
		Access Control for Mobile Devices
AC-20	Use of External Information Systems
	Enhancements
		Use of External Information Systems
	(1)	Use of External Information Systems | Limits on Authorized Use
	(2)	Use of External Information Systems | Portable Storage

I tried setting the On Format for GroupHeader3 with

  If Me.Enhancement = "" Then
    Me.Enhancement.Visible = False
    Me.Text9.Visible = False
  Else
    Me.Enhancement.Visible = False
    Me.Text9.Visible = False
  End If

I also tried the same variation with if IsNull(Me.Enhancement) Then but none of those work.

Any ideas?

 

[mdnuts]

Interesting, I put this in the on Print event and it works. Is there not one that would work for both print and screen?

Private Sub GroupHeader3_print()
  If IsNull(Me!Enhancement) Then
    Me!Enhancement.Visible = False
    Me!Text9.Visible = False
  Else
    Me!Enhancement.Visible = True
    Me!Text9.Visible = True
  End If
End Sub

 

[pbaldy]

I'm guessing you're using Report view. The format event would work for Preview or Print views. Drove me crazy when they first came out with Report view.

 

[mdnuts]

that would explain it I guess. I'm sure there's some reason for it, not one i can think of though haha.

thank you.

 

[pbaldy]

No problem. I assume there's reason internally, but there are events that fire for Report view but not Preview, and vice versa.

 

[mdnuts]

i'm running into another issue with sorting & grouping on a report. in report view it correctly orders and groups - I put it into print view and it doesn't.

frustrating.

 

[pbaldy]

I've only used Report view a couple of times. What is the issue exactly? Can you attach the db here?

 

[mdnuts]

attached, just close the form that shows automatically then open rptRSCDGuidance. Enter "moderate" when it prompts you.

I think what's going on is, I have txtControl set to not show duplicates and that makes it work in report view. For some reason that doesn't carry over onto print view.

it should show like

AC-2 <title>
Supplemental RSCD Guidance:

(1) <title>
Supplemental RSCD Guidance

instead of

AC-2 <title>
Supplemental RSCD Guidance:

AC-2 <title>
Supplemental RSCD Guidance:
(1) <title>
Supplemental RSCD Guidance

I'm thinking i'll need to do a sub-report to let it handle it correctly.

 

[mdnuts]

a subreport did work. Additionally I set the On No Data event to cancel=True to suppress the subreport if no data was to be found.

 

[pbaldy]

Sorry, when I first looked there was no attachment. Glad you got it sorted.