Formatting Report by VBA

  • Thread starter Thread starter Remco
  • Start date Start date
R

Remco

To all,

I am trying to make a report showing the input for one record. The
record contains 31 fields relevant for the report. However, some of
the fields might be empty and therefore need not to be shown. In order
to have a decent lay out (the report is exported into Word) I am
trying to do the following.

If Me.ComplyGuidelines = "No" Then
Me.Label11.Visible = True
Me.Deviation.Visible = True
Else
Me.Label11.Visible = False
Me.Deviation.Visible = False
Me.Label11.Height = 0
Me.Deviation.Height = 0
TopValue = Me.Label10.Top
TopValue = (TopValue + 0.61) '** 0.61 is the height of Label10 +
margin
Me.Label12.Top = TopValue
Me.Rotterdam_CC.Top = TopValue
End If

I have two questions regarding this problem:
1. For some reason the value of 0.61 is not added to 'TopValue',
therefore Label12 is shown on top of Label10. Are calculations not
possible in VBA in reports?
2. Is this the way to go, or is there a much easier manner of creating
a properly formatted report?

TIA,
Remco
 
Remco said:
I am trying to make a report showing the input for one record. The
record contains 31 fields relevant for the report. However, some of
the fields might be empty and therefore need not to be shown. In order
to have a decent lay out (the report is exported into Word) I am
trying to do the following.

If Me.ComplyGuidelines = "No" Then
Me.Label11.Visible = True
Me.Deviation.Visible = True
Else
Me.Label11.Visible = False
Me.Deviation.Visible = False
Me.Label11.Height = 0
Me.Deviation.Height = 0
TopValue = Me.Label10.Top
TopValue = (TopValue + 0.61) '** 0.61 is the height of Label10 +
margin
Me.Label12.Top = TopValue
Me.Rotterdam_CC.Top = TopValue
End If

I have two questions regarding this problem:
1. For some reason the value of 0.61 is not added to 'TopValue',
therefore Label12 is shown on top of Label10. Are calculations not
possible in VBA in reports?
2. Is this the way to go, or is there a much easier manner of creating
a properly formatted report?
Click to expand...


Several things for you to incorporate into your thinking:

1. The units of measurements in VBA is twips (1440 per
inch). So if you're trying to set the top of label12 to .61
inches below label10, then you would use:
TopValue = (TopValue + .61 * 1440)

2. If you move a control around for one record, you must
set its position for all records. Once you set the Top
property, it stays set until you change it again.

3. If a label control is attached to a text box, setting
the text box to be invisible will also make the attached
label invisible. I don't any reason for your code to set a
label's Height to 0.

4. If the label and text box are the only controls in a
horizontal band, you can use the CanShrink property to
reclaim the space they would normally consume. A text box
with a value of either Null or a zero length string ("")
will shrink. An invisible text box (and its attached label)
will also shrink.

As best I can tell, the only thing you need to do is set the
text box's Visible property and use the CanShrink property.
I just don't see where you need to do all those other
manipulations.
 
Dear Marshall,

Thanks for your reply - you certainly did make me think...


Several things for you to incorporate into your thinking:

1. The units of measurements in VBA is twips (1440 per
inch).
Click to expand...
I did not think of that one!
2. If you move a control around for one record, you must
set its position for all records. Once you set the Top
property, it stays set until you change it again.
Click to expand...
I am only showing one record per Report, so this problem is not really
an issue for me.
4. If the label and text box are the only controls in a
horizontal band, you can use the CanShrink property to
reclaim the space they would normally consume.
Click to expand...
In my case, they are the only controls in the horizontal band.
A text box with a value of either Null or a zero length string ("")
will shrink.
Click to expand...
I played around and noticed a textbox showing a Null value will reduce
to one line - the label remains visible. I want it to go invisible as
well.
As best I can tell, the only thing you need to do is set the
text box's Visible property and use the CanShrink property.
I just don't see where you need to do all those other
manipulations.
Click to expand...
Those manipulations, especially setting height and top of labels (and
textboxes) is due to line spacing. Imagine you have 3 labels above
each other. The first is Label1, below that one Label2 and finally
below Label2, you have Label3. Between these labels, you have a margin
of 0.2 inch. Now if you make Label 2 invisible, the margin between
label1 and label3 is 0.4 inch. I want Label 3 to appear on the
position of Label 2 when label2 is not shown - resulting in a 0.2 inch
margin.

I hope I managed to get my problem across, and would be delighted if
you know a solution to it.

Thanks,
Remco
 
Back
Top