Auto-resize continous subform

[Lukael]

Hello everyone,

I have a continous subform, which I would like to grow/shrink based on records in It. I know this can be accomplished by selecting Vertical anchor property to "both", but you have to set max height/width of form, which is not desired.

All I want is to set height of subform to +/- when record in subform is added/deleted, and this must be done with VBA, in After_Update Event (probably). Code should trigger after combobox adds/deletes record.



I have attached sample. Open TblName, and you'll see a subform. Curently there are 2 records entered in subform. When user would enter another record in blank combobox, subfom should resize so that It can display new blank combobox. Same way should work if deleting records from combobox - subform should shrink. How can I do this ?

So far I've tried this (subform resize when record is deleted):

Private Sub Combo5_AfterUpdate()
If IsNull(Combo5) Then
DoCmd.RunCommand acCmdDeleteRecord
Me.[TblJoin].Height = Me.WindowHeight - 390
End If
End Sub

[CJ_London]

you need to calculate the required height of the subform and then have some code to ensure it does not oversize

to calculate the size, you need to add together the height of the header and footer plus the number of rows required x the detail height plus perhaps a small amount to allow for borders etc.

If you don't have any controls below the subform, you could just hide the subform border

[Lukael]

I know theory a little bit, but haven't seen some VBA example yet. Could you post some example?

[CJ_London]

something like

subfrmctrl.height=subfrmctrl.form.section(1).heigh t+subfrmctrl.form.section(2).height+(subfrmctrl.fo rm.section(0).height*subfrmctrl.form.recordset.cou nt)+60


see this link fro more info about the section property

https://msdn.microsoft.com/en-us/lib.../ff835642.aspx

[Lukael]

I tried this in attached sample DB, but I receive runtime error 2465:

[TblJoin].Height = [TblJoin].Form.Section(1).Height + [TblJoin].Form.Section(2).Height + ([TblJoin].Form.Section(0).Height * [TblJoin].Form.Recordset.Count) + 60

[CJ_London]

regret I don't keep a mental note of the thousands of runtime errors numbers and what they mean - my guess is the description will give you a clue

[Lukael]

Error says this:

microsoft access can't find the field ' 1' referred to in your expression

Sorry, but Access VBA is kinda of strange to me

[CJ_London]

you said your subform is a continuous form - they usually have headers, if they don't then remove the relevant sections - see the link I provided

[Lukael]

My subfrom doesn't have header and footer, but even when I added them code didn't work. I tried this without header/footer as in sample file, where just a detail section is:

Private Sub Combo5_AfterUpdate()

If IsNull(Combo5) Then
DoCmd.RunCommand acCmdDeleteRecord
[TblJoin].Height = ([TblJoin].Form.Section(0).Height * [TblJoin].Form.Recordset.Count) + 60
End If

End Sub

Not working - Runtime error: "can't find the field reffered to in your expression"

WHAT AM I DOING WRONG !?!

[CJ_London]

sorry, my mistake - should be

[TblJoin].Form.Recordset.RecordCount

[Lukael]

I tried your code, but It's not working in After_update event. Maybe your code must be inserted into module or Load_event of Master form, I really don't know...


However, I found this solution which auto-resizes continous form (as my subform in sample is) :

Private Sub Form_Load()


Const MaxRecs As Integer = 10
Dim NumRecs As Integer


With Me.[TblJoin].Form
.RecordsetClone.MoveLast
NumRecs = .RecordsetClone.RecordCount
If NumRecs > MaxRecs Then NumRecs = MaxRecs
.InsideHeight = .Section(1).Height + .Section(2).Height _
+ NumRecs * .Section(0).Height + 650


End With
End Sub

This code is inserted in Load_Event of master form, and It resizes subform according to records in It.
Code works when you open form, but has some issues while form is opened - subform resizes If you delete record and add another one, but If you add second one NOT. Looks like that subform remembers what size It was when loaded. Where's the catch, do you see It?

I attached another sample, check It out !

[CJ_London]

that is basically the same solution I provided, just a different way of getting the recordcount

Looks like that subform remembers what size It was when loaded

since you only run the code on the load event - which only runs the once, this is to be expected. So you need to find an event that runs when you want it to run - perhaps the current event, perhaps the afterupdate event, you will need to experiment

[Lukael]

since you only run the code on the load event - which only runs the once, this is to be expected. So you need to find an event that runs when you want it to run - perhaps the current event, perhaps the afterupdate event, you will need to experiment

Yes I figured that out. But solving this issue was to create a new module with this code, and call It from Form_Load and Combobox_AfterUpdate Event (control on subform). From Current or afterUpdate code doesn't work...

Here's complete code (changed a little bit - see Sub shrink_grow) :

Private Sub Form_Load()

Call shrink_grow 'You call this from Master form

End Sub

Private Sub Combo5_AfterUpdate()

'This is afterUpdate event of the subform control

If IsNull(Combo5) Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
End If

Call shrink_grow


End Sub

Sub shrink_grow()

'Code that shrinks/grows subform control according to records in continous form, needs to be inserted in module

Const MaxRecs As Integer = 10
Dim NumRecs As Integer

On Error Resume Next

With Forms![TblName]![TblJoin].Form
.RecordsetClone.MoveLast
NumRecs = .RecordsetClone.RecordCount
If NumRecs > MaxRecs Then NumRecs = MaxRecs
.InsideHeight = NumRecs * .Section(0).Height + 1650

End With

End Sub

I hope this helps someone, I find It very useful for me. Problem is only when you have some other controls under subform and you need to move them when subform resizes. But that is another issue, which I'm facing too and will need to open different thread and discuss about It there

Solution can be seen in attached sample - open TblName, you'll see subform where surnames like "Deere" are displayed. You can delete/add those records and subform will resize automatically.