How to squeeze out vertical space in a report section

  • Thread starter Thread starter ccw
  • Start date Start date
C

ccw

MSAccess 2003
This sounds so simple - what am I doing wrong?

My simple report has several fields arranged vertically in the detail
section.
Some of the fields may have null or zero-length string values.
I have the Can Shrink property of each control set to "Yes".
In the code for the Detail_Format event, I have the following type of logic,
replicated for each control:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.Controls("Description").Visible = True

If Me.Controls("Background").Text = "" Then
Me.Controls("Background").Visible = False
End If

'Same thing for next control....
'Same thing for next control....

End Sub

(I am writing the code because the usual property settings do not appear to
workin this case, for this data, which sounds strange.)

In the debugger I can see that the Visible property is set to False when the
text value of the control is "".

WHAT I DO NOT GET IS why the print preview still shows vertical space where
there is a shrinkable invisible control???

Would a non-printable character in the field cause peculiar behavior? Would
it test equal to "" if it were a linefeed, CR, etc? I have cannot detect any
character at all, len(control.text) returns 0. In the debugger, the
control's value, as a variant, is null.

Thanks in advance. -CCW
 
Did you set the Details Can Shrink property to Yes?

Checking for a zero-length string won't do anything to control whose value
is Null. I would use the following construct

Me.Description.Visible = Len(Me.Description & "") <> 0
Me.BackGround.Visible = Len(Me.Background & "") <> 0
Me.AnotherControl.Visible = Len(Me.AnotherControl & "") <> 0

Also, you should be checking the Value property of the control, not the Text
property of the control. The text value of control is only available when
the control has the focus and in a report the text value is almost useless.

Me.Description uses the default property of the control - which is Value.
Also if you have LABELS associated with the controls, you are going to need
to set the visible property of the associated labels

Me.LabelAnotherControl.Visible = Len(Me.AnotherControl & "") <> 0

OR
Me.AnotherControl.Visible = Len(Me.AnotherControl & "") <> 0
Me.LabelAnotherControl.Visible = Me.AnotherControl.Visible

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John, Thank much for the suggestions. I think I have tried everything you
mention, but still have the extra white space. It doesn't seem like I should
have to write code for this (shrinking) to work? I have the same exact
issue, I believe on another report as well.

I'm not familiar with the protocol here, but would you be interested in
seeing this on my screen ( I use GoToMyPC), sometime after 4:00 today?

Thank you,
Christopher Woodard
ActioNet, Inc
Contracted to HHS/CMS
 
No, I cannot do that.

Is it possible for you to copy the report and the datastructure of the
tables used to a small database and zip that up and email it to
Spe@ncer@chpdm dot umbc dot dot dot edu ?

Clean that address up by removing the first @ and change dot dot dot to just
one dot.

If you do that I will try to look at the report and see if I can spot the
problem.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Yes, I think so. I will do that later this morning...
Thanks much for the assistance.
-Chris
Christopher.Woodard@@@cms.hhs.gov
 
Solution?:

I rewrote the VBA to read:

Me.Description.Visible = Len(Me.Description & "") > 0
Me.DescriptionLabel.Visible = Me.Description.Visible

Me.Background.Visible = Len(Me.Background & "") > 0
Me.Controls("Background").Visible = True

Me.CorrectiveAction.Visible = Len(Me.CorrectiveAction & "") > 0

Me.Source.Visible = Len(Me.Source & "") > 0
Me.Label112.Visible = Me.Source.Visible

Me.StartDate.Visible = Len(Me.StartDate & "") > 0
Me.Label120.Visible = Me.StartDate.Visible

Me.EndDate.Visible = Len(Me.EndDate & "") > 0
Me.Label116.Visible = Me.EndDate.Visible

Me.Contact.Visible = Len(Me.Contact & "") = 0
Me.Label118.Visible = Me.Contact.Visible

Me.Controls("AdministrativeDataLabel").Visible = True
Me.Controls("AdministrativeDataLabel").Visible = Me.StartDate.Visible Or
Me.EndDate.Visible

'========= Code did read as follows ============
Me.Description.Visible = Len(Me.Description & "") > 0
Me.DescriptionLabel.Visible = Me.Description.Visible

Me.Background.Visible = Len(Me.Background & "") > 0
Me.Controls("Background").Visible = True

Me.CorrectiveAction.Visible = Len(Me.CorrectiveAction & "") > 0

Me.Source.Visible = Len(Me.Source & "") > 0
Me.Label112.Visible = Me.Source.Visible

Me.StartDate.Visible = Len(Me.StartDate & "") > 0
Me.Label120.Visible = Me.StartDate.Visible

Me.EndDate.Visible = Len(Me.EndDate & "") > 0
Me.Label116.Visible = Me.EndDate.Visible

Me.Contact.Visible = Len(Me.Contact & "") = 0
Me.Label118.Visible = Me.Contact.Visible

Me.Controls("AdministrativeDataLabel").Visible = True
Me.Controls("AdministrativeDataLabel").Visible = Me.StartDate.Visible Or
Me.EndDate.Visible


Me.Controls("Description").Visible = True
Me.Controls("Background").Visible = True
Me.Controls("CorrectiveAction").Visible = True
Me.Controls("Source").Visible = True

Me.Controls("AdministrativeDataLabel").Visible = True

Me.Controls("StartDate").Visible = True
Me.Controls("Label120").Visible = True

Me.Controls("EndDate").Visible = True
Me.Controls("Label116").Visible = True

Me.Controls("Contact").Visible = True

If Me.Controls("Description").Text = "" Then
Me.Controls("Description").Visible = False
End If

If Me.Controls("Background").Text = "" Then
Me.Controls("Background").Visible = False
End If

If Len(Me.Controls("CorrectiveAction").Value & "") = 0 Then
Me.Controls("CorrectiveAction").Visible = False
End If

If Len(Me.Controls("StartDate").Value & "") = 0 And _
Len(Me.Controls("EndDate").Value & "") = 0 Then
Me.Controls("AdministrativeDataLabel").Visible = False
End If

If Len(Me.Controls("StartDate").Value & "") = 0 Then
Me.Controls("StartDate").Visible = False
Me.Controls("Label120").Visible = False
End If

If Len(Me.Controls("EndDate").Value & "") = 0 Then
Me.Controls("EndDate").Visible = False
Me.Controls("Label116").Visible = False
End If

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Turns out that was not a solution. There is still a blank line printed in
certain circumstances.

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