Navigating a Recordset and Binding controls to a recordset using Visual Basic

The Recordset object enables your application to access data returned from an SQL query or stored procedure. Using the Recordset object, you can navigate through the records that have been returned. A Recordset has a conceptual pointer, using which Visual Basic points at any one particular record, which is the current record.

A Recordset object has certain properties and methods, which can be used to navigate and manage the recordset. The properties of a Recordset object are listed below:

Properties of Recordset:

BOF: Indicates if the pointer has moved before the first record.

EOF: Indicates if the pointer has moved past the last record.

Abosoluteposition: Sets or returns the obsolute position of the record.

Methods of Recordset object:

MoveFirst: Moves to the first record

MoveLast: Moves to the last record,

MoveNext: Moves to the next record

AddNew: Appends a blank record to the recordset

Update: Saves the modification permanently on the recordset object.

Create the form frmAccountDetails and place the controls as shown below.

User Interface, Name, Caption

Form, frmAccDetails, Account Details
Label, lblcode, Code
Label, lblname, Name
Textbox, mcode
Textbox, mname
Command Button, cmdfirst, First
Command Button, cmdprev, Previous
Command Button, cmdnext, Next
Command Button, cmdlast, Last

Declare the necessary objects as given in the following code of the General section

Option Explicit
Dim CN As Connection
Dim rs As Recordset

In the Form_Load event, initialize the Connection object and the Recordset object. Set the Provider and the ConnectionString properties of the Connection object to required values as done earlier. Set the CursorLocation, LockType, and CursorType properties of the Recordset object to adUseServer, adLockOptimistic, and adOpenDynamic respectively. Now call the Open method of the Recordset object by passing the SQL statement and the name of the Connection object as arguments. Next you need to bind the Recordset object with Text property of the text boxes by initializing the DataSource and the DataField properties. The code for the Form_Load event is given below.

Private Sub Form_Load()
Set CN = New Connection
Set rs = New Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic

With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open
End With
rs.Open “SELECT * FROM AccountsTable”, CN

Set mcode.DataSource = rs
Set mname.DataSource = rs

mcode.DataField = “AccountCode”
mname.DataField = “AccountName”

End Sub

There are two ways to present data to the user. You can manually place the contents of the current record’s fields in appropriate controls, such as a text box, or you can bind the controls to the Recordset object.

Referencing Fields in a Recordset

In order to show a record’s value to the user, you must reference the fields of the recordset. The most efficient technique is to reference the field name directly. You can use the Fields collection, which is a more explicit code. But it is less efficient. The following two examples give the most efficient code and lesser efficient code.

Most efficient

mcode.text=rs!AccountCode
mname.text=rs!AccountName

Explicit, but less efficient

mcode.text=rs Fields(“AccountCode”).value
mname.text=rs Fields(“AccountName”).value

Manually Populating Controls

After a recordset has been created, you can manually reference fields of the recordset in order to present its values to the user. For example, if the Visual Basic form you are using has two text boxes, one to display the account’s code and a second to display the account’s name, you can manually populate the text boxes. This process must occur each time the user navigates to a new record.

Create a procedure FillControls and place the code as given below.

Sub FillControls()
mcode.text=rs!AccountCode
mname.text=rs!AccountName
End Sub

Binding controls to a Recordset

Recent addition to Visual Basic is the ability to bind controls to objects. This is similar to the technique that you use to bind controls to an ActiveX Data control using the Recordset object. You can bind any data-aware control to any field in a recordset. As the user navigates from record to record, the text box automatically shows the record’s values.

Set mcode.DataSource = rs
Set mname.DataSource = rs

mcode.DataField = “AccountCode”
mname.DataField = “AccountName”

Note: Use the binding technique as an alternative to writing a general procedure that manually populates controls on a form.

Assign the following code to the Click event of the command button, cmdFirst.

Private Sub CmdFirst_Click()
rs.MoveFirst
End Sub

Assign the following code to the Click event of the command button, cmdLast

Private Sub CmdLast_Click()
rs.MoveLast
End Sub

Write the following code in the Click event of the command button, cmdNext. The MoveNext method of the Recordset object moves the record pointer to the next record, that is, it makes the next record as the current record. This might cause a problem if the current record is the last record. In that case, the record pointer points beyond the last record. When the record pointer points beyond the last record, then the EOF property of the Recordset object is automatically set to True. To avoid such a situation, just after using the MoveNext method, you should check the value of the EOF property of the Recordset object. If the EOF contains TRUE, then the record pointer should be set to the last record by executing the MoveLast method of the Recordset object as shown below.

Private Sub CmdNext_Click()
rs.MoveNext
‘Check to see if tried to move beyond the last record
If rs.EOF = True Then
‘Moved beyond the last record
‘Set the user back to the last
rs.MoveLast
End If
End Sub

Write the following code in the Click event of the command button, cmdPrev

The MovePrevious method of the Recordset object moves the record pointer to the previous record, that is, it makes the previous record as the current record. This might cause a problem if the current record is the first record. In such a case, the record pointer points before the first record.

When the record pointer points before the first record, then the BOF property of the Recordset object is automatically set to True. To avoid such a situation, just after using the MovePrevious method, you should check the value of the BOF property of the Recordset object. If BOF returns True, then the record pointer should be set to the first record by executing the MoveFirst method of the Recordset object as shown below.

Private Sub CmdPrev_Click()
rs.MovePrevious
‘Check to see if tried to move prior to the first record
If rs.BOF = True Then
‘Moved prior to the first record
‘Set the user back to the first
rs.MoveFirst
End If
End Sub

To develop your own accounting application using visual basic, incorporating the above techniques like navigating a recordset, binding controls to a recordset, moving the pointers in a recordset, read this book titled : develop your own accounting application using visual basic