Growing the subform?

[Lostguy]

Hello! I have a continuous subform on a mainform. The subform is sized
to show 3 records, but there could be one or 100 records there. If it
is more than 3, I type in the fourth, scroll down, type in the 5th,
scroll down, etc.

The subform is the in the detail section of the mainform.
The Can Grow/Can Shrink properties of the detail section of the
mainform are set to Yes.
The Can Grow/Can Shrink properties of the subform control itself and
the detail section of the subform are set to Yes.

Is there some setup that I am missing or a macro which grows your
subform when there is alot of records and shrinks it when there is
only one or none?

VR/Lost

 

[John Spencer]

Just FYI.

The Can grow and Can shrink properties only apply when you are PRINTING a
form. They have nothing to do with how a form appears when you are using it to
view and enter data.

It can be done with VBA code. I've done similar things with a continuous
subform. In my case the subform grows to fill the available space and is not
controlled by the number of records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Lostguy]

John,

Thanks! Good info, as usual.

I Googled this "subform growing" and "new entry at the top of the
subform vice the bottom" stuff before I posted, and one of the
questions that always comes up is "What is your Can Grow/Shrink
settings?". I never print forms (just reports), so that is good info.

BTW, where would be a good place to see your code?

VR/Lost

 

[John Spencer]

Here is a copy of the code that I use in very limited circumstances. My
subform is always the bottom most control on the form and is in the detail
section. I may have a more current version, but I can't locate it.

Public Sub GrowSubForm(sfrmControl As Control)
'===============================================================================
' Procedure : GrowSubForm
' Created : 9/15/2005 12:42
' Author : John Spencer
' Purpose : Expands/Shrinks the detail section and the subform to conform to
' the available size of the detail section of the Parent form window
' There should not be any controls below the subform
'===============================================================================

Dim lngWindowHigh As Long, lngHeaderHigh As Long, lngFooterHigh As Long
Dim lngMargin As Long
Dim frmAny As Form

Set frmAny = sfrmControl.Parent

On Error GoTo ERROR_GrowSubForm
'---------------------------------------------------------------
' Code Change Needed: Should check for the existence of the
' form header and footer sections and the visibility of the sections.
' Also check for page header and page footers (and visibility)
'---------------------------------------------------------------

lngWindowHigh = frmAny.InsideHeight
lngHeaderHigh = frmAny.Section(acHeader).Height
lngFooterHigh = frmAny.Section(acFooter).Height
lngMargin = frmAny.Section(acDetail).Height - sfrmControl.Height

'Debug.Print lngWindowHigh, lngHeaderHigh, lngFooterHigh, lngMargin, _
lngWindowHigh - lngHeaderHigh - lngFooterHigh, _
frmAny.Section(acDetail).Height

If lngWindowHigh - lngMargin - lngFooterHigh - lngHeaderHigh < 0 Then
'Do nothing as this would cause an error

ElseIf frmAny.Section(acDetail).Height < _
lngWindowHigh - lngHeaderHigh - lngFooterHigh Then
frmAny.Section(acDetail).Height = _
lngWindowHigh - lngHeaderHigh - lngFooterHigh
sfrmControl.Height = frmAny.Section(acDetail).Height - lngMargin
' Debug.Print "enlarged"

ElseIf frmAny.Section(acDetail).Height > _
lngWindowHigh - lngHeaderHigh - lngFooterHigh Then
sfrmControl.Height = _
lngWindowHigh - lngMargin - lngFooterHigh - lngHeaderHigh
frmAny.Section(acDetail).Height = _
lngWindowHigh - lngHeaderHigh - lngFooterHigh
' Debug.Print "shrank"

Else
' Debug.Print "maintained"
End If

EXIT_GrowSubForm:
On Error GoTo 0
Exit Sub

ERROR_GrowSubForm:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
" in procedure GrowSubForm of ModFunctions"
sfrmControl.Height = _
lngWindowHigh - lngHeaderHigh - lngFooterHigh - lngMargin
frmAny.Section(acDetail).Height = _
lngWindowHigh - lngHeaderHigh - lngFooterHigh
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

If I want a subform to take advantage of space on a main form, my typical
code also assumes the subform is at or near the bottom of the main form.

'---------------------------------------------------------------------------------------
' Procedure : Form_Resize
' DateTime : 10/10/2003 14:51
' Author : hookomd
' Purpose : Change the size of the subform to fit the form siz
'---------------------------------------------------------------------------------------
'
Private Sub Form_Resize()
On Error GoTo Form_Resize_Error

Me.SubForm.Height = Me.InsideHeight - (Me.Section(1).Height +
Me.SubForm.Top + 100)
Me.SubForm.Width = Me.InsideWidth - 200

On Error GoTo 0
Exit Sub

Form_Resize_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_Resize of VBA Document Form_frmMyQueryResults"
End Sub