Visual Basic 6 Database How-To

Previous chapterNext chapterContents


- 1 -
Accessing a Database with Bound Controls


The Microsoft Jet database engine, supplied with Visual Basic, gives you the ability to access many types of databases--Microsoft Access databases; other PC-based databases such as dBASE, FoxPro, Paradox, and Btrieve; and any relational database that supports the open database connectivity (ODBC) standard. Visual Basic provides two basic techniques for working with the Jet database engine: the data control and the data access objects (DAO). The data control requires less code, but data access objects are much more flexible. This chapter shows you how to use the data control to perform common database operations. Chapter 2, "Accessing a Database with Data Access Objects," describes the use of data access objects.


VISUAL BASIC TERMINOLOGY PRIMER

If you're new to database programming, many Visual Basic terms might be new to you. Visual Basic works with all databases through a recordset consisting of all the records in a table or all the records satisfying a particular Structured Query Language (SQL) SELECT statement. A SELECT statement asks the database to retrieve specified database fields from one or more database tables in which record fields meet certain criteria. SQL itself is discussed in Chapter 3, "Creating Queries with SQL."

The programmer's interaction with the user is through visual controls placed on the form for data entry, command buttons, menus, labels, list boxes, and so on. The most common controls are text boxes for entering data, command buttons for getting the program to do useful work, menus, and labels to describe the other controls. List boxes and combo boxes allow the program to provide the user with multiple selections for text entry.

Most visual controls, including text, list, and combo boxes, can be bound to a data source for automatic display of data or have a special data-bound version. Binding is the process of connecting the data in a visual control to a field in a recordset. The most common binding method is the data control. The data control has a visual interface to support data movement through the records and a recordset object to manage the interface to the database engine. The data control component also supports several methods and properties for programmatic or design-time control. A component is simply a "piece part" used to build a Visual Basic application. A method is equivalent to a function call to the component to get the component to do useful work. A property is a data element of the component that helps control its behavior. For example, the data control has a DatabaseName property to tell it where the database can be found and a Move method to move the visual control around on the form. In addition, the data control exposes all the methods and properties of its contained recordset object.


All examples in this chapter use existing Microsoft Access database files delivered with Visual Basic (later chapters demonstrate how to create a database with Visual Basic). The techniques, however, apply to all the databases that Visual Basic can access through the Jet engine. In addition, the Enterprise Edition remote data control uses very similar techniques for direct use with ODBC databases. The remote data control bypasses the Jet engine and usually delivers faster performance than access through the Jet engine.

1.1 Browse a Recordset Using Bound Controls

One of the most fundamental operations in database work is the user's ability to browse through records in an existing database and modify data. In this How-To, you'll use the data control, bind its fields to some text boxes, and write one line of executable code to browse a database.

1.2 Validate Data Entered into Bound Controls

People make data entry errors, and an industrial-strength application anticipates and traps those errors before the data entry errors corrupt the integrity of the database. This How-To shows how to trap and respond to entry errors when you're using the data control and bound visual controls.

1.3 Allow People to Undo Changes They've Made in Bound Controls

Sometimes people catch their own mistakes. In this How-To, you'll learn how to enable them to undo those mistakes when the application uses the data control.

1.4 Add and Delete Records Using Bound Controls

A database is fairly useless without some means of adding and deleting records. In this How-To, you'll see how to add and delete records with bound controls.

1.5 Create and Use Bound Lists

One way to reduce data entry errors--and make people's lives a bit easier--is to provide people with lists from which they can choose appropriate values for database fields. Visual Basic 6 provides the DBCombo and DBList controls that make this easy to do. In this How-To, you'll use the DBCombo control to display suggested field values.

1.6 Display Many Detail Records for a Single Master Record

Frequently, you need to work with related records at the same time in a master-detail relationship. You might want to show an invoice header and all its detail lines or show all the orders for a particular product. This How-To shows how the DBGrid control can place multiple detail records on a form for each master record.

1.7 Change Data in Data-Bound Grid Cells from Code

The master-detail grid looks great, but some applications require the capability to expand and edit grid data from the main form. This How-To walks through a form that edits DBGrid data from the form's code.

1.8 Gracefully Handle Database Errors

Whenever you're working with disk files, unanticipated errors can occur. Your Visual Basic database program should handle errors gracefully. This How-To shows how.


FINDING THE SAMPLES

All the How-To's in this book are on the accompanying CD-ROM. After you install the source code, you will find a directory for each chapter; and within each chapter directory there is a directory for each How-To. The steps of each How-To start with an opportunity to preview the completed How-To from your installation directory. If you decide to work through a How-To in its entirety, we assume that you are working in a separate work area on your computer.


1.1 How do I...

BROWSE A RECORDSET USING BOUND CONTROLS?

Problem

I need to see the records in a database, but I don't want to write a lot of code. How can I do this with Visual Basic?

Technique

The Visual Basic data control object, in conjunction with data-bound controls, allows you to browse records in a supported database without writing a single line of code.

To use the data control, place it on your form and set two properties: DatabaseName, which specifies the database to which it will be linked, and RecordSource, which designates the source of data within the database. Add a text box to your form for every database field you want to access from the RecordSource, and bind each text box to the data control object and RecordSource field.


COMPATIBLE DATABASES

Databases that are compliant with the Visual Basic data control--and with Visual Basic data access objects, discussed in Chapter 2--include Microsoft Access, dBASE, FoxPro, Paradox, Btrieve, and any other database products that support the ODBC standard. Most relational database products for desktop systems and multiuser systems support ODBC. The examples throughout this book use Microsoft Access databases, except for those in Chapters 6, "Connecting to an ODBC Server," and 7, "SQL Server Databases and Remote Data Objects," which relate specifically to other database products. Virtually all the examples in the book (except for those in Chapters 6 and 7) can be applied to any of the database products.


When you work with Microsoft Access databases, DatabaseName is the name of a Microsoft Access database file. When you work with other database products, what constitutes "the database" depends on the type of database--for dBASE, Paradox, and FoxPro databases, for example, DatabaseName is the name of the directory in which data files are stored. RecordSource can also be a table or a SQL SELECT statement. Microsoft Access also allows you to specify the name of a query stored within the database as the RecordSource.

The data control not only provides the link between your form and the database, but it also provides tools for navigating through the database. Figure 1.1 shows a data control. The Next Record and Previous Record buttons move you through the database one record at a time. The First Record and Last Record buttons move you quickly to the beginning or end of the database.

Figure 1.1 The data control.

Steps

To preview this How-To, open the project BrowseBound.VBP in the Chapter01\HowTo01 directory. Change the DatabaseName property of the data control datEmployees to point to the copy of NWind.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. The form shown in Figure 1.2 appears. Use the buttons on the data control to view records in the Titles table of NWind.MDB.

Figure 1.2 The Bound Browser form.

1. Create a new project in your work area called BrowseBound.VBP. Use Form1 to create the objects and properties listed in Table 1.1, and save the form as BrowseBound.FRM. Substitute the path to your copy of NWIND.MDB for the DatabaseName property of datEmployees.

Table 1.1 Objects and properties for the Bound Browser form.

OBJECT Property Setting
Form Name Form1
Caption "Bound Browser"
Data Name datEmployees
Caption "Employees"
DatabaseName "D:\Program Files\Microsoft Visual Studio\VB6\NWIND.MDB"
RecordSource "Employees"
TextBox Name txtEmpLastName
DataField "LastName"
DataSource "datEmployees"
TextBox Name txtEmpFirstName
DataField "FirstName"
DataSource "datEmployees"
TextBox Name txtBirthDate
DataField "BirthDate"
DataSource "datEmployees"
TextBox Name txtEmployeeId
DataField "EmployeeID"
DataSource "datEmployees"
Enabled False
Label Name Label1
Caption "Employee:"
Label Name Label2
Caption "Birth Date:"
Label Name Label3
Caption "Employee ID:"

2. Use the Visual Basic Menu Editor to create the menu shown in Table 1.2.

Table 1.2 Menu specifications for the Bound Browser.

CAPTION Name Shortcut Key
&File mnuFile
----E&xit mnuFileExit

3. Enter the following code as the Click event for mnuExit:

Private Sub mnuFileExit_Click()
    Unload Me
End Sub

How It Works

When the application starts, the data control opens the NWind.MDB database, creates a recordset from the Titles table, and displays values from the first record of the recordset in the form's bound controls. A recordset is a Visual Basic object used to manipulate the contents of a database. Bound controls are visual interface controls such as text boxes that people can see on the screen but that are also linked, or bound, to fields managed by a data control's recordset. Recordsets provide methods for moving between records, as well as for adding, updating, and deleting records. When users click on one of the data control's record navigation buttons, the data control positions the record pointer to the selected record and updates the bound controls with the values from the new record.

Under the covers, the data control is working hard. You see a screen form with text boxes. Figure 1.3 shows the main interactions between bound text boxes, the data control, and the data control's recordset. Every time the data control moves to a different record, it checks for changed data between the bound controls and the recordset fields. If changes are found, the data control moves the data to the fields and performs an automatic update to the recordset and the underlying database. Finally, the data control retrieves the desired record from the database and copies the field data to text controls for display. In the remainder of this chapter, you'll explore the data control's events and methods to build solid applications with very little work.

Figure 1.3 Under the data control's covers.

Comments

This is truly code-free development. The only executable line of code closes the application. However, this is a very limited application; there's no way to validate entries, add records, or delete records. To perform these operations, some code is necessary--not a lot of code, but some code nonetheless. The following How-To's show how to add these functions to this simple beginning.

1.2 How do I...

Validate data entered into bound controls?

Problem

The data control and bound controls provide low-code database access. But I need to verify that entered form data is valid before I update the database. How can I check entered data when I'm using bound controls?

Technique

Each time you change the current record in a recordset attached to a data control--by moving to a different record, deleting the current record, or closing the recordset--Visual Basic triggers the data control's Validate event. You can write an event subroutine to check any changes made to data in bound controls.

The Validate event subroutine receives two arguments:

In your event subroutine, you can check the value of Save. If it is True, you can then check each entry to verify that it falls within the bounds of what is legal in your application. If any entry is not legal, you can set the Action argument to the built-in constant dbDataActionCancel, which cancels the event that caused the Validate event. For example, if the Validate event was triggered by clicking on the data control to move to a different record, setting Action to dbDataActionCancel cancels the Move event and leaves the data control positioned on the original record. Your Validate event subroutine should also display a problem message so that the entry can be corrected.

Steps

Open the project ValidateBound.VBP in the Chapter01\HowTo02 directory to preview the results of this How-To. Change the DatabaseName property of the data control datEmployees to point to the copy of NWind.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. A form similar to that shown previously in Figure 1.2 appears. Use the buttons on the data control to view records in the Employees table of NWind.MDB. Select all the text in the Employee and Birth Date boxes and delete it; then try to move to another record. You'll see an error message like the one shown in Figure 1.4, informing you that you must enter a last name, first name, and birth date. Choose the File | Exit menu option to close the project.

1. Create a new project called ValidateBound.VBP in your work area. Use Form1 to create the objects and properties listed earlier in Table 1.1, and save the form as ValidateBound.FRM. (Note that this is the same form used for How-To 1.1.) Substitute the path to your copy of NWind.MDB for the DatabaseName property of datEmployees. Use the Visual Basic Menu Editor to create the menu shown earlier in Table 1.2.

2. Add the file clsUtility.cls to your project from the Chapter01\HowTo02 directory by selecting Project | Add File from the main menu or by pressing Ctrl+D on the keyboard. Use the File common dialog to select the file.

Figure 1.4 The Validate Bound form.

3. Add the following code to the declarations section of Form1. The Utility class is used to tie MsgBox strings together in a common place.

Private Utility As New clsUtility
Private mblnValidationFailed As Boolean


4. Enter the following code into Form1 as the Validate event for the datEmployees data control. This code checks to make sure that valid data have been entered into all controls. If there are any invalid data, the subroutine displays an error message, cancels the Validate event, and sets the form-level variable mblnValidationFailed to True.

Private Sub datEmployees_Validate(Action As Integer, Save As Integer)
    Dim strMsg As String
    Dim enumMsgResult As VbMsgBoxResult
    If Save = True Or Action = vbDataActionUpdate _
              Or Action = vbDataActionUnload Then
        ` One or more bound controls has changed or the form
        ` is being unloaded, so verify that all fields have
        ` legal entries. If a field has an incorrect value,
        ` append a string explaining the error to strMsg and
        ` set the focus to that field to facilitate correcting
        ` the error. We explain all errors encountered in a
        ` single message box.
        strMsg = ""
        If txtEmpLastName.Text = "" Then
             Utility.AddToMsg strMsg, _
                "You must enter a last name."
             txtEmpLastName.SetFocus
        End If
        If txtEmpFirstName.Text = "" Then
             Utility.AddToMsg strMsg, _
                "You must enter a first name."
             txtEmpFirstName.SetFocus
        End If
        If Not IsDate(txtBirthDate.Text) Then
             Utility.AddToMsg strMsg, _
                "You must enter a birth date."
             txtBirthDate.SetFocus
        Else
            If CDate(txtBirthDate.Text) >= Date Then
                 Utility.AddToMsg strMsg, _
                    "Birth date must be in the past."
                 txtBirthDate.SetFocus
            End If
        End If
        If strMsg <> "" Then
             ` We have something in the variable strMsg, which
             ` means that an error has occurred. Display the
             ` message. The focus is in the last text box where
             ` an error was found.
             MsgBox strMsg, vbExclamation
             ` Cancel the Validate event
             Action = vbDataActionCancel
             ` Deny form Unload until fields are corrected
             mblnValidationFailed = True
         Else
             mblnValidationFailed = False
         End If
    End If
End Sub


5. Enter the following code into Form1 as the Unload event. If the Validate event has set the UpdateCancelled flag, this procedure cancels the Unload event.

Private Sub Form_Unload(Cancel As Integer)
    ` Don't allow the unload until the data is validated.
    If mblnValidationFailed Then Cancel = True
End Sub


6. Enter the following code as the Click event for mnuExit:

Private Sub mnuExit_Click()
    Unload Me
End Sub

How It Works

Each time the Validate event is called, the contents of the controls are checked to make sure that they contain valid data. If they do not, the Validate event is cancelled. This prevents the record from being saved with invalid data. The validation event procedure makes use of a "helper" utility class to append multiple messages from each error check to the displayed results. Displaying all validation errors at once is a good design technique because it reduces frustration for the user.

When the form is unloaded, the contents of bound controls are automatically saved through the data control. And that means that the Validate event gets called. If a control has invalid data, the Validate event is cancelled, but that does not in itself cancel the Form Unload event. Therefore, the Validate event sets a form-level flag variable, mblnValidationFailed, which the Form Unload procedure checks. If mblnValidationFailed is true, the Form Unload event is cancelled and the application does not terminate.

Comments

The validating browse form helps control data entry errors, but it is unforgiving without a cancellation option to undo form changes. After a field has been changed on this form, valid data must be entered before the user can change records or exit the application. Clearly, there should be a better way--and there is.

1.3 How do I...

Allow users to undo changes they've made in bound controls?

Problem

I want my form to have the capability to undo changes made to a record before the record is saved. How can I accomplish this when I'm using bound controls?

Technique

Your form gains the capability to undo changes to the current record by using the UpdateControls method of the data control. This method causes Visual Basic to reread the current record from the database file and refresh the value of each bound control with the respective field value from the database. Simply execute this method and any bound control changes are overwritten with the original data from the database.

Steps

Open the project UndoBound.VBP to preview this How-To. Change the DatabaseName property of the data control datEmployees to point to the copy of NWind.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. The form shown in Figure 1.5 appears. Use the buttons on the data control to view records in the Employees table of NWind.MDB. Make a change in a record. Before you move to another record, select Edit | Undo. You'll see your changes "backed out" of the form.

Figure 1.5 The Undo Bound form.

1. Create a new project called UndoBound.VBP. Use Form1 to create the objects and properties listed earlier in Table 1.1, and save the form as UndoBound.FRM. (Note that this is the same form used for How-To's 1.1 and 1.2.) Substitute the path to your copy of NWind.MDB for the DatabaseName property of datEmployees. Use the Visual Basic menu editor to create the menu shown in Table 1.3.

Table 1.3 Menu specifications for UndoBound.FRM.

CAPTION Name Shortcut Key
&File mnuFile
----E&xit mnuFileExit
&Edit mnuEdit
----&Undo mnuEditUndo Ctrl+Z

2. Add the file clsUtility.cls to your project from the Chapter01\HowTo03 directory by selecting Project | Add File from the main menu or by pressing Ctrl+D on the keyboard. Use the File common dialog to select the file.

3. Add the following code to the declarations section of Form1:

Private Utility As New clsUtility
Private mblnValidationFailed As Boolean


4. Enter the following code into Form1 as the Validate event for the datEmployees data control. (Note the changes from How-To 1.2 highlighted in bold.) This code checks to make sure that valid data have been entered into all controls. If there are any invalid data, the subroutine displays an error message and asks for an OK or a Cancel response. An OK response cancels the Validate event and sets the form-level variable mblnValidationFailed to True. A Cancel response retrieves the database values to the bound form controls and backs out the changes.

Private Sub datEmployees_Validate(Action As Integer, Save As Integer)
    Dim strMsg As String
    Dim enumMsgResult As VbMsgBoxResult
    If Save = True Or Action = vbDataActionUpdate _
              Or Action = vbDataActionUnload Then
        ` One or more bound controls has changed or the form
        ` is being unloaded, so verify that all fields have
        ` legal entries. If a field has an incorrect value,
        ` append a string explaining the error to strMsg and
        ` set the focus to that field to facilitate correcting
        ` the error. We explain all errors encountered in a
        ` single message box.
        strMsg = ""
        If txtEmpLastName.Text = "" Then
             Utility.AddToMsg strMsg, _
                "You must enter a last name."
             txtEmpLastName.SetFocus
        End If
        If txtEmpFirstName.Text = "" Then
             Utility.AddToMsg strMsg, _
                "You must enter a first name."
             txtEmpFirstName.SetFocus
        End If
        If Not IsDate(txtBirthDate.Text) Then
             Utility.AddToMsg strMsg, _
                "You must enter a birth date."
             txtBirthDate.SetFocus
        Else
            If CDate(txtBirthDate.Text) >= Date Then
                 Utility.AddToMsg strMsg, _
                     "Birth date must be in the past."
                 txtBirthDate.SetFocus
            End If
        End If
        If strMsg <> "" Then
             ` We have something in the variable strMsg, which
             ` means that an error has occurred. Display the
             ` message. The focus is in the last text box where
             ` an error was found
             enumMsgResult = MsgBox(strMsg, _
                             vbExclamation + vbOKCancel + 
                             vbDefaultButton1)
             If enumMsgResult = vbCancel Then
                 ` Restore the data to previous values using the
                 ` data control
                 datEmployees.UpdateControls
                 ` Allow form unload.
                 mblnValidationFailed = False
             Else
                 ` Cancel the Validate event
                 Action = vbDataActionCancel
                 ` Deny form unload until fields are corrected
                 mblnValidationFailed = True
             End If
         Else
             mblnValidationFailed = False
         End If
    End If
End Sub


5. Enter the following code into Form1 as the Unload event. (This code is the same as that for the identically named procedure in How-To 1.2.) If the Validate event has set the UpdateCancelled flag, this procedure cancels the Unload event.

Private Sub Form_Unload(Cancel As Integer)
    ` Don't allow the unload until the data is validated or the
    ` update is cancelled
    If mblnValidationFailed Then Cancel = True
End Sub


6. Enter the following code as the Click eventfor mnuEditUndo:

Private Sub mnuEditUndo_Click()
    ` Undo all pending changes from form by copying recordset
    ` values to form controls
    datEmployees.UpdateControls
End Sub


7. Enter the following code as the Click event for mnuExit. (This code is the same as that for the identically named procedure in How-To 1.2.)

Private Sub mnuExit_Click()
    Unload Me
End Sub

How It Works

The mnuEditUndo_Click procedure allows for removing any pending changes from the database by using the data control's UpdateControls method. This method takes the copy of the field data from the data control's recordset and "updates" the displayed bound controls. Remember from Figure 1.3 that there are constantly two copies of all data in a data control application--the copy on the screen fields (in the bound controls) and the copy in the data control's recordset fields. Data is moved from the bound controls to the recordset fields during an update but only after validation is successful. So no matter how much the data on the screen has changed, nothing happens until the recordset gets updated. (In this application so far, a recordset is updated only when the data control is moved from one record to another.)

Another useful enhancement in this version of the program is the use of a Cancel response from the validation error message box to refresh the screen display automatically without making the user make a menu selection. Figure 1.6 shows the modified error message box. If the response indicates a cancellation, the validation is cancelled and the data values are restored from the database to the bound controls.

Figure 1.6 The Cancel button added to an error message box.

The validation event procedure (in step 4) makes extensive use of Visual Basic constants such as vbDataActionCancel and vbCancel rather than numeric constants to improve the ability of programmers to understand the code. Values for constants can easily be found by pressing the F2 key from within Visual Basic to bring up the Object Browser window from which constants can be copied and pasted into your code. Declaring enumMsgResult as a VbMsgBoxResult type shows the use of strong typing to help make the program's meaning clearer to subsequent programmers.

Comments

Even though you can update, validate, and undo changes to your employee records, you still can't hire or fire anyone with the information you have so far. Let's complete the core application by adding the add and delete functions.

1.4 How do I...

Add and delete records using bound controls?

Problem

How do I add and delete records when I'm using bound controls?

Technique

To add a record to the recordset of a data control, use the AddNew method of the recordset established by the data control. Visual Basic sets all bound controls to their default values (as determined by the table definition in the database you're accessing) and makes the new record the current record. After all data has been entered into the bound controls, Visual Basic creates a new record in the table and fills it with the values from the controls. Visual Basic knows that data entry is complete when you move to a different record, you add another new record, or your code executes the recordset's Update method. All records get added to the end of the data control's recordset.

If you make changes to an existing record and then unload the form, Visual Basic automatically updates the recordset with your changes. When you add a record, enter data into the record, and then either add another record or move to an existing record, Visual Basic automatically saves the new record. However, if you add a record, enter data into the new record, and then unload the form--before you move to another record--Visual Basic does not automatically save the new record. If you want to save the new record, you can invoke the Recordset object's Update method from the form's Unload event. The Update method saves the data in the form's bound controls to the corresponding fields in the recordset.

To delete the currently displayed record from the database, use the data control recordset's Delete method. Visual Basic deletes the current record from the database. It does not, however, move to a new record or update the controls. You must do this through your code by using one of the four Move methods: MoveFirst, MoveLast, MovePrevious, or MoveNext. If you do not move to a new record after executing the Delete method, there will be no current record. Visual Basic will, therefore, generate an error when you try to perform any operation on the current record.

Steps

Preview the project AddDeleteBound.VBP. Change the DatabaseName property of the data control datEmployees to point to the copy of NWind.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. The form shown in Figure 1.7 appears. Select Data | Add Record. Enter some representative values into the fields. Move to another record or select Data | Save Record from the main menu. Move to the last record in the recordset by clicking the >| button on the data control. You should see the record you just added. Select Data | Delete Record. Move to the last record in the recordset again. The record you added should be gone.

1. Create a new project called AddDeleteBound.VBP. Use Form1 to create the objects and properties listed earlier in Table 1.1, and save the form as AddDeleteBound.FRM. Substitute the path to your copy of NWind.MDB for the DatabaseName property of datEmployees. You might find it easier to start from the UndoBound.VBP form from How-To 1.3. Use the Visual Basic menu editor to create the menu shown in Table 1.4.

Figure 1.7 The Bound Add/Delete/Update form.

Table 1.4 Menu specifications for the Bound Add/Delete form.

CAPTION Name Shortcut Key
&File mnuFile
----E&xit mnuFileExit
&Edit mnuEdit
----&Undo mnuEditUndo Ctrl+Z
&Data mnuData
----&Add Record mnuDataAdd
----&Delete Record mnuDataDelete

----&Save Record mnuDataSave
2. Add the file clsUtility.cls to your project from the Chapter01\HowTo03 directory by selecting Project | Add File from the main menu or by pressing Ctrl+D on the keyboard. Use the File common dialog to select the file.

3. Add the following code to the declarations section of Form1:

Private Utility As New clsUtility
Private mblnValidationFailed As Boolean


4. Add the following code as the Validate event of the data control datEmployees. (This code is the same as that for the identically named procedure in How-To 1.3, except for the code in bold.) The Validate event is called every time the current record changes, when the form is unloaded, and when the Update method is invoked. This procedure verifies that all entries meet the requirements of the application when data in bound controls have been changed. If an entry is incorrect, the routine cancels the Validate event and sets the form-level flag variable mblnValidationFailed.

Private Sub datEmployees_Validate(Action As Integer, _
        Save As Integer)
    Dim strMsg As String
    Dim enumMsgResult As VbMsgBoxResult
    If Save = True Or Action = vbDataActionUpdate _
    Or Action = vbDataActionUnload _
    Or Action = vbDataActionAddNew Then
        ` One or more bound controls has changed or the form
        ` is being unloaded, so verify that all fields have
        ` legal entries. If a field has an incorrect value,
        ` append a string explaining the error to strMsg and
        ` set the focus to that field to facilitate correcting
        ` the error. We explain all errors encountered in a
        ` single message box.
        strMsg = ""
        If txtEmpLastName.Text = "" Then
             Utility.AddToMsg strMsg, _
                 "You must enter a last name."
             txtEmpLastName.SetFocus
        End If
        If txtEmpFirstName.Text = "" Then
             Utility.AddToMsg strMsg, _
                 "You must enter a first name."
             txtEmpFirstName.SetFocus
        End If
        If Not IsDate(txtBirthDate.Text) Then
             Utility.AddToMsg strMsg, _
                 "You must enter a birth date."
             txtBirthDate.SetFocus
        Else
            If CDate(txtBirthDate.Text) >= Date Then
                 Utility.AddToMsg strMsg, _
                     "Birth date must be in the past."
                 txtBirthDate.SetFocus
            End If
        End If
        If strMsg <> "" Then
             ` We have something in the variable strMsg, which 
             ` means that an error has occurred. Display the 
             ` message. The focus is in the last text box where an 
             ` error was found
             enumMsgResult = MsgBox(strMsg, _
                    vbExclamation + vbOKCancel + vbDefaultButton1)
             If enumMsgResult = vbCancel Then
                 ` Restore the data to previous values using 
                 ` the data control
                 datEmployees.UpdateControls
                 mblnValidationFailed = False
             Else
                 ` Cancel the Validate event
                 Action = vbDataActionCancel
                 ` Deny form Unload until fields are corrected
                 mblnValidationFailed = True
             End If
         Else
             ` Allow form unload
             mblnValidationFailed = False
             ` Disable the Save menu
             mnuDataSave.Enabled = False
         End If
    End If
End Sub


5. Enter the following code as the Click method of the Edit | Undo menu item. If the user chooses Undo while adding a new record, the subroutine uses the Recordset object's CancelUpdate method to cancel the pending AddNew operation. If the user clicks the menu item while editing an existing record, the procedure updates the form's controls by filling them with the current values from the recordset.

Private Sub mnuEditUndo_Click()
    ` Undo all pending changes from form by copying recordset 
    ` values to form controls
    datEmployees.UpdateControls
    If datEmployees.Recordset.EditMode = dbEditAdd Then
        ` Disable the menu save and cancel the update
        datEmployees.Recordset.CancelUpdate
        mnuDataSave.Enabled = False
    End If
End Sub


6. Add the following code as the Click event of the Data menu's Add Record item. This subroutine uses the Recordset object's AddNew method to prepare the form and the recordset for the addition of a new record.

Private Sub mnuDataAdd_Click()
    ` Reset all controls to the default for a new record
    ` and make space for the record in the recordset copy
    ` buffer.
    datEmployees.Recordset.AddNew
    `Enable the Save menu choice
    mnuDataSave.Enabled = True
    ` Set the focus to the first control on the form
    txtEmpLastName.SetFocus
End Sub


7. Add the following code as the Click event of the Data menu's Delete Record item. The procedure confirms that the user wants to delete the record and then deletes it. It then ensures that the record pointer is pointing at a valid record.

Private Sub mnuDataDelete_Click()
    Dim strMsg As String
    `Verify the deletion.
    strMsg = "Are you sure you want to delete " _
            & IIf(txtEmpLastName.Text <> "", _
                txtEmpLastName.Text, _
                "this record") & "?"
    If MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton2) = _
        vbYes Then
        ` We really want to delete
        datEmployees.Recordset.Delete
        ` Make a valid record the current record and 
        ` update the display.
        datEmployees.Recordset.MoveNext
        ` If we deleted the last record, move to the new last 
        ` record because the current record pointer is not defined 
        ` afterdeleting the last record, even though EOF is 
        ` defined.
        If datEmployees.Recordset.EOF Then 
        datEmployees.Recordset.MoveLast
    End If
End Sub


8. Add the following code as the Click event of the Data menu's Save Record item. The Save Record subroutine uses the Update method of the Recordset object to write the values in the form's bound controls to their respective fields in the recordset. The If statement prevents a recordset Update without a preceding AddNew or Edit.

Private Sub mnuDataSave_Click()
    ` Invoke the update method to copy control contents to
    ` recordset fields and update the underlying table
    datEmployees.Recordset.Update
    If datEmployees.Recordset.EditMode <> dbEditAdd Then
        ` If we added move to the new record
        datEmployees.Recordset.MoveLast
   End If
End Sub


9. Add the following code as the Click event of the File menu's Exit item. (This code is the same as that for the identically named procedure in How-To 1.2.)

Private Sub mnuFileExit_Click()
    Unload Me
End Sub


10. Add the following code as the form's Unload event. If the data currently in the bound controls is invalid, the procedure cancels the Unload event. If the data is valid and an add-record operation is in progress, the code invokes the Update event to save the data.

Private Sub Form_Unload(Cancel As Integer)
    ` Don't allow the unload until the data is valid or the
    ` update is cancelled
    If mblnValidationFailed Then Cancel = True
End SubEnd Sub

How It Works

Including record addition and deletion has made the data control program more complex, but it now looks like a real database application. A Data menu allows the user to explicitly control the data control's recordset activities through the appropriate click procedures. The Data Add Record procedure (step 6) adds a new, blank record to the data control's recordset. The data control is automatically positioned on the new record. The Data Save procedure (step 8) updates the recordset and moves to the last record (the new record) if the current action is a record addition. The Data Save Record menu choice is also managed explicitly by the program during record additions to provide clear feedback from the programmer about what is happening within the program.

Notice in the record deletion processing (step 7) that you have to manage the deletion of the last record carefully because the recordset object does not handle all changes without an error. In particular, deleting the last record can leave the recordset with "No current record." In this state, any update actions (potentially caused by a record movement) can cause an error in your application.


RUNTIME ERRORS

You should also note that you will receive a runtime error if you attempt to delete certain default records contained in the Employees database. The Employees table has a relationship with the Orders table within the same database, and the employee records you cannot delete have at least one entry in the Orders table. A runtime error will occur if you delete an employee record that has other data in the database because you would have entries in the Orders table that do not have a corresponding Employees record--which would result in a loss of data integrity in the database. To properly delete these records, you must delete the corresponding data in any other tables in the database. Refer to How-To 4.4 for information on defining and using relations between tables.


Comments

A Visual Basic data control maintains a record pointer into its RecordSource. The record pointer keeps track of where you are within the RecordSource. It always points to the current record--except when you move past the end or the beginning of the RecordSource.

You can move past the end of the RecordSource by clicking the Next Record button when the record pointer is positioned on the last record; similarly, you can move past the beginning of the RecordSource by clicking the Previous Record button when you are on the first record. The record pointer then points at a special location, known as the end of file (EOF) or the beginning of file (BOF). When you are on EOF or BOF, there is no current record. If you try to delete or edit the record when you are on EOF or BOF, Visual Basic generates an error message. EOF and BOF are useful when you use data access objects for checking to see when you've reached the end or beginning of a RecordSource; but when you use the data control, you generally don't want to stay on EOF or BOF.

For this reason, Visual Basic gives you a choice of what to do when your data control reaches EOF or BOF. You execute this choice by setting the BOFAction and EOFAction properties. The possible settings for each property are shown in Table 1.5.

Table 1.5 The EOFAction and BOFAction properties of the data control.

PROPERTY Description Result
BOFAction 0 - MoveFirst (default) Positions the record pointer on the first record.
1 - BOF Positions the record pointer on BOF.
EOFAction 0 - MoveLast (default) Positions the record pointer on the last record.
1 - EOF Positions the record pointer on EOF.
2 - AddNew Adds a new record at the end of the RecordSource and positions the record pointer on it.

The Visual Basic data control does not handle empty recordsets well; trying to move to another record generates an error. The only thing you can do with a bound, empty recordset is to add a new record. When you open an empty recordset, its EOF property is initially set to True. If you have the data control's EOFAction property set to AddNew, when you open an empty recordset Visual Basic immediately adds a record. This is a low-cost, no-code way to prevent empty recordset errors when working with bound controls.

1.5 How do I...

Create and use bound lists?

Problem

Many tables in my database have fields that are related to other tables. I need to restrict entry into these fields to values that exist in the related tables. At the same time, I'd like to make it easy to select valid entries for these fields. How do I accomplish this when I'm using bound controls?

Technique

Assume that you have a warehouse application. You have two tables: Products and Categories. The Products table defines available products:

ProductID

ProductName

SupplierID

CategoryID

QuantityPerUnit

UnitPrice

UnitsInStock

UnitsOnOrder

ReorderLevel

Discontinued

The Categories table defines product categories and is related to the Products table via the CategoryID field:

CategoryID

CategoryName

Description

Picture

You have a form that displays basic product information from the Products table and its related category. Because almost everybody has trouble remembering customer ID numbers, you want to provide the capability to designate the category by name. With a DBCombo or DBList control, people can choose a category name and have the control insert the category ID number corresponding to that category name into the Products table.

The DBList and DBCombo controls both display values in a list format. The DBList control creates a list box, with several lines always visible. The DBCombo control can create a drop-down list. They are both bound controls. Unlike with most bound controls, however, you bind them not to a single data control but to two data controls. The first data control maintains the recordset represented by the form as a whole--the data records you are browsing or editing. The second data control refers to the validation recordset, the recordset that is displayed in the list box or combo box. (You normally make the second data control--the data control that displays the values in the list--invisible, because people do not need to access it.)

In the example, one data control is linked to the Products table--the table into which category ID numbers are inserted. The other data control is linked to the Categories table--the source of the list. The table that is the source of the list must include both the information to be displayed (in this case, the information in the CategoryName field) and the value to be inserted into the other table (in this case, the CategoryID).

You link the DBCombo or DBList control to its recordsets by setting five properties. Two properties describe the recordset to be updated; they are shown in Table 1.6. The other three properties define the recordset that makes up the list; these appear in Table 1.7.

Table 1.6 DBList/DBCombo properties that describe the recordset to be updated.

PROPERTY Description
DataSource Name of the data control with the recordset to be updated
DataField Name of the field to be updated

Table 1.7 DBList/DBCombo properties that create the list.

PROPERTY Description
RowSource Name of the data control that provides the values to display in the list
ListField Name of the field with the values to display in the list
BoundColumn Name of the field with the value to be inserted in the table being updated


DBCOMBO STYLE

If you set the Style property of the DBCombo control to 2 (Dropdown List), the control acts exactly like a DBList control--except, of course, that it displays only a single item until you drop it down. You can't add new items to the list through the control.

If you want to give the user the opportunity to add new items, set Style to 0 (Dropdown Combo) or 1 (Simple Combo). Your code must handle the addition of the user's entry to the underlying row source; the control does not do this automatically for you.


Open the project ListBound.VBP to preview this How-To. Change the DatabaseName property of the data control datEmployees to point to the copy of NWind.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. The form shown in Figure 1.8 appears. Select Data | Add Record, and enter some representative values into the fields. Use the drop-down list to enter the publisher. When you move to another record, your new record is automatically saved.

1. Create a new project called ListBound.VBP. Use Form1 to create the objects and properties listed in Table 1.8, and save the form as LISTBND.FRM. Substitute the path to your copy of NWind.MDB for the DatabaseName property of datEmployees and datPublishers.

Table 1.8 Objects and properties for the Bound Lister form.

OBJECT Property Setting
Form Name Form1
Caption " Bound Lister "
Data Name datProducts
Caption "Products"
DatabaseName "D:\Program Files\Microsoft Visual Studio\VB6\Nwind.mdb"
RecordSource "Products"
Data Name datCategories
Caption "Categories"
DatabaseName "D:\Program Files\Microsoft Visual Studio\VB6\Nwind.mdb"
RecordSource "Categories"
TextBox Name txtProductName
DataField "ProductName"
DataSource "datProducts"
DBCombo Name dbcCategory
BoundColumn "CategoryID"
DataField "CategoryID"
DataSource "datProducts"
ListField "CategoryName"
RowSource "datCategories"
Label Name Label2
Caption "Category:"
Label Name Label1
Caption "Product Name:"

2. Use the Visual Basic menu editor to create the menu shown in Table 1.9.

Figure 1.8 The Bound Lister form.

Table 1.9 Menu specifications for the Bound Lister.

CAPTION Name Shortcut Key
&File mnuFile
----E&xit mnuFileExit
&Edit mnuEdit
----&Undo mnuEditUndo Alt+Backspace
&Data mnuData
----&Add Record mnuDataAdd
----&Delete Record mnuDataDelete

----&Save Record mnuDataSave
3. Add the following code to the declarations section of Form1:

Private Utility As New clsUtility
Private mblnValidationFailed As Boolean


4. Add the following code as the Validate event of the data control datProducts. (This code is very similar to the Validate Event code for How-To 1.4 with the exceptions of data control name and actual field-checking logic.) The Validate event is called every time the current record changes, when the form is unloaded, and when the Update method is invoked. This procedure verifies that when data in bound controls have been changed, all entries meet the requirements of the application. If an entry is incorrect, the routine cancels the Validate event and sets the form-level flag variable mblnValidationFailed.

Private Sub datProducts_Validate(Action As Integer, Save As Integer)
    Dim strMsg As String
    Dim enumMsgResult As VbMsgBoxResult
    If Save = True Or Action = vbDataActionUpdate _
    Or mblnValidationFailed Or Action = vbDataActionAddNew Then
        ` One or more bound controls has changed or a previous 
        ` validation failed, so verify that all fields have legal 
        ` entries. If a field has an incorrect value, append a 
        ` string explaining the error to strMsg and set the focus 
        ` to that field to facilitate correcting the error. We 
        ` explain all errors encountered in a single message box.
        strMsg = ""
        If txtProductName.Text = "" Then
             Utility.AddToMsg strMsg, _
                 "You must enter a Product name."
             txtProductName.SetFocus
        End If
        If strMsg <> "" Then
             ` We have something in the variable strMsg, which 
             ` means that an error has occurred. Display the 
             ` message. The focus is in the last text box where an 
             ` error was found
             enumMsgResult = MsgBox(strMsg, vbExclamation + _
                 vbOKCancel + vbDefaultButton1)
             If enumMsgResult = vbCancel Then
                 ` Restore the data to previous values using the 
                 ` data control
                 datProducts.UpdateControls
                 mblnValidationFailed = False
             Else
                 ` Cancel the Validate event
                 Action = vbDataActionCancel
                 ` Deny form Unload until fields are corrected
                 mblnValidationFailed = True
             End If
         Else
             mblnValidationFailed = False
         End If
    End If
End Sub


5. Enter the following code as the Click method of the Edit | Undo menu item. (This code is very similar to that for the identically named procedure in How-To 1.4, except for the reference to a different data control.) The procedure updates the form's controls by filling them with the current values from the recordset. If the user chooses Undo while adding a new record, the subroutine uses the Recordset object's CancelUpdate method to cancel the pending AddNew operation and turns off the data save menu item.

Private Sub mnuEditUndo_Click()
    ` Undo all pending changes from form by copying recordset 
    ` values to form controls
    datProducts.UpdateControls
    If datProducts.Recordset.EditMode = dbEditAdd Then
        ` Disable the menu save and cancel the update
        datProducts.Recordset.CancelUpdate
        mnuDataSave.Enabled = False
    End If
End Sub


6. Add the following code as the Click event of the Data menu's Add Record item. (This code is very similar to that for the identically named procedure in How-To 1.4.) This subroutine uses the Recordset object's AddNew method to prepare the form and the recordset for the addition of a new record. It also enables the Data | Save menu.

Private Sub mnuDataAdd_Click()
    ` Reset all controls to the default for a new record
    ` and make space for the record in the recordset copy
    ` buffer.
    datProducts.Recordset.AddNew
    `Enable the Save menu choice
    mnuDataSave.Enabled = True
    ` Set the focus to the first control on the form
    txtProductName.SetFocus
End Sub


7. Add the following code as the Click event of the Data menu's Delete Record item. (This code is very similar to that for the identically named procedure in How-To 1.4.) The procedure confirms that the user wants to delete the record, deletes the record, and then ensures that the record pointer is pointing at a valid record.

Private Sub mnuDataDelete_Click()
    Dim strMsg As String
    `Verify the deletion.
    strMsg = "Are you sure you want to delete " _
            & IIf(txtProductName.Text <> "", _
            txtProductName.Text, _
                "this record") & "?"
    If MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton2) = _
        vbYes Then
        ` We really want to delete
        datProducts.Recordset.Delete
        ` Make a valid record the current record and update the 
        ` display.
        datProducts.Recordset.MoveNext
        ` If we deleted the last record, move to the new last 
        ` record because the current record pointer is not defined 
        ` after deleting the last record, even though EOF is 
        ` defined.
        If datProducts.Recordset.EOF Then 
        datProducts.Recordset.MoveLast
    End If
End Sub


8. Add the following code as the Click event of the Data menu's Save Record item. (This code is very similar to that for the identically named procedure in How-To 1.4.) The Save Record subroutine uses the Update method of the Recordset object to write the values in the form's bound controls to their respective fields in the recordset.

Private Sub mnuDataSave_Click()
    ` Invoke the update method to copy control contents to
    ` recordset fields and update the underlying table
    datProducts.Recordset.Update
    If datProducts.Recordset.EditMode <> dbEditAdd Then
        ` If we added move to the new record
        datProducts.Recordset.MoveLast
   End IfEnd Sub


9. Add the following code as the Click event of the File menu's Exit item. (This code is the same as that for the identically named procedure in How-To 1.4.)

Private Sub mnuFileExit_Click()
    Unload Me
End Sub


10. Add the following code as the form's Unload event. (This code is the same as that for the identically named procedure in How-To 1.4.) If the data currently in the bound controls is invalid, the procedure cancels the Unload event.

Private Sub Form_Unload(Cancel As Integer)
    ` Don't allow the unload until the data is valid or the
    ` update is cancelled
    If mblnValidationFailed Then Cancel = True
End Sub

When the form is loaded, the combined actions of datCategories and dbcCategories fill the Category combo box with a list of category names from the Categories table in NWind.MDB. When a category is chosen from the list, the category ID associated with the chosen category is inserted into the CategoryID field in the Products table.

Unlike the unbound list box and combo box controls, their bound cousins DBList and DBCombo do not have a Sorted property. If you want to provide a sorted list, therefore, you must make sure that the recordset providing the list itself is sorted on the appropriate field. You can accomplish this by setting the RecordSource property of the data control named in the DBList or DBCombo's RowSource property to a SQL statement with an ORDER BY clause. In the example cited in the "Technique" section of this How-To, you could provide a sorted list of customers by setting the RecordSource property of the data control to this:

SELECT * FROM Categories ORDER BY CategoryID

With DBList and DBCombo, you can designate how the list reacts to characters typed at the keyboard when the control has the focus. If the control's MatchEntry property is set to vbMatchEntrySimple, the control searches for the next match for the character entered using the first letter of entries in the list. If the same letter is typed repeatedly, the control cycles through all the entries in the list beginning with that letter. If you set the MatchEntry property to vbMatchEntryExtended, the control searches for an entry matching all the characters typed by the user. As you type additional characters, you are further refining the search.

Comments

The DBCombo and DBList controls are powerful additions to your programming arsenal, but be careful about the performance implications in everyday use. Each DBCombo and DBList control requires a data control, and the data control is a fairly large bit of code. In one experiment, replacing eight DBCombo controls with plain Combo Box controls loaded from a database reduced the form load time by more than 40%.

1.6 How do I...

Display many detail records for a single master record?

Problem

I want to display product inventory and order detail information for a displayed product. How do I build a form to display "master-detail" information showing products and order quantities?

Technique

A "master-detail" display is frequently used to show a hierarchical relationship between two tables such as invoice headers and invoice lines. In this How-To, you build a form to display all the orders for a particular product.

Assume you have a warehouse application. The Products table contains the following fields:

ProductID

ProductName

SupplierID

CategoryID

QuantityPerUnit

UnitPrice

UnitsInStock

UnitsOnOrder

ReorderLevel

Discontinued

The Order Details table defines the quantity of the product included on each order:

OrderID

ProductID

UnitPrice

Quantity

Discount

You have a form that displays product and stock information together with order quantities for the displayed product. This master-detail relationship requires two data controls to display a single product and multiple order lines. The master data control has a recordset tied to the Products table, and the detail recordset is tied to the Order Details table. Master table information is usually displayed in text boxes or other appropriate controls; detail information is displayed in a DBGrid control.

The DBGrid control displays multiple rows from a recordset in a scrolling table that looks much like a spreadsheet. The DBGrid control allows recordset scrolling, column width changes, display formatting, and other useful capabilities. It is most useful as a display-only tool, but the DBGrid control can provide recordset maintenance functions as well. Table 1.10 describes important properties that control DBGrid runtime behavior.

Table 1.10 Important DBGrid design-time properties.

PROPERTY Description
AllowAddNew Controls ability to add new records (default is False)
AllowDelete Controls ability to delete records displayed by the grid (default is False)
AllowUpdate Controls ability to update records through the grid (default is True)
ColumnHeaders Controls display of column headers (default is True)

You specify a recordset at design time for DBGrid so that you can design the initial column layout and formatting. The DBGrid control can retrieve the field names from a linked recordset at design time to populate the initial column display. You then edit the column properties to set headers, formats, and default values.

Steps

Open the project GridLister.VBP to preview this How-To. Change the DatabaseName property of the data controls datProducts and datOrderDetails to point to the copy of NWind.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. The form shown in Figure 1.9 appears. Navigate through the records using the product data control. Observe how the order detail information changes. Experiment with the grid's sliders to control the data display. Use the mouse to select rows or columns. Drastically change a column's display width, and observe how the horizontal scrollbar appears and disappears.

1. Create a new project called GridLister.VBP. Use Form1 to create the objects and properties listed in Table 1.11 and save the form as GridLister.FRM. Substitute the path to your copy of NWind.MDB for the DatabaseName property of datProducts and datOrderDetails.

Figure 1.9 The Grid Lister form.

Table 1.11 Objects and properties for the Grid Lister form.

OBJECT Property Setting
Form Name Form1
Caption "Grid Lister"
TextBox Name txtProductName
DataField "ProductName"
DataSource "datProducts"
TextBox Name txtUnitsInStock
DataField "UnitsInStock"
DataSource "datProducts"
TextBox Name txtProductId
DataField "ProductID"
DataSource "datProducts"
Data Name datProducts
Caption "Products"
Connect "Access"
DatabaseName "D:\Program Files\Microsoft Visual Studio\VB6\Nwind.mdb"
RecordSource "Products"
Data Name datOrderDetails
Caption "Order Details"
DatabaseName "D:\Program Files\Microsoft Visual Studio\VB6\Nwind.mdb"
RecordSource "Order Details"
Visible False
DBGrid Name dbgOrderDetails
AllowAddNew False
AllowDelete False
AllowUpdate False
DataSource "datOrderDetails"
Label Name Label1
Caption "Product Name:"
Label Name Label2
Caption "Units in Stock"
Label Name Label3
Caption "Product ID"

2. Use the Visual Basic menu editor to create the menu shown in Table 1.12.

Table 1.12 Menu specifications for the Grid Lister.

CAPTION Name Shortcut Key
&File mnuFile
----E&xit mnuFileExit

3. Use the DBGrid design-time controls to define the columns. Right-click the grid to display the menu shown in Figure 1.10, and then select the Retrieve Fields option. The DBGrid column information will be retrieved from the datOrderDetails recordset. Right-click the DBGrid again and select Edit to make on-screen modifications to column widths and row heights.

4. Right-click the DBGrid and select the Properties menu item to adjust the column formats. Figure 1.11 shows the Columns tab of the DBGrid design-time properties page.

Figure 1.10 The DBGrid right-click design-time menu.

Figure 1.11 The DBGrid design-time properties.

5. Add the following code to the Reposition event of the data control datProducts:

Private Sub datProducts_Reposition()
    Dim strSql As String
    If datProducts.Recordset.RecordCount = 0 Then
        ` Don't re-query the Order Details if there are
        ` no products displayed.
        Exit Sub
    End If
    ` Re-query the Order Detail grid by SQL SELECT statement.
    ` The WHERE clause picks up only the order details for
    ` the displayed product.
    strSql = "SELECT * FROM [Order Details] WHERE ProductID = " _
        & datProducts.Recordset.Fields("ProductID")
    ` Assign the desired SQL statement as the record source.
    datOrderDetails.RecordSource = strSql
    ` Re-query the database to bring new data to the recordset.
    datOrderDetails.Refresh
    ` Set the default value for ProductID for any possible future
    ` Order Details inserts to the displayed product ID.
    dbgOrderDetails.Columns("ProductID").DefaultValue = _
        datProducts.Recordset.Fields("ProductID")
End Sub


6. Add the following code as the Click event of the File menu's Exit item. (This code is the same as that for the identically named procedure in How-To 1.5.)

Private Sub mnuFileExit_Click()
    Unload Me
End Sub

How It Works

When the form is loaded, the datProducts data control retrieves the first Products record and fires the Reposition event. The event procedure creates a SQL statement to query only those order detail records you want to see by using a WHERE clause.

SELECT * FROM [Order Details] WHERE ProductID = <displayed product ID>

When the data control is refreshed, the DBGrid is populated with only the order detail records for the displayed product. A more complicated SQL statement (see Chapter 3, "Creating Queries with SQL") could also retrieve the order number and customer information for display on the form.

Comments

DBGrid is a powerful control well worth exploring in the Visual Basic 6 help files and books online. It provides powerful display capabilities as well as add, update, and delete capabilities. When your program is running, users can resize columns and rows to suit their display needs.

The DBGrid is also useful as the display for a master query in any database that requires logical partitioning. A multiple warehouse inventory application might use a DBGrid to select a "master" warehouse before browsing through "detail" items in order to limit inventory item display to a particular location. Logical partitioning is often required in service bureau applications to prevent making incorrect changes to a customer's account. Telephone companies and Internet service providers frequently need to see individual accounts but restrict the view to a particular corporate customer. DBGrid can help partition at the high level and provide "drill-down" capability through hierarchies.

1.7 How do I...

Change data in data-bound grid cells from code?

Problem

I want to display product inventory and order detail information; I also want to restrict editing to the quantity-ordered information. How do I edit a single DBGrid cell?

Technique

Assume you have the same warehouse application as you did in How-To 1.6. Product information and order detail information are stored in two different tables, the structures of which are also shown in the preceding How-To. You have a form that displays product and stock information together with order quantities for the displayed product. Your management team wants to have a secure editing function to allow adjustment of order quantities for particular products.

The DBGrid control can allow data updates, but all columns shown on the grid then become available for updates. You don't want to let the warehouse supervisor adjust prices or discounts--only the order quantity. You will have to directly manipulate the DBGrid cells to update the order quantity only.

Steps

Open the project GridChange.VBP. Change the DatabaseName property of the data controls datProducts and datOrderDetails to point to the copy of NWind.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. The form shown in Figure 1.12 appears. Navigate through the product records and observe how the order detail information changes. Highlight an order detail row, enter a new quantity in the text box, and press the Change Qty command button. The quantity will be updated in the DBGrid control and the underlying recordset and database table.

Figure 1.12 The Grid Change form.

1. Create a new project called GridChange.VBP. Use Form1 to create the objects and properties listed in Table 1.13, and save the form as GridChange.FRM. Substitute the path to your copy of NWind.MDB for the DatabaseName property of datProducts and datOrderDetails.

Table 1.13 Objects and properties for the Grid Lister form.

OBJECT Property Setting
Form Name Form1
Caption "Grid Lister"
TextBox Name txtProductName
DataField "ProductName"
DataSource "datProducts"
TextBox Name txtUnitsInStock
DataField "UnitsInStock"
DataSource "datProducts"
TextBox Name txtProductId
DataField "ProductID"
DataSource "datProducts"
TextBox Name txtChangeQuantity
DataField ""
DataSource ""
Data Name datProducts
Caption "Products"
Connect "Access"
DatabaseName "D:\Program Files\Microsoft Visual Studio\VB6\Nwind.mdb"
RecordSource "Products"
Data Name datOrderDetails
Caption "Order Details"
DatabaseName "D:\Program Files\Microsoft Visual Studio\VB6\Nwind.mdb"
RecordSource "Order Details"
Visible False
DBGrid Name dbgOrderDetails
AllowAddNew False
AllowDelete False
AllowUpdate False
DataSource "datOrderDetails"
Label Name Label1
Caption "Product Name:"
Label Name Label2
Caption "Units in Stock"
Label Name Label3
Caption "Product ID"

2. Use the Visual Basic menu editor to create the menu shown in Table 1.14.

Table 1.14 Menu specifications for the Grid Lister.

CAPTION Name Shortcut Key
&File mnuFile
----E&xit mnuFileExit

3. Use the DBGrid design-time controls to define the columns. Right-click the grid to display the menu shown earlier in Figure 1.10, and then select Retrieve Fields. The DBGrid column information will be retrieved from the datOrderDetails recordset. Right-click the DBGrid again and select Edit to make on-screen modifications to column widths and row heights.

4. Right-click the DBGrid and select the Properties menu item to adjust column formats.

5. Add the following code to the Reposition event of the data control datProducts. The Reposition event is called when the current record changes. To handle this event, query the database for the new data.

Private Sub datProducts_Reposition()
    Dim strSql As String
    If datProducts.Recordset.RecordCount = 0 Then
        ` Don't re-query the Order Details if there are
        ` no products displayed.
        Exit Sub
    End If
    ` Re-query the Order Detail grid by SQL SELECT statement.
    ` The WHERE clause picks up only the order details for
    ` the displayed product.
    strSql = "SELECT * FROM [Order Details] WHERE ProductID = " _
        & datProducts.Recordset.Fields("ProductID")
    ` Assign the desired SQL statement as the record source.
    datOrderDetails.RecordSource = strSql
    ` Re-query the database to bring new data to the recordset.
    datOrderDetails.Refresh
    ` Set the default value for ProductID for any possible future
    ` Order Details inserts to the displayed product ID.
    dbgOrderDetails.Columns("ProductID").DefaultValue = _
        datProducts.Recordset.Fields("ProductID")
End Sub


6. Add the following code as the Click event of cmdChangeGridCell. This code validates the entered amount as a positive number and updates the displayed grid cell.

Private Sub cmdChangeGridCell_Click()
    ` Change the selected grid cell value to the entered value
    If Not IsNumeric(txtChangeQuantity.Text) Then
        MsgBox "Change quantity must be a positive number", _
               vbInformation
    ElseIf CInt(txtChangeQuantity.Text) < 0 Then
        MsgBox "Change quantity must be a positive number", _
               vbInformation
    Else
        dbgOrderDetails.Columns("Quantity").Text = _
           txtChangeQuantity.Text
    End If
End Sub


7. Add the following code as the Click event of the File menu's Exit item. (This code is the same as that for the identically named procedure in How-To 1.5.)

Private Sub mnuFileExit_Click()
    Unload Me
End Sub

How It Works

When the Change Qty button is pressed, the event procedure validates the entered number and updates the cell value in the grid. The heart of the code is the following statement:

dbgOrderDetails.Columns("Quantity").Text = txtChangeQuantity.Text

The data contents of the DBGrid control can be addressed directly, just as the data contents of any other visual control can be. The currently selected grid row is available to have its columns directly manipulated by code. The data control will update the recordset field and table when the record pointer is repositioned.

Comments

Another useful DBGrid trick with a bound grid is to make the data control visible and allow recordset movement with the data control. The DBGrid control automatically shows database positions. It can also be used as a record selector because it can function as a multicolumn list box.

1.8 How do I...

Gracefully handle database errors?

Problem

When I access a database through Visual Basic, I have limited control over the environment. A user might move a database file or another program might have made unexpected changes to the database. I need my programs to be able to detect errors that occur and handle them in the context of the program. How do I accomplish this task with Visual Basic?

Technique

When an error occurs during execution of a Visual Basic program, control passes to error-handling logic. If you have not made provisions in your program to trap errors, Visual Basic calls its default error-handling process. When a compiled Visual Basic program is running, the default error-handling process displays a message describing the cause of the error--sometimes a helpful message, but often not--and terminates the program.

That's never a good solution, but fortunately Visual Basic gives you a choice. You can build error-trapping and error-handling logic into your Visual Basic code. Every Visual Basic program should make provisions for trapping and handling errors gracefully, but it's especially important in database work, in which many potential error conditions can be expected to exist at runtime.

Trapping Errors

Visual Basic error-trapping is accomplished through the On Error statement. When an On Error statement is in effect and an error occurs, Visual Basic performs the action specified by the On Error statement. You therefore avoid Visual Basic's default error-handling behavior.

An On Error statement is "in effect" when it has been executed before the occurrence of the error in the same function or subroutine where the error occurred or in a function or subroutine that called the function or subroutine where the error occurred. For example, assume that you have these five subroutines (subroutines are used here for the example; exactly the same principles apply for functions):

Sub First()
    .
    .
    .
    Second
    Third
    .
    .
    .
End Sub
Sub Second()
    `On Error Statement here
    .
    .
    .
End Sub
Sub Third()
    `On Error Statement here
    .
    .
    Fourth
    Fifth
    .
    .
    .
End Sub
Sub Fourth()
    .
    .
    .
End Sub
Sub Fifth()
    `On Error Statement here
    .
    .
    .
End Sub

The subroutine First calls the subroutines Second and Third. The subroutine Third calls the subroutines Fourth and Fifth. Second, Third, and Fourth have On Error statements; First does not. If an error occurs during the execution of First, Visual Basic will use its default error handling because no On Error statement has been executed. This will be true even after the calls to Second and Third have completed; the On Error statements in Second and Third have no effect on the procedure that calls them.

If an error occurs during the execution of Second, Visual Basic will take whatever action is specified by the On Error statement at the beginning of Second. Likewise, if an error occurs during Third, the error handling specified by Third applies.

What happens if an error occurs during Fourth? There is no On Error statement in Fourth. However, because Fourth is called by Third, and because there is an On Error statement in Third (that is executed before Fourth is called), an error in Fourth will cause the error handling specified by the On Error statement in Third to execute.

Fifth is also called by Third, but Fifth has an On Error statement of its own. If an error occurs in Fifth, the error handling specified in its local On Error statement overrides that specified in Third's.

The On Error Statement

These are the two forms of the On Error statement that you will use routinely:

On Error Goto label
On Error Resume Next

The On Error Goto label form tells Visual Basic this: When an error occurs, transfer execution to the line following the named label. A label is any combination of characters that starts with a letter and ends with a colon. An error-handling label must begin in the first column, must be in the same function or subroutine as the On Error statement, and must be unique within the module. In the code that follows the label, you take whatever action is appropriate to deal with the specific error that occurred. Most of the time, you will use the On Error Goto label form of the On Error statement because you normally want to respond to errors in a predetermined way.

On Error Resume Next tells Visual Basic this: If an error occurs, simply ignore it and go to the next statement you would normally execute. Use this form when you can reasonably expect an error to occur but are confident that the error will not cause future problems. For example, you might need to create a temporary table in your database. Before you create the temporary table, you need to delete any existing table with the same name, so you execute a statement to delete the table. If you try to delete a table that does not exist, Visual Basic will create an error. In this case, you don't care that the error occurred, so you insert an On Error Resume Next statement before the delete table statement. After the delete table statement, you would probably insert an On Error Goto label statement to restore the previous error-handling routine.

Determining the Error Type

Errors generated by Visual Basic or the Jet database engine are associated with error numbers. There are hundreds of error types, each with a specific error number. When an error occurs, Visual Basic puts the error number into the Number property of the Err object. You can determine the error that occurred by looking at that property.

After you know the error type, you can take a specific action based on that information. This is most often accomplished through a Select Case statement.

Assume that your application will be used in a multiuser environment and that you need to trap errors caused by more than one user working with the same record at the same time. (A full list of trappable data access object errors can be found in Visual Basic Help file JetErr.HLP, located in the VB6 Help directory.) In your error-handling routine, you might include code similar to what's shown in Listing 1.1.

Listing 1.1 Multiuser error handler.

Select Case Err.Number
  Case 3197
     ` Another user has updated this record since the last time
     ` the Dynaset was updated. Display a meaningful error message
     ` and give the user the chance to overwrite the other user's
     ` change.
     strMsg = "The data in this record have already been modified "
     strMsg = strMsg & " by another user. Do you want to overwrite "
     strMsg = strMsg & " those changes with your own?"
     If MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then
            ` The user said yes, so reexecute the Update method.
            ` This time it should "take."
            Resume
     Else
             ` The user said no, so refresh the dynaset with the
             ` current data and display that data. Then display a
             ` message explaining what's happened.
             rs.Requery
             DisplayRecord
             strMsg = "The current values of the record are now displayed."
             MsgBox strMsg, vbInformation
             ` Exit from the procedure now to bypass the code after
             ` the End Select statement.
             Exit Sub
      End If
  Case 3020
          ` The user clicked Update without previously having clicked
          ` Edit. The default error message is "Update without AddNew
          ` or Edit." Create an error that is more meaningful in the
          ` current context. (The message gets displayed after the
          ` End Select statement).
           strMsg = "You must click Edit before you click Update!"
   Case 3260
           ` Another user has the page locked. Create a meaningful
           ` message. (The message gets displayed after the End Select
           ` statement.)
          strMsg = "Locking error " & Str$(Err) & " on Update."
          strMsg = strMsg & " Optimistic locking must be enabled!"
   Case Else
          ` An unanticipated error, so just pass through Visual Basic's
          ` message.
           strMsg = Err.Description
End Select
       MsgBox strMsg, vbExclamation

Determining the Error Location

If your error handler needs to determine where in the code the error occurs, you can use old-fashioned line numbers. When an error occurs, the built-in Erl function returns the line number of the line that generated the error. If the line that generated the error has no number, the Erl function returns the line number of the most recent numbered line. If there are no numbered lines preceding the line that caused the error, Erl returns 0.


THE Err OBJECT The Err object incorporates the functionality of the Err statement, Err function, Error statement, Error function, and Error$ function from earlier versions of Visual Basic. (These older techniques are still supported for purposes of backward compatibility in Visual Basic 6.0.)

Terminating an Error Handler

Error handling code must terminate with a statement that clears the error. If it does not, the error handler itself will generate an error when it reaches the next End Sub, End Function, or End Property statement. The statements listed in Table 1.15 are those that clear an error.

Table 1.15 Statements that clear an error.

STATEMENT Effect
Resume Next Resumes execution at the line that follows the line that generated the error
Resume Reexecutes the line that generated the error
Resume label Resumes execution at the line following the named label
Resume number Resumes execution at the line with the indicated number
Exit Sub Exits immediately from the current subroutine
Exit Function Exits immediately from the current function
Exit Property Exits immediately from the current property
On Error Resets error-handling logic
Err.Clear Clears the error without otherwise affecting program execution
End Terminates execution of the program


OPENING NWIND.MDB FOR HOW-TO'S

In many of the How-To's that use data access objects to work with NWIND.MDB, you will see directions to add READINI.BAS to the project. (READINI.BAS is a module that is installed in the main VB6DBHT directory.) In the code for the project you will see the following lines:

 ` Get the database name and open the database.
strName = strNWindDb()
Set db = DBEngine.Workspaces(0).OpenDatabase(strName)



strNWindDb() is a function in READINI.BAS that reads the VB6DBHT.INI file and returns the fully qualified filename (that is, the directory, path, and name) of NWIND.MDB. The code assigns that fully qualified filename to the string variable strName and uses strName as the argument to the OpenDatabase method.


Steps

Open and run the project Errors.VBP. Three errors will occur in succession. For each, the message box reporting the error gives you the error number, error description, and line number where the error occurred. Figure 1.13 shows the first of these errors.

Figure 1.13 One of the errors.

1. Create a new project called Errors.VBP. Use Form1 to create the objects and properties listed in Table 1.16, and save the form as Errors.FRM.

Table 1.16 Objects and properties for the Errors form.

OBJECT Property Setting
Form Name Form1
Caption "Errors"

2. Add the file READINI.BAS to your project from the Chapter01\HowTo08 directory.

3. Add the following code as the Load event of Form1. This code generates three errors:

Each error causes execution to branch to the label LoadError. The code beginning with LoadError displays an informative message and then executes a Resume Next. The Resume Next transfers execution back to the line following the line that caused the error.

Private Sub Form_Load()
    Dim dbErrors As Database
    Dim strDbName As String
    Dim rsTest As Recordset
    Dim strTmp As String
    On Error GoTo LoadError
  ` Get the database name and open the database.
    strDbName = strNWindDb()  ` NWindPath is a function in 
                              ` READINI.BAS
10  Set dbErrors = DBEngine.Workspaces(0).OpenDatabase(strDbName)
    ` This statement will cause an error, because there's no such 
    ` table as No Such Table.
20  Set rsTest = dbErrors.OpenRecordset("No Such Table", _
        dbOpenTable)
    ` There is a table named Products, so this one should work.
30  Set rsTest = dbErrors.OpenRecordset("Products", dbOpenTable)
    ` There's no such field as No Such Field, so here's another 
    ` error.
40  strTmp = rsTest![No Such Field]
    ` This causes an error because UnitPrice only takes currency 
    ` values.
50  rsTest![UnitPrice] = "XYZ"
    ` Finally!
60  End
Exit Sub
LoadError:
    MsgBox "Error #" & Str$(Err.Number) & _
           " at Line " & Str$(Erl) & _
        " - " & Err.Description & " - reported by " & Err.Source
Resume Next
End Sub

How It Works

This simple example merely shows the error-handling logic that is possible using Visual Basic. The key to this sample is the use of the MsgBox function to show the various Err Object properties on the screen. Meaningful error handling has to be written into your application as you discover the most common problems with your applications.

Comments

As you work with DAO, be forewarned that you might get some unexplainable errors. You can't assume that anything as complex as the data control will always behave the way you would have written it, so you sometimes have to learn parts of the data control's behavior through a process of discovery. The best way to trap elusive errors is to eliminate controls from your form and code from your program until the errors stop occurring. The last thing deleted before the program works again is, no matter how unlikely, the part of the code or control causing the problem. The multiple cascading of events between the data control, its recordset object, and your event handlers can have unforeseen consequences that result in inexplicable errors. The most common of these data control errors is referencing invalid recordset fields during a reposition event because the recordset has moved to the "No current record" area.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.