Access 2010中的VBA形式 - 自动调整大小表(Access 2010 VBA Forms

我已经完成我的形式在办公室使用，然而，在不同计算机上打开犯规resize.Instead表单时，会出现滚动条。我怎样才能使窗体和控件自动调整？

Answer 1:

下面是一些VBA代码，你可以添加到您的形式，将保持形式寻找相同的，无论多么大或小的用户作出了窗口的显示器或他们的显示器分辨率为上。

你也可以使文本通过按住Ctrl键的同时滚动鼠标滚轮上下更大或更小（或者，按住Shift键并击中+键或-键）

要使用此功能，只需打开访问，并在设计视图中打开窗体。首先，形式的图像上单击鼠标右键，并添加Form Header/Footer 。

如果不将页眉和页脚添加到窗体，下面的代码会报错了。但是，您可以缩小页眉和页脚没有两者的高度，如果你不希望它们出现在窗体上。

通过点击小盒子在表单的左上角，只是选项卡下选择窗体本身：

当我们观看这将确保我们正在寻找的形式本身的属性Property Sheet 。

要查看Property Sheet的形式（如果它是不可见的话），按住Alt键，然后按Enter键。

选择Event选项卡。

然后，您需要将文字文本添加[Event Procedure]到窗体本身背后的以下五个项目：

负载
On键向上
按下按键时
在调整大小
在鼠标滚轮

您可以键入文字文本[Event Procedure]到文本框旁边的这些事件，或点击旁边的每个事件的省略号（...）按钮，然后选择Code Builder ，从弹出菜单中。

它会是这个样子：

...

此外，在事件列表的底部，你还需要将更改Key Preview属性Yes ：

最后，你可能会想要把Scroll Bars关闭的形式，使他们不重叠的任何内容。要做到这一点，转到Format的选项卡Property Sheet在设计视图窗体，并更改Scroll Bars属性Neither 。

现在，添加VBA代码，按住Alt键并击F11查看VBA编辑器。

一旦VBA编辑器中，在双击Form_YourFormName下的选项Microsoft Access Class Objects文件夹：

如果你没有看到Microsoft Access Class Objects文件夹，然后返回到设计视图中的表格，然后点击旁边的文字文本省略号（...） [Event Procedure]任何刚修改过的事件。

这将带你回到VBA编辑器，你现在应该是内部Form_YourFormName代码区。目前就已经有一些代码在那里，但你可以继续下一步骤之前删除所有的它。

然后在屏幕的右侧的主要部分，只需复制并粘贴下面的代码，你就大功告成了。

Option Compare Database
Option Explicit


'Set an unchangeable variable to the amount (10% for example) to increase or
'decrease the font size with each zoom, in or out.
Const FONT_ZOOM_PERCENT_CHANGE = 0.1


'Create the fontZoom and ctrlKeyIsPressed variables outside of
'the sub definitions so they can be shared between subs
Private fontZoom As Double
Private ctrlKeyIsPressed As Boolean


'Create an enum so we can use it later when pulling the data out of the "Tag" property
Private Enum ControlTag
    FromLeft = 0
    FromTop
    ControlWidth
    ControlHeight
    OriginalFontSize
    OriginalControlHeight
End Enum


Private Sub Form_Load()
    'Set the font zoom setting to the default of 100% (represented by a 1 below).
    'This means that the fonts will appear initially at the proportional size
    'set during design time. But they can be made smaller or larger at run time
    'by holding the "Shift" key and hitting the "+" or "-" key at the same time,
    'or by holding the "Ctrl" key and scrolling the mouse wheel up or down.
    fontZoom = 1

    'When the form loads, we need to find the relative position of each control
    'and save it in the control's "Tag" property so the resize event can use it
    SaveControlPositionsToTags Me
End Sub


Private Sub Form_Resize()
    'Set the height of the header and footer before calling RepositionControls
    'since it caused problems changing their heights from inside that sub.
    'The Tag property for the header and footer is set inside the SaveControlPositionsToTags sub
    Me.Section(acHeader).Height = Me.WindowHeight * CDbl(Me.Section(acHeader).Tag)
    Me.Section(acFooter).Height = Me.WindowHeight * CDbl(Me.Section(acFooter).Tag)

    'Call the RepositionControls Sub and pass this form as a parameter
    'and the fontZoom setting which was initially set when the form loaded and then
    'changed if the user holds the "Shift" key and hits the "+" or "-" key
    'or holds the "Ctrl" key and scrolls the mouse wheel up or down.
    RepositionControls Me, fontZoom
End Sub


Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    'PURPOSE: Make the text on the form bigger if "Shift" and "+" are pressed
    'at the same time and smaller if "Shift" and "-" are pressed at the same time.
    'NOTE: Using the "Ctrl" key instead of the "Shift" key conflicts with Access's
    'default behavior of using "Ctrl -" to delete a record, so "Shift" is used instead

    'Was the "Shift" key being held down while the Key was pressed?
    Dim shiftKeyPressed As Boolean
    shiftKeyPressed = (Shift And acShiftMask) > 0

    'If so, check to see if the user pressed the "+" or the "-" button at the
    'same time as the "Shift" key. If so, then make the font bigger/smaller
    'by the percentage specificed in the FONT_ZOOM_PERCENT_CHANGE variable.
    If shiftKeyPressed Then

        Select Case KeyCode
            Case vbKeyAdd
                fontZoom = fontZoom + FONT_ZOOM_PERCENT_CHANGE
                RepositionControls Me, fontZoom

                'Set the KeyCode back to zero to prevent the "+" symbol from
                'showing up if a textbox or similar control has the focus
                KeyCode = 0

            Case vbKeySubtract
                fontZoom = fontZoom - FONT_ZOOM_PERCENT_CHANGE
                RepositionControls Me, fontZoom

                'Set the KeyCode back to zero to prevent the "-" symbol from
                'showing up if a textbox or similar control has the focus
                KeyCode = 0

        End Select

    End If

    'Detect if the "Ctrl" key was pressed. This variable
    'will be used later when we detect a mouse wheel scroll event.
    If (Shift And acCtrlMask) > 0 Then
        ctrlKeyIsPressed = True
    End If

End Sub

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
    'Change the ctrlKeyIsPressed variable to false when
    'any key is let up. This will make sure the form text does
    'not continue to grow/shrink when the mouse wheel is
    'scrolled after the ctrl key is pressed and let up.
    ctrlKeyIsPressed = False
End Sub


Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
    'If the "Ctrl" key is also being pressed, then zoom the form in or out
    If ctrlKeyIsPressed Then
        Debug.Print ctrlKeyIsPressed
        'The user scrolled up, so make the text larger
        If Count < 0 Then

            'Make the font bigger by the percentage specificed
            'in the FONT_ZOOM_PERCENT_CHANGE variable
            fontZoom = fontZoom + FONT_ZOOM_PERCENT_CHANGE
            RepositionControls Me, fontZoom

        'The user scrolled down, so make the text smaller
        ElseIf Count > 0 Then

            'Make the font smaller by the percentage specificed
            'in the FONT_ZOOM_PERCENT_CHANGE variable
            fontZoom = fontZoom - FONT_ZOOM_PERCENT_CHANGE
            RepositionControls Me, fontZoom
        End If

    End If
End Sub


Public Sub SaveControlPositionsToTags(frm As Form)
On Error Resume Next

    Dim ctl As Control

    Dim ctlLeft As String
    Dim ctlTop As String
    Dim ctlWidth As String
    Dim ctlHeight As String
    Dim ctlOriginalFontSize As String
    Dim ctlOriginalControlHeight As String

    For Each ctl In frm.Controls

        'Find the relative position of this control in design view
        'e.g.- This control is 5% from the left, 10% from the top, etc.
        'Those percentages can then be saved in the Tag property for this control
        'and used later in the form's resize event
        ctlLeft = CStr(Round(ctl.Left / frm.Width, 4))
        ctlTop = CStr(Round(ctl.Top / frm.Section(ctl.Section).Height, 4))
        ctlWidth = CStr(Round(ctl.Width / frm.Width, 4))
        ctlHeight = CStr(Round(ctl.Height / frm.Section(ctl.Section).Height, 4))

        'If this control has a FontSize property, then capture the
        'control's original font size and the control's original height from design-time
        'These will be used later to calculate what the font size should be when the form is resized
        Select Case ctl.ControlType
            Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
                ctlOriginalFontSize = ctl.FontSize
                ctlOriginalControlHeight = ctl.Height
        End Select

        'Add all this data to the Tag property of the current control, separated by colons
        ctl.Tag = ctlLeft & ":" & ctlTop & ":" & ctlWidth & ":" & ctlHeight & ":" & ctlOriginalFontSize & ":" & ctlOriginalControlHeight

    Next

    'Set the Tag properties for the header and the footer to their proportional height
    'in relation to the height of the whole form (header + detail + footer)
    frm.Section(acHeader).Tag = CStr(Round(frm.Section(acHeader).Height / (frm.Section(acHeader).Height + frm.Section(acDetail).Height + frm.Section(acFooter).Height), 4))
    frm.Section(acFooter).Tag = CStr(Round(frm.Section(acFooter).Height / (frm.Section(acHeader).Height + frm.Section(acDetail).Height + frm.Section(acFooter).Height), 4))

End Sub


Public Sub RepositionControls(frm As Form, fontZoom As Double)
On Error Resume Next

    Dim formDetailHeight As Long
    Dim tagArray() As String

    'Since "Form.Section(acDetail).Height" usually returns the same value (unless the detail section is tiny)
    'go ahead and calculate the detail section height ourselves and store it in a variable
    formDetailHeight = frm.WindowHeight - frm.Section(acHeader).Height - frm.Section(acFooter).Height

    Dim ctl As Control

    'Loop through all the controls on the form
    For Each ctl In frm.Controls

        'An extra check to make sure the Tag property has a value
        If ctl.Tag <> "" Then

            'Split the Tag property into an array
            tagArray = Split(ctl.Tag, ":")

            If ctl.Section = acDetail Then
                'This is the Detail section of the form so use our "formDetailHeight" variable from above
                ctl.Move frm.WindowWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                                   formDetailHeight * (CDbl(tagArray(ControlTag.FromTop))), _
                                   frm.WindowWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                                   formDetailHeight * (CDbl(tagArray(ControlTag.ControlHeight)))
            Else
                ctl.Move frm.WindowWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                                   frm.Section(ctl.Section).Height * (CDbl(tagArray(ControlTag.FromTop))), _
                                   frm.WindowWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                                   frm.Section(ctl.Section).Height * (CDbl(tagArray(ControlTag.ControlHeight)))
            End If

            'Now we need to change the font sizes on the controls.
            'If this control has a FontSize property, then find the ratio of
            'the current height of the control to the form-load height of the control.
            'So if form-load height was 1000 (twips) and the current height is 500 (twips)
            'then we multiply the original font size * (500/1000), or 50%.
            'Then we multiply that by the fontZoom setting in case the user wants to
            'increase or decrease the font sizes while viewing the form.
            Select Case ctl.ControlType
                Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
                    ctl.FontSize = Round(CDbl(tagArray(ControlTag.OriginalFontSize)) * CDbl(ctl.Height / tagArray(ControlTag.OriginalControlHeight))) * fontZoom
            End Select

        End If

    Next

End Sub

这里有一个什么样的形式，看起来缩水时，像一些截图。

之前：

后：

另外，还可以使文本通过握住Ctrl键并滚动鼠标滚轮向上更大（通过保持Shift键和按+键，或者可选地）。

而且，您可以使文本通过按住Ctrl键的同时滚动鼠标滚轮向下较小（按住Shift键的同时按，或者-键）

Answer 2:

几点注意事项：

看看如何控制锚定到形式，使他们能够与形式调整。
设计你的形式，使他们在您的用户具有最小屏幕尺寸正常显示。
这一点很重要，你想想你的用户将如何与应用程序交互。你不能指望获得神奇的回流和调整了一切，这是你的东西，作为应用程序的设计者，需要思考的问题。
因此，限制你的窗体上的控件的数量，并保持足够小，它们在无论是在你的办公室是最小合理的屏幕分辨率正确显示。
如果你不想酒吧出现，看看形式的scrollbars属性。
考虑各种形式的风格，你可以使用：在Access 2007及以上版本中，您可以在标签中使用的形式。你也可以让他们弹出，并防止它们被调整。
看看下面的表单属性和周围的各种组合发挥，以获得所需的效果：