How to hide detail if there's no data

[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.

 

[Allen Browne]

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.

 

[moondaddy]

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?

 

[MikeC]

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.

 

[moondaddy]

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.

 

[david epsom dot com dot au]

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)