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.
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
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 editModeThis 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.