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.
- Save this code into a new module called FormHelper.
- 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.