Day 12

Data-Bound List Boxes, Grids, and Subforms

Today you'll learn about the use of data-bound lists, combo boxes, and grids in your Visual Basic 5 database applications. Before Visual Basic, incorporating list boxes, combo boxes, and grids into an application was an arduous task that required a great deal of coding and program maintenance. Now, Visual Basic 5 ships with the tools you need to add lists, combo boxes, and data grids to your project with very little coding.

You'll learn how to add features to your data entry forms that provide pick lists that support and enforce the database relationships already defined in your data tables. You'll also learn the difference between data lists and combo boxes, and you'll learn where it's appropriate to use them.

We will also show you how to easily add a data grid to your form to show more than one record at a time in a table form. This grid can be used for display only, or for data entry, too. We'll show you how to decide which is the best method for your project.

After you learn how to use the data-bound list, combo box, and grid, you'll use them to create a new custom control that provides an easy "find" dialog for all your data entry forms. You also learn how to build a data entry Subform that combines all three controls on a single form.

The Data-Bound List and Combo Boxes

The data-bound list and combo controls are used in conjunction with the data control to allow you to display multiple rows of data in the same control. This provides you with a pick list of values displayed in a list or combo box. You can use these types of controls on your data entry forms to speed data entry, provide tighter data entry validation and control, and give users suggested correct values for the data entry field.

Setting up data-bound lists and combo boxes is a bit trickier than setting up standard data-bound controls. But once you get the hang of it, you'll want to use data-bound lists and combo boxes in every data entry screen you can.

Using the Data-Bound List Box

Although the data-bound list control looks like the standard list control, there are several differences between the two. The data-bound list control has properties that provide the data-binding aspects that are not found in the standard list control (for example, the data-bound list control is self-populating, while the standard list control is not). The first two of these properties are the RowSource and ListField properties of the data-bound list control.

These two properties are used to bind the list control to a data control. Once these two properties are set, Visual Basic 5 automatically populates the list control for you when you open the data entry form.

Let's start a new project and illustrate the data-bound list control. Once you start the new project, you must make sure you have added the data-bound list controls to your project. Select the Project | Components... item from the Visual Basic 5 main menu. Locate and select the Microsoft Data Bound List Controls 5.0 item. Your screen should look like the one in Figure 12.1.

Figure 12.1. Adding the data-bound list controls to your project.

Now you need to add the data-bound list control, a standard data control, and two labels and text boxes. Use Table 12.1 and Figure 12.2 as guides as you build your first data-bound list project. Be sure to save your work periodically. Save the form as LSTCNTRL.FRM and the project as LSTCNTRL.VBP.

TIP: If you lay out the controls in the order in which they are listed in the table, you can use the down arrows of most of the property fields to get a selection list for the field names, and so on. This saves you some typing.

Table 12.1. The controls for the CH1301.VBP project.
Controls Properties Settings
Form Name LSTCNTRL
Caption Data-Bound List Controls
Height 2670
Left 1215
Top 1170
Width 4995
DataControl Name Data1
Caption Data1
DatabaseName C:\TYSDBVB5\SOURCE\DATA\LSTCNTRL.MDB
Height 300
Left 120
RecordsetType 2 - Snapshot
RecordSource ValidNames
Top 1860
Width 1875
DBList Name DBList1
Height 1620
Left 120
RowSource Data1
ListField NameText
Top 120
Width 1875
Label Name Label1
Alignment 1 - Right justify
BorderStyle 1 - Fixed Single
Caption List Field:
Height 300
Left 2160
Top 120
Width 1200
Label Name Label2
Alignment 1 - Right Justify
BorderStyle 1 - Fixed Single
Caption Text:
Height 300
Left 2160
Top 540
Width 1200
Textbox Name Text1
Height 300
Left 3540
Top 120
Width 1200
Textbox Name Text2
Height 300
Left 3540
Top 540
Width 1200
Command Button Name cmdGetList
Caption &Get List
Height 300
Left 2160
Top 1860
Width 1200


Figure 12.2. Laying out the LSTCNTRL form.


Notice that in the preceding table, a single data control has been added to open the database and create a Snapshot object of the ValidNames table. It's always a good idea to use Snapshot objects as the RowSource for data-bound lists and combo boxes. Snapshot objects are static views of the data set and, even though they take up more workstation memory than Dynaset objects, they run faster. Notice also that we set the ListField property of the data-bound list to NameText. This fills the control with the values stored in the NameText column of the data set.

Now you need to add two lines of code to the project. Open the cmdGetList_Click event and enter the following lines of code:

Private Sub cmdGetList_Click()
    Text1 = DBList1.ListField
    Text2 = DBList1.TEXT
End Sub

These two lines of code update the text box controls each time you press the GetList button on the form. That way you are able to see the current values of the ListField and Text properties of the data-bound list control.

Save the form as LSTCNTRL.FRM and the project as LSTCNTRL.VBP. Now run the project. When the form first comes up, you see the list box already filled with all the values in the NameText column of the data set (that is, the ListField used for the DBList). Select one of the items in the list box by clicking on it. Now press the GetList button. You'll see the two text controls updated with the ListField and Text values of the list control. Your screen should look like the one in Figure 12.3.

Figure 12.3. Running the LSTCNTRL.VBP project.


The data-bound list control has two more properties that you need to know. These are the properties that you can use to create an output value based on the item selected from the list. The two properties are

Usually, data-bound lists present the user with a familiar set of names. The user can pick from these names, and then the program uses the selection to locate a more computer-like ID or code represented by the familiar name selected by the user. The table created for this example contains just such information.

Set the BoundColumn property of the data-bound list control to point to the NameID column of the ValidNames data set. To do this, select the data-bound list control, and then press F4 to bring up the property window. Now locate the BoundColumn property and set it to NameID.

Add two more labels and text boxes to display the new properties. Do this by selecting the existing two labels and the two text controls all as a set. Then select Edit | Copy. This places the four selected controls on the Clipboard. Now select Edit | Paste from the Visual Basic 5 main menu. This places copies of the controls on your new form. Answer Yes to the prompts that ask if you want to create a control array. Set the caption properties of the two new labels to Bound Column: and Bound Text:. Use Figure 12.4 as a guide in laying out the new controls.

Figure 12.4. Adding new controls to the CH1301.VBP project.


Finally, modify the code in the cmdGetList_Click event to match the following code. This shows you the results of the new BoundColumn and BoundText properties:

Private Sub cmdGetList_Click()
    Text1(0) = DBList1.ListField
    Text2(0) = DBList1.TEXT
    Text1(1) = DBList1.BoundColumn
    Text2(1) = DBList1.BoundText
End Sub

Notice that you added the array references to the code to account for the new control arrays. Now save and run the project. When you select an item from the list and click the GetList button, you'll see the BoundColumn and BoundText properties displayed in the appropriate textboxes, as shown in Figure 12.5.

Figure 12.5. Displaying the new BoundColumn and BoundText properties.


NOTE: You can also activate the Get List event by entering cmdGetList_Click in the Dbl_Click event of DBList. The user can get the same results by selecting the command button, or by double-clicking the item in the list. This type of call provides a quick way of adding functionality to your code. You don't need to enter or maintain the code in both events.

The data that is produced by the BoundText property can be used to update another column in a separate table. The easiest way to do this is to add a second data control and link the data-bound list control to that second data control. You can do this by setting the following two properties of the data-bound list control.

Now let's add a second data control to the form and a bound input control that is updated by the data-bound list. First, add a data control. Set its DatabaseName property to C:\TYSDBVB5\SOURCE\DATA\LSTCNTRL.MDB and its RecordSource property to Destination. Also, set the EOFAction property of the Data2 data control to AddNew. Now add a text control to the project. Set its DataSource property to Data2 and its DataField property to NameID. Use Figure 12.6 as a layout guide.

Before you save and run the project, set the DataSource and DataField properties of the data-bound list control. Set these to Data2 and NameID, respectively. This tells the list control to automatically update the Destination.NameID field. Now, each time a user selects an item in the list and then saves the data set of the second control, the designated field of the second data set is automatically updated with the value in the BoundColumn property of the data-bound list.

Figure 12.6. Adding a second data control and text control.


Save and run the project. This time, select the first item in the list by clicking on it. Now click on the GetList button to bring up the list properties in the text boxes. Force the second data control to save its contents by repositioning the record pointer by clicking the left-most arrow to force the second data set to the first record in the set. You should now see that the second data set, Destination, has been updated by the value in the BoundColumn property of the data-bound list. Your screen should look like the one in Figure 12.6.

Do this a few times to add records to the Destination table. Also notice that each time you move the record pointer of the Destination table, the data-bound control reads the value in the bound column and moves the list pointer to highlight the related NameText field. You now have a fully functional data-bound list box!

Using the Data-Bound Combo Box

The data-bound combo box works very much the same as the data-bound list control. The only difference is the way the data is displayed. The data-bound combo control can be used as a basic data entry text box with added validation. Allowing experienced users to type values they know are correct can speed up the data entry process. Also, new users are able to scan the list of valid entries until they learn them. The data-bound combo is an excellent data entry control.

Let's build a new project that shows how you can use the data-bound combo box to create friendly data entry forms. Start a new Visual Basic 5 project. Use Table 12.2 and Figure 12.7 as guides as you build your new form. Save your form as COMBO.FRM and the project as COMBO.VBP.

Table 12.2. The controls for the CH1302.VBP project.
Controls Properties Settings
Form Name frmCombo
Caption Data Bound ComboBox
Height 2500
Left 2750
Top 2500
Width 3000
DataControl Name dtaDestination
Caption Destination
DatabaseName C:\TYSDBVB5\SOURCE\DATA\LSTCNTRL.MDB
EOFAction 2 - AddNew
Height 300
Left 120
RecordsetType 1 - Dynaset
RecordSource Destination
Top 960
Width 2535
DataControl Name dtaValidStates
Caption Valid States
DatabaseName C:\TYSDBVB5\SOURCE\DATA\LSTCNTRL.MDB
Height 300
Left 120
RecordsetType 2 - Snapshot
RecordSource "ValidStates"
Top 1320
Visible False
Width 2535
DataControl Name dtaValidNames
Caption Valid Names
DatabaseName C:\TYSDBVB5\SOURCE\DATA\LSTCNTRL.MDB
Height 300
Left 120
RecordsetType 2 - Snapshot
RecordSource ValidNames
Top 1680
Visible False
Width 2535
DBCombo Name DBCombo1
DataSource dtaDestination
DataField StateCode
Height 315
Left 120
RowSource dtaValidStates
ListField StateName
BoundColumn StateCode
Top 120
Width 1200
DBCombo Name DBCombo2
DataSource dtaDestination
DataField NameID
Height 315
Left 120
Top 540
Width 1200
RowSource dtaValidNames
ListField NameText
BoundColumn NameID
Label Name Label1
BorderStyle 1 - Fixed Single
DataSource dtaDestination
DataField StateCode
Height 300
Left 1440
Top 120
Width 1200
Label Name Label2
BorderStyle 1 - Fixed Single
DataSource dtaDestination
DataField NameID
Height 300
Left 1440
Top 540
Width 1200


Figure 12.7. Laying out the COMBO.VBP project.


You need to add two lines of code to the project before it's complete. The following lines force Visual Basic 5 to update the form controls as soon as the user makes a selection in the combo box:

Private Sub DBCombo1_Click(Area As Integer)
    Label1 = DBCombo1.BoundText
End Sub

Private Sub DBCombo2_Click(Area As Integer)
    Label2 = DBCombo2.BoundText
End Sub

Save the form as COMBO.FRM and the project as COMBO.VBP. Now run the project and check your screen against the one in Figure 12.8.

Figure 12.8. Running the COMBO.VBP project.


You can make selections in either of the two combo boxes and see that the label controls are updated automatically. Also, you can move through the dataset using the data control arrow buttons and watch the two combo boxes automatically update as each record changes.

Deciding When to Use the List Box or Combo Box

The choice between list and combo controls depends on the type of data-entry screen you have and the amount of real estate available to your data entry form. Typically, you should use lists where you want to show users more than one possible entry. This encourages them to scroll through the list and locate the desired record. The data-bound list control doesn't allow users to enter their own values in the list. Therefore, you should not use the data-bound list control if you want to allow users to add new values to the list.

The data-bound combo box is a good control to use when you are short on form space. You can provide the functionality of a list box without using as much space. Also, combo boxes have the added benefit of allowing users to type in their selected values. This is very useful for users who are performing heads-down data entry. They type the exact values right at the keyboard without using the mouse or checking a list. Also, novices can use the same form to learn about valid list values without slowing down the more experienced users.

The Data-Bound Grid

The data-bound grid control in Visual Basic 5 adds power and flexibility to your database programs. You can easily provide grid access to any available database. You can provide simple display-only access for use with summary data and on-screen reports. You can also provide editing capabilities to your data grid, including modify only, add rights, or delete rights.

Creating Your First Data-Bound Grid Form

It's really quite easy to create a data-bound grid form. First, start a new Visual Basic 5 project. Next, make sure you add the data-bound grid tool to your list of custom controls. To do this, select Project | Components... from the Visual Basic 5 main menu. Locate and select the Microsoft Data Bound Grid Control. Your screen should resemble Figure 12.9.

Figure 12.9. Adding the Data-bound Grid Control to your project.


Now drop a standard data control on the form. Place it at the bottom of the form. Set the DatabaseName property to C:\TYSDBVB5\SOURCE\DATA\DBGRID.MDB and the RecordSource property to HeaderTable. Now place the data-bound grid tool on the form and set its DataSource property to Data1. That's all there is to it. Now save the form as DBGRID.FRM and the project as DBGRID.VBP and run the project. Your screen should look like the one in Figure 12.10.

Figure 12.10. Running the first data-bound grid project.


You can move through the grid by clicking the left margin of the grid control. You can also move through the grid by clicking the navigation arrows of the data control. If you select a cell in the grid, you can edit that cell. As soon as you leave the row, that cell is updated by Visual Basic 5. Right now, you cannot add or delete records from the grid. You'll add those features in the next example.

Adding and Deleting Records with the Data-Bound Grid

It's very easy to include add and delete capabilities with the data grid. Bring up the same project you just completed. Select the data grid control and press F4 to bring up the Properties window. Locate the AllowAddNew property and the AllowDelete property and set them to True. You now have add and delete power within the grid.

Before you run this project, make two other changes. Set the Visible property of the data control to False. Because you can navigate through the grid using scroll bars and the mouse, you don't need the data control arrow buttons. Second, set the Align property of the grid control to 1 - vbAlignTop. This forces the grid to hug the top and sides of the form whenever it is resized.

Now save and run the project. Notice that you can resize the columns. Figure 12.11 shows the resized form with several columns adjusted.

Figure 12.11. Resizing the form and columns of a data grid control.


To add a record to the data grid, all you need to do is place the cursor at the first field in the empty row at the bottom of the grid and start typing. Use Figure 12.12 as a guide. Visual Basic 5 creates a new line for you and allows you to enter data. Take note how the record pointer turns into a pencil as you type. When you leave the line, Visual Basic 5 saves the record to the dataset.

Figure 12.12. Adding a record to the data grid.


Setting Other Design-Time Properties of the Data Grid

A problem with resizing the form at runtime is that the moment you close the form, all the column settings are lost. You can prevent this problem by resizing the form at design time. Select the data grid control and press the alternate mouse button. This brings up the context menu. Select Retrieve Fields. This loads the column names of the data set into the grid control. Next, select Edit from the context menu. Now you can resize the columns of the control. The dimensions of these columns are stored in the control and used each time the form is loaded.

You can modify the names of the column headers at design time by using the built-in tabbed property sheet. To do this, click the alternate mouse button while the grid control is selected. When the context menu appears, select Properties from this menu. You should now see a series of tabs that allow you to set several grid-level and column-level properties. (See Figure 12.13.)

Figure 12.13. Using the data grid tabbed properties page.


Trapping Events for the Data Grid Control

The data grid control has several unique events that you can use to monitor user actions in your grid. The following events can be used to check the contents of your data table before you allow the user to continue:

You can use the events listed here to perform field and record-level validation and force user confirmation on critical events, such as adding a new record or deleting an existing record. Let's add some code to the DBGRID.VBP project to illustrate the use of these events.

The Add Record Events

First, add code that monitors the adding of new records to the grid. Select the grid control and open the DBGrid1_BeforeInsert event. Add the code in Listing 12.1.

Listing 12.1. Code to monitor addition of new records to a data-bound grid.

Private Sub DBGrid1_BeforeInsert(Cancel As Integer)
    `
    ` make user confirm add operation
    `
    Dim nResult As Integer
    `
    nResult = MsgBox("Do you want to add a new record?",
    _vbInformation + vbYesNo, "DBGrid.BeforeInsert")
    If nResult = vbNo Then
        Cancel = True   ` cancel add
    End If
End Sub


In Listing 12.1, you present a message to the user to confirm the intention to add a new record to the set. If the answer is No, the add operation is canceled.

Now let's add code that tells the user the add operation has been completed. Add the following code in the DBGrid1_AfterInsert event window:

Private Sub DBGrid1_AfterInsert()
    `
    ` tell user what you just did!
    `
    MsgBox "New record written to data set!", vbInformation,
    _ "DBGrid.AfterInsert"
End Sub

Now save and run the project. Go to the last row in the grid. Begin entering a new record. As soon as you press the first key, the confirmation message appears. (See Figure 12.14.)

Figure 12.14. Attempting to add a record to the grid.


After you fill in all the columns and attempt to move to another record in the grid, you'll see the message telling you that the new record was added to the data set.

The Update Record Events

Now add some code that monitors attempts to update existing records. Add Listing 12.2 to the DBGrid1.BeforeUpdate event.

Listing 12.2. Code to monitor for attempted data updates.

Private Sub DBGrid1_BeforeUpdate(Cancel As Integer)
    `
    ` make user confirm update operation
    `
    Dim nResult As Integer
    `
    nResult = MsgBox("Write any changes to data set?",
    _ vbInformation + vbYesNo, "DBGrid.BeforeUpdate")
    If nResult = vbNo Then
        Cancel = True   ` ignore changes
        DBGrid1.ReBind  ` reset all values
    End If
End Sub


This code looks similar to the code used to monitor the add record events. The only thing different here is that you force the ReBind method to refresh the data grid after the canceled attempt to update the record.

Now add the code to confirm the update of the record. Add the following code to the DBGrid1.AfterUpdate event:

Private Sub DBGrid1_AfterUpdate()
    `
    ` tell `em!
    `
    MsgBox "The record has been updated.", vbInformation, "DBGrid.AfterUpdate"
End Sub

Now save and run the project. When you press a key in any column of an existing record, you'll see a message asking you to confirm the update. When you move off the record, you'll see a message telling you the record has been updated.

The Delete Record Events

Now add some events to track any attempts to delete existing records. Place the code in Listing 12.3 in the DBGrid1.BeforeDelete event.

Listing 12.3. Code to track for record deletes.

Private Sub DBGrid1_BeforeDelete(Cancel As Integer)
    `
    ` force user to confirm delete operation
    `
    Dim nResult As Integer
    `
    nResult = MsgBox("Delete the current record?",
    _vbInformation + vbYesNo, "DBGrid.BeforeDelete")
    If nResult = vbNo Then
        Cancel = True   ` cancel delete op
    End If
End Sub


Again, no real news here. Simply ask the user to confirm the delete operation. If the answer is No, the operation is canceled. Now add the code to report the results of the delete. Put this code in the DBGrid1.AfterDelete event:

Private Sub DBGrid1_AfterDelete()
    `
    ` tell user the news!
    `
    MsgBox "Record has been deleted", vbInformation, "DBGrid.AfterDelete"
End Sub

Now save and run the project. Select an entire record by clicking the left margin of the grid. This highlights all the columns in the row. To delete the record, press the Delete key or Ctrl+X. When the message pops up asking you to confirm the delete, answer No to cancel. (See Figure 12.15.)

Figure 12.15. Attempting to delete a record from the grid.


Column-Level Events

Several column-level events are available for the data grid. The following are only two of them:

NOTE: Refer to the Visual Basic 5 documentation for a list of all the events associated with the DBGrid control.

These events work just like the BeforeUpdate and AfterUpdate events seen earlier. However, instead of occurring whenever the record value is updated, the BeforeColUpdate and AfterColUpdate events occur whenever a column value is changed. This gives you the ability to perform field-level validation within the data grid.

Add some code in the BeforeColUpdate event to force the user to confirm the update of a column. Open the DBGrid.BeforeColUpdate event and enter the code in Listing 12.4.

Listing 12.4. Code to request confirmation on column updates.

Private Sub DBGrid1_BeforeColUpdate(ByVal ColIndex As Integer,
_ OldValue As Variant, Cancel As Integer)
    `
    ` ask user for confirmation
    `
    Dim nResult As Integer
    `
    nResult = MsgBox("Write changes to Column", vbInformation + vbYesNo,
    _ "DBGrid.BeforeColUpdate")
    If nResult = vbNo Then
        Cancel = False      ` cancel change & get old value
    End If
End Sub


Now add the code that tells the user the column has been updated as requested. Place the following code in the DBGrid1.AfterColUpdate event:

Private Sub DBGrid1_AfterColUpdate(ByVal ColIndex As Integer)
    `
    ` tell user
    `
    MsgBox "Column has been updated", vbInformation, "DBGrid.AfterColUpdate"
End Sub

Save and run the project. Now, each time you attempt to alter a column, you are asked to confirm the column update. (See Figure 12.16.)

Figure 12.16. Updating a grid column.


You can also see a message when you leave the column telling you that the data has been changed.

Creating the dbFind Custom Control

A very common use of the data-bound list controls is the creation of a dialog box that lists all the primary keys in a table. This dialog lets users select an item from the list and then displays the complete data record that is associated with the primary key. In this section, you'll learn how to build a custom control that does just that. Once this control is completed, you'll be able to place it on any Visual Basic form and add an instant "Find" dialog to all your Visual Basic forms.

This custom control project has two main parts. The first is the find button. This is the object that users place on their forms. By pressing the button, users see a dialog box containing a list of all the records in the table. The dialog box itself is the second part of the custom control. This dialog contains a data-bound list box, a data control, and two command buttons.

TIP: A good custom control also has a property page interface for setting control properties at design time. Because this is not a required feature, it has been left out of our custom control design so that you can concentrate on building the data-bound aspects of the control.

After you build and compile the find dialog custom control, you build a small data entry form that tests the new control.

The dbFind Control Button

The first step in the process is to start a new Visual Basic 5.0 ActiveX Control project. Name the project dbFindCtl and name the UserControl dbFind. Now add a single command button to the UserControl. Set its Height and Width properties to 315 and specify ... as its caption property. Set the font properties to Arial, 8pt Bold. Refer to Figure 12.17 as a guide.

Figure 12.17. Setting up the dbFind button.


Once you have set these properties, save the control as DBFIND.CTL and the project as DBFINDCTL.VBP.

This custom control has six custom properties and two declared events. Open the code window for the dbFind control and add the code from Listing 12.5 to the general declarations section of the project.

Listing 12.5. Coding the General Declarations section of the dbFind control.

Option Explicit
`
` local storage
Private strListField As String
Private strBoundColumn As String
Private strDBName As String
Private strRSName As String
Private strConnect As String
Private strBoundColumn as String
`
` event messages
Public Event Selected(SelectValue As Variant)
Public Event Cancel()


After declaring the local storage variables, you're ready to build the actual properties associated with the storage space. Add the DatabaseName property to your project by selecting Tools | Add Procedure... from the main menu and entering DatabaseName as the procedure name and selecting the Property and Public option buttons (see Figure 12.18).

Figure 12.18. Adding the Databasename property.


After the Visual Basic editor creates the Property Let and Property Get functions, edit them to match the code in Listing 12.6.

Listing 12.6. Editing the DatabaseName property functions.

Public Property Get DatabaseName() As String
    `
    DatabaseName = frmFind.Data1.DatabaseName
    `
End Property

Public Property Let DatabaseName(ByVal vNewValue As String)
    `
    strDBName = vNewValue
    frmFind.Data1.DatabaseName = strDBName
    `
End Property

NOTE: All the property routines you'll code here refer to the frmFind form. This form will be built in the next section of the chapter. If you attempt to run this project before building the frmFind form, you'll receive errors.

Next, add the Connect property to the project and enter the code from Listing 12.7.

Listing 12.7. Coding the Connect property procedures.

Public Property Get Connect() As String
    `
    Connect = frmFind.Data1.Connect
    `
End Property

Public Property Let Connect(ByVal vNewValue As String)
    `
    strConnect = vNewValue
    frmFind.Data1.Connect = strConnect
    `
End Property


Now add the RecordSource property and enter the code from Listing 12.8.

Listing 12.8. Adding the RecordSource property.

Public Property Get RecordSource() As String
    `
    RecordSource = frmFind.Data1.RecordSource
    `
End Property

Public Property Let RecordSource(ByVal vNewValue As String)
    `
    strRSName = vNewValue
    frmFind.Data1.RecordSource = strRSName
    `
End Property


Next, build the ListField property and add the code from Listing 12.9.

Listing 12.9. Building the ListField property.

Public Property Get ListField() As String
    `
    ListField = frmFind.DBList1.ListField
    `
End Property

Public Property Let ListField(ByVal vNewValue As String)
    `
    strListField = vNewValue
    frmFind.DBList1.ListField = strListField
    `
End Property


Next, create the BoundColumn property and enter the code from Listing 12.10.

Listing 12.10. Adding the BoundColumn property.

Public Property Get BoundColumn() As String
    `
    BoundColumn = frmFind.DBList1.BoundColumn
    `
End Property

Public Property Let BoundColumn(ByVal vNewValue As String)
    `
    strBoundColumn = vNewValue
    frmFind.DBList1.BoundColumn = strBoundColumn
    `
End Property


Finally, add the BoundText property and enter the code from Listing 12.11.

Listing 12.11. Adding the BoundText property.

Public Property Get BoundText() As Variant
    `
    BoundText = frmFind.DBList1.BoundText
    `
End Property

Public Property Let BoundText(ByVal vNewValue As Variant)
    `
    frmFind.DBList1.BoundText = vNewValue
    `
End Property


Now save the control (DBFIND.CTL) and the project (DBFINDCTL.VBP) before continuing.

The next set of routines handles some basics of custom control management. These routines exist in almost all custom controls. First, you need to add a routine to save the design-time state of the custom properties. This ensures that the values you set at design time are avail-able to the runtime version of the control. Add the code in Listing 12.12 to the UserControl_WriteProperties event.

Listing 12.12. Coding the WriteProperties event of the User control.

Private Sub UserControl_WriteProperties(PropBag As PropertyBag)
    `
    ` save design-time vars
    `
    With PropBag
        .WriteProperty "Connect", strConnect, ""
        .WriteProperty "DatabaseName", strDBName, ""
        .WriteProperty "RecordSource", strRSName, ""
        .WriteProperty "ListField", strListField, ""
        .WriteProperty "BoundColumn", strBoundColumn, ""
    End With
    `
End Sub


Next, you need to add the routine that reads the saved values. This event occurs when the runtime version of the control first begins. Add the code from Listing 12.13 to the UserControl_ReadProperties event.

Listing 12.13. Coding the UserControl_ReadProperties event.

Private Sub UserControl_ReadProperties(PropBag As PropertyBag)
    `
    ` get design-time vars
    `
    With PropBag
        strDBName = .ReadProperty("DatabaseName", "")
        strConnect = .ReadProperty("Connect", "")
        strRSName = .ReadProperty("RecordSource", "")
        strListField = .ReadProperty("ListField", "")
        strBoundColumn = .ReadProperty("BoundColumn", "")
    End With
    `
End Sub


The Initialize and Resize events can be used to set and adjust the size of the control. Enter the code from Listing 12.14 into the Initialize and Resize events of the User control.

Listing 12.14. Coding the Initialize and Resize events of the User control.

Private Sub UserControl_Initialize()
    `
    ` set default size
    `
    UserControl.Height = 315
    UserControl.Width = 315
    `
End Sub

Private Sub UserControl_ReadProperties(PropBag As PropertyBag)
    `
    ` get design-time vars
    `
    With PropBag
        strDBName = .ReadProperty("DatabaseName", "")
        strConnect = .ReadProperty("Connect", "")
        strRSName = .ReadProperty("RecordSource", "")
        strListField = .ReadProperty("ListField", "")
        strBoundColumn = .ReadProperty("BoundColumn", "")
    End With
    `
End Sub

Private Sub UserControl_Resize()
    `
    ` fill out control space with button
    `
    With Command1
        .Left = 1
        .Top = 1
        .Width = UserControl.Width
        .Height = UserControl.Height
    End With
    `
End Sub


Now you need to add just a few more routines to complete this portion of the control. First, you need to create a new private subroutine called LoadProperties. This routine moves all the property values onto the frmFind form that displays the selection dialog box. Enter the code from Listing 12.15 into your project.

Listing 12.15 Coding the LoadProperties subroutine.

Private Sub LoadProperties()
    `
    ` move properties into dialog
    `
    frmFind.Data1.Connect = strConnect
    frmFind.Data1.DatabaseName = strDBName
    frmFind.Data1.RecordSource = strRSName
    frmFind.DBList1.ListField = strListField
    frmFind.DBList1.BoundColumn = strBoundColumn
    frmFind.Data1.Refresh
    frmFind.DBList1.Refresh
    `
End Sub

NOTE: The LoadProperties routine is declared private so that users of the ActiveX control cannot see and use this routine. The LoadProperties routine is for internal use and should not be called from outside the control's own code space.

Now you need to add code behind the command button that makes it all work. Enter the code from Listing 12.16 in the Command1_Click event of the control.

Listing 12.16. Coding the Command1_Click event of the control.

Private Sub Command1_Click()
    `
    ` user pressed the button!
    `
    Dim varTemp As Variant
    `
    LoadProperties
    frmFind.Show vbModal
    If frmFind.CloseFlag = True Then
        varTemp = frmFind.SelectedValue
        Unload frmFind
        RaiseEvent Selected(varTemp)
    Else
        Unload frmFind
        RaiseEvent Cancel
    End If
    `
End Sub


Notice that this last bit of code fires off the Selected and Cancel events, depending on the value stored in the frmFind.CloseFlag variable. You'll code the frmFind form in the next section.

Finally, to round out the control, add the following two subroutines to the project (see Listing 12.17). These create two public methods that can be called from within the user's program.

Listing 12.17. Adding the ReturnSelected and ReturnCancel methods.

Public Sub ReturnSelected()
    `
    RaiseEvent Selected(frmFind.DBList1.BoundText)
    `
End Sub

Public Sub ReturnCancel()
    `
    RaiseEvent Cancel
    `
End Sub


That is all the coding you need to do for the first part of the custom control. Be sure to save the control and the project before continuing to the next section.

The dbFind Dialog Box

Now you're ready to build the dialog box that displays the selection list to the user. Add a new form to the custom control project and set its name to frmFind; its BorderStyle to 3; its ControlBox property to False; and its StartUpPosition to 2. Then add a data-bound list control and a single command button to the form. Copy the command button. Select the command button and then select Edit | Copy and Edit | Paste from the menu. Be sure to answer Yes when asked if you want to create a control array. Finally, add a data control to the form and set its visible property to False. Also, set the DBList1 control's DataSource property to Data1. Your form should look something like the one in Figure 12.19.

Figure 12.19. Laying out the frmFind form.


Don't worry about placing the controls on the form, you'll do that at runtime using Visual Basic code. Now save the form (FRMFIND.FRM) and the project (DBFINDCTL.VBP) before going to the next step.

Now it's time to code the frmFind form. First, add the following lines to the general declaration section of the form:

Option Explicit
`
Private blnCloseFlag As Boolean
Private varSelectValue As Variant

This code declares local storage for two form-level custom properties. Now add the CloseFlag Property (select Tools | Add Procedure) and enter the code from Listing 12.18.

Listing 12.18. Adding the CloseFlag property.

Public Property Get CloseFlag() As Variant
    `
    CloseFlag = blnCloseFlag
    `
End Property

Public Property Let CloseFlag(ByVal vNewValue As Variant)
    `
    blnCloseFlag = vNewValue
    `

End Property Next, add the SelectedValue property and enter the code from Listing 12.19.

Listing 12.19. Adding the SelectedValue property.


Public Property Get SelectedValue() As Variant
    `
    SelectedValue = varSelectValue
    `
End Property

Public Property Let SelectedValue(ByVal vNewValue As Variant)
    `
    varSelectValue = vNewValue
    `
End Property


These properties are used to pass information from the completed form back to the control button you built earlier in the project. Save the form and project before continuing.

Now add the code from Listing 12.20 to the Form_Load event. This code refreshes the dialog at startup.

Listing 12.20. Coding the Form_Load event.

Private Sub Form_Load()
    `
    Me.Caption = "Select a Record"
    Data1.Refresh
    DBList1.Refresh
    `
End Sub


Now enter the code from Listing 21.21 into the Form_Resize event. This is the code that sizes and places the list and command buttons on the dialog box.

Listing 12.21. Coding the Form_Resize event.

Private Sub Form_Resize()
    `
    With DBList1
        .Left = 1
        .Top = 1
        .Width = Me.ScaleWidth
        .Height = Me.ScaleHeight - (300 + 90 + 90)
    End With
    `
    With Command1(0)
        .Left = 120
        .Top = Me.ScaleHeight - (390)
        .Height = 300
        .Width = Me.ScaleWidth * 0.45
        .Caption = "OK"
        .Default = True
    End With
    `
    With Command1(1)
        .Left = Me.ScaleWidth * 0.5
        .Top = Command1(0).Top
        .Height = Command1(0).Height
        .Width = Command1(0).Width
        .Caption = "Cancel"
        .Cancel = True
    End With
    `
End Sub


Now it's time to write the code for the Command1_Click event. This is the code that executes when the user presses a command button. Add the code from Listing 12.22 to your form.

Listing 12.22. Coding the Command1_Click event.

Private Sub Command1_Click(Index As Integer)
    `
    ` handle user button selection
    `
    Select Case Index
        Case 0 ` OK
            CloseFlag = True
            varSelectValue = frmFind.DBList1.BoundText
        Case 1 ` cancel
            CloseFlag = False
    End Select
    `
    If Trim(varSelectValue) = "" Then
        CloseFlag = False
    End If
    `
    Me.Hide
    `
End Sub


Note that the CloseFlag is set along with the SelectedValue property. These property values are used by the control button you created earlier.

Finally, you need to add a bit of code to make the dialog box more user friendly. The code in Listing 12.23 executes when the user clicks or double-clicks the list. Add this to your project.

Listing 12.23. Coding the Click and DblClick events of the DBList control.

Private Sub DBList1_Click()
    `
    SelectedValue = DBList1.BoundText
    `
End Sub

Private Sub DBList1_DblClick()
    `
    Command1_Click 0
    `
End Sub


That's all the coding you need to complete the custom control. Now save the control and project. In the next section, you'll test the control in a sample data entry form.

Before you go to the next section, you should compile the dbFind.ocx control. This forces Visual Basic to review all the code and report any coding errors you may have in your project.

Testing the dbFind Custom Control

Now add a new Standard EXE project to the group (select File | Add Project... from the main menu). Use Table 12.3 and Figure 12.20 as guides when building the test form.

Figure 12.20. Laying out the test form.


Table 12.3. Test Form layout.
Control Property Setting
VB.Form Name FrmTest
Caption "Form1"
ClientHeight 1680
ClientLeft 60
ClientTop 345
ClientWidth 3885
StartUpPosition 2 `CenterScreen
dbFindCtl.dbFind Name dbFind1
Height 315
Left 2640
Top 240
Width 315
VB.Data Name Data1
Align 2 `Align Bottom
DatabaseName C:\TYSDBVB5\Source\Data\BOOKS5.MDB
RecordSource "Authors"
Top 1335
Width 3885
VB.TextBox Name Text3
DataSource "Data1"
Height 315
Left 1380
Top 960
Width 1200
VB.TextBox Name Text2
DataSource "Data1"
Height 315
Left 1380
Top 600
Width 2400
VB.TextBox Name Text1
DataSource "Data1"
Height 315
Left 1380
Top 240
Width 1200
VB.Label Name Label3
Caption "Date of Birth"
Height 315
Left 120
Top 960
Width 1215
VB.Label Name Label2
Caption "Author Name"
Height 315
Left 120
Top 600
Width 1215
VB.Label Name Label1
Caption "Author ID"
Height 315
Left 120
Top 240
Width 1215


Note the use of the new dbFind control on the form. You need to add very little code to this project. Listing 12.24 shows the code for the Form_Load event. Add this to your project.

Listing 12.24. Coding the Form_Load event.

Private Sub Form_Load()
    `
    ` set database control values
    Data1.DatabaseName = "c:\tysdbvb5\source\data\books5.mdb"
    Data1.RecordSource = "Authors"
    `
    ` set field binding
    Text1.DataField = "AUID"
    Text2.DataField = "Name"
    Text3.DataField = "DOB"
    `
    ` set up dbfind control
    dbFind1.DatabaseName = Data1.DatabaseName
    dbFind1.RecordSource = "SELECT * FROM Authors ORDER BY Name"
    dbFind1.BoundColumn = "AUID"
    dbFind1.ListField = "Name"
    dbFind1.Refresh
    `
    ` some other nice stuff
    Me.Caption = Data1.RecordSource
    `
End Sub


The code in Listing 12.24 sets up the data control properties, binds the text boxes to the Data1 control, and then sets up the dbFind1 control properties. You'll notice that the RecordSource for the dbFind1 control is the same data table used for the Data1 control. The only difference is that the dbFind1 control data set is sorted by Name. This means that when the user presses the Find button, the dbFind dialog displays the records in Name order.

NOTE: Most of the code in Listing 12.24 repeats property settings that can be performed at design time. They are set here in order to show you how the Data1 and dbFind1 properties are closely related.

The only other code you need in this form is a list of code in the dbFind1_Selected event that repositions the data pointer to display the record selected by the user. Add the following code to the dbfind1_Selected event:

Private Sub dbFind1_Selected(SelectValue As Variant)
    `
    ` re-position record based on return value
    `
    Data1.Recordset.FindFirst Text1.DataField & "=" & SelectValue
    `
End Sub

Now save the form (FRMTEST.FRM) and project (PRJTEST.VBP), then run the test form. When you press the Find button, you should see a dialog box that lists all the records in the table, in Name order (see Figure 12.21).

Figure 12.21. Running the test form.


When you select a name from the list (highlight a name and press OK or double-click the name), you'll see that the main form returns to focus and the data pointer is moved to display the selected record. You now have a custom control that offers instant "find" features by adding just a few lines of code to your projects.

Using the Data Grid to Create a Subform

In this last section of the chapter, you'll use the data grid to create one of the most common forms of data entry screens, the Subform. Subforms are data entry forms that actually contain two forms within the same screen. Usually, Subforms are used to combine standard form layout data entry screens with view-only or view and edit lists. For example, if you want to create a form that shows customer information (name, address, and so on) at the top of the form and a list of invoices outstanding for that customer at the bottom of the form, you have a Subform type entry screen.

Typically, Subforms are used to display data tables linked through relationship definitions. In the case just mentioned, the customer information is probably in a single master table, and the invoice data is probably in a related list table that is linked through the customer ID or some other unique field. When you have these types of relationships, Subforms make an excellent way to present data.

If you spend much time programming databases, you'll meet up with the need for a good Subform strategy. Let's go through the process of designing and coding a Subform using Visual Basic 5 data-bound controls, especially the data grid.

Designing the Subform

For example, you have a database that already exists, CH1203.MDB, which contains two tables. The first table is called Header. It contains all the information needed to fill out a header on an invoice or monthly statement, such as CustID, CustName, Address, City, State, and Zip. There is also a table called SalesData. This table contains a list of each invoice currently on file for the customer, and it includes the CustID, Invoice Number, Invoice Description, and the Invoice Amount. The two tables are linked through the CustID field that exists in both tables. There is a one-to-many (Header-to-SalesData) relationship defined for the two tables.

You need to design a form that allows users to browse through the master table (Header), displaying all the address information for review and update. At the same time, you need to provide the user with a view of the invoice data on the same screen. As the customer records are changed, the list of invoices must also be changed. You need a Subform.

Laying Out and Coding the Subform with Visual Basic 5

Start a new project in Visual Basic 5. Lay out the Header table information at the top of the form and the SalesTable information in a grid at the bottom of the form. You need two data controls (one for the Header table and one for the SalesTable), one grid for the sales data, and several label and input controls for the Header data. Use Table 12.4 and Figure 12.22 as guides as you lay out the Subform.

The controls table and Figure 12.22 contain almost all the information you need to design and code the Visual Basic 5 Subform. Notice that all the textbox and label controls have the same name. These are part of a control array. Lay out the first label/textbox pair. Then use the alternate mouse button to copy and repeatedly paste these two buttons until you have all the fields you need for your form.

TIP: Not only is it easier to build forms using data controls because you save a lot of typing, but it also saves workstation resources. To Visual Basic 5, each control is a resource that must be allotted memory for tracking. Control arrays are counted as a single resource, no matter how many members you have in the array.


Figure 12.22. Laying out the Header/SubForm example.


Table 12.4. The Controls for the Subform Project.
Controls Properties Settings
Form Name frmSubForm
Caption Header/Sales SubForm
Height 4545
Left 1395
Top 1335
Width 6180
Data Control Name Data1
Caption Publisher Data
DatabaseName C:\TYSDBVB5\SOURCE\DATA\BOOKS5.MDB
EOfAction 2 - AddNew
Height 300
Left 120
RecordsetType 1 - Dynaset
RecordSource Publishers
Top 1800
Width 5835
Data Control Name Data2
Caption Titles Data
DatabaseName C:\TYSDBVB5\SOURCE\DATA\BOOKS5.MDB
EOFAction 2 - AddNew
Height 300
Left 120
RecordsetType 1 - Dynaset
RecordSource Titles
Top 3780
Visible 0 - False
Width 5835
Text Box Name Text1
DataSource Data1
DataField PubID
Height 300
Left 1440
Top 120
Width 1200
Text Box Name Text1
DataSource Data1
DataField Name
Height 300
Left 1440
Top 540
Width 2400
Text Box Name Text1
DataSource Data1
DataField Address
Height 300
Left 1440
Top 960
Width 2400
Text Box Name Text1
DataSource Data1
DataField City
Height 300
Left 1440
Top 1380
Width 2400
Text Box Name Text1
DataSource Data1
DataField StateProv
Height 300
Left 4020
Top 1380
Width 600
Text Box Name Text1
DataSource Data1
DataField Zip
Height 300
Left 4740
Top 1380
Width 1200
Label Name Label1
BorderStyle 1 - Fixed Single
Caption PubID
Height 300
Left 120
Top 120
Width 1200
Label Name Label1
BorderStyle 1 - Fixed Single
Caption Name
Height 300
Left 120
Top 540
Width 1200
Label Name Label1
BorderStyle 1 - Fixed Single
Caption Address
Height 300
Left 120
Top 960
Width 1200
Label Name Label1
Borderstyle 1 - Fixed Single
Caption City/State/Zip
Height 300
Left 120
Top 1380
Width 1200
MSDBGrid Name DBGrid1
Height 1455
Left 120
Top 2222
Width 5835


It would be nice to say that you could build a Subform without using any Visual Basic 5 code, but that's not quite true. You need just over 10 lines of code to get your data grid at the bottom of the form linked to the master table at the top of the form. Place the code in Listing 12.25 in the Data1_Reposition event of the Publishers table data control.

Listing 12.25. Code to update the Subform with the Reposition event.

Private Sub Data1_Reposition()
    `
    Dim strSQL As String
    Dim strKey As String
    `
    ` create select to load grid
    If Text1(0).Text = "" Then
        strKey = "0"
    Else
        strKey = Trim(Text1(0).Text)
    End If
    `
    strSQL = "SELECT ISBN,Title,YearPub FROM Titles WHERE PubID=" & strKey
    Data2.RecordSource = strSQL  ` load grid-bound data control
    Data2.Refresh   ` refresh data control
    DBGrid1.ReBind  ` refresh grid
    `
End Sub


The preceding code is used to create a new SQL SELECT statement using the PubID value of the Publishers table. This SQL statement is used to generate a new data set for the Data2 data control. This is the control that supplies the data grid. Once the new record source has been created, invoke the Refresh method to update the data control and the ReBind method to update the data grid. That's it; there are only eleven lines of Visual Basic code, including the comments. Now save the form as SUBFORM.FRM and the project as SUBFORM.VBP, and run the program. When the form loads, you see the first record in the Header table displayed at the top of the form, and a list of all the outstanding invoices for that customer in the grid at the bottom of the form (see Figure 12.23).

Figure 12.23. Running the Header/Subform example.


As you browse through the Publishers table, you'll see the data grid is updated, too. You can add records to the data grid or to the Publisher master. If this were a production project, you would add event-trapping features like the ones mentioned in the previous section in order to maintain data integrity. You can also add the dbFind button to the header section of the form.

Summary

Today, you learned how to load and use three of the new data-bound controls that are shipped with Visual Basic 5.

You learned how to link these new controls to Recordsets using the Visual Basic 5 data controls and how to use these links to update related tables.

You also learned several of the important Visual Basic 5 events associated with the data grid. These events let you create user-friendly data entry routines using just a data control and the data grid.

You also built a new dbFind custom control that uses the DBList control to build a data-bound list of all records in a table. This new control can be used to provide primary key (or some other unique value) selection dialogs to all your Visual Basic data entry forms.

Finally, you drew upon your knowledge of data grids, SQL, and form layout to design and implement a data entry Subform. This form showed a master table at the top and a related list table at the bottom of the form in a data-bound grid.

Quiz

1. What are some of the advantages of using a data-bound list or combo box?

2. What property of the data-bound list box do you set to identify the name of the Recordset object that provides the data to fill the list box?

3. What function does the BoundColumn property of the data-bound list box serve?

4. What data-bound list/combo box properties do you set to identify the destination data set and field to be updated.

5. What properties of the data-bound grid control must be set to allow additions and removal of records?

6. What event of the data-bound grid control would you modify to prompt the user to confirm deletion of a record?

7. Why would you use the column-level events of the data-bound grid control?

8. When would you use the data-bound combo box instead of the data-bound list box?

9. What data-bound grid control method do you use to refresh the grid?

10. In what scenarios would you employ a Subform using a data grid?

Exercises

Assume that you have been assigned the responsibility of maintaining the BIBLIO.MDB database application that ships with Visual Basic 5. Your organization has determined that the information contained in this database will be of value to Help Desk personnel. The Help Desk Manager has come to you and requested a Visual Basic 5 application for Help Desk use.

Build a data form that contains a data-bound list box that displays the Name field from the Publishers table. Once selection is made in this list box, text boxes should display PubID, CompanyName, Address, City, State, Zip, Telephone, and Fax of the publisher selected.

In addition, a listing of all publications of the selected publisher should appear in a data-bound grid Subform. For each entry, display the Title, Year Published, and ISBN from the Titles table.

Hint: You will need to use three data controls for this form.