Automatically readjusting size of everything in a continuous form

78 Views Asked by At

I'm building a database in MsAccess. It's going to be used in my office where everybody has displays with different screen resolutions and so I wanted my database to automatically resize forms to fit screen sizes.

The best solution I found (and implemented) was from this post: Access 2010 VBA Forms - Automatic Form Resize

Now the code works great for single forms, but it did not work at all for continuous forms, so I partially rewrote it for it to also resize continuous forms:

Option Compare Database
Option Explicit

Const FONT_ZOOM_PERCENT_CHANGE = 0.1

Private fontZoom As Double

Private Enum ControlTag
    FromLeft = 0
    FromTop
    ControlWidth
    controlHeight
    originalFontSize
    originalControlHeight
End Enum

Private Sub Form_Load()
    fontZoom = 1
    SaveControlPositionsToTags Me
End Sub

Private Sub Form_Resize()
    RepositionControls Me, fontZoom

End Sub

Public Sub SaveControlPositionsToTags(frm As Form)
    Dim ctl As Control
    For Each ctl In frm.Controls
        SaveControlPosition ctl, frm
    Next ctl
End Sub

Private Sub SaveControlPosition(ctl As Control, frm As Form)
    Dim ctlLeft As String
    Dim ctlTop As String
    Dim ctlWidth As String
    Dim ctlHeight As String
    Dim ctlOriginalFontSize As String
    Dim ctlOriginalControlHeight As String

    ctlLeft = CStr(Round(ctl.left / frm.InsideWidth, 4))
    ctlTop = CStr(Round(ctl.top / frm.InsideHeight, 4))
    ctlWidth = CStr(Round(ctl.width / frm.InsideWidth, 4))
    ctlHeight = CStr(Round(ctl.height / frm.InsideHeight, 4))

    Select Case ctl.ControlType
        Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
            ctlOriginalFontSize = ctl.fontSize
            ctlOriginalControlHeight = ctl.height
    End Select

    ctl.Tag = ctlLeft & ":" & ctlTop & ":" & ctlWidth & ":" & ctlHeight & ":" & ctlOriginalFontSize & ":" & ctlOriginalControlHeight
End Sub

Public Sub RepositionControls(frm As Form, fontZoom As Double)
    Dim tagArray() As String
    Dim ctl As Control

    On Error Resume Next

    For Each ctl In frm.Controls
        If ctl.Tag <> "" Then
            tagArray = Split(ctl.Tag, ":")

            ctl.Move frm.InsideWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                     frm.InsideHeight * (CDbl(tagArray(ControlTag.FromTop))), _
                     frm.InsideWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                     frm.InsideHeight * (CDbl(tagArray(ControlTag.controlHeight)))

            Select Case ctl.ControlType
                Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
                    Dim newFontSize As Double
                    newFontSize = CDbl(tagArray(ControlTag.originalFontSize)) * (ctl.height / CDbl(tagArray(ControlTag.originalControlHeight))) * fontZoom

                    If newFontSize >= 1 And newFontSize <= 127 Then
                        ctl.fontSize = Round(newFontSize)
                    End If
                End Select
            End If
        Next
    On Error GoTo 0
End Sub

So this solution mostly works, because it does automatically readjust the size of labels, font size, buttons etc. But the problem with this one is that I can't figure out how to also readjust record sizes - they stay the same. So when I shrink the window, everything but record sizes shrinks and I get big spaces between records and if I make it too large, some records overlap with each other.

I've tried many ways to somehow store the information about record spacing or record size in the tags to continue the logic of the code, but nothing seems to work and I'm trying to figure out if MsAccess allows record spacing/size to be adjustable at all like this.

I use this continuous form as a subform and I also tried a few times to somehow force the parent form to readjust the record sizes, but that also did not seem to work (by the way, both with the original code and this one, you can put it in a subform VBA code and it will readjust sizes when overall parent form is resized).

I'm basically looking for a way to store information about the size of record entry/space relative to the fields that are contained in it and then simply the ability to force the form size readjustment once this is done. This would basically fix the problem of automatically adjusting the size of the forms in MsAccess form, because in conjunction with the code that I linked to (designed for the single form), everything in MsAccess would become automatically adjustable.

Please help out.

0

There are 0 best solutions below