Chapter
12
Chapter 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\guideS5.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\guides5.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\guideS5.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\guideS5.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.
|