The Never shrinking whitespace problem 1

[lameid]

I have a report that has multiple text boxes with a little bit of white space between them.

The values are set programmatically via code.

In some cases a few text boxes at the bottom will not have any values. The can shrink on all of these is set to yes as well as the section. The problem is in some instances of the empty controls, the white space is causing another blank page as white space between controls does not shrink.

Unfortunately the white space in between is smaller than an 8 point extra line so I can't just combine the two values with an extra line inserted. These are rich text so I would either use HTML or use the vertical tab chr(11) depending on the contents.

Are there any other creative ways to deal with this situation that I may be overlooking?

 

[dhookom]

Just a shot in the dark but if the text boxes are by themselves at the bottom of the detail section, could you create another "detail" section by grouping on the primary/unique field in the record source? Show the group footer for this new grouping and use it as an extension of the detail section. You can then use code cancel its printing if the value is empty.

I haven't tried this but it could just ignore the entire extra detail sections.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

I have never cancelled the printing of a section before. That seems like a viable solution, I added sections frequently to set keep together so the last page does not end up too empty. I will just have to lookup the cancelling. I think the problem was made to go away by other manipulation.

In a totally unrelated issue, something that has me more hung up is the below thread. Another set of eyes would be great. I'm hoping I did something stupid rather than it is being difficult. Sometimes I wish this forum had a feature to tag others to look at threads. Maybe so many up votes to be able to do it.

thread705-1784944

 

[dhookom]

I look at most of the Access/SQL/Office threads and will contribute if:
[ul]
[li]I have some knowledge that others haven't contributed yet[/li]
[li]I don't find the OP too tight with providing details[/li]
[li]I have the time[/li]
[li]Someone else is plowing through the details with the OP[/li]
[/ul]
Regarding the other thread I'm not sure I have the knowledge or time to figure out the issue.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

I was hopeful maybe you had hit that one... I really want PHV for that one

Back to the issue at hand, I am not finding cancelling printing of the section but I notice that both the format and print events for the section have a cancel parameter. Is it as easy as setting that to true? That seems vaguely familiar like I read it here.

MSDN is all jacked up not separating Form and report Section and the members of each right now, feedback left... The events are totally missing. MS: "We'll make it better by moving it to the web and removing the existing documentation that was in the offline help."

I think there is an offline help or was for a previous version... I'm just not allowed to download it here if it exists.

 

[dhookom]

You can set Cancel=True in the Format event if you don't want to see the section. Try it ;-)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

I believe you just waiting to try it out.
Something like the below may work well if I assume only text boxes have data...

  Dim cntl as Control
  Dim lngValueCount as long

  lngValueCount = 0

  For Each cntl In Me.Section("ThisSectionName").Controls
    If cntl.controltype = 109 Then 'acTextBox 
      If cntl.value <> "" Then
        lngValueCount = lngValueCount + 1
      End If 
    End if
  Next cntl

  If lngValueCount = 0 Then
    Cancel = True
  End if

Another thought I just had is to make another textbox the height of the intended white space in the white space... Set can
grow to false and can shrink to true. Set the fore color to white etc. so it is not visible.

Then make the control source something like =IIF([txtExample] <> "", "White Space,"")

That way if there is a value in the control there will be short invisible text to provide whitespace otherwise it will shrink away.

Caveat with my way being it would still print white on a color printer. But on the pro side, it is so much less hassle to copy and paste a control in and only modify the control source for each companion textbox.

The cancel though... I'm going to use that for sure... The below will be so much shorter.

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
  Dim lngPage As Long
  Dim cntl As Control
  lngPage = Me.Page

  'Hide Footer on Page 2
  For Each cntl In Me.Section(acPageFooter).Controls
    cntl.Visible = Not (lngPage = 2) 
  Next cntl
  
End Sub