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.
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.
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. When you first load Visual Basic, it automatically creates a new project for you. Now you're ready to create the data entry screen.
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 on the data control in the Visual Basic toolbox. This will place 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.
TIP: Are you not sure which of those icons in the toolbox is the data control? You can hit F1 while the toolbox window is highlighted to get 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 on the icon and hitting F1 to activate Visual Basic help.
Tool Tips are also available in Visual Basic 4. 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 Environment tab, and then checking the Show Tool Tips checkbox.
The data control as it appears when first added to your form.
Next you need to set a few of the control's properties to indicate the database and data table you want to access.
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 BIBLIO.MDB database will be used in the exercise that follows. This database can be found in the root directory of Visual Basic 4.
NOTE: The BIBLIO.MDB database was copied to the Visual Basic 4 directory during installation. If you need a clean copy of this database, you can find it on the accompanying CD-ROM in the \TYSDBVB\CHAP01 subdirectory. This file can also be found on the Visual Basic 4 distribution CD in the VB directory.
TIP:If you do not see the Properties dialog box, press F4 or select View | Properties 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 on the control. This will force the data control properties to appear in the Visual Basic Properties dialog box. Locate the DatabaseName property (all properties are listed in alphabetical order), and click on the property name. When you do this, three small dots appear to the right of the data entry box. Clicking the three dots brings up Windows standard File | Open dialog box. You should now be able to select the BIBLIO.MDB file from the list of available database files. For the first database program, you will use the Titles data table in the BIBLIO.MDB database. Your screen should look something like the one in Figure 1.2.
Using the Visual Basic File | Open dialog box to set the DatabaseName property.
When you have located the BIBLIO.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, the word data table is used to refer to a single table of data and the word 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 Biblio 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 on the property, and then single-clicking on the small down arrow to the right of the property input box. This brings up a list of all the tables in the BIBLIO.MDB database, as shown in 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.
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. 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.
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 4 and is used throughout this book.
Use the search phrase "Object Naming Conventions" in the Visual Basic 4 Books Online to find a complete listing of the suggested object prefixes.
A data control stretched to show the Caption property.
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 C:\TYSDBVB\CHAP01\DATCNTRL.FRM.You will then be prompted for a filename for the project. Enter C:\TYSDBVB\CHAP01\DATCNTRL.VBP.
It's always a good idea to save your work often.
NOTE: This, and all other projects that you will complete from this book, can be found on the CD included with this book.
TIP: One way to make sure that you keep an up-to-date copy of your project saved on disk is to set the Save Project Before Run environment variable to Yes. You can do this by selecting Options | Environment from the menu and toggling the Save Project Before Run value to Yes.
Now that you have successfully linked the form to a database with the data control and selected a data table to access, you are ready to add input controls to the form. Visual Basic 4 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. You place an input control on the form by double-clicking on the text box control in the Visual Basic 4 toolbox. This inserts a text box 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 can copy additional input controls by double-clicking the text box 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 on the Label control. Set the Name property of the label to lblTitles, 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 topmost control to another portion of the form.
You must set two text box properties in order for the text box 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 text box control binds the input control to the data control. To set the text box DataSource property, first select the text box control (click on it once), and then click on the DataSource property in the Property window. By clicking on 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 Titles by clicking on the word Titles in the drop-down list box.
Setting the DataSource property of a bound text box.
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 on 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 on the Titles field to set the DataField property for this control.
Selecting the DataField property of the bound text box control.
Now that you have the general idea, finish up the data entry form by adding bound input controls for the remaining four fields in the Titles data table. Refer to Table 1.1 for details. While you're at it, add Label controls to the left of the text box 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 will 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.
| Text Box | DataSource | DataField |
|---|---|---|
| txtYearPublished | Titles | Year Published |
| txtAUID | Titles | Au_ID |
| txtISBN | Titles | ISBN |
| txtPubID | Titles | Pub_ID |
| txtDescription | Titles | Description |
Table 1.2. The Label Control Caption properties for the Titles form.
| Label | Caption |
|---|---|
| lblTitle | Title |
| lblYearPublished Year | Published |
| lblAuthorID | Author ID |
| lblISBN | ISBN |
| lblPubID | Pub ID |
| lblDescription | Description |
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 now walk through the data table by clicking the left and right arrows on the data control at the bottom of the form. The leftmost arrow (the one with the bar on it) moves you to the first record in the data table. The rightmost 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 permanent any changes 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.
Up to this point, you have not written a single line of Visual Basic code. However, in order to add the capability to add 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 to the form labeled Add and Delete. 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 a 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.
The form layout after adding the Add and Delete command buttons.
Double-click on 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.
Sub cmdNew_Click () datTitles.Recordset.AddNew ' add a new record to the table End Sub
NOTE: Visual Basic automatically creates the Sub[el]End Sub routines for each new procedure you create. When you are performing the exercises in this book, only insert the code between these two lines (in other words, don't repeat the Sub[el]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:
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 4, "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 previous two 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 5, "Data Entry 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. Select the record you just added (it's the last record in the table), and 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!
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 4 saying that you have invalid data in one of the fields. This is supposed to happen! Visual Basic 4 (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 6, "Input Validation," and error trapping is reviewed in the lesson on Day 14, "Error Trapping." You can skip over these messages for now.
In today's lesson you learned the following:
