  
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.
- RowSource: The name of the Recordset object that is providing the data set used
to fill the data-bound list box.
- ListField: The name of the column in the RowSource data set that is used to fill
the list box. This is the display field for the list.
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
- BoundColumn: The name of the column in the RowSource data set that is used to
provide the output of the list selection. This can be the same column designated
in the ListField property, or it can be any other column in the RowSource data set.
- BoundText: The value of the column designated by the BoundColumn property. This
is the actual output of the list selection.
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.
- DataSource: The data set that is updated by the output of the data-bound list
control. This is the data control used to open the destination Recordset.
- DataField: The name of the column in the Recordset referred to by the DataSource
property.
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:
- BeforeInsert: This event occurs before a new row is inserted into the
grid. Use this event to confirm that the user wants to add a new record.
- AfterInsert: This event occurs right after a new row has been inserted
into the grid. Use this event to perform clean-up chores after a new record has been
added.
- BeforeUpdate: This event occurs before the data grid writes the changes
to the data control. Use this event to perform data validation at the record level.
- AfterUpdate: This event occurs after the changed data has been written
to the data control. Use this event to perform miscellaneous chores after the grid
has been updated.
- BeforeDelete: This event occurs before the selected record(s) are deleted
from the grid. Use this event to perform confirmation chores before deleting data.
- AfterDelete: This event occurs after the user has already deleted the
data from the grid. Use this event to perform related chores once the grid has been
updated.
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:
- BeforeColUpdate: This event occurs before the column is updated with
any changes made by the user. Use this event to perform data validation before the
update occurs.
- AfterColUpdate: This event occurs after the column has been updated
with user changes. Use this event to perform other duties after the value of the
column has been updated.
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.
- The data-bound list box
- The data-bound combo box
- The data-bound grid
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.
  
|