More on DoCmd OpenForm: Control Form Opening Part Two

Last week I blogged about how to Control Form Opening using the DoCmd OpenForm method of VBA.  I covered the DataMode and OpenArgs parameters in some detail.  Since being able to control form opening is an important part of database customization, I shall continue by looking at two other parameters of this method - namely the VIEW and WHERE parameters.

If we recap briefly on what we learnt last week, you may remember that we used DoCmd OpenForm to control whether the form was opened ready to add a new record, or edit an existing record. A label caption was also set according the value passed to the form in the statement's OpenArgs parameter. This time we are going to try something slightly different.  We are going to create a command button, which when pressed by the user, opens that same form in DATASHEET VIEW as well as displaying records matching a given criteria. The end result will be a datasheet list of all employees where the Position field contains the value of "Manager".

Let's remind ourselves of the syntax for DoCmd OpenForm:

DoCmd.OpenForm "FormName", View, "FilterName", "WhereCondition",  DataMode, WindowMode, "OpenArgs"

As you may remember, only the FormName parameter is required, all others being optional.  Just remember to include a comma if any of the parameters are not used (unless they comes after  the last used parameter, in which case they are not needed).  So let's look at the specific syntax we need in order to achieve the task of producing a list of Managers in Datasheet View:

DoCmd.OpenForm "frmEmployees",  acFormDS, , "position = 'manager'"

As you can see, the VIEW parameter has been set to acFormDS.  This value simply indicates that we want the form to open in Datasheet View. When you come to enter this yourself, you will see that intellisense opens a drop down list of valid parameter values:

Figure 1: Intellisense displaying a list of valid parameter values for VIEW.

The next used parameter is the WhereCondition.  This is basically an SQL WHERE clause, but without the  WHERE keyword (see my Introduction to Access SQL for more information about SQL WHERE).  You may also think of this as being like the criteria you enter in the Criteria Row of a Query Design Grid. When used as a OpenForm parameter,  just remember to enclose it within quotation marks, as it is a string value - ie:

"position = 'manager'"

Now that we have grasped the particular syntax for DoCmd OpenForm, we are going to add a command button to the Switchboard form we created last week, and add the following code to its OnClick Event:

Private Sub ctlManagers_Click()
    DoCmd.OpenForm "frmEmployees", acFormDS, , "position = 'manager'"
    DoCmd.Close acForm, Me.Name    
End Sub


NB the DoCmd.Close statement simply closes the switchboard so it does not get in the way once our employees form opens.

Our Switchboard should now look like this:

Figure 2: The new updated switchboard from last week.
The code above it is written behind the ctlManagers button's (top) OnClick Event.

Here is the result when the user clicks the ctlManagers button (captioned "List Managers"):

Figure 3: frmEmployees displayed in Datasheet View with WhereCondition applied.

As you can see, rather than our employees form being displayed in standard Form View, passing the acFormDS as the View parameter has instead opened frmEmployees as a Datasheet similar to a table or query; and out of a list of 22 employees, passing "position = 'manager'" as the  WhereCondition  parameter has provided a list of the two employees who have "Manager" as their Position.  

If you have not already created this database for yourself, you might like to download the completed solution and experiment by changing, amongst others, the WhereCondition, to see the versatility of DoCmd OpenForm for yourself.