automatically adjust subform height based on amount of records

[Andrew]

Hi. I would like the subform and also the form to automatically adjust height
based on amount of records in the subform (instead of having the vertical
scroll bar appear). The subform is directly below the form information. Is
there an easy way to do this. Thank you.

Form
-Location -Source -Comment

Subform (Some form records will have many units. Each unit is a subform
record)
-Unit -Thickness
1 5
2 15
3 26

 

[freakazeud]

Hi,
check this for the scrollbar issue:
http://www.databasedev.co.uk/subform_scrollbars.html
Additionally check out a form's detail section's can grow; can shrink
property.
HTH
Good luck

 

[freakazeud]

Hi,
check this for the scrollbar issue:
http://www.databasedev.co.uk/subform_scrollbars.html
Additionally check a forms detail section can grow; can shrink property.
HTH
Good luck

 

[Stephen Lebans]

Can Grow/Shrink it only for Forms and Report objects in Print/Preview mode.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Stephen Lebans]

You should learn how to search GoogleGroups. It's a must have tool for all
users/developers.



Newsgroups: microsoft.public.access.modulesdaovba
From: Marshall Barton <[email protected]> - Find messages by
this author
Date: Sun, 22 Sep 2002 23:41:47 -0500
Local: Mon, Sep 23 2002 12:41 am
Subject: Re: Sub form height
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

How can I get a subform's height programatically adjusted so that it just
fits the amount of record that the underlying records source returns?

Here's a procedure I've used to do that kind of thing.
Since the calculations are dependent on the size of the main
form, they have to be redone whenever the main form's size
changes.

Private Sub Form_Resize()
Dim AvailSpace As Long
Dim DetailSpace As Long
Dim DetailHeight As Long
Dim HeadersHeight As Long
Dim NumDetails As Long
Dim NumRecords As Long
' Determine max space available for subform
AvailSpace = Me.InsideHeight - Subform.Top - 100 _
- (Me.Section(1).Height +
Me.Section(2).Height)
With Subform.Form
HeadersHeight = .Section(1).Height + .Section(2).Height
DetailHeight = .Section(0).Height
' Determine space for subform details
DetailSpace = AvailSpace - HeadersHeight
If DetailSpace < DetailHeight Then Exit Sub
' Get number of subform records (+1 for new record)
NumRecords = .RecordsetClone.RecordCount + 1
' Get max number of details that can fit
NumDetails = DetailSpace \ DetailHeight
If NumRecords <= NumDetails Then
NumDetails = NumRecords
.ScrollBars = 0 ' None
Else
.ScrollBars = 2 ' Vertical only
End If
' Set height to number of details or available space
Subform.Height = DetailHeight * NumDetails _
+ HeadersHeight
End With


If you're adding or deleting records from the subform then
be sure to call the procedure after those operations.


Also be sure to add:


Subform.Form.RecordsetClone.MoveLast


to the main form's Load event to make sure that the
subform's RecordCount will be accurate.


--
Marsh
MVP [MS Access]



--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Andrew]

Hi Thanks. This is a really dumb question, but i am kinda new to access. Do I
put all the code into the main form code area or create a macro. When I enter
that line into the load event in the main form properties, I get an error
saying that it cant find the macro "subform". I think i have the correct
names for my forms.

You should learn how to search GoogleGroups. It's a must have tool for all
users/developers.



Newsgroups: microsoft.public.access.modulesdaovba
From: Marshall Barton <[email protected]> - Find messages by
this author
Date: Sun, 22 Sep 2002 23:41:47 -0500
Local: Mon, Sep 23 2002 12:41 am
Subject: Re: Sub form height
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

How can I get a subform's height programatically adjusted so that it just
fits the amount of record that the underlying records source returns?

Here's a procedure I've used to do that kind of thing.
Since the calculations are dependent on the size of the main
form, they have to be redone whenever the main form's size
changes.

Private Sub Form_Resize()
Dim AvailSpace As Long
Dim DetailSpace As Long
Dim DetailHeight As Long
Dim HeadersHeight As Long
Dim NumDetails As Long
Dim NumRecords As Long
' Determine max space available for subform
AvailSpace = Me.InsideHeight - Subform.Top - 100 _
- (Me.Section(1).Height +
Me.Section(2).Height)
With Subform.Form
HeadersHeight = .Section(1).Height + .Section(2).Height
DetailHeight = .Section(0).Height
' Determine space for subform details
DetailSpace = AvailSpace - HeadersHeight
If DetailSpace < DetailHeight Then Exit Sub
' Get number of subform records (+1 for new record)
NumRecords = .RecordsetClone.RecordCount + 1
' Get max number of details that can fit
NumDetails = DetailSpace \ DetailHeight
If NumRecords <= NumDetails Then
NumDetails = NumRecords
.ScrollBars = 0 ' None
Else
.ScrollBars = 2 ' Vertical only
End If
' Set height to number of details or available space
Subform.Height = DetailHeight * NumDetails _
+ HeadersHeight
End With


If you're adding or deleting records from the subform then
be sure to call the procedure after those operations.


Also be sure to add:


Subform.Form.RecordsetClone.MoveLast


to the main form's Load event to make sure that the
subform's RecordCount will be accurate.


--
Marsh
MVP [MS Access]



--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

Hi. I would like the subform and also the form to automatically adjust
height
based on amount of records in the subform (instead of having the vertical
scroll bar appear). The subform is directly below the form information.
Is
there an easy way to do this. Thank you.

Form
-Location -Source -Comment

Subform (Some form records will have many units. Each unit is a subform
record)
-Unit -Thickness
1 5
2 15
3 26

 

[Andrew]

Nevermind. I added indentations to the code when I should have just left it
alone.

 

[Andrew]

Hi thank you for your reply. I have a few questions about the code. The Top
property does not pop up after the Subform. So, I used .WindowTop instead. Im
not sure if thats the same though. Then, the subform.Height property does not
pop up (near the end of the code). So, I used .windowheight. Then on the line
of HeadersHeight =.... i get a runtime error saying the section number is
invalid. thanks.

Andrew