Now our variables are defined, we can proceed to write the control's properties and methods. In order for our control to know how to handle data, we simply set a single property. Let's do it.
1. Bring up the property box for the dbCtl control and set the DataSourceBehavior property to 1 – vbDataSource:
As in the previous example that we worked through with our simple bound controls, setting this property will automatically add a new subroutine to our control - the GetDataMember subroutine. This is the main routine for creating our connection and recordset. We need to put some detail into this subroutine.
2. Bring up the UserControl_GetDataMember subroutine and add the following code:
Private Sub UserControl_GetDataMember(DataMember As String, Data As Object)
Dim iReturn As Integer
On Error GoTo ohno
'—Reasonability test --
If (adoRecordset Is Nothing) Or (adoConnection Is Nothing) Then
If Trim$(m_connectionString) = "" Then
iReturn = MsgBox("There is no connection string!", _
vbCritical, Ambient.DisplayName)
Exit Sub
End If
If Trim$(m_recordSource) = "" Then
iReturn = MsgBox("There is no recordsource!", vbCritical, _
Ambient.DisplayName)
Exit Sub
End If
Set adoConnection = New ADODB.Connection
adoConnection.Open m_connectionString
Set adoRecordset = New ADODB.RecordSet
adoRecordset.CursorLocation = adUseClient
adoRecordset.CursorType = adOpenDynamic
adoRecordset.LockType = adLockBatchOptimistic
adoRecordset.Open m_recordSource, adoConnection, , , adCmdTable
lTotalRecords = adoRecordset.RecordCount
adoRecordset.MoveFirst
Call cmdButton_Click(cmdMoveFirst)
End If
Set Data = adoRecordset
Exit Sub
ohno:
MsgBox Err.Description
Exit Sub
End SubAs mentioned, when our control is run, this sub will be invoked to create the ADO recordset based on the user's parameters. We first do a reasonability test to ensure that there is indeed a connection string and record source. If not, we notify the user. If you wish, you could raise an error to accomplish the same thing. But the message box serves our purposes here.
If, however, our reasonability test is passed, we go ahead and create a new ADO connection and open it using the m_connectionString variable. Of course, this won't be called until the control is in design mode. When the user sets the connection string, the variable m_connectionString will be set. Then, the connection will be opened:
Set adoConnection = New ADODB.Connection
adoConnection.Open m_connectionStringWe must set the various properties of the recordset before we actually open it so our control can use it:
Set adoRecordset = New ADODB.RecordSet
adoRecordset.CursorLocation = adUseClient
adoRecordset.CursorType = adOpenDynamic
adoRecordset.LockType = adLockBatchOptimistic
Notice that we set the CursorType as adOpenDynamic because we need the RecordSource to be scrollable backwards and forwards, as well as being updateable.
Once these properties are set, we open the recordset. The ADO recordset is opened using the value of the variable m_recordSource, which will also be set by the user of our control:
adoRecordset.Open m_recordSource, adoConnection, , , adCmdTable
Notice that we open the recordset as type adCmdTable. This is done so we can get the RecordCount property and read the AbsolutePosition property to update our lblCaption as the user navigates the recordset:
adoRecordset.Open m_recordSource, adoConnection, , , adCmdTable
lTotalRecords = adoRecordset.RecordCount
adoRecordset.MoveFirst
Once our recordset is created, a call to cmdButton_Click (cmdMoveFirst) will move the recordset to the first record. And the code will enable/disable the appropriate buttons on our control, just like our data class did. Then, finally, we set the Data object variable to our new open recordset. This is what VB uses to permit our control to work with the recordset automatically:
Call cmdButton_Click(cmdMoveFirst)
Set Data = adoRecordset