Chapter
One
Chapter One
Your
First Database Program in Visual Basic
This chapter is for readers who have never created
database applications using Visual Basic. Those who already know how to use the Visual
Basic data control and the bound controls to make simple data entry programs might want to
skip this chapter and move on to Day 2, "Creating Databases."
Your project today is to create a completely
functional data entry program using Visual Basic. The program you create will be able to
access data tables within an existing database; it will also allow users to add, edit, and
delete records.
Sound like a lot for one day? Not really. You will
be amazed at how quickly you can put together database programs. Much of the drudgery
commonly associated with writing data entry programs (screen layout, cursor control, input
editing, and so on) is automatically handled using just a few of Visual Basic's input
controls. In addition, with Visual Basic's data controls it's easy to add the capability
to read and write database tables, too.
So let's get started!
Starting Your
New Visual Basic Project
If you already have Visual Basic up and running on
your PC, select File | New Project to create a new project. If you haven't started Visual
Basic yet, start it now. Select Standard EXE and click OK in the dialog that appears. Now
you're ready to create the data entry screen.
Adding the
Database Control
The first thing you need to do for the database
program is open up the database and select the data table you want to access. To do this,
double-click the data control in the Visual Basic toolbox (see Figure 1.1). This places a
data control in the center of the form. When this is done, the form is ready to open a
data table. At this point, your screen should look something like the one in Figure 1.1.
Figure 1.1. The data
control as it appears when first added to your form.
TIP: Are you not sure which of those icons in
the toolbox is the data control? You can press F1 while the toolbox window is highlighted
to display a help screen describing each of the Visual Basic tools. This screen shows the
tool icon and points to additional help, listing the properties, events, and methods
available for each of the controls. You can get help on a particular control in the
toolbox by clicking the icon and pressing F1 to activate Visual Basic help. Tool Tips are
also available in Visual Basic 5. Simply rest the mouse pointer on any icon to view a
pop-up description of that item. This option can be toggled on and off by selecting Tools
| Options, choosing the General tab, and then checking the Show ToolTips checkbox.
Next you need to set a few of the control's
properties to indicate the database and data table you want to access.
Setting the
DatabaseName and RecordSource Properties
You must first set the following two properties when
linking a data control to a database:
DatabaseName |
Selected database |
RecordSource |
Selected data table in the
database |
The guideS5.MDB database will be used in the
exercise that follows. This database can be found in the TYSDBVB5\Source\Data
directory on the CD that shipped with this guide.
TIP: If you do not see the Properties dialog
box, press F4 or select View | Properties Window from the menu, or click the properties
icon on the Visual Basic toolbar at the top of the screen.
To set the DatabaseName of the data control, first
select the data control by single-clicking the control (the data control will already be
selected if you did not click anywhere else on the form after you double-clicked the data
control in the Toolbox). This forces the data control properties to appear in the Visual
Basic Properties dialog box. Locate the DatabaseName property (properties are listed in
either alphabetical or categorical order, depending upon the tab you select in the
Properties box), and click the property name. When you do this, three small dots (. . .),
the properties ellipsis button, appear to the right of the data entry box. Clicking the
ellipsis button brings up the Windows standard File | Open dialog box. You should now be
able to select the guideS5.MDB file from the list of available database files (\\TYSDBVB5\SOURCE\DATA\guideS5.MDB).
Your screen should look something like the one in Figure 1.2.
Figure 1.2. Using the
Visual Basic File | Open dialog box to set the DatabaseName property.
When you have located the guideS5.MDB file and selected OK, Visual Basic inserts
the complete drive, path, and filename of the database file into the input area, linking
the database and your program together. Always double-check this property to make sure
that you correctly selected the desired database.
NOTE: People often use the words database and
data table interchangeably. Throughout this guide, data table is used to refer to a single
table of data and database is used to refer to a collection of related tables. For
example, the Titles table and the Publishers table are two data tables in the guideS5
database.
Now that you know what database you
will use, you must select the data table within that database that you want to access by
setting the RecordSource property of the data control. You can do this by locating the
RecordSource property in the Properties window, single-clicking the property, and then
single-clicking the small down arrow to the right of the property input box. This brings
up a list of all the tables in the guideS5.MDB database, as shown in Figure 1.3.
For the first database program, you will use the Titles data table in the guideS5.MDB
database.
Figure 1.3. Setting the
RecordSource property to the Titles table.
To select the Titles table from this list, simply click on it. Visual Basic automatically
inserts the table name into the RecordSource property in the Properties window.
Setting the
Caption and Name Properties
You need to set two other data control properties in
the project. These two properties are not required, but setting them is a good programming
practice because it improves the readability of the programming code. Here are the
optional properties:
Caption |
Displayed name of the data
control |
Name |
Program name of the data
control |
Setting the Caption property of the data control
sets the text that displays between the record selection arrows on the data control.
(Please note that you will need to expand the width of the data control to read this
text.) It is a good habit to set this to a value that makes sense to the user.
Setting the Name property of the data control sets
the text that will be used by the Visual Basic programmer. This is never seen by the user,
but you should set the Name to something similar to the Caption to make it easier to
relate the two when working on your program.
For your program, set the Caption property of the
data control to Titles and the Name property of the data control to datTitles. Now that
you've added the Caption property, use the mouse to stretch the data control so that you
can see the complete caption. Your form should look like the one in Figure 1.4.
Figure 1.4. A data
control stretched to show the Caption property.
NOTE: The name of the data control
(datTitles) might seem unusual. It is, however, a logical name if you remove the first
three letters, dat. This prefix is added to designate this object as a data control. The
three-character-prefix naming convention is
Microsoft's suggested nomenclature for Visual Basic 5 and is used throughout this guide.
Use the search phrase "Object Naming Conventions" in the Visual Basic 5 guides
Online to find a complete listing of the suggested object prefixes.
Saving Your
Project
Now is a good time to save your work up to this
point. To save this project, select File | Save Project from the main menu. When prompted
for a filename for the form, enter DATCNTRL.FRM. You will then be prompted for a
filename for the project. Enter DATCNTRL.VBP.
It's always a good idea to save your work often.
NOTE: This, and all other projects that you
complete from this guide, can be found on the CD included with this guide.
TIP: One way to make sure you keep an
up-to-date copy of your project saved on disk is to set the "When a program
starts:" environment variable to Save Changes. You can do this by selecting Tools |
Options... and choosing the Environment tab. Then select either the Save Changes option or
the Prompt to Save Changes option.
Adding the
Bound Input Controls
Now that you've successfully linked the form to a
database with the data control and se- lected a data table to access, you are ready to add
input controls to the form. Visual Basic 5 supplies you with input controls that can be
directly bound (connected) to the data table you want to access. All you need to do is
place several input controls on the form and assign them to an existing data control.
NOTE: Associating a control on a form to a
field in a data table is referred to as binding a control. When they are assigned to a
data source, these controls are called bound input controls.
Let's add the first bound input control to the
Titles table input form. Place an input control on the form by double-clicking the textbox
control in the Visual Basic 5 toolbox. This inserts a textbox control directly in the
center of the form. When the control is on the form, you can use the mouse to move and
resize it in any way you choose. You could add additional input controls by
double-clicking the textbox button in the toolbox as many times as you like. Set the Name
property of this control to txtTitle. Add a label to describe this control by
double-clicking the Label control. Set the label's Name property to lblTitle, and the
Caption property to Title. Refer to Figure 1.1 if you have any problems finding a
particular Visual Basic control.
TIP: When double-clicking controls onto a
form, each instance of the control is loaded in the center of the form. When you add
several controls in this manner, each control is loaded in exactly the same place on the
form, like a stack of pancakes. It looks as though you still only have one, but they're
all there! You can view each of the controls you loaded on your form by using the mouse to
drag and drop the top-most control to another portion of the form.
Setting the
DataSource and DataField Properties
You must set two textbox properties in order for the
textbox control to interact with the data control. These are the two required properties:
DataSource |
Name of the data control |
DataField |
Name of the field in the
table |
A relationship is established between a field (the
DataField property) in a table (the DataSource property) and a bound control when you set
these two properties. When this is done, all data display and data entry in this input
control is linked directly to the data table/field you selected.
Setting the DataSource property of the textbox
control binds the input control to the data control. To set the textbox DataSource
property, first select the textbox control (click it once), and then click the DataSource
property in the Property window. By clicking this property's down arrow, you can see a
list of all the data controls currently active on this form. You have only added one data
control to this form, so you see only one name in the list (see Figure 1.5). Set the
DataSource value to datTitles by clicking the word datTitles in the drop-down list box.
Figure 1.5. Setting the
DataSource property of a bound textbox.
The second required property for a bound input control is the DataField property. Setting
this property binds a specific field in the data table to the input control. Set the
DataField property of the current input control by single-clicking the DataField property
in the Property window and then single-clicking the down arrow to the right of the
property. You now see a list of all the fields that are defined for the data table that
you selected in the DataSource property (see Figure 1.6). Click the Title field to set the
DataField property for this control.
Figure 1.6. Selecting the
DataField property of the bound textbox control.
Now that you have the general idea, finish up the data entry form by adding bound input
controls for the remaining fields in the Title data table. Refer to Table 1.1 for details.
While you're at it, add Label controls to the left
of the textbox controls and set their Caption properties to the values shown in Table 1.2.
Size and align the controls on the form, too. Also, size the form by selecting its borders
and dragging to a desired shape. Your form should look something like the one in Figure
1.7 when you're done.
Table 1.1. The Input Control
DataSource and DataField properties for the Titles form.
Textbox |
DataSource |
DataField |
txtISBN |
datTitles |
ISBN |
txtTitle |
datTitles |
Title |
txtYearPub |
datTitles |
YearPub |
txtPubID |
datTitles |
PubID |
txtDescription |
datTitles |
Description |
txtNotes |
datTitles |
Notes |
txtSubject |
datTitles |
Subject |
txtComments |
datTitles |
Comments |
Table 1.2. The Label Control Caption
properties for the Titles form.
Label |
Caption |
lblISBN |
ISBN |
lblTitle |
Title |
lblYearPub |
Year Published |
lblPubID |
Publisher ID |
lblDescription |
Description |
lblNotes |
Notes |
lblSubject |
Subject |
lblComments |
Comments |
Figure 1.7. The completed
data entry form for Titles.
You can now run the program and see the data control in action. Select Run | Start (or
press F5) to compile and run your program. You can walk through the data table by clicking
the left and right arrows on the data control at the bottom of the form. The left-most
arrow (the one with the bar on it) moves you to the first record in the data table. The
right-most arrow (which also has a bar) moves you to the last record in the data table.
The other two arrows simply move you through the data table one record at a time.
You can make any changes permanent to the data table
by moving to a different record in the table. Try this by changing the data in the Title
input control, moving the record pointer to the next record, and then moving the pointer
back to the record you just edited. You will see that the new value was saved to the data
table.
Now let's include the capability to
add new records to the data table and to delete existing records from the data table.
Adding the
New and Delete Command Buttons
Up to this point, you have not written a single line
of Visual Basic code. However, in order to add the capability to insert new records and
delete existing records, you have to write a grand total of two lines of Visual Basic
code: one line for the add record function, and one line for the delete record function.
The first step in the process is to add two command
buttons labeled Add and Delete to the form. Refer to Table 1.3 and Figure 1.8 for details
on adding the command buttons to your form.
Table 1.3. Command Button properties
for the Title form.
Name |
Caption |
cmdAdd |
&Add |
cmdDelete |
&Delete |
NOTE: Adding an ampersand (&) to the
Caption of a command button causes the letter immediately following the ampersand to be
underlined. The underlined letter (also known as a shortcut key or hot key) serves as a
prompt to the user to indicate that it can be pressed in conjunction with the Ctrl key to
execute the procedure that the button contains.
Figure 1.8. The form
layout after adding the Add and Delete command buttons.
Double-click the Add button to bring up the Visual Basic code window to add code behind
the Add command button. You see the subroutine header and footer already entered for you.
All you need to do is add a single line of Visual Basic code between them.
Private Sub cmdAdd_Click()
datTitles.Recordset.AddNew ` add a new record to the table
End Sub |
NOTE: Visual Basic automatically creates the Sub_End
Sub routines for each new procedure you create. When you are performing the exercises
in this guide, insert the code only between these two lines (in other words, don't repeat
the Sub_End Sub statements, or your code will not work properly).
Now open the code window behind the Delete button
and add this Visual Basic code:
Private Sub cmdDelete_Click()
datTitles.Recordset.Delete ` delete the current record
End Sub |
Runtime and
Design Time Properties
RecordSet is a runtime only property of the data
control. This property is a reference to the underlying data table defined in the design
time RecordSource property. The RecordSet can refer to an existing table in the database
or a virtual table, such as a Visual Basic Dynaset or Snapshot. This is covered in more
depth on Day 3, "Visual Basic Database Objects." For now, think of the RecordSet
property as a runtime version of the RecordSource property you set when you designed the
form.
In the two preceding code snippets, you used the Visual Basic methods AddNew and Delete.
You will learn more about these and other Visual Basic methods in the lesson on Day 4,
"Creating Data Entry Forms with Bound Controls."
Save the project and run the program again. You can
now click the Add button and see a blank set of input controls for data entry. Fill them
all with some data (refer to Figure 1.9 for an example of a new record), and then move to
another record in the table. The data is automatically saved to the data table. You can
also use the Delete button to remove any record from the table. First, find the record you
just added (it's the last record in the table), and then click the Delete button. Now move
to the previous record in the table and try to move forward again to view the record you
just deleted. You can't. It's not there!
NOTE: When you entered data into this form,
you may have noticed that the tab sequence didn't follow a logical progression. This
happened because you added the txtTitles control first, but placed the txtISBN control in
the first position on the form. Visual Basic defines the tab order of controls in the
sequence they are placed on the form. To correct this problem quickly, select the last
control you want in your tab sequence (in this case, the Exit button) and enter 0 in its
TabIndex property. Next, select the second-to-last control in the tab sequence (the Delete
button) and enter 0 in its TabIndex property. Continue to set all the TabIndex values to
zero for all controls in your tab sequence by moving backward through the form. Complete
the process by setting the TabIndex value of the txtISBN control to 0.
The TabIndex property of a control is incremented by
1 each time a lower value is entered in another control. Therefore, by setting the
TabIndex property of the txtISBN control to 0, you reset the value of the TabIndex
property of txtTitle to 1, txtYearPub to 2, and so on.
Figure 1.9. Example data
filling in blank fields after clicking the Add button.
If you didn't enter data into the data entry form that you created in this exercise in
quite the same way as Figure 1.9 (for example, you incorrectly entered characters in the
Year field, which only accepts numbers), you might have received an error message from
Visual Basic 5 saying that you have invalid data in one of the fields. This is supposed to
happen! Visual Basic 5 (more precisely, the Microsoft JET Engine) verifies all data
entries to ensure that the correct data type is entered in each field. Input validation
routines, a means of restricting data entry even further, are covered in depth on Day 5,
"Input Validation," and error trapping is reviewed in the lesson on Day 14,
"Error Handling in Visual Basic 5.0." You can skip over these messages for now.
Summary
In today's lesson you learned the following:
- You learned how to use the data control to bind a
form to a database and data table by setting the DatabaseName and DataSource properties.
- You learned how to use the textbox bound input
control to bind an input box on the form to a data table and data field by setting the
DataSource and DataField properties.
- You learned how to combine standard command buttons
and the AddNew and Delete methods to provide add record and delete
record functionality to a data entry form.
Quiz
- 1. What are the two properties of the data
control that must be set when you link a form to an existing database and data table?
2. What property must you set if you want the data control to display the name of
the data table in the window between the record pointer arrows?
3. What are the two properties of the textbox control that must be set when you
bind the input control to the data control on a form?
4. How many lines of code does it take to add a delete record function to a Visual
Basic form when using the data control?
5. What environment setting can you use to make sure that Visual Basic will
automatically save your work each time you attempt to run a program in design mode?
Exercises
1. Add the caption "The
Titles Program" to the data entry form created in this chapter.
2. Place an additional command button labeled Exit on the data entry form. Add code
behind this command button to end the program when it is clicked.
3. Modify the Add button to move the cursor to the first input control (txtISBN) on
the data entry form. (Hint: search for SetFocus in the Visual Basic online help.)
|