SQL Server Magazine

If you go to the Data Environment Designer, you can expand the data command to see the fields in a data command's recordset. You can open a field's properties and change the data command's caption and ActiveX control. You use these properties when you drag a data command to a form, as in this example.

Now, you can add the code to navigate through the recordset. When you create a data command, VB will automatically create a recordset. The recordset name will be rs plus the name of the data command. In this example, the recordset name is rsCustomers.

You can access the recordset through the Data Environment object. In this example, you use this syntax in the cmdFirst_Click event to move to the first record:

Private Sub cmdFirst_Click()
envNorthWind.rsCustomers.MoveFirst
End Sub

To complete the navigation buttons, add the following code in the event subroutine for the respective button:

Private Sub cmdLast_Click()
envNorthWind.rsCustomers.MoveLast
End Sub

Private Sub cmdNext_Click()
envNorthWind.rsCustomers.MoveNext
End Sub

Private Sub cmdPrevious_Click()
envNorthWind.rsCustomers.Move
Previous
End Sub

Use error handling in your applications to prevent errors such as reaching the end or beginning of the recordset. You can use the EOF or BOF properties of the recordset to detect this error. For example, to check for the end of recordset when the user clicks the button, you can change the cmdNext_Click event to the following code:

If Not envNorthWind.rsCustomers.EOF 
Then envNorthWind.rsCustomers.MoveNext
End If

For a better approach, you can detect the end or beginning of the recordset and disable any navigation controls so users can't click them. This method provides better feedback than letting the users click the button, then letting them find out that they are at the end of the recordset.

The data command's default setting is read-only, lock type, which prevents updates to the recordset. You can use the Advanced page in the data command's Properties dialog box to change the lock type, cursor type, and other parameters. If you change the lock type to Optimistic, you can update a record by navigating to another record after you've changed anything in the recordset. You must have update access to the database to make any updates.

Now that you have an updateable recordset, you can add an Update button to the form. Add the button and place this code in Click event:

Private Sub cmdUpdate_Click()
envNorthWind.rsCustomers.Update
End Sub

The Update method updates the current record. The Data Environment recordset exposes the properties of the underlying ADO recordset. You can use the recordset's methods and properties to control the recordset.

Fast and Easy
VB's Data Environment lets you quickly develop applications that you can easily maintain. Most wizards and other tools help you to develop applications quickly, but they don't help you maintain the application. You can use the data command to centralize application changes, which simplifies mantenance tasks. Also, creating the data command is a straightforward task.

Next month, I'll show you how to use the Data Environment and ActiveX to build parameterized queries and more robust database applications.

End of Article

Prev. page     1 [2]     next page -->