resize subform

[slimjen1]

All; using 2010. How can I get a subform to grow or shrink based on num of records returned? It’s a continuous subform. Tried changing Vertical and Horizontal Anchors as well as some VBA suggestions but haven’t been able to make any of them work. Thanks

 

[BigHappyDaddy]

The method I use is:

Public Sub ResizeForm(ByRef frm As Form)
   Const Twips As Long = 1440
   Const lngFormFrameTop As Long = (3 * 0.125) * Twips
   Const lngFormFrameBtm As Long = (5 * 0.125 / 3) * Twips
   Dim Head As Long    'Form Header plus estimated height of top form frame
   Dim Foot As Long    'Form Footer plus estimated height of bottom form frame
   Dim RecordHeight As Long
   Dim lngHeight As Long
   Dim lngMaxHeight As Long
   Dim lngMaxWidth As Long
   Dim lngCurrentWidth As Long
   Dim lngLeft As Long
   Dim lngTop As Long
   Dim lngMaxRecShow As Long
   Dim lngRecords As Long
   Dim rs As DAO.Recordset
 
   lngMaxHeight = 11205 'Me.InsideHeight - lngFormFrameTop
   lngMaxWidth = 18375 'Me.InsideWidth
   lngCurrentWidth = frm.InsideWidth
   Head = frm.Section(acHeader).Height + lngFormFrameTop
   Foot = frm.Section(acFooter).Height + lngFormFrameBtm
   RecordHeight = frm.Section(acDetail).Height
   lngLeft = (lngMaxWidth - lngCurrentWidth) / 2
   lngMaxRecShow = (lngMaxHeight - Head - Foot) / RecordHeight
   Set rs = frm.RecordsetClone
   If rs.EOF Then
      lngRecords = 0
   Else
      rs.MoveLast
      rs.MoveFirst
      lngRecords = rs.RecordCount
   End If
   If lngRecords >= lngMaxRecShow Then
      lngRecords = lngMaxRecShow
   End If
   lngHeight = (lngRecords * RecordHeight + Head + Foot)
   lngTop = ((lngMaxHeight - lngHeight + lngFormFrameTop) / 2)
   frm.Move lngLeft, lngTop, , lngHeight
 
End Sub

 

[slimjen1]

Thanks I'll try this