Visual Basic Concepts

Adding Data Handling Code

In order to turn the MyData control into a data source, you'll need to add some code to handle connecting to the data and moving through the records. You'll also need to expose a number of properties to allow a developer using the control to select a data source at design time.

Note This topic is part of a series that walks you through creating sample data source components. It begins with the topic Creating Data Sources.

To add data handling code to the MyData control

In the Project Explorer window, click MyData to select it, then press F7 or click the Code button on the Project Explorer window toolbar, to open the Code window.
Select References from the Project menu to open the References dialog box. Add a reference to the Microsoft ActiveX Data Objects 2.0 Library.

Add the following Property Let / Property Get procedures to expose design-time properties:

' read only
Public Property Get RecordSet() As ADODB.RecordSet
   Set RecordSet = rs
End Property

Public Property Get RecordSource() As String
   RecordSource = m_RecordSource
End Property

Public Property Let RecordSource(ByVal New_RecordSource As String)
   m_RecordSource = New_RecordSource
End Property

Public Property Get BOFAction() As BOFActionType
   BOFAction = m_BOFAction
End Property

Public Property Let BOFAction(ByVal New_BOFAction As BOFActionType)
   m_BOFAction = New_BOFAction
End Property

Public Property Get EOFAction() As EOFActionType
   EOFAction = m_EOFAction
End Property

Public Property Let EOFAction(ByVal New_EOFAction As EOFActionType)
   m_EOFAction = New_EOFAction
End Property

Public Property Get ConnectionString() As String
   ConnectionString = m_ConnectionString
End Property

Public Property Let ConnectionString(ByVal New_ConnectionString _
      As String)
   m_ConnectionString = New_ConnectionString
End Property

In the Object box, select cmdFirst. In the Procedure box, select the Click event. Add the following code to the cmdFirst_Click event procedure to move to the first record in a recordset:
```
Private Sub cmdFirst_Click()
   If rs Is Nothing Then Exit Sub
   rs.MoveFirst
End Sub
```
In the Object box, select cmdLast. In the Procedure box, select the Click event. Add the following code to the cmdLast_Click event procedure to move to the last record in a recordset:
```
Private Sub cmdLast_Click()
   If rs Is Nothing Then Exit Sub
   rs.MoveLast
End Sub
```

In the Object box, select cmdPrev. In the Procedure box, select the Click event. Add the following code to the cmdPrev_Click event procedure to move backwards through a recordset:

Private Sub cmdPrev_Click()
   If rs Is Nothing Then Exit Sub

   If rs.BOF Then
      Select Case m_BOFAction
         Case BOFActionType.adDoMoveFirst
            rs.MoveFirst
         Case BOFActionType.adStayBOF
            Exit Sub
         Case Else
            Exit Sub
      End Select
   Else
      rs.MovePrevious
   End If
End Sub

In the Object box, select cmdNext. In the Procedure box, select the Click event. Add the following code to the cmdNext_Click event procedure to move forward through a recordset:

Private Sub cmdNext_Click()
   If rs Is Nothing Then Exit Sub

   If rs.EOF Then
      Select Case m_EOFAction
         Case EOFActionType.adDoAddNew
            rs.AddNew
         Case EOFActionType.adDoMoveLast
            rs.MoveLast
         Case EOFActionType.adStayEOF
            Exit Sub
         Case Else
            Exit Sub
      End Select
   Else
      rs.MoveNext
   End If
End Sub

In the Object box, select UserControl. In the Procedure box, select the Terminate event. Add the following code to the UserControl_Terminate event procedure to ensure that the objects created by the control are shut down properly:

Private Sub UserControl_Terminate()
   On Error Resume Next

   If Not rs Is Nothing Then
      rs.Close
      Set rs = Nothing
   End If

   If Not cn Is Nothing Then
      cn.Close
      Set cn = Nothing
   End If

   Err.Clear
End Sub

In the Object box, select UserControl. In the Procedure box, select the WriteProperties event. Add the following code to the UserControl_WriteProperties event procedure to save the property values that are entered at design time:

Private Sub UserControl_WriteProperties(PropBag As PropertyBag)
   'Write property values to storage
   Call PropBag.WriteProperty("Caption", _
      lblCaption.Caption, Ambient.DisplayName)
   Call PropBag.WriteProperty("RecordSource", _
      m_RecordSource, m_def_RecordSource)
   Call PropBag.WriteProperty("BOFAction", _
      m_BOFAction, m_def_BOFAction)
   Call PropBag.WriteProperty("EOFAction", _
      m_EOFAction, m_def_EOFAction)
   Call PropBag.WriteProperty("ConnectionString", _
      m_ConnectionString, m_def_ConnectionString)
End Sub

In the Object box, select UserControl. In the Procedure box, select the ReadProperties event. Add the following code to the UserControl_ReadProperties event procedure to save the property values that are entered at design time:

Private Sub UserControl_ReadProperties(PropBag As PropertyBag)
   'Load property values from storage
   lblCaption.Caption = PropBag.ReadProperty("Caption", _
      Ambient.DisplayName)
   m_RecordSource = PropBag.ReadProperty("RecordSource", _
      m_def_RecordSource)
   m_BOFAction = PropBag.ReadProperty("BOFAction", m_def_BOFAction)
   m_EOFAction = PropBag.ReadProperty("EOFAction", m_def_EOFAction)
   m_ConnectionString = PropBag.ReadProperty("ConnectionString", _
      m_def_ConnectionString)
End Sub

In the Object box, select UserControl. In the Procedure box, select the GetDataMember event. Add the following code to the UserControl_GetDataMember event procedure to connect to the data specified in the ConnectionString and RecordSource properties:

Private Sub UserControl_GetDataMember(DataMember As String, _
      Data As Object)
   Dim conn As String

   On Error GoTo GetDataMemberError

   If rs Is Nothing Or cn Is Nothing Then
      ' make sure various properties have been set
      If Trim$(m_ConnectionString) = "" Then
         MsgBox "No ConnectionString Specified!", _
               vbInformation, Ambient.DisplayName
         Exit Sub
      End If

      If Trim$(m_RecordSource) = "" Then
         MsgBox "No RecordSource Specified!", _
            vbInformation, Ambient.DisplayName
         Exit Sub
      End If

      If Trim$(m_ConnectionString) <> "" Then
         ' Create a Connection object and establish
         ' a connection.
         Set cn = New ADODB.Connection
         cn.ConnectionString = m_ConnectionString
         cn.Open

         ' Create a RecordSet object.
         Set rs = New ADODB.RecordSet
         rs.Open m_RecordSource, cn, adOpenKeyset, adLockPessimistic
         rs.MoveFirst
      Else
         Set cn = Nothing
         Set rs = Nothing
      End If
   End If

   Set Data = rs

   Exit Sub

GetDataMemberError:

   MsgBox "Error: " & CStr(Err.Number) & vbCrLf & vbCrLf & _
      Err.Description, vbOKOnly, Ambient.DisplayName
   Exit Sub
End Sub

Save your work before going on to the next step.

In the next step we'll run our project to see the results.

Step by Step

This topic is part of a series that walks you through creating sample ActiveX data sources.

To	See
Go to the next step	Running the MyDataControl Project
Start from the beginning	Creating Data Sources