Microsoft Access Visual Basic Form Helper: Replacement for DoCmd.OpenForm for neater code

Frequently in Microsoft Access Visual Basic you will want to programmatically display a different record on a currently open form but this common requirement turns out to be a bit more fiddly than you might expect. Manually setting the Filter and FilterOn form properties or using RecordSetClone and FindFirst are all somewhat less than intuitive.

Microsoft provides optional arguments for their DoCmd.OpenForm function that allow you to specify which record or records to display when first opening the form. It is a very neat and a very useful shortcut. However, because it always runs through all your form initialisation code and appears to do something else mysterious, it can prove to be a slow method to use to reset a currently open form to display a different record or set of records and can move focus to an unexpected control (especially when your form has tabs and subforms).

My FormHelper.OpenForm avoids the usability performance hit of DoCmd.OpenForm by, if your form is already open, bringing your form to the top and updating the properties where possible. If the form is not open or you require properties that cannot be reset while the form is open, then the normal DoCmd.OpenForm is used.

To use:

  1. Save this code into a new module called FormHelper.
  2. Replace calls to DoCmd.OpenForm with FormHelper.OpenForm.

The code is fully commented and contains more details on implementation and what the functions can and can’t do.

''' <summary>
''' Opens form or, if open already, brings form to the top and activates it. Is an exact replacement for DoCmd.OpenForm.
''' </summary>
''' <remarks>
''' <para>There are some significant limitations on passing arguments to forms that are already open.
'''       <b>View</b>, <b>FilterName</b>, <b>WindowMode</b> and <b>OpenArgs</b> will all be ignored.
'''       If these arguments are supplied, the form will be closed and re-opened using DoCmd.OpenForm.
''' </para>
''' </remarks>
Public Function OpenForm( _
                FormName As String, _
                Optional View As AcFormView = acNormal, _
                Optional FilterName As String, _
                Optional WhereCondition As String, _
                Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, _
                Optional WindowMode As AcWindowMode = acWindowNormal, _
                Optional OpenArgs As String)
  
  ' Determine whether we must use DoCmd.OpenForm
  Dim DoCmdOpenFormRequired As Boolean
  DoCmdOpenFormRequired = (View <> acNormal) Or (FilterName <> "") Or (WindowMode <> acWindowNormal) Or (OpenArgs <> "")
  
  If FormOpen(FormName) And Not DoCmdOpenFormRequired Then
  
    ' Bring open form to top
    BringToTop Forms(FormName)
    
    With Forms(FormName)
      ' Update Filter property using WhereCondition
      If WhereCondition <> .Filter Then
        .Filter = WhereCondition
        .FilterOn = (WhereCondition <> "")
      End If
      
      Select Case DataMode
        Case acFormAdd: .AllowAdditions = True: .AllowDeletions = False: .AllowEdits = False
        Case acFormEdit: .AllowAdditions = True: .AllowDeletions = True: .AllowEdits = True
        Case acFormReadOnly: .AllowAdditions = False: .AllowDeletions = False: .AllowEdits = False
      End Select
      
    End With
    
  Else
    ' Open form in standard way using DoCmd.OpenForm
    DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
  End If
  
  End Function

You can also download FormHelper.bas. This is always the latest complete version of this module and contains more functions than detailed here. The functions are fully commented.

This code was written in Visual Basic for Applications (VB, VBA) for Microsoft Access 2002 (XP) on Windows XP.

2 thoughts on “Microsoft Access Visual Basic Form Helper: Replacement for DoCmd.OpenForm for neater code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s