MS Access VBA – Set Form’s GridX and GridY Properties

One thing I find when taking over projects from various sources, especially when the database has already been through the hands of several developers is that many default properties can be all different depending on the object types and who create/worked on them. As such, one can spend a lot of time trying to open each form and clean things up. Things like:

  • Date Picker
  • Record Locking
  • Background Colors
  • Alternate Background Colors (for continuous forms)
  • …
  • GridX and GridY

As such, I created a very straightforward function that you can run to apply a standard GridX and GridY value to all your forms in once shot.

'---------------------------------------------------------------------------------------
' Procedure : SetFrmsGridProp
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loops through all the database forms to set the GridX and GridY properties
'               great way to quickly set a common value to all your forms
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' iGridX    : New GridX value to set
' iGridY    : New GridY value to set
'
' Usage:
' ~~~~~~
' Call SetFrmsGridProp(20, 20)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015                    Initial Release
' 2         2018-09-20              Updated Copyright
'---------------------------------------------------------------------------------------
Public Sub SetFrmsGridProp(iGridX As Integer, iGridY As Integer)
    On Error GoTo Error_Handler
    Dim db                    As Object
    Dim obj                   As AccessObject
    Dim frm                   As Access.Form
    Dim i                     As Long
    
    Set db = Application.CurrentProject
    For Each obj In db.AllForms
        i = i + 1
        DoCmd.OpenForm obj.Name, acDesign
        Set frm = Forms(obj.Name).Form
        frm.GridX = iGridX
        frm.GridY = iGridY
        DoCmd.Close acForm, obj.Name, acSaveYes
    Next obj

Error_Handler_Exit:
    On Error Resume Next
    Set frm = Nothing
    Set obj = Nothing
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SetFrmsGridProp" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Obviously, this could be adapted to be applied to Reports as well with one minor tweak of the code.