How to hide detail if there's no data

  • Thread starter Thread starter moondaddy
  • Start date Start date
M

moondaddy

Im writing a report in Access 2003 and would like to hide the detail when
there's no data in it. And if possible, I would like to reduce the height
of the header just above it. Is any of this possible and if so, how?

Thanks.
 
Use the NoData event of the report to set the Visible property of the Detail
section to No, and the Height of the Report Header section.

Notes:
1. Me.Section(acDetail) does have a Visible property, even though it is not
enumerated.

2. The Height is measured in twips, where 1440 twips = 1 inch.

3. Use the Report Header section or a group header. You may not be able to
resize the Page Header section.
 
Thanks, but the nodata event works when there's no data for the report. In
my case, the detail section will list multiple records in a parent/child
relationship and the parents data will be in a header above the detail.
some parent records wont have child records so I want to get rid of the
wasted space in the detail section when there's no data in that particular
row. I tried the detail_format event, but it fires before data is loaded
into the detail section so I cant test to see if there's data or not.
There's no Afterformat event which would probable work for me if it existed.
I know Crystal reports has a number of event you can use but Access seems to
be short. Can you give another recommendation?
 
Moondaddy,

Below are two similar methods you can use the utilize the report's Activate
event.

If you only need to check whether a *single* control is null, then you can
do this:

If IsNull(Me!txtID) Then
MsgBox "ID control is null. Making detail section invisible."
Me.Section(acDetail).Visible = False
End If

Alternatively, if you need to check whether all controls (of the below
specified types) are null, then you can do this:

Dim i As Integer
Dim blnAllNull As Boolean

For i = 0 To Me.Section(acDetail).Controls.Count - 1
With Me.Section(acDetail).Controls(i)
If (.ControlType = acCheckBox Or .ControlType = acComboBox _
Or .ControlType = acListBox Or .ControlType = acTextBox)
Then
If IsNull(.Value) Then
blnAllNull = True
Else
blnAllNull = False
Exit For
End If
End If
End With
Next

If blnAllNull Then
MsgBox "All detail controls are null. Making detail section
invisible."
Me.Section(acDetail).Visible = Not blnAllNull
End If


....and of course, you can remove the message boxes at your convenience.
 
Got it. Set all the controls in the detail section to Can Shrink=yes and
the same for the detail section. When the detail section shrink because
there's no data in it, I also want the labels for it in the header just
above to go away too so I converted the labels to textboxes and put in a
function like this. Note: LsNumer is a textbox in the detail section:

=IIf(Count([LsNumber])=0,"","Ls Number")
Now each label in this group was converted to a textbox and the will all
have a value of "" when there's no detail section. Now set each textbox Can
Shrink = yes and the space where they were will also disappear.

wala.
 
Also, you can use PrintSection, MoveLayout and NextRecord
in the format event of each section. (PrintSection only
hides the text, MoveLayout suppresses the white space:
NextRecord skips a record).


Private Sub Section_Line1_Format(Cancel As Integer, FormatCount As Integer)
If mFlg Then PrintSection = False
If mFlg Then MoveLayout = False
End Sub

These 'properties' are also important when you want to hide
a header section (because of a bug using .visible), but the main
purpose is to hide or move individual records.

(david)
 
Back
Top