Bookmarking and Moving through Records with ADO - Binding the recordset to Controls on the form
(Page 4 of 4 )
In an earlier tutorial we discussed the new property of a form, namely the Recordset. By assigning the recordset under investigation to Me.Recordset you will be creating a record source for all objects that you place on the form. Review the following code that assigns each of the fields to a control (in this case a Text box). The record navigation on the form will be available to you unless you turn it off in design.
Drag and drop four text box controls. Text0 to Text6 are the text box controls in this tutorial. Change the label caption of the text box controls so that they are indicative of the column names and implement the code as shown. When you open this form, you will see the first record filling the text boxes. You can now use the navigation to move forwards and backwards.
Private Sub Form_Load()
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
Set Me.Recordset = rst
Me.Text0.ControlSource = rst.Fields.Item(0).Name
Me.Text2.ControlSource = rst.Fields.Item(1).Name
Me.Text4.ControlSource = rst.Fields.Item(2).Name
Me.Text6.ControlSource = rst.Fields.Item(3).Name
MsgBox (rst.Fields.Count)
Set rst = Nothing
Set cnn = Nothing
End Sub
Summary
In this tutorial we have seen some of the basic features of ADO's recordset as applied to data retrieved using a simple select statement on a table in the Northwind sample database. As discussed in the previous tutorial not all cursors support the Bookmark property. When in doubt use the 'Supports' property of recordset. The CursorOptionEnums which show what can or cannot be done is a function of the data provider, the CursorType, CursorLocation and the LockType. Using the recordset property of a Form you can see how easily you can bind data to the controls on the form.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 8 
