Hide detail if only one record

[Neal]

I am trying to have a report NOT show a detail if there is only one record in
a group. I would like to only see the group total since there is only one
record. My grouping works fine, but a single record group is taking up too
much room on the report. Does anyone have any idea how to accomplish this?

 

[Duane Hookom]

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup > 1
End Sub

 

[AccessMan]

I am trying to get this solution to work, but have not yet been successful.
I am trying to hide a Detail section, and the count keeps returning 1 whether
there are 0, 1, or more records in the detail section. Any idea what I might
be doing wrong?

 

[John Spencer]

The control needs to be added to the detail section not the group header or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[AccessMan]

Thanks John and Duane, that works. I have a wrinkle that I'd like to apply
though. I'd like the detail section to not appear at all if there are no
related records. Unfortunately, txtGroupCount yields a value of 1 even when
there are no related records. Setting 'Can Shrink' to Yes doesn't work.

 

[John Spencer]

That makes no sense to me. If there are no records, then there would be no
detail section at all and also there should be no group. Obviously I don't
understand something about your report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[AccessMan]

Sorry. For the sake of discussion, let's say that I have a set of tables for
tracking married couples' names and the children they have, if any. I use a
query with a left outer join to select ALL married couples and whatever
children they have. For those married couples that have no children, the
query returns non-null values for the married couple names fields and null
values for the children's names fields. I use a report with one grouping
field to display the married couple data, and the detail section for the
children data. As things stand now, I get the field labels and null values
for the children when the couple has no children. I'd like to fully suppress
the detail section when there are no children.

 

[John Spencer]

If there is one record then the lines below should hide the detail
section completely.

Me.Detail.Visible = (Me.txtCountGroup > 1)

If you want to hide labels in the group header, then you need something
like the following in the format event of the group.

Me.ChildNameLabel.Visible = (Len(Trim(Me.ChildName & "")) > 0)
Me.ChildDOBLabel.Visible = ((Len(Trim(Me.ChildDOB & "")) > 0)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[AccessMan]

This did not work when I tried it a few days ago. I displayed txtCountGroup
as part of debugging, and its value was 1 whenever there were null records
for display. Aside from that, the displayed value reflected the true number
of records for display in the detail section. I will create a new, simple
report and repeat the solution attempt, and I'll report back.

Thanks!

 

[AccessMan]

I got the same results with a new, bare bones set of database objects and
data. The counter returns a value of 1 when there is no data to be shown in
the detail section of the report.

 

[AccessMan]

Solution found!

I used

Me.Detail.Visible = Not (IsNull(Child))

with the OnFormat event of the Detail section. Child is a field that is
displayed in the detail section.

Unless anybody sees a potential problem with this, I thank you all very much
for your help!