Solved I use VBA to adjust the height of a continuous subform based on # of records, but my subform only adjusts to show up to 3 at a time w/ scrollbar (1 Viewer)

[diamondDog]

How can I have my continuous form always expand to see all records instead of having a scroll wheel?

The following code seems to work well when I have up to three records, but any more than three records and the subform no longer grows but only displays a vertical scroll bar. How can I eliminate a vertical scrollbar to help force the resize of my subform to always show all records. I do not want any scrolling.

Private Sub Form_Current()

With Me.subform
    .Height = .Form.Section(1).Height + .Form.Section(2).Height + (.Form.Section(0).Height * .Form.Recordset.RecordCount)
End With

End Sub

 

[cheekybuddha]

You probably will have to adjust the subform container control's height as well and possibly the .InsideHeight of the parent form too.

 

[diamondDog]

Thanks for the response! I am a noob and found my code online. How would I implement your suggestions? Are these settings in the Property Sheet for my subform or would I have to add more VBA code?

 

[DHookom]

I would create long number variable to store the result of your height calculation and then use
? Debug.Print lngNewHeight

You can also set a breakpoint in your code to review the values.

 

[Pat Hartman]

You also need to define your maximum and minimum heights.

 

[cheekybuddha]

Duane, can you post a link to a resource explaining the height properties?

 

[DHookom]

Here is some code that is used in the sample file linked at the bottom:

'---------------------------------------------------------------------------------------
' Procedure : Form_Resize
' DateTime  : 10/10/2003 14:51
' Author    : hookomd
' Purpose   : Change the size of the subform to fit the form size
'---------------------------------------------------------------------------------------
'
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

This code is used in the Resize event of the main form that displays the query by form results.

 

[cheekybuddha]

@DHookom first attachment?

 

[DHookom]

@DHookom first attachment?

Yes and I've been going a little crazy

 

[diamondDog]

Here is some code that is used in the sample file linked at the bottom:

'---------------------------------------------------------------------------------------
' Procedure : Form_Resize
' DateTime  : 10/10/2003 14:51
' Author    : hookomd
' Purpose   : Change the size of the subform to fit the form size
'---------------------------------------------------------------------------------------
'
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

This code is used in the Resize event of the main form that displays the query by form results.

Thank you so much for the example, but I am struggling to make it work for me. To clarify my intentions, I have a form with a subform that will display related records via a parent/child relationship. Every time a user toggles between records on the main form, I want the subform height to expand and shrink to snugly match the total number of records being displayed (i have the form and subform fixed so that a horizontal resize won't ever be necessary). I don't need any code having to do with resizing the main form--it will be a fixed size. I just want the subform to veritically expand to the size to view all records without having to scroll in the subform.

Also, would it be possible to have the subform in my main form header so that everything below my main form header is always right up beneath the header after it resized to the # of subform continuous records?

The example you provided probably gives me the keys to realize what I want, but I am but a novice and cannot yet think in terms of the 3D chess needed to create the code to do what I can imagine. Thanks again for your helpful response!

 

[arnelgp]

see this demo also, open MainForm form.
also see the Code on Current event of the MainForm.

 

[diamondDog]

see this demo also, open MainForm form.
also see the Code on Current event of the MainForm.

Thanks very much for the response and for the attachment share!

I swapped out my code for the code in the attachment (updating the subform name, of course) but now my subform is only ever the size of the header row and one row of record data. It's almost as if there is no count of records going on, as it doesnt seem to be factoring in as there is no resizing going on.

And just to give you an idea, the height of a single record of my subform looks to be the default excel row height. Not sure why the code in the example isnt working or what I would need to adjust:

Private Sub Form_Current()

    With Me.subForm
        If .Form.Recordset.RecordCount = 0 Then
            .Height = .Form.Section(acHeader).Height + .Form.Section(acDetail).Height + 300
            .Form.ScrollBars = 0
        Else
           
            If .Form.Recordset.RecordCount > conMaxRec Then
                .Height = .Form.Section(acHeader).Height + (.Form.Section(acDetail).Height * conMaxRec) + 300
                .Form.ScrollBars = 2
            Else
                .Height = .Form.Section(acHeader).Height + (.Form.Section(acDetail).Height * (.Form.Recordset.RecordCount + 1)) + 300
                .Form.ScrollBars = 0
            End If
        End If
    End With


End Sub

 

[Mark_]

To avoid some major headaches, I would suggest creating a variable to hold your calculated height, assign your calculated height to the variable, then check to make sure you don't try and resize too tall. Something like

Dim CtrlHeight AS Integer

With Me.SubForm
CtrlHeight = .Form.Section(acHeader).Height + .Form.Section(acDetail).Height + 300
IF CtrlHeight < YourMaxHeightHere
.Height = CtrlHeight
Else
.Height = YourMaxHeightHere
End If
End With

This way you don't get nasty error messages if you end up with more records than you thought.

 

[arnelgp]

e) but now my subform is only ever the size of the header row and one row of record data.

you can always share a sample db.

 

[diamondDog]

To avoid some major headaches, I would suggest creating a variable to hold your calculated height, assign your calculated height to the variable, then check to make sure you don't try and resize too tall. Something like

Dim CtrlHeight AS Integer

With Me.SubForm
CtrlHeight = .Form.Section(acHeader).Height + .Form.Section(acDetail).Height + 300
IF CtrlHeight < YourMaxHeightHere
.Height = CtrlHeight
Else
.Height = YourMaxHeightHere
End If
End With

This way you don't get nasty error messages if you end up with more records than you thought.

Thanks for the response!

I am attempting to try your code out, but the "IF CtrlHeight < YourMaxHeightHere" line is red in VBA and gives me a Compile Error (Expected: Then or GoTo). Is the code missing something?

Also, just to confirm, I am replacing "YourMaxHeightHere" with my max height #, but that still gives me a compile error.

 

[cheekybuddha]

Is the code missing something?

Yes, it's missing a 'Then' at then end of the line.

 

[diamondDog]

you can always share a sample db.

Happy Friday! Please see the attached mock-up database I made to demonstrate the issue I am having. You will see that the Main form's On Current code seems to work once the form is immediately loaded, or the view switched back to Form View, but once you toggle the main form's records forward and back, you will see that the size of the subform record count no longer works. I have tried a number of suggestions and variations but am at a loss.

Thank you for your time, attention and assistance.

 

[diamondDog]

To avoid some major headaches, I would suggest creating a variable to hold your calculated height, assign your calculated height to the variable, then check to make sure you don't try and resize too tall. Something like

Dim CtrlHeight AS Integer

With Me.SubForm
CtrlHeight = .Form.Section(acHeader).Height + .Form.Section(acDetail).Height + 300
IF CtrlHeight < YourMaxHeightHere
.Height = CtrlHeight
Else
.Height = YourMaxHeightHere
End If
End With

This way you don't get nasty error messages if you end up with more records than you thought.

Thank you for the reply. I tried your suggestion but it did not work. I have uploaded a copy of a database, in an above response to someone else, to demonstrate how the code does not work properly.
You will see that the Main form's On Current code seems to work once the form is immediately loaded, or the view switched back to Form View, but once you toggle the main form's records forward and back, you will see that the size of the subform record count no longer works. I have tried a number of suggestions and variations but am at a loss.

 

[diamondDog]

Yes, it's missing a 'Then' at then end of the line.

Thank you again for your help, but I was not able to get the code to work. I uploaded

Thank you for the reply. I tried your suggestion but it did not work. I have uploaded a copy of a database, in an above response to someone else, to demonstrate how the code does not work properly.
You will see that the Main form's On Current code seems to work once the form is immediately loaded, or the view switched back to Form View, but once you toggle the main form's records forward and back, you will see that the size of the subform record count no longer works. I have tried a number of suggestions and variations but am at a loss.

 

[arnelgp]

here is the fix. see the code behind the Main form.
also i added Index (Unique) on MainTbl on Account field.
same on SubTbl, but the index this time is not Unique.
also added Relationship between these tables.