Double-Action doesn’t mean that with one Click you can execute two different procedures. For that matter you can do several things at one click. Here, the emphasis is on programing the same Command Button for doing two different Actions i.e. when the Button is Clicked a second time it will execute a different action than it did for the first one.

To make the point more clear, let us look into a real world example. When the user opens a Data Editing Screen we want the screen to be kept locked for safety of the data and to prevent users changing the field values accidently.

But, when it is time to make some changes in one or more fields on the current record the user will Click the Command Button to unlock the Form for editing. After editing, the User will Click the same Button again to lock the screen.

Let us try an Example. But, before starting with the design task let us note down the above points with more specifics so that you will have a general idea what we are trying to do, when we proceed with the implementation steps.

Consider the Requirements

(a) When the Form is open for the first time, or when moved from one record to the other, the Form must be Locked from editing. Why, when moving from one record to the other? Well, the User clicked the Button to unlock the Form for editing, but he forgot or didn’t bother to click a second time to lock the Form. So we will apply an automatic Lock when she moves into another record.

(b) When the user goes for adding a New Record the Form is fully open for keying in values and our Double-Action Command Button will be in a disabled state.

(c) When the user Clicks the Double-Action Command Button (as we call it now) for the first time, unlock the Form for editing.

(d) When she Clicks the same button a second time, lock the form again for safety of data.

The only question at this point is that how do we determine which click is for what, and if the user repeats the clicks several times, then what happens? It is very simple, read on.

Design Task

  1. Open one of your existing Projects with a Data Editing Screen already available in it.
  2. Create a Command Button at the Footer of the Form.
  3. Click on the Command Button to select it, if it is not already in a selected state, display the Property Sheet (View – – > Properties).
  4. Change the Name Property to cmdEdit.
  5. Change the Caption Property (very important) Value to Edit.
  6. To take care of the points that we have noted at (a). and (b). above, display the VBA Module of the Form (View – – > Code) copy and paste the following Code into the Module.
    Private Sub Form_Current()
    If Me.NewRecord Then
        With Me
            .cmdEdit.Caption = "Edit"
            .cmdEdit.ForeColor = 0
            .cmdEdit.FontBold = False
            .AllowEdits = True
            .cmdEdit.Enabled = False
        End With
    Else
        With Me
            .AllowEdits = False
            .cmdEdit.Caption = "Edit"
            .cmdEdit.ForeColor = 0
            .cmdEdit.FontBold = False
            .cmdEdit.Enabled = True
        End With
    End If
    End Sub 

    The above code will take care of the New Record entry event as well as applying the automatic Lock when the User didn’t Click the Command Button to Lock the Form directly.

    Now we can concentrate on the Button Click part i.e. (c) and (d) points given above. Here, we have to check for the sate of the Button when Clicked by the User. The trick is checking the Caption Value of the Button and decide what is the user’s intentions and set the Form Locked or Unlocked state. At every click on the Button we will change the Caption Value to Edit or Lock interchangably.

    • If the Caption Value was Edit when the user clicked the button then he/she wants to edit values on the Form.
    • If the Caption Value was Lock, then the user wants to lock the Form after editing the data.
  7. The following Code will do the trick. Copy and paste the VBA Code into the Form’s Module and save the Form.
Private Sub cmdEdit_Click()
Dim cap As String
cap = Me.cmdEdit.Caption
Select Case cap
     Case "Edit"
         With Me
          .AllowEdits = True
          .cmdEdit.Caption = "Lock"
          .cmdEdit.ForeColor = 128
          .cmdEdit.FontBold = True
          .Refresh
         End With
    Case "Lock"
        With Me
          .AllowEdits = False
          .cmdEdit.Caption = "Edit"
          .cmdEdit.ForeColor = 0
          .cmdEdit.FontBold = False
          .Refresh
        End With
End Select
End Sub 

Experiment with your design and check whether it works every time correctly as expected.

If any suggestions for improvement or found that it didn’t work the way you expected it then please post them in comments.