Control Form Opening: DataMode, OpenArgs, and the DoCmd.OpenForm Method

Lets imagine we have an employees form to add and edit our employee records.  The form is accessed via the database switchboard which contains a couple of Command Buttons.  The user clicks the NEW EMPLOYEE button and the form opens in DATA ENTRY MODE; that is, as a blank form ready for a new record to be entered.  If, however, he or she clicks the EDIT EMPLOYEE button, the form opens in FORM EDIT MODE; at an actual employee record ready to be edited as required.  What's more, the form contains a label displaying a caption inviting the user to add a new employee, or edit an existing employee record depending on which button was pressed on the switchboard.

Figure1 (above): The Switchboard with Command Buttons
to open frmEmployees.

Figure 2: Form Design for frmEmployees. 
Note the label containing the text "Instructions": this text is
dynamically reset as the form opens.

Since we have learnt some basic VBA programming skills at the end of last year, lets look at how we can do this using the DoCmd.OpenForm method.   As we shall soon see, this method passes a number of parameters to the opening form, determining various aspects of how we want it to function.  DataMode and OpenArgs are the two parameters we shall be using to accomplish the task set out in the above mentioned scenario.

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

Figure 3: The IntelliSense feature of the VBA Editor will
help when you come to enter the DoCmd.OpenForm parameters.

Here is an outline of the tasks involved:

1. Create the Switchboard Form with two Command buttons - ctlNew and ctlEdit.
2. Add code to each of the command buttons which opens frmEmployees in the desired DataMode and with the corresponding label caption for adding or editing.
3. Create frmEmployees along with its underlying data source (ie an employees table).  The form should have a label control (lblInstuctions), and text boxes/combo boxes for each of the fields.  NB for the purposes of this exercise it does not particularly matter what the fields are.
4. Add the relevant code to the OnOpen Event of frmEmployees.  This is going to read the value of the forms OpenArgs Property, which is set when the user clicks one of the two command buttons on the switchboard.  Then, depending on what that value is, display the appropriate caption for lblInstructions.

(Download the complete solution here)

Let's go through stage two and stage four in more detail.  

To begin with lets look at the code used behind each of the two command buttons on frmSwitchboard. These statements are executed behind each command button's OnClick Event: 

Stage Two Code:

Private Sub ctlEdit_Click()

    DoCmd.OpenForm "frmEmployees", , , , acFormEdit, , "Edit"   

End Sub

Private Sub ctlNew_Click()    

    DoCmd.OpenForm "frmEmployees", , , , acFormAdd, , "Add"  

End Sub

As you can see, the DoCmd.OpenForm method has a total of seven parameters, but only the first of these is required (as opposed to optional).  Not suprisingly the required parameter is the name of the form to be opened, which in our case is "frmEmployees".  In addition to the form name we also set the DataMode and OpenArgs parameters.  For our scenario, DataMode determines whether the form is going to be opened in DataEntry (acFormAdd) or Edit (acFormEdit) mode.  It is the fifth parameter in the list. The  OpenArgs parameter, on the other hand, is seventh in the list.  This is simply a string value which will set the opening form's OpenArgs property.  Once the form has opened, we are going to read the value of this property to determine which message we are going to display in the label caption shown in figure two above. As such we are going to pass a string value which indicates whether we are adding or editing the employee record.  Please note that the text of the string value is entirely arbitrary.  We just need to choose a unique value in order to differentiate between adding and editing as the form opens.  

Here is the code we need to enter behind the frmEmployees OnOpen Event:

Stage Four Code:

Private Sub Form_Open(Cancel As Integer)

    If Me.OpenArgs = "Edit" Then

        lblInstructions.Caption = "Please Edit Employee Record"

    ElseIf Me.OpenArgs = "Add" Then

        lblInstructions.Caption = "Please Add New Employee Record"

    End If

End Sub

As you can see this code reads the value stored  in the opening form's OpenArgs property and uses a If ... Then ... Else Statement to determine whether our instructions label should display information pertaining to Editing or Adding an Employee Record.  

Figure 4: frmEmployees for Editing.

Figure 5:  frmEmployees for Adding (DataEntry).

As you can see, the screenshot in Figure 4 shows what our form looks like when it opens for Editing, and that in Figure 5 shows what it looks like for Adding. So despite being exactly the same form, we have dynamically controlled how it looks and functions by setting the DataMode and OpenArgs parameters of the DoCmd.OpenForm method.