Platinum Edition Using Visual Basic 5

Previous chapterNext chapterContents


- 30 -
Doing More with Bound Controls

While Microsoft Jet (Access) is Visual Basic's database format of choice, the Data control can work with several other types of databases.
Some properties of the Data control allow it to perform additional tasks in specific situations.
You are not limited to the recordset that was created when you set the DatabaseName and RecordSource properties at design time.
You can bind list boxes and combo boxes to a Data control. There are also special versions of these controls that automatically can populate themselves from a Data control's recordset.
Several of the new Windows 95 controls also can be bound to a Data control.
In many situations, you need more than one Data control on your form.

In Chapter 29, "Using the Visual Basic Data Control," you got a first look at the Data control and some of the bound controls that are available in Visual Basic. You saw how the controls can work together to create a good data access application. What you might not have realized is that the Data control and bound controls have a wider range of functionality than that presented in Chapter 29.

In this chapter, we'll learn how to use the Data control in conjunction with the data bound controls to make our data-aware applications extremely powerful and easy to create.

Exploring the Data Control In-Depth

One of the Data control's additional features is the capability to access a number of other database types beside the Jet database. It also can work with all types of recordsets, not just dynaset-type recordsets. In addition to this flexibility, you can use program code to change the properties of the Data control and enhance its capabilities. You get a brief look at this when program code is used to enable you to add and delete records in the data entry application in Chapter 31, "Improving Data Access with the Data Access Objects (DAO)."

What Are Its Advantages and Limitations?

While the Data control does have a lot of capabilities, there are also some things that only can be done with program code. As Chapter 28, "Improving Data Access with the Data Access Objects," shows you, you can create a database application without using the Data control at all. In order to help you determine whether to create your program with the Data control, with just the data access objects, or with a combination of the two, you need to have an understanding of the advantages and limitations of the Data control.

See "Creating the Database," Chapter 28

Advantages of the Data Control The key advantage to using the Data control is that you don't have to do much, if any, programming to develop a data access application. You don't have to provide program code to open a database or recordset, to move through the records, to edit existing records, or to add new records. The Data control makes initial application development quicker and code maintenance easier. When using the Data control, you also have the advantage of specifying data objects (database and recordset) at design time, and you can select these options from dialog boxes and lists. Selecting options from lists cuts down on typographical errors that you can introduce into the application.

Another advantage of the Data control is that it provides a direct link to the data. You don't have to specifically invoke the Edit and Update methods to modify the data in the database. Consequently, your users' changes show up in the database as soon as they enter them.

In addition to these advantages of using the Data control, there are several bound controls, which provide an easy way to accomplish tasks that are difficult to duplicate with just the data access objects and program commands. These bound controls are the data bound list box, data bound combo box, and data bound grid. (See the sections on these controls in this chapter: "Data Bound Lists and Combos," and "Data Bound Grids.")

Limitations of the Data Control As useful as the Data control is, it also has a few limitations. These limitations include the following:

In the section "Programming the Data Control's Events" later in this chapter, you see how to overcome some of these limitations by combining the Data control with program code.

Using Other Databases

The Data control is designed to work best with Jet (Access) databases, but you can work just as easily with other database formats. These formats include some traditional database formats like dBase, FoxPro, and Paradox, as well as data that is not typically thought of as a database--for example, Excel spreadsheets or text files.

For any of the database formats, you still need to set the DatabaseName and RecordSource properties of the Data control. You also need to set the control's Connect property to identify the type of database being used. In addition, the DatabaseName property is treated differently for some database formats than for Jet databases. For example, with dBase files, the DatabaseName property refers to a directory instead of a single file.

Setting the Connect Property The Connect property tells the Jet engine what kind of database you are using. A listing of Connect property settings for various database types can be found in the Connect Property (DAO) topic of Visual Basic's help system. The Data control makes it easy for you to set the Connect property. To change its value, you select the desired database format from a drop-down list in the Properties window, as shown in Figure 30.1.

FIG. 30.1
The Connect property lets you use the Data control with many types of databases.

Considerations for the DatabaseName Property Some database formats, like Jet, store all the tables of the database along with other database information in one database file. Other formats, like FoxPro and dBase, store each table in a separate file, and may even have other files for such things as indexes and Memo field contents. Depending on the database format you select, the information you specify in the DatabaseName property may represent something different. For Jet databases, you just specify the name of the database file. For dBase databases, the DatabaseName property must be set to the name of the folder containing the database files. If you need to use a database type other than Jet, refer to Visual Basic's help system for specific DatabaseName property requirements.

Working Directly with Tables and Snapshots

The Data control, by default, creates a dynaset-type recordset when you specify the RecordSource property. However, you can have the Data control create a snapshot-type recordset or even a table-type recordset to access a table directly. To handle this, you just need to change the setting of the RecordsetType property. You do this by selecting the desired type from a drop-down list in the Properties dialog box (see Figure 30.2).

FIG. 30.2
Use the RecordsetType property to determine the type of recordset created by the Data control.

One reason you might want to change the RecordsetType is for performance. For example, if you do not need to be able to edit the contents of the recordset, you might want to use a snapshot-type recordset because it provides faster access than a dynaset-type. In another case, you might want to use a table-type so that you can change the presentation order of the recordset by changing the controlling index of the table, since indexes are supported only by table-type recordsets.


CAUTION: If you specify a RecordsetType that cannot be created in a particular situation, you will get an error when you try to run your program. This is most likely to occur when you set the RecordsetType to Table and use a SQL statement or query in the RecordSource property.


TROUBLESHOOTING:I tried to set an index for my recordset using the Index property of the Data control, but was unable to do so. The Index property does not refer to a database index, but to the index position in an array. A Data control, like any other Visual Basic control object, can be part of a control array. If you have such an array, the Index property specifies the position of the current control in that array. Remember that a Data control, by default, creates a dynaset-type recordset, and you cannot apply an index to this type of recordset. You can use an index if you specify that the Data control should create a table-type recordset, but you must set the Index property of the Recordset object associated with the Data control, as in this example:

datYouth.Recordset.Index = "Name"

You can set an index only for the Data control's Recordset object if you have set the RecordsetType property to 0 - Table.


Tracking the Current Record

A recordset has a record pointer that keeps track of the current record. There cannot be more than one current record at any given time.

You may think of the beginning and end of a file as the first and last record, respectively, of a recordset. However, this is not actually the case. A recordset has a special position called beginning-of-file (BOF) that is located before the first record. Similarly, the special end-of-file (EOF) position is located after the last record. This can create problems in some data access programs because there is no current record when the record pointer is positioned at the beginning or the end of the file.

By default, the Data control avoids this problem by setting the record pointer to the first record when the beginning-of-file is reached and setting the pointer to the last record when the end-of-file is reached. This assures that there is always a current record for viewing or editing. However, there may be times when you want to know when you have actually reached the BOF or EOF positions even while using the Data control. You can control what the Data control does at the beginning or end of the file by setting the BOFAction and EOFAction properties of the Data control.

The BOFAction property, which tells the Data control what to do when the beginning of file is reached, has two possible settings:

The EOFAction property, which tells the Data control what to do when the end-of-file is reached, has three possible settings:

You can choose the values of each of these properties from a drop-down list in the Properties dialog box. The 2 - Add New setting of the EOFAction property can be useful if you have an application that needs to add new records. As with most other properties of the Data control, you can reset these properties at runtime.


NOTE: These BOF and EOF actions are triggered only when the user reaches the beginning or end of the file by using the Data control's navigation buttons. They have no effect if the beginning- or end-of-file is reached by using data access methods (such as MoveNext) in code.

Other Optional Properties

In addition to the properties already covered, there are three other key properties for the Data control that you can set:

Exclusive Determines whether others can access the database while your application is using it. You can set the property to True (your application is the only one that can access the database) or False (others can access the database). The default value is False.

ReadOnly Determines whether your application can modify the data in your defined recordset. You can set the property to True (your application can't modify data) or False (your application can modify data). The default value is False. Setting this property to True is not the same as using a snapshot. Because a snapshot-type recordset is a copy of the data in memory, it is faster than a read-only dynaset-type recordset.

Options Allows you to specify other properties for the recordset created by the Data control.

Programming the Data Control

There are several ways that you can use program code to work with the Data control and the bound controls. By using program code, you can make your program more flexible than with the Data control alone. The following list gives you a few of the ways that you can have program code work with the Data control:

Changing Properties On-the-Fly

Like any other control that you have on a form, you can change the properties of the Data control and bound controls at runtime. Most of the properties of the Data control and the bound controls are changeable. Only a few, like the Name property, are read-only at runtime.

Properties of the Data Control You can choose to set (or reset) the DatabaseName, RecordSource, and RecordsetType properties of the Data control at runtime. The following list outlines some of the reasons why you might want to do this:


TIP: If you distribute your application, you will often need to change the DatabaseName property of the Data control to handle differences between your directory structure and that of the user.

If you need to set the parameters at runtime, simply set the properties with code statements like those shown in Listing 30.1. Note that you must specify the name of your Data control, the property name, and the desired property value.

After you set the properties, use the Data control's Refresh method to implement the changes, as shown in the last line of Listing 30.1. The changes to the Data control (that is, the creation of the new recordset) take affect after the Refresh method is invoked.

Listing 30.1 Setting or Changing the DatabaseName and RecordSource Properties of a Data Control at Runtime

`*******************************************
`Set the value of the DatabaseName property
`*******************************************
datMembers.DatabaseName = "C:\YOUTH\YOUTHTRK.MDB"
`******************************************
`Set the value of the RecordSource property
`******************************************
datMembers.RecordSource = "Family"
`*********************************************************
`Set the value of the RecordsetType property to table (0)
`*********************************************************
datMembers.RecordsetType = vbRsTypeTable
`***********************************************
`Use the Refresh method to implement the changes
`***********************************************
datMembers.Refresh

Properties of the Bound Control In a similar manner, you can set the properties of the bound controls at runtime. You can change the DataSource of a bound control to access a different Data control on the form. You also can change the setting of the DataField property to have the control display the contents of a different field in the recordset.


CAUTION: Be careful when you change the DataSource or DataField properties in code. If you enter an invalid Data control name or field name, an error will occur.

Recordsets and the Data Control (Set Command)

One particularly useful feature of the Data control is the capability to create a recordset with the data access objects and then assign the recordset to the Data control. This gives you increased flexibility in the recordsets that can be used. Because you also can assign the recordset of the Data control to a recordset object, this means you can pass the recordset to other procedures or to class modules for processing. This feature is something that was not available in Visual Basic prior to version 4.

The Set statement is the key statement to use when you want to allow recordsets to be moved back and forth between a recordset object and the Data control. Use the Set statement any time you want to assign a value to an object. You will also see the Set statement used in Chapter 31, "Improving Data Access with Data Access Objects (DAO)," when you open databases and recordsets with the data access objects.

The following code shows you how to use the OpenRecordset method to create a recordset:

`Create the recordset
SQLSel = "Select LastName, FirstName From Members "
SQLSel = SQLSel & "Where Member Order By LastName, FirstName"
Set datRec = OldDb.OpenRecordset(SQLSel, dbOpenDynaset)

After the recordset has been created, this code will assign it to a Data control:

`Assign the recordset to the Data control
Set datNames.Recordset = datRec

You can use this capability to enable your users to specify sort or filter criteria for a recordset. Then, with the user-defined criteria you can create a recordset using a SQL statement. This recordset then can be assigned to an existing Data control to display the results of the query in a grid or a series of bound controls.

Being able to pass the contents of a Data control to a recordset object enables you to do things like write a generic routine for handling deletions. This way, you can write one procedure or class to query the user for verification of the deletion and then perform the deletion if the user agrees. The code in Listing 30.2 shows you how such a procedure works.

Listing 30.2 Procedures Let You Reuse Code Easily

`Define the procedure
Sub DelRecord(Navset As Recordset)
Dim MsgStr As String
`Delete the current record
MsgStr = "Are you sure you want to delete this record"
RetCode = MsgBox(MsgStr, vbYesNo, "Deletion Confirmation")
If RetCode = vbYes Then
     avset.Delete
End If
End Sub
`*******************************************************
`Call the procedure with the recordset of a Data control
`*******************************************************
DelRecord datNames.Recordset


CAUTION: A recordset object and the Data control recordset are identical immediately after the Set statement, and at this time, they both point to the same record. However, after a record movement function is performed on either recordset, they are out of synchronization. Therefore, be careful when you are using both a recordset object and a Data control to manipulate the same data.

Programming the Data Control's Events

The Data control has three key events for which you can write program code: the Validate event, the Reposition event, and the Error event. While most of the actions of the Data control are handled automatically, programming for these events can help you add capabilities to your program and help you handle errors.

Using the Validate Event When you create a Jet database, you can specify validation rules to be checked before each record is saved. However, you might have situations where the validation rules for your program are more complex than the Jet engine can handle. In this case, you need to perform the data validation in program code. How can you do this when the Data control saves data automatically? The Data control has an event, the Validate event, that is triggered whenever the record pointer is about to be moved. This event occurs when the user presses one of the navigation buttons on the Data control or when the form containing the Data control is unloaded.

When the Validate event is triggered, the Data control examines all controls that are bound to it to determine if any data in any of the controls has changed. Two parameters then are set for the Validate event: the Save parameter, which tells you whether any data has been changed, and the Action parameter, which tells you what caused the Validate event to fire. The Save parameter can be either True or False. The Action parameter can have one of 12 values, as defined in Table 30.1.

Table 30.1 The Value of the Action Parameter Tells You Why the Validate Event Fired

Constant Value Description
vbDataActionCancel 0 Cancels any Data control actions
vbDataActionMoveFirst 1 MoveFirst
vbDataActionMovePrevious 2 MovePrevious
vbDataActionMoveNext 3 MoveNext
vbDataActionMoveLast 4 MoveLast
vbDataActionAddNew 5 AddNew
vbDataActionUpdate 6 Update
vbDataActionDelete 7 Delete
vbDataActionFind 8 Find
vbDataActionBookmark 9 Sets a bookmark
vbDataActionClose 10 Uses the Close method of the Data control
vbDataActionUnload 11 Unloads the form

Listing 30.3 shows how you can use the Validate event to perform data checking.

Listing 30.3 Data Checking in the Validate Event

Private Sub datMembers_Validate(Action As Integer, Save As Integer)
    Dim CompStr As String
    If Save = True Then
       CompStr = Trim(txtYouth(0).Text)
       If Len(CompStr) = 0 Then
          MsgBox "First Name cannot be blank"
          Action = vbDataActionCancel
       End If
    End If
End Sub

This code checks the Save parameter to see if any of the data in the bound controls has been changed. If it has, the code proceeds to perform the data checking. In this case, the code is checking to make sure a non-null string was entered in one of the text boxes. If the string is zero-length, a message is displayed to the user, and the action that was being processed is canceled by setting the Action parameter to vbDataActionCancel.


NOTE: This particular data verification can be handled by the database engine when you set a validation rule or set the AllowZeroLength property of the field to True. However, many programmers prefer to handle data validation from within the program, rather than relying upon the Jet engine's validation rules.

Using the Reposition Event The Reposition event occurs after the Data control's current record has changed, as opposed to the Validate event, which occurs before the current record changes. This allows you to perform some type of processing once the new record becomes current. For example, you may want to modify the form's caption to reflect the record that is being displayed, or recalculate information such as an invoice total based on data from the new record. This code sample demonstrates how to accomplish both of these tasks:

Private Sub Data1_Reposition()
    Dim crTemp As Currency
    Me.Caption = "Invoice Number " & txtInvNumber.Text
    crTemp = Val(txtInvGross) - Val(InvSalesTax)
    lblInvTotal.Caption = Format(crTemp, "currency")
End Sub 

Handling Errors with the Error Event The other event of note for the Data control is the Error event. The Error event also has two parameters associated with it: the DataErr and the Response parameters. The Error event is triggered when a data access error occurs when no program code is running, such as when the Data control is loaded or the user clicks one of its buttons, but the database specified in the DatabaseName property cannot be found or has been corrupted. When the event is triggered, the error number is reported via the procedure's DataErr parameter. You then can write code in the event procedure to handle the different data access errors. The Response parameter determines the action to be taken by your program. If the parameter is set to vbDataErrContinue (0), your program attempts to continue with the next line of code. If the parameter is set to vbDataErrDisplay (1), the default value, an error message is displayed. When you write your error handling for the Error event, you can set the Response parameter to vbDataErrContinue for those errors that are corrected by your code and set it to vbDataErrDisplay for all other errors.

Data Control Methods

In addition to being able to respond to events, the Data control also has several methods that you probably will want to use in your programming. We'll discuss these three:

The Refresh method causes the Data control to rerun the query that created the recordset and access all data that is currently in the recordset. There are several occasions when you need to use the Refresh method:

The UpdateRecord method forces the Data control to save the information in the bound controls to the recordset. Typically, you will want to place a Save or Update button on any data entry form you create. (This is done automatically if you use the data form designer.) The reason for this is to enable the user to save his work on the current record without having to move to another record. The Data control only saves changes when the record pointer is moved or when the form containing the Data control is unloaded. The following line of code shows how you can force a Data control to update the current record:

datYouth.UpdateRecord

The companion method to the UpdateRecord method is the UpdateControls method. This method retrieves information from the current record and redisplays it in the bound controls. This has the effect of canceling any changes made by the user. By placing the following line of code in the Click event procedure of a command button, you can implement a cancellation feature:

datYouth.UpdateControls

Other Bound Controls

In Chapter 29, "Using the Visual Basic Data Control," you were introduced to five bound controls: the TextBox, Label, CheckBox, PictureBox, and Image controls. Visual Basic actually has quite a few bound controls that you can use in your programs:

See "Bound Control Basics," in "SQL Primer," on this book's CD-ROM.

Lists and Combo Boxes

The ListBox and ComboBox controls enable the user to choose one item from a list of items; the ComboBox control also allows the user to enter an item that's not in the list. You can bind either of these controls to a data field to store the user's choices in a field. You do this by setting the DataSource and DataField properties of the control. To give your user a list of items from which to select, use the control's AddItem method. For this sample case, use a combo box to enable users to select the title for the member they are editing (see Figure 30.3). Listing 30.4 shows how to populate the list of choices.

Listing 30.4 Populating the List with the AddItem Method

Combo1.AddItem "Mr."
Combo1.AddItem "Mrs."
Combo1.AddItem "Ms."
Combo1.AddItem "Miss"
Combo1.AddItem "Dr."


TIP: You also can enter the list items at design time by using the List property.

FIG. 30.3
Use a list or combo box to present your user with a list of choices.

Data Bound Lists and Combos

The data bound list box (DBList control) and data bound combo box (DBCombo control) are similar in function to their standard counterparts. They are designed to present the user with a list of choices. The key difference is that the data bound list and combo box controls get their list information from a recordset rather than from a series of AddItem statements.


TIP: In order to use the DBList or DBCombo controls, you must add them to the Toolbox. Choose Project, Components. In the Controls tab of the Components dialog box, make sure Microsoft Data Bound List Controls 5.0 is selected.

Consider an example from the sample case. As your users enter data about a member, you want the user to be able to enter easily the county and state names of the member. One of the tables in the database contains county information. You can use the data bound list box to let your users select a county from those contained in the county table. The data bound list takes the county ID selected from the County table and stores it in the appropriate field of the Members table. You might think it would be hard to select the appropriate county if all you can see is the ID. However, the data bound list and combo boxes let you select a second field from the source table to serve as the display in the list. This means that you can display the name of the county in the list box but store only the county ID in the Members table. Figure 30.4 shows this concept graphically.

You set up the data bound list or combo box by specifying five properties. Table 30.2 describes these properties.

Table 30.2 Properties for Data Bound List Box or Combo Box

Property Sample Case Setting Description
RowSource datCounty The ame of the Data control containing the information used to populate the list
BoundColumn CountyID The ame of the field containing the value to be copied to the other table
ListField CountyName The ame of the field to be displayed in the list
DataSource datYouth The ame of the Data control containing the recordset that is the destination of the information
DataField CountyID The ame of the destination field

FIG. 30.4
The data bound list and combo boxes let you pick an item from one table for inclusion in another table.

You can set each of these properties by selecting the property from the Properties dialog box and choosing the setting from a drop-down list. When setting the properties of the data bound list and combo boxes, keep in mind the following points:

Figure 30.5 shows the data bound combo box added to a sample data entry form.

FIG. 30.5
A data bound combo box lets the user select from a list of counties.

Data Bound Grids

The data bound grid (DBGrid control) provides a means to view the fields of multiple records at the same time. The data bound grid is similar to the table view used in Access or the Browse command used in FoxPro. It displays information in a spreadsheet style of rows and columns. You can use it to display any alphanumeric information.

To set up the data bound grid, you need only to specify the DataSource property to identify the Data control containing the data. The grid then displays all fields of all records in the recordset. If the information is larger than the area of the grid you defined, scroll bars are presented to let you view the remaining data.


TIP: To conserve application resources, use a QueryDef or SQL statement in the grid Data control's RecordSource property. This way you can keep the number of records and fields that the grid handles to a minimum.

Your user can select a grid cell to edit by clicking the cell with the mouse. To add a new record, the user positions the pointer in the last row of the grid indicated by an asterisk (*) and enters the desired data. These capabilities are governed by the AllowUpdate and AllowAddNew properties, respectively. The default value of the AllowUpdate property is True; the default value of the AllowAddNew property is False. You can set these properties appropriately either at design time or runtime.

For the sample case, use the data bound grid to display the Members information in a browse mode. Allow the user to switch between the browse mode and single-record mode by using the command button at the lower-right corner of the screen. This saves screen real estate. Figure 30.6 shows the data bound grid for the sample case.

FIG. 30.6
You can use the data bound grid to display information from many records at once.

Other Visual Basic Controls

The other bound controls are set up the same way you would set up a text box or check box. Specifically, you set the DataSource property to the name of the Data control containing the data to be displayed and then set the DataField property to the specific field in the recordset.

Third-Party Controls

One of the greatest features about Visual Basic is the capability to extend its functionality through the use of third-party controls. This functionality also extends to bound controls. Many third-party vendors have controls that can be bound to a Data control, and some vendors even market enhanced Data controls. For example, Apex Software Corporation, which developed the DBGrid control included with Visual Basic, sells an enhanced version called True DBGrid Pro, which adds many features to the standard control.

Further Enhancements

In Chapter 29, "Using the Visual Basic Data Control," you saw how you needed to use code to implement some features that were not available with the Data control alone. Specifically, an Add and Delete function were added to the capabilities of the data entry form with some simple coding. There are a few other enhancements that can be implemented with just a little bit of code and some ingenuity.

See "Essential Functions the Data Control Forgot," Chapter 29

Find and Seek Operations

Another enhancement to the features provided by the Data control is the capability to search for a specific record. To add this feature, you must use either the Find method or the Seek method of the Data control's Recordset object, depending upon the recordset type. For a table-type recordset, use the Seek method; for the others, use the Find method. To implement the search in our example, add a command button to the form. This command button invokes a dialog box that requests the ID to be found and then uses the appropriate method to perform the search (see Listing 30.5).

Listing 30.5 Use the Seek or Find Method to Search for a Specific Record

`***************************************************************
`The variable SrchCond contains the value of the search criteria
`***************************************************************
If dayYouth.RecordsetType = vbRSTypeTable Then
  datYouth.Recordset.Seek ">=", SrchCond
Else
   datYouth.Recordset.FindFirst "datYouth.Recordset([LastName]) >= " _
        & SrchCond
End If

What About Options? (Option Buttons)

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 letting the user select between a number of mutually exclusive choices. A typical use is for selecting gender in a membership application, as shown in Figure 30.7.

FIG. 30.7
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 can be selected. Option buttons can be grouped on a form, in a picture box, or in a frame. It is good practice to set up each group of option buttons in a picture box or frame to avoid conflicts with other groups.

After 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 an F (female) is stored, depending on the option button selected.


TIP: 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 example:
isel = 0
`Loop through five option buttons
For I = 0 To 4
If Option(I).Value Then
isel = I
`Exit the loop when the selection is found
Exit For
End If
Next I


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 keeps 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:

Sub Male_Click()
   txtGender.Text = `M'
End Sub
Sub Female_Click()
   txtGender.Text = `F'
End Sub

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 follows:

If txtGender.Text = `M' Then
   Male.Value = True
Else
   Female.Value = True
End If

While this discussion has focused on how to use the option buttons with the Data control, you also can 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:

OldTbl.Edit
If Male.Value Then
   OldTbl("Gender") = `M'
Else
   OldTbl("Gender") = `F'
End If
OldTbl.Update

From Here...

This chapter has shown you how to use some additional features of the Data control, what other bound controls are available to you, and how you can add features to enhance your data entry forms. There are other chapters that address other aspects of database applications:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.