Sizing bound subforms based on number of rows

[CDM]

I have a subform that displays rows of data for a single client. the subform
is bound to a local table. When I select a new client on the main form, the
local table is populated with the new client's data and the subform is
requeried. My problem is that I would like to shrink or expand the subform
container depending on the number of rows in the table. I can get a record
count, but I don't know how to reference the container. I'm assuming the
property to adjust is 'insideheight'?
Thanks in advance for your help.

 

[Jeff Boyce]

Have you checked on the subform control's CanGrow and CanShrink properties?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

Have you checked on the subform control's CanGrow and CanShrink
properties?

I don't think they would apply in form view, would they? Aren't they only
for printing?

 

[Dirk Goldgar]

I have a subform that displays rows of data for a single client. the
subform
is bound to a local table. When I select a new client on the main form,
the
local table is populated with the new client's data and the subform is
requeried. My problem is that I would like to shrink or expand the
subform
container depending on the number of rows in the table. I can get a record
count, but I don't know how to reference the container. I'm assuming the
property to adjust is 'insideheight'?

For a subform, I think you'd just adjust the Height of the subform control
on the main form. The only thing is, you might also have to adjust the size
of the main form to accomodate the subform, which *would* involve the
InsideHeight property (of the main form), as well as the Height property of
its Detail section.

 

[CDM]

cangrow = yes
canshrink = no

Should both be set to yes? I also read somewhere that it helped to give the
container a different name than the default subform name. Is that true?

 

[Jeff Boyce]

Dirk

You might be right!

Regards

Jeff

I don't think they would apply in form view, would they? Aren't they only
for printing?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[CDM]

Thanks. I'm still wondering how to reference the container in VB.

 

[Dirk Goldgar]

Thanks. I'm still wondering how to reference the container in VB.

This function, when passed a reference to the subform's form object, will
return a reference to the subform control on the parent form that contains
that subform:

'------ start of code ------
Function ParentSubformControl( _
frmMe As Access.Form) _
As Access.SubForm

Dim frmParent As Access.Form
Dim ctl As Control

On Error Resume Next
Set frmParent = frmMe.Parent
If Err.Number <> 0 Then
' This is not a subform.
Set ParentSubformControl = Nothing
Else
For Each ctl In frmParent.Controls
If ctl.ControlType = acSubform Then
If ctl.Form Is frmMe Then
Set ParentSubformControl = ctl
Exit For
End If
End If
Next ctl
End If

Set ctl = Nothing
Set frmParent = Nothing

End Function
'------ end of code ------

You would call it from the code module of a subform like this:

Dim sfContainer As Access.SubForm

Set sfContainer = ParentSubformControl(Me)

If sfContainer Is Nothing Then
' Something's wrong! I'm not a subform.
Else
' Do something with the subform control.
' For example, double its height:
sfContainer.Height = sfContainer.Height * 2
End If

 

[CDM]

Exactly what I needed. Thanks!

This function, when passed a reference to the subform's form object, will
return a reference to the subform control on the parent form that contains
that subform:

'------ start of code ------
Function ParentSubformControl( _
frmMe As Access.Form) _
As Access.SubForm

Dim frmParent As Access.Form
Dim ctl As Control

On Error Resume Next
Set frmParent = frmMe.Parent
If Err.Number <> 0 Then
' This is not a subform.
Set ParentSubformControl = Nothing
Else
For Each ctl In frmParent.Controls
If ctl.ControlType = acSubform Then
If ctl.Form Is frmMe Then
Set ParentSubformControl = ctl
Exit For
End If
End If
Next ctl
End If

Set ctl = Nothing
Set frmParent = Nothing

End Function
'------ end of code ------

You would call it from the code module of a subform like this:

Dim sfContainer As Access.SubForm

Set sfContainer = ParentSubformControl(Me)

If sfContainer Is Nothing Then
' Something's wrong! I'm not a subform.
Else
' Do something with the subform control.
' For example, double its height:
sfContainer.Height = sfContainer.Height * 2
End If


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)