On To the cmdButton Control Array

Visual Studio 6.0

Remember the interesting thing about control arrays - they all share the same code. So bring up the code window and select the Click event for the cmdButton control array. When the user clicks any of our nine buttons, this code will be invoked and the number of the button pressed will be passed to this sub from VB. We then handle each button individually just as we did in our data class a few chapters back.

This is the longest sub for our control, but it does an awful lot of work for us. After we have added the code we will discuss what is going on in each part of this sub.

Try It Out - Adding Code to the cmdButton_Click Event

Bring up the code window for the cmdButton_Click event and add the following code:

Private Sub cmdButton_Click(Index As Integer)
Static vMyBookmark As Variant
Dim bCancel As Boolean

'-- sanity check here --
If adoRecordset Is Nothing Then Exit Sub

Select Case Index
 Case cmdMoveFirst      '--- move first ---
    adoRecordset.MoveFirst
    editStatus = nowStatic
    Call updateButtons
    lblControl = "Record " & adoRecordset.AbsolutePosition & _
   " of " & lTotalRecords
 Case cmdMovePrevious  '--- move previous ---
    adoRecordset.MovePrevious
    editStatus = nowStatic
    Call updateButtons
    lblControl = "Record " & adoRecordset.AbsolutePosition & _
   " of " & lTotalRecords

 Case cmdMoveNext      '--- move next ---
    adoRecordset.MoveNext
    editStatus = nowStatic
    Call updateButtons
    lblControl = "Record " & adoRecordset.AbsolutePosition & _
   " of " & lTotalRecords

 Case cmdMoveLast      '-- move last ---
    adoRecordset.MoveLast
    editStatus = nowStatic
    Call updateButtons
    lblControl = "Record " & adoRecordset.AbsolutePosition & _
   " of " & lTotalRecords

 '-- Now we are modifying the database --
 Case cmdAddNew       '-- add a new record
    RaiseEvent validateRecord("Add", bCancel)
    If (bCancel = True) Then Exit Sub

    editStatus = nowadding
    With adoRecordset
      If (.RecordCount > 0) Then
        If (.BOF = False) And (.EOF = False) Then
          vMyBookmark = .Bookmark
        Else
          vMyBookmark = ""
         End If
      Else
          vMyBookmark = ""
      End If
      .AddNew
      lblControl = "Adding New Record"
      Call updateButtons
    End With

 Case cmdEdit '-- edit the current record
    RaiseEvent validateRecord("Edit", bCancel)
    If (bCancel = True) Then Exit Sub
     editStatus = nowEditing
     With adoRecordset
        vMyBookmark = adoRecordset.Bookmark
       'We just change the value with ado
        lblControl = "Editing Record"
        Call updateButtons
    End With

 Case cmdSave '-- save the current record
     Dim bMoveLast As Boolean
     RaiseEvent validateRecord("Save", bCancel)
     If (bCancel = True) Then Exit Sub
     
     With adoRecordset
         If .editMode = adEditAdd Then
             bMoveLast = True
         Else
             bMoveLast = False
         End If
         .Move 0
         .Update
         editStatus = nowStatic
         If (bMoveLast = True) Then
            .MoveLast
         Else
            .Move 0
         End If
         editStatus = nowStatic
         lTotalRecords = adoRecordset.RecordCount
         updateButtons True
         lblControl = "New Record Saved"
     End With '

 Case cmdDelete  '-- delete the current record
    Dim iResponse As Integer
    Dim sAskUser As String
    
    RaiseEvent validateRecord("Delete", bCancel)
    If (bCancel = True) Then Exit Sub
    
    sAskUser = "Are you sure you want to delete this record?"
    iResponse = MsgBox(sAskUser, vbQuestion + vbYesNo  _
       + vbDefaultButton2, Ambient.DisplayName)
    If (iResponse = vbYes) Then
      With adoRecordset
          .Delete
          If (adoRecordset.RecordCount > 0) Then
            If .BOF Then
              .MoveFirst
           Else
             .MovePrevious
          End If
          lTotalRecords = adoRecordset.RecordCount
          lblControl = "Record Deleted"
        End If
      End With
   End If
   editStatus = nowStatic
   Call updateButtons '
   
 Case cmdUndo '-- undo changes to the current record
    RaiseEvent validateRecord("Undo", bCancel)
    If (bCancel = True) Then Exit Sub
    
    With adoRecordset
        
       If editStatus = nowEditing Then
           .Move 0
           .Bookmark = vMyBookmark
        End If
        .CancelUpdate
        If editStatus = nowEditing Then
           .Move 0
        Else
          If Len(vMyBookmark) Then
            .Bookmark = vMyBookmark
          Else
            If .RecordCount > 0 Then
              .MoveFirst
            End If
          End If
        End If
        lblControl = "Cancelled"
     End With
     editStatus = nowStatic
     updateButtons True
     
End Select
End Sub

How It Works

Since we are using the enumerated values for our command buttons, we can use the familiar Case cmdMoveFirst instead of Case 0. Most of this code is familiar from our data class, but a few points are worth mentioning. The navigation buttons, 0 through 3 are self-explanatory. We navigate the recordset, enable/disable the appropriate buttons, and display our location within the recordset in the lblCaption. This is keeping with our philosophy of giving the user visual feedback. But we added another variable here, the editStatus:

editStatus = nowStatic

This just sets the form-level variable to nowStatic, essentially telling our program that there is no adding or editing taking place. This is needed in our updateButtons helper routine so it knows how to behave.

Next comes the code for adding a new record to the database from our ActiveX Control:

Case cmdAddNew       '-- add a new record
    RaiseEvent validateRecord("Add", bCancel)
    If (bCancel = True) Then Exit Sub

    editStatus = nowadding
    With adoRecordset
      If (.RecordCount > 0) Then
        If (.BOF = False) And (.EOF = False) Then
          vMyBookmark = .Bookmark
        Else
          vMyBookmark = ""
         End If
      Else
          vMyBookmark = ""
      End If
      .AddNew
      lblControl = "Adding New Record"
      Call updateButtons
    End With

With each of the operations that can change the database, we want to raise an event to the host form. As we mentioned earlier, this will permit the programmer to cancel the operation if they choose. So if the event in the form sets the bCancel variable to True, we just exit the routine. In this case if we exit, then the status will be what is was before. There might be a case where the programmer does not want a record added for some reason – for example, certain users aren't allowed to add records, for security reasons perhaps. Well, if the user profile does not permit this, the programmer just sets the cancel variable to True within the validateRecord event and the user can't add a record.

If the user does in fact wish to add a new record, next we tell our program that we are adding a record by setting the form level variable editStatus. Next, we see if there is a valid record. And if there is, we set the locally scoped variant vMyBookMark to the current record bookmark. We do this so that if the user thinks better of adding the record and decides to cancel the add, we can return to the record that was current before the add took place. (Many programs just move to the first record in the recordset in cases like this, but the user will appreciate this nice touch.)

We also check to ensure that both the .BOF and .EOF properties of the recordset are not false. This will happen if the user Adds then Cancels twice in a row - there will be no current record! And VB will be kind enough to provide our user with a polite error message. These are the types of error situations we must keep our eyes peeled to find and handle before our control gets out in the wild:

With a bit of forethought and testing we can shield our users from these nasty things.

The next part of the sub is for editing a current record from our control. One of the differences between DAO and ADO recordsets was that when we wanted to edit a record using DAO, we needed to invoke the Edit method of the recordset. This was seen as redundant, or superfluous in ADO. With ADO, we just make a change to the field and there is no need to call the Edit method. ADO calls this type of editing Immediate Mode. This makes sense, but we must take care to determine if the user is actually editing. Why? Because of our indirection again, when we are editing a record, the ADO Recordset.EditMode is set to adEditNone! Our recordset is not notified immediately when an edit takes place, as it is when we are working directly with a recordset. You might remember that in the general declarations section of the control we dimensioned our variable editStatus as type editMode:

Dim editStatus As editMode

This handy little variable is used to inform our program of the editing state of the program. It's kind of a pain that we have to do this, but I think you will agree that it's a small price to pay for the power of creating a data bound ActiveX control.

Here's the next significant code – it caters for when the user is editing a record:

Case cmdEdit '-- edit the current record
    RaiseEvent validateRecord("Edit", bCancel)
    If (bCancel = True) Then Exit Sub
     editStatus = nowEditing
     With adoRecordset
        vMyBookmark = adoRecordset.Bookmark
       'We just change the value with ado
        lblControl = "Editing Record"
        Call updateButtons
    End With

Again, we set the vMyBookMark to the current record in case the user decides to cancel the edit. As usual, we tell the user what is happening by way of the lblControl and then updateButtons will enable/disable the appropriate buttons on our control.

The next stage of the sub caters for saving an edited or added record to the database:

Case cmdSave '-- save the current record
     Dim bMoveLast As Boolean
     RaiseEvent validateRecord("Save", bCancel)
     If (bCancel = True) Then Exit Sub
     
     With adoRecordset
         If .editMode = adEditAdd Then
             bMoveLast = True
         Else
             bMoveLast = False
         End If
         .Move 0
         .Update
         editStatus = nowStatic
         If (bMoveLast = True) Then
            .MoveLast
         Else
            .Move 0
         End If
         editStatus = nowStatic
         lTotalRecords = adoRecordset.RecordCount
         updateButtons True
         lblControl = "New Record Saved"
     End With '

You will recall that when a new record is added to our recordset, it is placed at the end. So if the user edited a current record, we stay where we are, but if it is a brand new record, we will move to the end of the recordset to display it. Notice that we have a .Move 0 right before the .Update method is called. This might look strange, moving 0 records. However, in our control this is needed to force the adoRecordset.EditMode into adEditAdd. We need this so the .Update will work correctly for edited records. Again, because we are a level removed from the data, we need to gently coerce the .EditMode to ensure the record gets saved correctly. This is not needed when working directly with ADO recordsets. There, we can use code such as:

adoRecordset!myField = "New Value"
adoRecordset.Update

But since we don't know the fields ahead of time, and they are updated via bound controls, we need this extra .Move 0 step.

Then we proceed to change the editStatus value, update the form level lTotalRecords to ensure our display is in sync, and update our buttons. Of course, we want to reassure the user that the record was indeed successfully saved.

The next chunk of code handles deleting a record from the database using our control:

Case cmdDelete  '-- delete the current record
    Dim iResponse As Integer
    Dim sAskUser As String
    
    RaiseEvent validateRecord("Delete", bCancel)
    If (bCancel = True) Then Exit Sub
    
    sAskUser = "Are you sure you want to delete this record?"
    iResponse = MsgBox(sAskUser, vbQuestion + vbYesNo  _
                    + vbDefaultButton2, Ambient.DisplayName)
    If (iResponse = vbYes) Then
      With adoRecordset
          .Delete
          If (adoRecordset.RecordCount > 0) Then
            If .BOF Then
              .MoveFirst
           Else
             .MovePrevious
          End If
          lTotalRecords = adoRecordset.RecordCount
          lblControl = "Record Deleted"
        End If
      End With
   End If
   editStatus = nowStatic
   Call updateButtons '

There is no magic here. We just provide a message to the user that a record is about to be deleted. They can choose to proceed or cancel if they have had a change of mind. Notice that the title of the message box will be Ambient.DisplayName. Each and every control has access to various Ambient properties. One of them is the name of the current control, which is in the DisplayName property.

If the user proceeds with the deletion, we move to the previous record, or the first if there is only one left. If there are no records, our updateButtons routine will handle that for us.

Next, we code for canceling a change to an add or an edit:

Case cmdUndo '-- undo changes to the current record
    RaiseEvent validateRecord("Undo", bCancel)
    If (bCancel = True) Then Exit Sub
    
    With adoRecordset
        
       If editStatus = nowEditing Then
           .Move 0
           .Bookmark = vMyBookmark
        End If
        .CancelUpdate
        If editStatus = nowEditing Then
           .Move 0
        Else
          If Len(vMyBookmark) Then
            .Bookmark = vMyBookmark
          Else
            If .RecordCount > 0 Then
              .MoveFirst
            End If
          End If
        End If
        lblControl = "Cancelled"
     End With
     editStatus = nowStatic
     updateButtons True

Notice that we must call the .Move method again to get the .editMode in sync. We aren't going anywhere - just moving zero records. But this is enough to ensure the .editMode is set. Then we call the .CancelUpdate method of the adoRecordset that will either get rid of a new record or cancel any editing to the current record. When we say new record, the record wasn't really added yet. The fields are stored in a buffer in memory and this command just throws the information away:

Here is where our vMyBookMark really shines. If the user was simply editing an existing record, we cancel the update and stay put. But if a new record was added, we move to the record we were on prior to the add. As a safety net - if no bookmark was set, the recordset moves to the first record. As usual, we tell the user what happened via the lblControl label, update the current editStatus and let the updateButtons routine spruce up the interface.

So next, let's add our code for the updateButtons routine.

© 1998 by Wrox Press. All rights reserved.

Show: