Hide detail if only one record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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?
 
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
..
 
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.
 
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
..
 
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.
 
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
'====================================================
 
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!
 
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.
 
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!
 
Back
Top