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

 

[Steve]

Add New Records At The Top Of A Continuous Subform

In a continuous form the new record always appears at the bottom of the
existing records. There's no changing that. However, this method makes it
look like the new record is at the top of existing records snd new records
can be entered there.

1. Place a second subform at the top of the main subform. Set the height
to display one record.
2. Link the second subform in the same way as the main subform
3. Open this 2nd subform in Design View and in Properties set Data Entry
to Yes.
4. Add appropriate code in the AfterInsert of the second subform to
Requery the main subform.

With careful control of position of the second subform, it can *look* like
just one form with the new record line on top

Steve
(e-mail address removed)

 

[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