I have finally worked out a solution to this. I hope there is not a rule on line limits. If so, I will gladly edit my post to attach the code as a file instead.
Effectively, my approach was not to hide the controls, but rather count how many lines would go into those controls if I did show the administrative information, and replace those lines with dummy blank lines. Below is a simplified version of the code I used in my Detail.OnFormat event. Note, txtId, txtAdminNotes, and txtAdminNotesLabel are all textboxes on my form. All administrative controls are set with Visible, CanGrow, and CanShrink equal to True.
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim sql As String
Dim rs As DAO.Recordset
Dim adminNotes As String
sql = "SELECT * " & _
" FROM [My_Table] " & _
" WHERE [Id] = " & txtId.value
Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot, dbSeeChanges)
If Not rs.BOF Or Not rs.EOF Then
rs.MoveFirst
'Get special administrative notes.
adminNotes = Nz(rs!STIP_Comments_Internal, vbNullString)
'Add special administrative information (or blank lines) to the report.
HandleAdministrativeTextBox adminNotes, txtAdminNotes, "Admin. Notes:", txtAdminNotesLabel
End If
End Sub
Below you will find the helper subs that I used that actually do the majority of the work. I broke them out into reusable subs because there is more than one set of controls for which I used this same logic.
Code:
Private Sub HandleAdministrativeTextBox(ByRef textVal As String, _
ByRef txtBox As TextBox, _
labelText As String, _
txtBoxLabel As TextBox)
Dim lineCount As Long
textVal = BreakTextToFitTextBox(textVal, txtBox, lineCount)
If isAdministrative Then
'Add administrative information to the report.
txtBox.value = textVal
txtBoxLabel.value = IIf(Len(textVal), labelText, vbNullString)
Else
If Len(textVal) Then
'Add blank lines to the report.
If lineCount <> 1 Then
txtBox.value = GenerateBlankLines(lineCount)
Else
'Single blank line. (Trivial; avoid unnecessary function call)
txtBox.value = " "
End If
Else
txtBox.value = vbNullString
End If
End If
End Sub
Private Function GenerateBlankLines(numLines As Long) As String
Dim blankLines As String
Dim index As Long
For index = 1 To numLines
blankLines = blankLines & IIf(index <> 1, vbCrLf, vbNullString) & " "
Next
GenerateBlankLines = blankLines
End Function
Private Function BreakTextToFitTextBox(ByRef textVal As String, _
ByRef txtBox As TextBox, _
Optional ByRef numLines As Long = 0) As String
Dim brokenTextVal As String
Dim lineCount As Long
If InStrB(textVal, " ") Then
Dim words() As String
Dim wordIndex As Long
Dim currentWord As String
Dim lines() As String
Dim currentLine As String
Dim testLine As String
'Sync the formatting between the report and the textbox
'since the report is the object that measures the text.
Me.FontName = txtBox.FontName
Me.FontSize = txtBox.FontSize
Me.FontBold = txtBox.FontBold
Me.FontItalic = txtBox.FontItalic
'Split the text on spaces.
words = Split(textVal, " ")
lineCount = 0
ReDim lines(lineCount) As String
'Loop through the words (or otherwise cluster of characters) one by one.
For wordIndex = LBound(words) To UBound(words)
currentWord = words(wordIndex)
'Test if the word can fit in the textbox with the current line.
'NOTE: The "+ 100" that you see below is a hack. For some reason,
' the measurement didn't work perfectly. This seemed to work
' in most cases, but there is probably a better solution
' somewhere out there to be discovered.
testLine = IIf(Len(currentLine), currentLine & " ", vbNullString) & currentWord
If txtBox.Width > Me.TextWidth(testLine) + 100 Then
'It fit. Add the word to the line and move on.
currentLine = testLine
Else
'It didn't fit. Start a new line with the current word.
lineCount = lineCount + 1
ReDim Preserve lines(lineCount) As String
lines(lineCount - 1) = currentLine
currentLine = currentWord
End If
Next
'Cleanup: Make sure to add the last line to the lines array.
If Len(currentLine) Then
lineCount = lineCount + 1
ReDim Preserve lines(lineCount) As String
lines(lineCount - 1) = currentLine
End If
'Concatenate the lines array with vbCrLf.
brokenTextVal = Join(lines, vbCrLf)
Else
'No spaces to split the text on.
lineCount = IIf(Len(textVal), 1, 0)
brokenTextVal = textVal
End If
numLines = lineCount
BreakTextToFitTextBox = brokenTextVal
End Function