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.