Day 1

Your First Database Program in Visual Basic 5

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 BOOKS5.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 book.

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 BOOKS5.MDB file from the list of available database files (\\TYSDBVB5\SOURCE\DATA\BOOKS5.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 BOOKS5.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 book, 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 BOOKS5 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 BOOKS5.MDB database, as shown in Figure 1.3. For the first database program, you will use the Titles data table in the BOOKS5.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 book. Use the search phrase "Object Naming Conventions" in the Visual Basic 5 Books 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 book, can be found on the CD included with this book.

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 book, 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:

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.)