Visual Basic Expert SolutionsChapter 4Advanced Database Front EndsBy Michael McKelvy |
Database applications can be used for a wide variety of programming needs. Their particular strength is the ability to store data in a manner that makes data retrieval and maintenance easy. A database application can be used for something as simple as a personal mailing list or as complex as controlling the processes of a manufacturing plant. They are used extensively in financial applications for banking transactions, investment tracking, and loan processing.
Just as the types of applications are highly diverse, the front ends to these applications also vary widely according to the application and the experience level of the user. Database front ends provide the user interface to the data. The front end is used for displaying data, inputting data, and handling user queries and reports. While the database handles the storage of the information, you can think of the front end as the presentation of the information to the user. This involves screen designs and, quite often, the design of reports.
This chapter will focus specifically on the design of various screens for data input and data display. In this chapter, you will learn:
One of the advantages of working with Visual Basic to develop database front ends is that you are not constrained to a single database type. Visual Basic is designed to work best with Access databases, but the Jet engine used in Visual Basic also allows you to use other database formats such as FoxPro or Paradox. This means that, in addition to creating new databases, you can also link to existing databases on your machine or on database servers.
In designing the database front end, you should always keep the user's task in mind. Your design should be one that makes it as easy as possible for the user to perform his work. For example, if the user's function is to monitor warehouse inventory levels as items are sold, he may not need the capability to page through individual records. Or, if your user needs to be able to call up and modify specific records, he will need a search capability built into the application.
Visual Basic provides a variety of controls and methods for you to use in designing your database front end. For most applications, your only limitations are the task for which you are designing the application and your own imagination.
There are two methods of accessing databases from Visual Basic. These are as follows:
In this chapter, a combination of these methods will be used to achieve the desired results for the front ends being designed. For an introduction to database design and using these data access methods, see Que's Special Edition Using Visual Basic 4.
Any discussion of advanced topics needs to begin with a brief discussion of the basics. This is to ensure that everyone is communicating from a common knowledge base. In this discussion, the starting point is an example of a simple database front end. For this front end and for many of the other elements of this chapter, a sample application will be used. This sample case tracks information about the participants in a youth group and their attendance at various events and functions. If you wish to view the sample application, you may access it in Visual Basic by loading the YOUTH.VBP project file on the companion CD.
Note: In the interest of saving space, most of the information about the structure of the database and tables used in the sample case are omitted from the chapter text. If you want such a description, you may look at or print the YOUTHDAT.TXT file on the companion CD. Or, if you want a challenge, you can map the structure of the database using the data access objects.
A good starting point is a simple, one-form display of the participant information. If you have done any database programming, you have probably developed a form like this at some point. This first panel is developed with the Data control and the bound controls. It contains basic information about the group member such as name, address, and phone number. The form also contains command buttons to add new records and delete existing records. This sample form is shown in figure 4.1.
Fig. 4.1 A simple input form can be developed with the Data control and bound controls.
As previously stated, front ends can be developed with the data access objects as well as the Data control and the bound controls. To illustrate this point, figure 4.2 shows a form with the same functionality as the one in figure 4.1—but developed without the Data control and bound controls. Both of these forms are available in the sample application, as Youth-1 and Youth-2, respectively.
Fig. 4.2 A simple input form can also be developed with just the data access objects.
One of the most useful enhancements for a database front end is to add navigation keys. These keys allow the user to move through the records by pressing keys on the keyboard instead of having to use the mouse to click on the control buttons of the Data control or the command buttons of the data-entry form. If you have ever entered a long series of records, you know how time-consuming it can be to constantly move your hand from the keyboard to the mouse and back. Fortunately, this enhancement is also one of the easiest to implement.
To illustrate the use of navigation keys, support for the following keys will be added to the data-entry form of figure 4.1:
Note: You may wonder why we don't use the Insert and Delete keys to add or delete a record. The reason is that these have meaning in the editing of text in a field. That is, the Insert key toggles between the insert and overwrite edit modes and the Delete key is used for character deletion.
To implement the use of keys to navigate the database, the keystrokes must be intercepted and checked against an action list before they are passed to the active control (for example, a text box). Intercepting the keystrokes is done by setting the KeyPreview property to True for the current form. This is shown in the code:
Form1.KeyPreview = True
This code allows events in the form to examine the keystrokes to determine if any action is necessary. There are three form events where keystrokes can be examined. These are the KeyPress, KeyDown, and KeyUp events. (This discussion will not use the KeyUp event, but it works the same as the KeyDown event for our purposes.) The KeyPress event can be used if you do not need to know the shift state of the key being pressed.
Note: Shift state refers to whether the Shift, Ctrl, or Alt keys were held down when a key was pressed.
When the KeyPress event is triggered, it passes an argument to the event subroutine that specifies an integer value for the key. This value is compared against key code constants to determine if the desired key was pressed. These constants are defined in Visual Basic's help system.
If you need to know the shift state of the key, you should use the KeyDown event. When the KeyDown event is triggered, it passes the integer value of the key just like the KeyPress event did. However, the KeyDown event passes a second variable that indicates the shift state of the key. The possible values of this variable are summarized in Table 4.1.
Table 4.1
Table 4.1 Shift, Ctrl, and Alt Keys Pass Different Values to the Event.
Held Key |
Value |
(None) |
0 |
Shift |
1 |
Ctrl |
2 |
Alt |
3 |
Note: In addition to the values of the individual keys, the values may be combined to indicate a key combination. The value for the combination is just the sum of the individual keys (for example, the value for Ctrl+Alt is 6).
To process the desired keystrokes, a set of nested case statements is placed in the form's KeyDown event. These statements check the key code and shift state values against desired values, then process the keystrokes accordingly. The code to process the keystrokes for the data-entry form is shown in Listing 4.1. The code shown sets up the outer case statement on the value of the shift state, then uses inner case statements to process individual keys. You can, of course, change things around to suit your programming style. Setting the code up in this manner also allows you to easily add other keys such as Ctrl+F for a seek function.
Listing 4.1 Case Statements Allow the Processing of Different Keystrokes
Setting up this functionality has no effect on the appearance of the data-entry form. The form looks the same to the user, but it can help the user accomplish his data-entry tasks more easily.
Caution: Certain controls such as command buttons and list boxes do not pass specific keystrokes to the form even with the KeyPreview property set. These keys include the Enter key and arrow keys. You should be careful when trying to process these keys if you have command or list controls present.
Single-form database front ends are very useful for simple databases, typically where tables are not highly dependent on one another. Many applications require the user to be able to look at and modify data from multiple tables during the course of a data-entry session. When this is the case, it is usually desirable to present the data in multiple windows. While it might be possible to present all of the information on a single form, this is often confusing to the user, and usually results in a very cluttered form. Using multiple windows allows you to place data in logical groupings in separate windows.
For example, in the sample case, when a new youth is added to the database, you usually need to add information about his parents (unless he is the sibling of another youth in the group). You would typically want information such as the parents' names, places of employment, and work phone numbers. Trying to pack all this on the same form with the youth information would yield a screen like the one shown in figure 4.3. This screen is very cluttered and is difficult to use.
Fig. 4.3 Too much data on a single form creates a cluttered data-entry screen.
A better solution is to put the parent information in a separate data-entry window. This can be done in several ways. This section will look at the following:
If you have done much programming with Visual Basic, you know that you can use a Frame control as a container for other controls. This is convenient if you have a group of controls that you want to be able to hide or move during your application. With the controls contained by the Frame control, you have to set only the visible or position properties of the frame instead of each control in the group.
This behavior can be used to present different pieces of related data to the user. For instance, you could place a command button on the youth data part of the form (which causes the parent data to be shown), and a button on the parent data part of the form (which returns to the youth data view). Using this method would allow us to eliminate some of the clutter on the form, but still get to both sets of data fairly easily. This approach is shown in figures 4.4 and 4.5.
Fig. 4.4 Putting the youth data in a frame allows it to be swapped with the parent data.
Fig. 4.5 Putting the parent data in a second frame eliminates the clutter on the screen.
The swap function for these frames is easy to implement. Each command button hides one control and displays the other. The statements for this are shown below.
You do not have to place all the information inside the frames. For instance, if you wanted to leave the youth name on the screen while the parent data was shown, you could leave the name on the main form outside the frame. You would also need to size both frames so that they did not cover this part of the form.
There are other ways to use the frames to control what is presented to the user on the screen. One of these is to use the frames to swap between a single record mode and a browse mode. The browse mode uses the data-bound grid to display information about multiple records in a spreadsheet-like form. The data-bound grid will be discussed further in the section "Using Other Controls" later in this chapter.
Another way to use frames is to put data on multiple "pages." Creating multiple pages is an extension of the method of swapping two screens, as was shown with the youth and parent data swap. This method will be shown in the section "Optimizing the Use of Available Screen Space" later in this chapter.
There are several drawbacks to using the method of multiple frames.
Another way of presenting data in multiple windows is to use multiple forms. With multiple forms, you can segregate the information you need to present into two or more windows. You can set the forms up so that all of them are displayed at the same time, or you can call one form from another.
Using multiple forms to display and edit information can provide the following advantages to your users:
Continuing with the example of youth group membership, figure 4.6 shows how the youth data and the parent data would be presented on separate forms. The data shown on each form is the same as the separate frames used in the last section; however, with the two forms, all information is displayed at the same time.
Since it is desirable to have both the youth and parent data shown on the screen at the same time, you need to load both forms when the application is invoked. To do this, you can use one of two methods. With the first method, your program starts by loading the first form (Form1). Then, in the Load event of Form1, you place the code to show the second form as shown below.
Form2.Show
Using the second method, a program module or menu choice would contain the code to load both forms. This code is shown below. Note in the code listing that the form you want active should be the last one loaded.
If your application needs access to the second form but does not need it present all the time, you may wish to call the second form from a command button on the first form. In this case, you define the command button and place the Form.Show statement in the Click event of the command button. You might also want to use this method even if you typically display both forms at once. That way the user can call a form back up if he accidentally closed it.
MDI forms provide another structure for implementing multiple windows. With MDI forms, your application places the data on child forms within the MDI parent. The child forms are then constrained to the boundaries of the MDI parent instead of being able to move anywhere on the desktop. This is illustrated in figure 4.7.
Fig. 4.7 An MDI child form can only be moved and resized within the boundaries of the parent form.
Note: For the MDI discussion, parent and child refer to the relationship of the forms, not the people that are included in the youth tracking application.
Using MDI forms can be an advantage in situations where your application will be used in conjunction with several others. The MDI form provides the common container for all your applications forms. That way, if users switch from your application to another and then switch back, none of your application's open forms will be hidden. They will be brought back up as a unit. Similarly, if your users need to minimize the application, they have to minimize only the parent form, not each individual form in your application.
The MDI form also provides a convenient means of incorporating a menu with multiple sub-forms. The menu, and possibly even a Toolbar, can be placed on the parent form, then the child forms can be called as necessary.
A key benefit to using MDI forms is the ability to create multiple instances of a child form. Most of you are probably familiar with calling up multiple documents in a word processor. This is an example of using multiple instances of a child form. Each document is a new instance of the same basic form.
For a database application, being able to create multiple instances of a form allows you to look at multiple views of the data. Consider the youth application; you could run two queries that showed different groups of youth. One query could show youth in grades 6-8 while the other would show youth in grades 9-12. Or you could run the queries based on interests such as choir or mission work. Being able to look at different groupings of the data side-by-side often provides greater insight into the data than just looking at a single view.
Another possibility of using multiple instances would be with graphs. Using an MDI child form containing a Graph control, you could place a bar chart of total sales by region in one instance of the form. Then in a second instance, you could place a pie chart that showed each region's contribution to total sales. Again, this allows you to analyze your data in different ways.
To create an instance of an MDI child form, the form must first be defined in the design mode. To create the new form, you place the following code in the event from which the form is launched:
In this code, you identify the name of the new instance of the form (newgrid) and the form that is the basis of the new instance (Form2). Once the new form is dimensioned, the Show method loads it and places it on the screen. If you want to learn more about using MDI forms, you should refer to Chapter 2, "Multiple Document Interface (MDI)."
The key to making a multi-window interface work is being able to maintain the proper relationship between the data elements in each window. For example, with the youth data in one window and the parent data in another window, you need to make sure that the parent information shown corresponds with the youth record you are editing. This means that you have to set up the link between the youth data and the parent data when the forms are first opened, and maintain that link each time you change youth records.
To set up the example, a form is created containing the youth information. This form uses the Data control and a set of bound controls to access the data. A second form is created containing the parent information, also using the Data control and bound controls.
Note: You do not have to use the Data control and bound controls for this method to work. They are used for the example because their use simplifies the display of information and recordset navigation. This allows the explanation to focus on the link between the two forms.
The structure of the database defines a ParentID field in both the Youth table and the Parents table. (Examine the file YOUTHDAT.TXT to see the database structure.) This field provides the link between the two tables. To make this link work on the forms, a Label control bound to the ParentID is placed on the youth information form. Each time the record pointer is moved, this control will change to the new value of the ParentID. Placing the following code in the Change event of the Label control makes the link between the forms:
The first line of this code assigns the value of the ParentID for the current youth record to a variable. The second line of the code then searches the recordset in Form2 for the corresponding ParentID in the parent table. When the record in the parent table is found, its contents are displayed in the appropriate fields on Form2. This link is initialized when the two forms are loaded. The link is maintained each time the record pointer is moved because the record movement triggers the Change event containing the code.
Caution: It is possible to use a similar technique to provide a simultaneous link in the other direction, that is, from the parent form to the youth form. However, care must be exercised to avoid an infinite loop. For example, a change in the youth record triggers the first Change event. This causes the record pointer in the Parents recordset to move, causing a Change event on the parent form. This then causes the record pointer for the Youth recordset to move, and so on. To avoid this situation, set a global variable that indicates which form is in control. Then place an If statement around the code in the Change event. If the form containing the Change event is not in control, the code is not executed.
A major concern in managing the use of multiple information screens is optimizing the use of available screen space. Only so much information will be visible on the computer screen at any one time, no matter how many windows you use. In fact, as you add more forms to the desktop, the amount of visible information decreases, because each form has borders and a header that take up screen space.
There are several ways to maximize your use of available screen space. Some of these are as follows:
Shrinking and moving a form that is not in use will be covered together since they are variations on the same technique and are often used together. The purpose behind shrinking a form is to show only the essential information on the form in the smallest format possible. In the sample application, the parent information form might show only the parents' names while the youth form is being edited. Likewise, the youth form might only show the youth's name while the parents' information is being edited.
In addition to shrinking the form when it is not in use, it is often desirable to move the form to an edge of the screen. (I prefer moving it to the bottom.) This provides the maximum amount of contiguous space on the screen for the active form. Figures 4.8 and 4.9 show the two data views where the active form is full-size and the inactive form is reduced-size.
Fig. 4.9 Activating the Parent Information form reverses the form sizes and positions.
Invoking this technique involves placing code in the Activate and Deactivate events of each form. To get the position and size of the inactive form, you should create the desired look of the form while in the design mode. When the form is positioned and sized to your liking, read the form's Top and Left position properties, and the Height and Width size properties. (For the example, the following values will be assumed: Top = 5760, Left = 1245, Height = 1410, Width = 6840.) Then, in the Deactivate event of the form, place code that will set the position and size to the desired values as shown below.
This code will be invoked whenever another form is made active, thereby shrinking and moving the current form.
To restore the form to its full size, get the Top, Left, Height, and Width properties of the form as you want it to appear when it is active. (Example: Top = 1170, Left = 1080, Height = 4335, Width = 7710.) Then place these values in the Activate event of the form to restore the form to full size, as shown in the next listing.
This code will be run when the user activates the form, and will cause the form to be restored to its original size and position.
You will need to repeat this process for each form that you want to shrink.
Caution: You will want to exercise care in setting the sizes and positions of your forms to ensure that they do not overlap. If your active form completely covers any of your reduced forms, the user will not be able to see the information on the form, nor will he be able to activate the form with a mouse click.
Note: Your user may want the ability to move and resize the active form. In this case, you will want to preserve the size and position of the form when it is reduced then restored. For this, you will need to set global variables for the four size and position properties of each form. Then, in the form's Deactivate event, read the current values of the properties prior to reducing the form. To restore the form, you will need to reset the properties to the previous values. The next listing shows the code for the Activate and Deactivate events.
The user will not be able to change the size and position of the reduced form. Because, as soon as the user clicks on the form to move it or change its size, the Activate event is triggered.
Some applications may have a large amount of data that the user needs to process, but the data does not all have to be on the screen at the same time. In this case, you can present the information in pages using multiple frames to represent the pages. An example of this type of program would be one that handles mortgage applications. If you've ever applied for a mortgage, you know how much information they want about you—your name, address, employer, income, whether any of your ancestors arrived on the Mayflower, and so on.
To efficiently set up multiple pages, you need to have a way to easily navigate from one page to another. You will also probably need to keep some information on the screen that is constant for all pages (such as a borrower's name). Other information that would be beneficial to your users would be the number of the current page and the total number of pages for the application. This can be placed in a group of Label controls to exhibit something like "Page 1 of 5."
When you are developing pages, you will need to work with your user to determine the most efficient arrangement of the information on the pages. For the mortgage example, you might want separate pages for borrower information, spouse information, employment history, and credit history.
Once the pages are set up, you will need to give the user a means of moving from one page to the next. The easiest way to do this is to provide two command buttons, one for Next Page and one for Previous Page. Pressing these buttons moves the user forward or backward through the available pages in sequential order. This is good for applications with a small number of pages. The first page of a mortgage application with the two command buttons is shown in figure 4.10.
Fig. 4.10 A multi-page form requires a way to move between pages.
To implement the movement between pages, we place code in the Click event of the command buttons. This code will hide the current Frame control and show the frame for the next (or previous) page. This code is shown in Listing 4.2. The listing assumes that there are four pages of information. (Also, the command buttons are named NextPage and PrevPage.)
Listing 4.2 Mortgage.Frm These Subroutines Enable the Page Movement Buttons
You should note two things about Listing 4.2. The first is that the code updates the label caption that contains the page number. This is so the user knows the current page number. The second item of note is that the code disables the Next Page button when the last page is reached, and disables the Previous Page button when the first page is reached. This is for the convenience of the user. Disabling the buttons provides an additional visual indication of the user's position in the application. Adding this type of visual indicator is good programming practice.
Next Page and Previous Page buttons are sufficient for applications with a small number of pages (2-4). However, moving through several pages to get to the information you need can be frustrating. Also, the Next Page and Previous Page buttons provide the user with no information about what is on other pages.
Therefore, if you have a larger number of pages, you might want to provide the user with a means to go directly to any given page. This can be done by creating a "Toolbar" of command buttons on your form. Each command button is assigned to a specific page of the form. You can use either text descriptions, or, if your application is amenable to it, icons to identify each button. The text on the command buttons can be used to identify the information on the page it accesses. Figure 4.11 shows a redesign of the mortgage application with the Toolbar added.
Fig. 4.11 A Toolbar allows the user to access any page at any time.
Along with adding the Toolbar to the form, another change was made to the application—namely, the way in which frames are defined. Instead of using individually named frames for each page, a Frame control array is used. This simplifies the implementing code of each command button by using the current page number to determine which page to hide.
In this case, the current page number corresponds to an index of the Frame control array. When a command button is pressed, an array index is generated from the current page number. This frame of the array is then hidden. Next, the new page number is set along with the array index of its corresponding page. The new page is then shown, and the current page display is updated. This process is shown in the next code listing for the Page 3 command button.
Note that in the listing, the array index is one less than the page number. This is because the index for a control array starts with 0.
An interface that is gaining a lot of popularity lately is the use of tab pages. These pages look like a series of file folders lined up behind each other. You will often find this type of interface used for accessing multiple pages of a three-dimensional spreadsheet, or in the option setting dialogs of many programs. The advantage of using this method is that it provides a familiar metaphor for the user to access information.
Visual Basic comes with a custom control that allows you to add a tabbed interface to your program. You can add this control to your Toolbox by selecting the control from the Custom Controls dialog box. The individual tabs on the control work like a series of frames to allow you to present information on separate pages of the control.
To set up the Tab interface, you must first draw the control on your form. You do this like you would for any other control, drawing the Tab to the size and shape that you want. The initial Tab control will contain three tab pages on a single row. By setting the Tabs and TabsPerRow properties of the control, you can adjust the number of tabs and control how many rows will be presented. Figure 4.12 shows how the control would look with five tabs arranged in two rows (Tabs = 5, TabsPerRow = 3).
Fig. 4.12 The Tab control allows you to present information on a series of pages.
You can also control other aspects of the appearance of the Tab control using the following five properties:
After you have set the appearance of the Tab control to your liking, it is time to add information to the pages. For each tab page, you select the page by clicking on page's tab. You can then add any controls to that tab page. To add the controls, you just draw them on the tab page as you would on a frame, picture box, or a form. You can also set the Caption property of the tab page to indicate the type of information that appears on the page. As you move from page to page in the Tab control, only the controls on that page will be shown. All other controls on other pages will be hidden.
One caveat with using the Tab control: the individual tab pages cannot be used to group option buttons. That is, the control considers all option buttons on all tab pages to be part of the same group. Therefore, if you need separate groups of option buttons within the Tab control, you will need to place a frame on the tab page containing the option buttons, then place the buttons on the frame.
Figure 4.13 shows how the Tab control could be used in presenting information for the Mortgage Application.
Fig. 4.13 The Mortgage Application makes good use of the Tab control.
In addition to the standard input form with text and label controls to show the data, there are ways of getting data into a database. There are other controls that can be used to enter, edit, and display data. Several of these controls can be used as bound controls, meaning that they will work with the Data control. Other controls cannot be bound, but they can be used with the data access objects, and with a little work, can be made to function with the Data control.
For many applications, the Text Box and Label controls are sufficient for handling data display and entry functions. These controls handle a wide variety of data types. They are also commonly used because they can be bound to a Data control, making many data editing operations automatic.
Another bound control that is used less often is the Check Box. The check box only works with True/False data fields. There are times, however, when these controls are not sufficient to handle all of your data-entry needs. This section will look at several other controls that you can use to enhance your application.
For many applications, you will want to use a list of valid options from which your user can select. In earlier versions of Visual Basic, the List control was not bound, and data from a list had to be input using assignment statements. In Visual Basic Version 4, you can use a list to directly enter data into a data field. In fact, you have a choice of two bound List controls and two bound combo boxes for your application. Only list boxes will be covered in this section, but the combo boxes work the same way. Figure 4.14 shows a screen that uses both types of list boxes. This screen is from a library system.
Fig. 4.14 This screen uses both the Data-Aware List Box and the Data-Bound List Box controls.
The first type of list box is known as a Data-Aware List Box. The list box is drawn on a form just like any other control. The items available for selection in the list box are set up using the AddItem method. To initially set up the list, place a series of AddItem statements in the Load event of the form. For the library screen shown in figure 4.14, the Data-Aware List Box is used to select the type of media for the resource (for example, book, audio tape, video, or CD). The code to set up the list is shown below.
For use in a database application, this list box may be bound to a single field in a recordset. When a list box is bound to a field, the user's selection is automatically stored to the bound field. For instance, if the user chose Book from the media type list, the value Book would be stored in the Media field of the recordset. The list box can be bound to a recordset by setting its DataSource and DataField properties. The DataSource property specifies the Data control for the recordset being edited. The DataField property specifies the actual field in the recordset that will accept the selection from the list box.
If you are using the data access objects instead of the Data control, you will need to determine the user's selection manually. To do this, you check the ListIndex and List properties of the list box. The ListIndex tells you the index number of the selected item, then the list value for that index gives you the text of the selected item. The code below shows how the Media data field would be updated from the media selection list. The Media field is in a recordset named MyDyn.
The second type of list box is the Data-Bound List Box. The difference between this and the Data-Aware List Box is that the Data-Bound List Box generates the list from a field in a table. In the library example, there is a table containing a list of categories such as math, English literature, science fiction, and so on. The Data-Bound List Box uses the records in this table to set up the list for the user's selection. Three properties of the Data-Bound List Box are used to set up the list:
Two other properties of the data-bound list determine where the selection from the list will be stored. These are:
All five of these parameters must be specified or an error will be generated when the application is loaded. In setting up the list, the same Data control (and therefore the same recordset) can be used for both the RowSource and DataSource properties. Also, the BoundColumn and DataField properties must both reference the same type of field (for example, both-character or both-integer).
When a user makes a selection from the list, the value of the BoundColumn field of the source recordset is stored to the DataField field in the target recordset. This control and its ComboBox counterpart cannot be used with the data access objects.
Another very useful control is the option button. Unfortunately, it is not a bound control that can be used directly with the Data control. However, this does not have to stop you from using the control. Option buttons come in handy for allowing the user to select between a small number of choices. A typical use would be for selecting gender in a membership application, as shown in figure 4.15.
Fig. 4.15 Option buttons can be used to present the user with choices.
In code, you determine which option button was chosen by checking the Value property of each one. If the value of a button is True, then this was the selected button. Only one option button of a group may be selected. Option buttons may be grouped on a form, in a picture box, or in a frame. It is a good practice to set up each group of option buttons in a picture box or frame to avoid conflicts with other groups.
Once you have determined which option button was selected, you can assign the desired value for your data field based on the selection. For the membership case, either an M (male) or F (female) is stored, depending on the option button selected.
Note: If you have more than two option buttons, you might want to put them in a control array. Then you can use a loop to look for the selected option, as shown in this code listing.
The option buttons are not bound controls, but you can still use them in an application with the Data control. You can place code in the Validate event of the Data control to store the desired value from the option buttons. However, there is a method that I find easier to use. (It's a sneaky way to trick the Data control.) For the field that you are modifying, create a text box and bind it to the field. Then set the Visible property of the text box to False. This will keep the box from being seen by the user. Then, in the Click event of each option button, place a line of code that changes the contents of the text box to the value represented by the option button. Then, when the Data control is invoked to move the record pointer, the field bound to the hidden box is updated along with all other bound fields. For the membership case, the following code would be used:
Using this hidden box method has an additional benefit. You can use the Change event of the box to mark the proper option box for each record that is accessed. The code for this would be:
While this discussion has focused on how to use the option buttons with the Data control, you can also use them when you program with just the data access objects. In this case, you use an assignment statement to set the value of your field just like you do any other field. For the membership case (assuming the field is named Gender), you would use the following code:
Spin Button controls are another control that is not a bound control, but can be useful in a database application. The spin buttons are really a modifier for a Text or Label control. To use a spin button, you use a Text or Label control to hold the contents of a numeric field. Then you place a Spin Button control next to the Text or Label control. Finally, you place code in the SpinUp and SpinDown events of the Spin Button control to increment or decrement the value of the Text or Label control. The following code would add one to or subtract one from the value in a text box named Price; you can set the values of the spin button to any increments that you need:
Once the value of the text box has been set, the field in the database is updated the same as any other text box. This is handled by the Data control if you are using it, or with an assignment statement if you are using the data access objects. Figure 4.16 shows the youth data application with a spin box that is used in conjunction with the Grade text box.
Fig. 4.16 You can use a spin button to increment the value of a number.
You can also use the spin button to manipulate date information. This is slightly more complex than working with numbers because you cannot add a number directly to the text property that represents a number. If the text box represents a date, the following code in the SpinUp event will add one day to the date:
If you want to add a month at a time to the date (or a year at a time), you can use the DateAdd function. This function allows you to specify the date interval (day, week, month, quarter, or year) and number of intervals to be added to the current date. The following code shows how to add a week at a time to the date:
The data-bound grid is a new control for Visual Basic 4. This control only works with the Data control. It cannot be used with the data access objects. The data-bound grid provides a spreadsheet-like view of the records in your recordset. This view is similar to the Table mode in Access or the Browse mode in FoxPro.
As stated, the data-bound grid may only be used with the Data control. The data-bound grid is one of the custom controls provided with Visual Basic. You must add the DBGrid control to the Toolbar by selecting it from the Custom Controls dialog box. To use the grid, select it from the Toolbar and size it on the form just like you would any other control. Add a Data control to the form and set it up to access the desired recordset. Then set the DataSource property of the DBGrid to the Data control. The grid is then set up. When activated, the grid can show all fields in all records of the recordset. If all of the information will not fit on the screen, the grid will display scroll bars to allow the user to view additional data.
The data-bound grid can be used for input by setting up the grid and setting the AllowAddNew and AllowUpdate properties to True. Used in this manner, the grid provides your users with access to a number of records at a time. They can then use the cells of the grid to edit individual fields in any record shown. Scroll bars allow the user to move through fields and records that do not fit on the screen.
If the AllowAddNew property has been set to True, an asterisk (*) will be shown in the last row of the grid. Entering information in this row will add a new record to the grid.
As an additional option for your users, you may wish to place the data-bound grid in a frame. Then, in a second frame, place the controls necessary to edit a single record of the recordset. By hiding one frame and providing a command button to switch the frames, you can provide your user with a means of viewing records in either a single-record mode or a browse mode. This is useful if the users want to do most of their editing in the browse mode, but like the additional information (specifically labels used as input prompts) that can be placed on the single-record view.
The data-bound grid is also useful for displaying the results of developer-defined queries, or, if you include a query builder in your application, user-defined queries. An example of this method is included in the project contained in the SAMPLQRY.VBP file on the companion CD. The example allows the user to choose from a number of queries on the youth data table. The user may filter the youth shown by grade, gender, or school attended. Figure 4.17 shows the selection form for the queries. Once the user has selected the query he wants, he clicks the Execute Query button to display the results.
Fig. 4.17 The user may select from several filters in the query selection form.
In the example, an MDI parent form is used to contain the query selection form and the query results forms. An MDI child form contains the Data control and the data-bound grid. When a query is run, a new instance of the child form is created and the database name and the SQL statement to create a recordset are passed to the child window. These variables are then used to set the DatabaseName and RecordSource properties of the Data control. The code to invoke the query results form is shown in Listing 4.3. This listing shows the query with no filter condition. Figure 4.18 shows the results of one of the queries from the example project.
Listing 4.3 Youthflt.Frm A Child Form Containing a Data-Bound Grid is Loaded to Display the Results of a Query
Fig. 4.18 The query results are displayed in a data-bound grid.
There are times in applications when it is desirable to add a group of records at one time. In the youth group membership application, an attendance-tracking module is included to keep track of which youth were at a particular event. One method of inputting this information would be to set up a form where the youth names are entered one-at-a-time on the form, and an attendance record is created with each selection. This method works but is inefficient for several reasons. First, a user may accidentally enter a youth more than once. Second, there is no easy method of checking the list of attendees prior to adding the information to the database. Finally, the method is slow because the processing of each attendance record is done individually instead of as a batch.
There is, of course, a better method. (You knew there was or this section wouldn't be here.) By using two List Box controls and a command button, you can set up what is called a two-column pick list. You are probably familiar with these if you have ever chosen options for setting up a windows program. The two-column pick list for the attendance example is shown in figure 4.19.
Fig. 4.19 A two-column pick list provides an easy way to enter multiple records.
With the two-column pick list, the user selects youth names from the first list by double-clicking the name with the mouse. The name is then removed from the first list (Youth In Group) and added to the second list (Youth Attending Event). Removing the name from the first list prevents the user from accidentally adding a name twice. After all the attendees have been added to the attendance list, the user can review the list to look for any omissions or people who were added accidentally. If necessary, the user can remove a name from the attendance list by double clicking on the name. This removes the name from the attendance list and adds it back to the first list. After the user is satisfied with the list, he can click the Process Attendance List button to create the actual attendance records in the database.
Since you don't want to constrain the user to just using the mouse, you will also want to add keyboard functionality to this input method. This is easily accomplished using the KeyDown event of each list. While a list has focus, the user may press the arrow or page keys to move through the list. This is an inherent behavior of the list box. The only thing you need to do is set up a keystroke for the user to make a selection. The natural key for this is the Enter key. To activate the Enter key for List1, add the following line of code to the KeyDown event:
If KeyCode = vbKeyReturn Then Call List1_DblClick
This code calls the DblClick event subroutine shown in Listing 4.4 when the Enter key is pressed in List1. A similar statement activates the Enter key for List2.
If KeyCode = vbKeyReturn Then Call List2_DblClick
To set up the list initially, a loop is used to pick up names from the recordset and add them to the list. This code is shown in the first part of Listing 4.4. Each List Box control on the form has the Sorted property set to True. This makes it easier for the user to find names in the list. The selection process for each list box is enabled by placing code in the DblClick event of the control. The code determines the item number of the selection, removes it from one list and adds it to the other list. This code is shown in the second part of Listing 4.4. Finally, when the user clicks the Process Attendance List button, the actual attendance records are created. This is done using a loop to scan the contents of the attendance list and create a record for each name on the list. This code is shown in the third section of Listing 4.4.
Listing 4.4 Attend.Frm These Code Segments Handle all the Processing for the Two-Column Pick List
Several notes should be mentioned about this method. First, in the code to create the attendance records, transaction-processing statements were used so that all attendance records could be added at once. This increases the performance of the application. Second, the name used in the List Box control was used in the attendance record. In most cases, you would probably use a member's ID to reduce the space required for the records. The name was used here to simplify the code listing.
Caution: The two-column pick list should probably be used only for relatively small recordsets (less than 200 records) since the list controls store each list item in memory. A large recordset would probably exceed available resources.
The previous sections of this chapter have focused primarily on ways to get data into a database. However, part of a database front end involves the presentation of data to the user. Information in text form can be displayed to the user with the same types of controls and forms used for data entry. In fact, one of the examples of use of the data-bound grid involved displaying the results of queries. Another method of presenting data is by using pictures. Access databases can store pictures in a Long Binary field. These pictures can be displayed in a picture box on a form.
Note: You cannot directly edit a picture in the picture box.
Another powerful way of presenting information to the user is through the use of graphs. Two ways to create graphs on your forms are:
Creating graphics is covered in detail in Chapter 16, "Graphics: Data Analysis." The following sections will make use of these methods, but will focus on the purpose of creating the graph—rather than the details of how to create them.
Static graphs can be used to show all types of information and can be presented in many forms. Examples include: a pie chart to show expense distributions, a bar chart showing weekly sales levels, or a high/low/close chart for stock quotes. Static graphs are meant to show data at a specific point in time (for example, at the end of a month). Figure 4.20 shows an example of a bar chart showing a company's sales levels by region for the previous week.
Fig. 4.20 Data charts provide the user with a visual aid in analyzing data.
To create this chart, a Graph control was placed on the form with its GraphType property set to 3 (2D – Bar). To set up the rest of the graph, data is accessed from the sales table in the sales database. The code to set up the graph is shown in Listing 4.5. The first lines of the listing set up the table to access the data. The code then uses a MoveLast method to set the RecordCount property of the recordset to the number of records accessed. This value is then used to set the NumPoints property of the graph. Next, a For...Next loop is used to set the values of the LabelText and GraphData properties for each point, based on the contents of the fields of the recordset. Once this code is completed, the graph is displayed.
Listing 4.5 Salesgr.Frm Data is Entered into the Graph's Data Array from the Sales Table
By looking at a chart, users can quickly get a feel for the average sales per region, and which regions are the best and worst performers. Combining the chart with supporting data, such as actual sales figures in a grid, gives the user even greater insight into the information. This combination is shown in figure 4.21.
Fig. 4.21 Combining a graph with detail data gives the user more information than either one alone.
Dynamic graphs are those that change with time. They are typically used to analyze ongoing processes. An example would be to view the output of a manufacturing operation. A particular process produces pellets that range in diameter from 0.45 inches to 0.50 inches. Any pellets outside that dimension must be rejected. The user wants to monitor the process to be able to spot any trends that might show the process heading toward a rejectable condition. If the trends are spotted soon enough, adjustments can be made to the process. The measurements from the process are automatically stored in a database.
One approach to this application would be to show the actual values of the last 10 samples. This would provide some benefit to the user, but would not provide as much information as he needs. A better approach would be to set up a chart that has the upper and lower bounds of the range on it, then plot each of the last 50 measurements on the chart. The chart would be updated with each new measurement so that the latest 50 measurements are always shown. A chart of this type is shown in figure 4.22.
Fig. 4.22 A time-dependent chart can show data trends.
By using a continuously updated chart, the user can spot trends as they are occurring. Then, if necessary, he can take action to adjust the process before a problem occurs. This type of data presentation provides real value for the user. The creation of dynamic graphs is covered in detail in Chapter 16, "Graphics: Data Analysis."
Color is another consideration in the presentation of data to a user. Color can be used to provide visual clues to the user about the data itself, or about the status of the data. For instance, you could place code in the Change event of a text box to notify the user that the data has been changed. The following code changes the text from black to blue as soon as any change is made to the data in the text box.
You might also want to use a color change to indicate that information is available for editing. This is very useful when using the data access objects, since you have to specifically invoke the edit method. In this case, you could have a button on your form that invokes the edit and changes the foreground color of all text boxes. Then when the user saves the data, code is run to restore the foreground color of the text boxes to their original values.
Another use of color would be in a financial application to show whether a number was greater or less than zero. In a database application where the user is examining credits and debits, the Listing 4.6 code in the Change event displays credits in green and debits in red. The Change event is used because it is triggered each time a new record is accessed.
Listing 4.6 Using Color to Provide the User with Information About His Data
This chapter has presented several ideas for input and output user interfaces for accessing databases. There are, of course, several ways that you can design a database front end. Hopefully, the ideas presented here will provide you with the basis for more advanced interfaces for your own applications.
For a discussion of other database topics, see the following chapters:
| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |
| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |
To order books from QUE, call us at 800-716-0044 or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.