Subform Sizing MS Access 10

[puforee]

I understand there is a way to adjust the vertical height of a form/sub form based on the number of records returned.

I have a Form with 7 horizontal sub forms. Each sub form can show a different number of records. The original height setting for the sub forms is set to show two records. I need this height to automatically increase when more than two records show.

Help!!

 

[kjv1611]

If I'm not mistaken, you'd have to do this via VBA when using subforms inside a form. If it were just 1 form, then you could have it automatically resize to some extent, but with subforms, you basically set the control size for each subform, and then the subform works within the limits of the parent form.

You might could try to get a little more available space by changing things on the form and/or subforms like:
[ul]
[li]Record Selectors -> No[/li]
[li]Border Style -> None or Thin[/li]
[li]Navigation Buttons -> No[/li]
[li]Scrollbars -> None[/li]
[li]Control Box -> No[/li]
[li][/li]
[/ul]

Of course, you'd need to decide on each item, whether you need it or not, or whether it's helpful or not for the form as well as each subform control.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[puforee]

kjv1611,

You are of the same thought I am. I have set the appropriate controls to No/None.

Now, can you help me with the code to set the Detail Height of a sub form. I will copy paste to the other. So, the way I figure it...I have to have a count of the records showing on the sub form.....yes. Then based on the number of records I can set the DetailHeight property...sound right? The normal detail section is set to show 2 records and then scroll...so if the number of records returned was 4...I would have to approximately double the Detail Height...sound good?

So, now I am down to the hard part coding all of this. Any Ideas?

 

[MajP]

Usually this is done with a tab control, each subform on its own tab.

 

[puforee]

MajP...that is an idea worth considering...but right now I have a customer in my org to support.

Any ideas on the code for what I want to do?

 

[MajP]

This is my guess at how I would do it. This assumes each subform is formatted the same, and they are one after another. The issue is that you have to manually resize and then move them up or down the form. If there is all kind of formatting and other controls between the subforms then you will have to hard wire a lot of things.

In this simple example I say a record is .4 inches in height, but you can modify that. I say the header row is .25. I figure out how many records, resize the control, and then move the subform controls up are down.

Private Sub Command12_Click()
  GrowSub Me.subFormCtl1
  GrowSub Me.subFormCtl2
  'Growsub me.subformctl3
  MoveSubForms
End Sub
Public Sub GrowSub(subformctl As Access.SubForm)
  Dim intRecs As Long
  Dim rs As DAO.Recordset
  Const oneRecordHt = 0.4
  Const HeaderHt = 0.25
  intRecs = subformctl.Form.RecordsetClone.RecordCount
  subformctl.Height = InchesToTwips(oneRecordHt) * intRecs + InchesToTwips(HeaderHt)
End Sub
Public Function InchesToTwips(inches As Double) As Long
  InchesToTwips = CInt(inches * 1440)
End Function
Public Sub MoveSubForms()
  Const spaceBetween = 0.25
  'move subforms down based on the size of the other subforms
  Me.subFormCtl2.top = Me.subFormCtl1.top + Me.subFormCtl1.Height + InchesToTwips(spaceBetween)
  'if there was more
  ' Me.subFormCtl3.top = Me.subFormCtl2.top + Me.subFormCtl2.Height + InchesToTwips(spaceBetween)
End Sub