Programatic spacing issue

[Guest]

This may take a minute to explain, so kindly bear with me. I'm using Office
XP.

I'm in charge of making sure Nursing students are up to date in their
documentation. I've already automated Access emailing these students when
their various documentation expires. What I'm trying to do now is produce a
report that will give me a hard copy for quickly looking up the state of
various students.

Specifically, I want the report to list students who are missing
documentation and then indicating just what documentation they need. In
order to save space (I'd like it on one page), I'm not leaving any blank
space for unusued fields. Output for each student will look like this:

Name: MMR
PPD
HepB

with the list containing only the elements needed. I've been doing this by
making seven controls on the detail area. Each of these controls is
initially set to Height=0 and placed at the same location. The code in the
Detail_Format routine determines if each control in turn is blank. If it is
not, the control is moved do beneath the last resized control and the height
is expanded. Afterwards, a box that visually unites the tags thus produced
is resized and the detail area is sized to the controls.

It works great in theory, but in practice, this is frequently producing lots
of extra space for no reason I can see.

Any help would be appreciated. Here is the code I'm using:

Option Compare Database
Option Explicit

Private ReportControls(0 To 6) As Control

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const BoxMargin As Long = 60
Const NewHeight As Long = 240

Dim NextTop As Long
Dim X As Integer

NextTop = BoxMargin
Detail.Height = 30000

For X = 0 To 6
With ReportControls(X) 'ReportControls Array is filled in
Report_Open for efficiency.
If .Value <> "" Then
.Top = NextTop
.Height = NewHeight
NextTop = NextTop + NewHeight
End If
End With
Next X

boxFrameValues.Height = NextTop + BoxMargin

Detail.Height = Max(boxFrameValues.Height, txtName.Height)
End Sub



Private Sub Report_Open(Cancel As Integer)
Set ReportControls(0) = txtMMR
Set ReportControls(1) = txtHepB
Set ReportControls(2) = txtTet
Set ReportControls(3) = txtLic
Set ReportControls(4) = txtCPR
Set ReportControls(5) = txtPPD
Set ReportControls(6) = txtPPDX
End Sub

 

[Marshall Barton]

This may take a minute to explain, so kindly bear with me. I'm using Office
XP.

I'm in charge of making sure Nursing students are up to date in their
documentation. I've already automated Access emailing these students when
their various documentation expires. What I'm trying to do now is produce a
report that will give me a hard copy for quickly looking up the state of
various students.

Specifically, I want the report to list students who are missing
documentation and then indicating just what documentation they need. In
order to save space (I'd like it on one page), I'm not leaving any blank
space for unusued fields. Output for each student will look like this:

Name: MMR
PPD
HepB

with the list containing only the elements needed. I've been doing this by
making seven controls on the detail area. Each of these controls is
initially set to Height=0 and placed at the same location. The code in the
Detail_Format routine determines if each control in turn is blank. If it is
not, the control is moved do beneath the last resized control and the height
is expanded. Afterwards, a box that visually unites the tags thus produced
is resized and the detail area is sized to the controls.

It works great in theory, but in practice, this is frequently producing lots
of extra space for no reason I can see.

Any help would be appreciated. Here is the code I'm using:

Option Compare Database
Option Explicit

Private ReportControls(0 To 6) As Control

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const BoxMargin As Long = 60
Const NewHeight As Long = 240

Dim NextTop As Long
Dim X As Integer

NextTop = BoxMargin
Detail.Height = 30000

For X = 0 To 6
With ReportControls(X) 'ReportControls Array is filled in
Report_Open for efficiency.
If .Value <> "" Then
.Top = NextTop
.Height = NewHeight
NextTop = NextTop + NewHeight
End If
End With
Next X

boxFrameValues.Height = NextTop + BoxMargin

Detail.Height = Max(boxFrameValues.Height, txtName.Height)
End Sub



Private Sub Report_Open(Cancel As Integer)
Set ReportControls(0) = txtMMR
Set ReportControls(1) = txtHepB
Set ReportControls(2) = txtTet
Set ReportControls(3) = txtLic
Set ReportControls(4) = txtCPR
Set ReportControls(5) = txtPPD
Set ReportControls(6) = txtPPDX
End Sub

I think the problem is that you are setting the detail
Height to a huge number. 30000 twips is almost 21 inches.

With what you've explained above, I don't see the need for
any of this. Why can't you just put all the text boxes down
the detail section and set their CanShrink property to Yes.
Be sure to set the detail section's Can Shrink to Yes as
well.

Do you realize that if you normalized your data by putting
the docs that have been processed in a separate table and
joining that to the students table the report would only
need one text box in the detail section with the student
data in a group header.

 

[Guest]

I think the problem is that you are setting the detail
Height to a huge number. 30000 twips is almost 21 inches.

*Almost* 21? That's my problem, it needs to be BIGGER!

Rather than spend time working out the maximum possible size I might need, I
just throw in a preposterously oversized number. That shouldn't be a concern
since it's set back to a reasonable size based on the other processing once
it's through setting the other elements up:

Detail.Height = Max(boxFrameValues.Height, txtName.Height)

Stepping through the program, the routine ends with Detail.Height having
been set correctly so that there would be no gaps.

Keep in mind that the extra spaces I'm inconsistently getting are on the
order of a quarter inch, not nearly two feet.

With what you've explained above, I don't see the need for
any of this. Why can't you just put all the text boxes down
the detail section and set their CanShrink property to Yes.
Be sure to set the detail section's Can Shrink to Yes as
well.

I initially tried setting setting the text box's heights to zero, but
placing them beneath each other, then letting "CanGrow" take care of the
situation. This worked well only when contiguous boxes were turned on,
otherwise, rather annoying gaps were left. Using CanShrink would be
eliminate that problem (wish I'd thought of it), but still leave this one:

CanGrow and CanShrink are decided after the Detail_Format event occurs. I
can't size the rectangle based on their heights at this point, since they
won't be shrunk or grown yet. I can't size it during the Detail_Print event
since it won't let me adjust heights of controls in that event. Since I have
to size it in Detail_Format, I need to handle the sizing of the boxes
manually.

Do you realize that if you normalized your data by putting
the docs that have been processed in a separate table and
joining that to the students table the report would only
need one text box in the detail section with the student
data in a group header.

As a matter of fact, they are in seperate tables (for no really good
reason), but I could do that with them in the same table. All I really need
to do is present a single text box with a very long (though not complicated)
formula in it uniting all of the various tags and logical test conditions. I
can dispense with the rectangle control and just turn the text box border on.
However, I didn't think of this until after I posted. (Give me a problem
and I can invariably find a horribly over-complicated solution.)

Thank you for your input! I still want to know why this particular bit of
code isn't working when, as far as I can tell, it should be.

 

[Marshall Barton]

*Almost* 21? That's my problem, it needs to be BIGGER!

Rather than spend time working out the maximum possible size I might need, I
just throw in a preposterously oversized number. That shouldn't be a concern
since it's set back to a reasonable size based on the other processing once
it's through setting the other elements up:

Detail.Height = Max(boxFrameValues.Height, txtName.Height)

Stepping through the program, the routine ends with Detail.Height having
been set correctly so that there would be no gaps.

Keep in mind that the extra spaces I'm inconsistently getting are on the
order of a quarter inch, not nearly two feet.


I initially tried setting setting the text box's heights to zero, but
placing them beneath each other, then letting "CanGrow" take care of the
situation. This worked well only when contiguous boxes were turned on,
otherwise, rather annoying gaps were left. Using CanShrink would be
eliminate that problem (wish I'd thought of it), but still leave this one:

CanGrow and CanShrink are decided after the Detail_Format event occurs. I
can't size the rectangle based on their heights at this point, since they
won't be shrunk or grown yet. I can't size it during the Detail_Print event
since it won't let me adjust heights of controls in that event. Since I have
to size it in Detail_Format, I need to handle the sizing of the boxes
manually.


As a matter of fact, they are in seperate tables (for no really good
reason), but I could do that with them in the same table. All I really need
to do is present a single text box with a very long (though not complicated)
formula in it uniting all of the various tags and logical test conditions. I
can dispense with the rectangle control and just turn the text box border on.
However, I didn't think of this until after I posted. (Give me a problem
and I can invariably find a horribly over-complicated solution.)

Thank you for your input! I still want to know why this particular bit of
code isn't working when, as far as I can tell, it should be.

If setting the detail Height is working for you, you must be
using AXP or later. That was not allowed in earlier
versions.

With that in mind, the only reason I can see why you would
be getting gaps is if you had some values that contain only
space characters. The If condition will fail implicitly for
Null values and explicitly for zero length strings, so the
only "blank" lines that can get past the If is a string of
blanks. Double check your data to make sure the users are
not typing one or more spaces instead of leaving it empty.
OTOH, you can guard against the spaces (and explicitly deal
with Null) by using:
If Trim(Nz(.Value, "")) <> "" Then

You can still use the Can Shrink property by getting rid of
the rectangle control and drawing the box using the Line
method in the Print event. Since the detail section's final
height is available there you can use:
Me.Line (0,0)-(Me.Width, Me.Height), vbBlack, B

OTOH, I kind of like the idea of using a single CanGrow text
box, but the text box's control source expression rapidly
becomes unmanagable. However, you can do the same thing in
code. First make all of the bound text boxes invisible (you
still need them to get their field values). The code would
look like:

Dim strList As String
For X = 0 To 6
With ReportControls(X)
If Trim(Nz(.Value, "")) <> "" Then
strList = strList & vbCrLf & .Value
End If
End With
Next X
Me.thebigtextbox = Mid(strList, 3)

 

[Guest]

If setting the detail Height is working for you, you must be
using AXP or later. That was not allowed in earlier
versions.

I am indeed!

With that in mind, the only reason I can see why you would
be getting gaps is if you had some values that contain only
space characters.

Most of the weird spacing issues turned out to be from another control
having been set to "CanGrow". Although I still don't understand everything
that was going on, I've given up on that system and gone with the One Big
Textbox approach.

You can still use the Can Shrink property by getting rid of
the rectangle control and drawing the box using the Line
method in the Print event. Since the detail section's final
height is available there you can use:
Me.Line (0,0)-(Me.Width, Me.Height), vbBlack, B

Now that's a useful idea!

I didn't realize the old BASIC graphic commands had been carried forward to
VBA. And still with the old non-standard syntax! Ooo, are Data, Read &
Restore still viable? What about line numbers? *checks help* No joy on
Data/Read, but I think I'll start using line numbers just because I can.