Sometimes, we have recurring tasks that we would like to simplify and when those tasks involve interactions with the Access object model, we certainly don’t want to miss out on the built-in intellisense.
Do you have any intellisense?
At IT Impact we frequently open forms in our code with a where clause, and to make sure it does open correctly, we close the form if it’s open prior to opening it again. We’ve created a simple procedure called OpenMyForm using a regular string for the Where condition.
Public Sub OpenMyForm(FormName As String, WhereCondition As String)
If CurrentProject.AllForms(FormName).IsLoaded Then
DoCmd.Close acForm, FormName
End If
DoCmd.OpenForm FormName, WhereCondition:=WhereCondition
End Sub
This worked fine for us. Now, suppose we realized we wanted to support opening form into form view or into datasheet view. One way we could do this is to add another parameter:
Public Sub OpenMyForm( _
FormName As String, _
WhereCondition As String, _
OpenAsDatasheet As Boolean _
)
If CurrentProject.AllForms(FormName).IsLoaded Then
DoCmd.Close acForm, FormName
End If
If OpenAsDatasheet Then
DoCmd.OpenForm FormName, _
WhereCondition:=WhereCondition, _
View:=acFormDS
Else
DoCmd.OpenForm FormName, _
WhereCondition:=WhereCondition, _
View:=acNormal
End If
End Sub
We don’t want to continue adding more lines of code this way, so we modified OpenMyForm to use Access’ native AcFormView, AcFormOpenDataMode and AcWindowMode enums:
Public Sub OpenMyForm ( _
FormName As Variant, _
Optional View As AcFormView = acNormal, _
Optional FilterName As Variant, _
Optional WhereCondition As Variant, _
Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, _
Optional WindowMode As AcWindowMode = acWindowNormal, _
Optional OpenArgs As Variant _
)
If CurrentProject.AllForms(FormName).IsLoaded Then
DoCmd.Close acForm, FormName
End If
DoCmd.OpenForm FormName, _
View, _
FilterName, _
WhereCondition, _
DataMode, _
WindowMode, _
OpenArgs
End Sub
If you use object browser (F2) and look up DoCmd.OpenForm, you’ll note that the parameters are exactly identical (note that a parameter with no explicit data type defaults to a Variant data type). When you add a new OpenMyForm line, you still get the same intellisense aid that you had with original DoCmd.OpenForm.
Because parameters are defined using same data types (note that enumerations are a form of data type), named same names, and have same defaults, all possible variants of DoCmd.OpenForm, whether it omits arguments, uses commas to skip some optional arguments, uses named parameters, will work with the new OpenMyForm without any changes, other than the fact that we’ve guaranteed that filtering will be current.
To find out what enumerations are built-in to Access and DAO, it’s simple as using object browser in VBA editor (press F2 to open it) and looking at the Access and/or DAO libraries to see how enumerations are named. If you also look at the intellisense that shows when you type in DoCmd.OpenForm, you can see that it also tell you the data type, including the name of enumerations that it uses so you can use that to get the correct enumerations so you can go and re-use it in your own code. Of course, you’re not limited to just Access and/or DAO enumerations.
What are other uses for built-in enumerations you can find? Let us know in the comments!
Ten rows from the bottom, change “will work with the new OpenForm without any changes” to “will work with the new OpenMyForm without any changes”
Edited, thanks for clarification, grovelli!