• Creating an Instance of the Data Access Object with the Data Control
  • Properties of the Data Control
  • The Data-Related Methods of the Data Control
  • The Validate Event of the Data Control
  • Using the Data Control with Bound Controls
• Using Visual Basic Code with the Data Control
  • Using Code to Set the Value of Data Control Properties
  • Using Data Access Methods with Data Controls
    • Substituting Command Buttons for VCR Record Selection Controls
    • Preventing the No Current Record Error
    • Locating a Record with the Find. . . Methods
    • Updating, Adding, and Deleting Records
  • Validating Data with Visual Basic Code
• Summary

- 3 -
Using Visual Basic's Data Control

Visual Basic for Windows is a highly successful programming language; beginning programmers can use it to create an operating Windows application with little or no Visual Basic code. To carry this feature into the realm of database applications, Microsoft has added the Data control to Visual Basic 3.0, but they offer only a few controls that you can bind to the Data control. Therefore, developers of commercial VBX custom controls have created a variety of useful data-bound (also called data-aware) VBXs that connect to the Data control. Visual Basic 4.0 includes several, new data-bound OLE controls, which Microsoft has developed or licensed from established control developers.

Visual Basic 2.0 had very limited database connectivity; users were restricted to the ODBC API if they wanted to create data access objects. As well, few commercial ODBC drivers were available when Visual Basic 2.0 was released. The addition of the Data Access Object (DAO) and the Jet database engine to Visual Basic 3.0 gave Visual Basic developers the wide-ranging database connectivity of Access 1.x and, subsequently, Access 2.0. Virtually all client/server database suppliers now offer ODBC drivers for their products, though not all suppliers have released 32-bit versions of their ODBC drivers. The upgraded 16-bit and 32-bit Data control of Visual Basic 4.0 and the Jet 3.0 DAO combined with Visual Basic 4.0's new controls, which you can bind to the Data control, enable you to quickly create simple Windows database applications for desktop, client/server, and even mainframe databases with a minimum amount of code.

This chapter introduces you to the Data control and demonstrates how to use the Data control with simple data-bound controls. You use these controls to display and update the data in the fields of your database's tables.

Creating an Instance of the Data Access Object with the Data Control

The Data control automates the process of opening a database and creating a Recordset object by creating an instance of the Jet 2.5 (16-bit) or the Jet 3.0 (32-bit) DAO. The Recordset object is a virtual table containing the set of data elements you need for your application. The simplest instance of a Recordset object is an image of a table, similar to a VIEW that you create with the SQL statement CREATE VIEW name AS SELECT * FROM table. The difference between the Recordset object and a conventional VIEW of a table is that the data in a Recordset object is updatable if you have read-write access to the database and the table; a VIEW created with an SQL statement is read-only. The following sections describe the properties of and methods applicable to the Data control as well as show you how to create a simple form that uses the bound text box control to display and update data in a database table.

Properties of the Data Control

Table 3.1 lists the name, type of access (in design and run mode), description, and use of each of the data-related properties of the Data control. The remaining properties of the Data control, such as Caption, Enabled, and Visible, are shared with the majority of the other control objects of Visual Basic. The properties are listed in the order you ordinarily would enter them in the Properties window of the Data control.

---

If you change the value of the Database, RecordSource, Options, Exclusive, or ReadOnly property with Visual Basic code, you need to apply the Refresh method to the Data control to make the new values effective.

---

---

If your database application is designed for stand-alone (single-user) use, you can improve the performance of your application by opening the database with the Exclusive property of the Data control set to True. In either a stand-alone or multiuser environment, setting the ReadOnly property to True improves the performance of decision-support applications that, by definition, can't modify data values in the underlying tables. Exclusive and read-only access to databases and tables eliminates the overhead associated with record- or page-locking operations needed to maintain concurrency in a multiuser environment. Read-only and exclusive access eliminates the requirement for a Jet database lock (Database.ldb) file so that you can open a Jet database stored on a CD-ROM.

---

Table 3.2 specifies the values of the Options flag used to control access to the table(s) that participate in creating the Recordset object of your Data control. Enter the decimal value of the flag in the Options text box of the Properties window for a Data control. You can use the symbolic global constants, shown in Table 3.2, to make your Visual Basic code more readable. The symbolic Options constants are defined in the 32-bit Microsoft DAO 3.0 object library, supplied with Visual Basic 4.0. To use these symbolic constants, your Visual Basic project must have a reference to the DAO Object Library; by default, the 32-bit version of Visual Basic 4.0 inserts a reference to this DAO Library for all new projects. You can substitute the Microsoft DAO 2.5/3.0 Compatibility Library to maintain compatibility with existing Visual Basic applications that use db. . . constants defined in the DATACONS.TXT file that accompanied Visual Basic 3.0. (DATACONS.TXT is not included with Visual Basic 4.0.) Use the Object Browser (described in Chapter 2, "Understanding the Jet 3.0 Data Access Object") to view the available global symbolic constants; use the Tools | References command to add or delete references to a VB project.

---

A read-only Recordset is not the same as a snapshot-type Recordset object, nor is it the same as a Snapshot object. Read-only Recordset objects reflect changes made to the underlying tables by other applications in a multiuser environment.

---

The Data-Related Methods of the Data Control

In Visual Basic 4.0, the methods applicable to Dynaset-type Recordset objects in general also apply to Recordset objects belonging to a Data control (refer to Table 2.12 in Chapter 2). A Data control's Recordset object is accessible through the Recordset property (refer to Table 3.1). The Data control, however, has two methods that aren't included in Table 2.12 (because the methods apply only to the Data control itself, not to Recordset objects). Also, the Data control uses the Refresh method in a special way. Table 3.3 lists the three data-related methods that apply to the Data control.

Statements that contain the UpdateRecord and UpdateControls methods are only allowed within Validate event-handling procedures. Examples of using UpdateRecord and UpdateControls methods are provided later in this chapter. When the control is instantiated, in conjunction with opening the containing form object, the Refresh method automatically is applied to the Data control. As mentioned previously, after changing the value of any properties of the Data control, you need to apply the Refresh method. The Refresh method recreates the Recordset object's virtual table. So, when your application is designed for a multiuser environment, you can use the Refresh method to ensure that the data values in your Data control's Recordset reflect the current values of the data in tables.

The other methods applicable to the Data control are Drag, Move, and ZOrder; they act the same way on the Data control as they do on other control objects that support them.

The Validate Event of the Data Control

The Validate event is the only data-aware event that the Data control object can trigger. The purpose of the Validate event is to let you use event-handling code to test any or all changes to the values of bound controls. You can only test changes before they are made permanent in the database table(s) that underlie the Data control's Recordset. The Validate event is triggered under the following conditions:

• When you use the VCR buttons of the Data control or invoke one of the Move. . . methods to move the record pointer. The Validate event occurs before the record pointer moves from the current record to a new record.
  
  
• When you invoke the Update method before making the actual (physical) update to the data in the table(s) of the database. (The Validate event is not triggered when you execute the UpdateRecord method.)
  
  
• When you invoke the Delete or Close method or execute an Unload statement for a form containing a Data control (before the method or statement is executed).
  
  

The Validate event requires that your event-handling procedure includes parameters to accept the arguments returned by the Validate function. (This is the reason for the empty parentheses in the event's name. In this book, functions are identified by a pair of parentheses after the function name.) The syntax of the event-handling procedure stub for the Validate event is this:

Sub DataControl_Validate([iaintIndex As Integer,] intAction As Integer, fSave As Integer)



  ...



End Sub

If the control is a member of a control array, the optional iaintIndex parameter specifies the value of the index of the control. (The iaint prefix indicates that the variable is an index (i) into an array of the Integer (int) data type. (See Appendix B, "Naming Conventions for Visual Basic Objects and Variables," for details of the variable naming conventions used in this book.)

The required intAction parameter specifies the method that triggers the Validate event; Table 3.4 lists the values of the intAction parameter. The declarations for the global symbolic constants, listed in Table 3.4, that supply the values of the intAction parameters are part of the Visual Basic Object Library and are listed in the Object Browser. To use the values, just type them into your VB program code; if you wish, you may paste these constants into your code from the Object Browser.

The fSave flag determines whether data in bound controls has changed (in which case the data will be saved to the tables of the database) when your Validate procedure terminates. True indicates changed values, and False indicates that no change in data values has occurred. You can prevent saving changes by setting the value of the intAction parameter (cancel the event) or the fSave parameter to 0 (False).

---

This book uses the formal programming syntax for the terms arguments and parameters. When you call a function or procedure in your code, you pass arguments to it. The parameters of a function or procedure receive the values of the arguments. By default, Visual Basic passes references to the values of arguments, not the variable itself (except when you assign a literal value to an argument). Your function or procedure can change the value of variable arguments passed by reference. If you pass a literal value or precede the name of the variable passed as an argument with the By Val keyword pair, the actual value is passed to the function or procedure. You can't change the value of literal arguments or arguments passed by value.

---

You can't invoke methods, such as the Move. . . methods, that act on the Recordset object in a Validate event-handling procedure. Doing so causes recursion, a condition that happens when a function calls itself during its course of execution. Recursive functions, which are useful in arithmetic operations, are very difficult to write correctly and even harder to debug successfully.

You can use the events for individual bound controls to execute event-handler methods that enforce domain integrity at the field level, but individual event-handlers only execute if the controls receive the focus at some point during the update process. Events for validating individual control values are discussed later in this chapter. Data entry operators won't likely want to tab through every text box on a transaction processing form when they have altered the value of only one field. Therefore, the bound controls for some fields might never receive the focus. The Validate event is your only opportunity to ensure that domain integrity is enforced on all the fields of record.

---

Visual Basic's Validate event is similar to the BeforeUpdate event of Access 1+, except that the Validate event is triggered regardless of whether the data in the record is changed. (Access 1+ triggers the BeforeUpdate event only if the data in at least one field of a Recordset object is altered.)

---

Using the Data Control with Bound Controls

When you add a Data control to a form, all you get is a set of VCR buttons that control the position of the record pointer relative to the records that make up your Recordset. To create a Data control connected to a Jet database, follow these steps:

1. Choose File | New Project to open a new project with the default empty Form1 object.
   
   
2. Double-click the Data control button of the toolbox to add a Data control with the default name Data1 to the form. (The Data control button has two mini-VCR controls and two miniature bound text box symbols.)
   
   
3. Drag the right or left middle handle of Data1 to provide room for a caption between the left-hand and right-hand sets of VCR buttons.
   
   
4. Type d:\VB4\biblio.mdb as the value of the DatabaseName property. (Replace d:\VB4 with the name of the folder in which you installed Visual Basic 4.) Alternatively, you can click the ellipsis (. . .) button, located to the right of the DatabaseName text box, to open the Database Name dialog (a FileOpen common dialog) from which you can choose a database. The Database Name dialog is only applicable when selecting Jet (that is, Access) and Btrieve databases, because only Access and Btrieve databases use filenames in their DatabaseName property values. (The Database Name dialog only offers *.mdb and *.* as choices in the List Files of Type combo box.)
   
   
5. Type Publishers as the value of the RecordSource property. Alternatively, you can click the arrow button at the right of the RecordSource text box to open Biblio.mdb and display a list of the TableDef objects in Biblio.mdb's TableDefs collection. (Neither the QueryDef objects nor the system tables of Access databases appear.)
   
   
6. Type Publisher as the value of the Caption property.
   
   
7. Choose Run | Start to open the Biblio.mdb database and the Publishers table; then create the Database and Recordset objects for your Data control. (If you enter a non-existent database in the DatabaseName text box or misspell a name of a table in the RecordSource text box, you receive a Can't find 'ObjectName' error message.)
   
   
8. Choose Run | End to return to design mode.
   
   

---

The preceding step-by-step instructions are intended for xBase, PAL, Pascal, and C programmers who are just beginning to convert to Visual Basic 4.0. (Changing programming languages is akin to a religious conversion and usually causes similar effects on the convert.) Those readers who are accomplished Visual Basic programmers can be assured that, with a few minor exceptions, this is the only chapter of the book that includes beginning-level steps.

---

Figure 3.1 illustrates the design view of a Data control object connected to the Biblio.mdb sample database. The Publishers table is specified as the value of the RecordSource property.

Figure 3.1. Adding a Data control object to a Visual Basic form.

If your application includes a form that contains a Data control, Visual Basic 4.0 adds two additional properties. DataSource and DataField are added to the text boxes of the Properties window for the controls that can be bound to fields of a Recordset object. Visual Basic 4.0 offers the following fourteen bound controls:

Data binding of the ListBox, ComboBox, and OLE container controls in the preceding list is new with Visual Basic 4.0. Only the Professional Edition of Visual Basic 4.0 offers the last six controls in the preceding list, each of which is an OLE control (.ocx). Chapter 9, "Using Advanced Data-Aware OLE Controls," shows you how to get the most out of the DBList, DBCombo, and DBGrid controls. The standard Visual Basic 4.0 controls now offer the Windows 95 3D look when you set the Appearance property to 3D (the default).

The Publishers table has a total of ten fields. For this exercise, you add just four sets of bound labels and text boxes to display the names and values, respectively, of the PubID, Name, CompanyName, and Comments fields of the Publishers table. To do this, follow these steps:

1. Double-click the Label button of the toolbox to create a default label; then, double-click the Text Box button to create a default text box.
   
   
2. Size Label1 and Text1 to the approximate dimensions shown in Figure 3.2.
   
   Figure 3.2. Adding control objects bound to the Data control.
   
   
3. Bind Text1 to the Recordset object of the Data1 control by selecting Data1 in the drop-down list of the DataSource text box in the Properties window. This causes Data1 to appear.
   
   
4. Select both the label and the text box (click the label; then hold the Shift key down and click the text box, or drag a selection marquee around both controls). Then, press Ctrl+C to copy the combination to the Clipboard.
   
   
5. Press Ctrl+V to create a copy of the control set as Label2 and Text2. Click the No button when a message box appears that asks if you want to create a control array (that is, unless you want to create a control array).
   
   

   ---

   
   When you paste controls onto a form that already has controls with the same name, VB asks you to confirm adding the control, and then offers to create a control array. If you confirm adding the control (regardless of whether you create a control array), Visual Basic 4.0 renames the pasted control so there is no longer a name conflict, but it does not change the control's Text property. Therefore, if you paste a control named Text1 onto a form that already has a control named Text1, VB will rename the pasted control as Text2; the caption that you see in the text control will still say "Text1." Each text control in Figure 3.2 has had its Text property altered for clarity.
   

   ---

6. Repeat step 5 twice to create the control pairs Label3 and Text3 and Label4 and Text4, respectively.
   
   
7. To specify PubID as the value of the DataField property, select the Text1 control on the form and then select PubID in the drop-down list of the DataField text box of the Properties window. Size Text1 to approximately the size shown in Figure 3.2.
   
   
8. Repeat step 7 for Text2, Text3, and Text4, choosing Name, CompanyName, and Comments (as the values of the DataField property), respectively.
   
   
9. Select Text4 and double-click the Multiline text box to change the value of the Multiline property to True. The Comments field of the Publishers table is the Memo field data type, so you need to provide your users the ability to scroll through the content of the field.
   
   
10. Double-click the Scrollbars text box in the Property window until Vertical appears. Then, size Text4 until the text box has the approximate dimensions shown in Figure 3.2 so that Text4 can display multiline comments on the publisher's product line.
    
    
11. Type PubID:, Publisher:, Company:, and Comment:, as the values of the Caption property for Label1, Label2, Label3, and Label4, respectively.
    
    

Users of Microsoft Access will notice that the behavior of labels and text boxes in Visual Basic's design mode differs from that of Access's design mode. Visual Basic doesn't offer labels that are associated with text boxes, or that automatically assign the field name to their Caption property. When you double-click an Access label, you can edit the Caption property. When you double-click any Visual Basic control object in design mode, the code editing window for the default event-handler procedure stub (or the first event-handling procedure you wrote, in alphabetic order) appears.

---

Unlike Access, Visual Basic enables you to create label controls that are bound to a field of Recordset. Thus, you can add the ultimate read-only control to a Visual Basic form—a control that can't receive Windows focus. Many Access developers would trade their eyeteeth for the equivalent of a bound label control.

---

The design of the data display and editing form (the one you created in the previous steps) is illustrated by Figure 3.2.

When you choose the Run | Start command, your form appears similar to the one shown in Figure 3.3. (Although there is an entry for Sams Publishing in the Biblio.mdb sample database supplied with Visual Basic 4.0, it doesn't contain the comments shown here.) The VCR buttons of the Data control manipulate the record pointer to determine the current record. Here are the VCR buttons from left to right:

Figure 3.3. A very simple data display and editing application in run mode.

---

Access displays a "record number" between the two sets of its record selector VCR buttons, but Visual Basic's Data control does not. The record number displayed by Access isn't the same as the record number returned by the xBase function RECNO. RECNO returns a number that specifies the physical position of the record in the .DBF file, relative to the first record, which is numbered 1. The Access record number is a contrivance that represents the logical position of a record within an object of the Recordset class. Therefore, the number that Access associates with a record depends on the sort order of the Recordset. The concept of a record number doesn't exist in SQL databases, nor is record number an element of the relational algebra used to describe relational database structures. Both Access and Visual Basic use the Bookmark property to identify a specific record; therefore, you can return to the identified record after moving the record pointer to another location. Both Access and Visual Basic provide the RecordCount property, which you can use to determine the number of records in a Recordset object. Jet 3.0 also includes the new AbsolutePosition property that identifies each record in a Recordset (in the sort order of the Recordset) by number, starting with 0 for the first record. The new PercentPosition property specifies a record that corresponds to a particular percentage of the number of records in the Recordset.

---

A form can include more that one Data control. Figure 3.4 illustrates the form from Figures 3.2 and 3.3 with an added Data control that's connected to the Titles table of Biblio.mdb, together with the labels and bound text boxes necessary to display some of the fields of the Titles table.

Figure 3.4. A form with two independent Data controls and their associated bound control objects.

Each of the Data controls creates a Recordset object and manipulates its own record pointer. Text boxes and other bound controls with a binding to one of the Data controls reflect only changes to the record pointer of the Data control to which the control is bound. As an example, you can copy and paste a duplicate of all the controls associated with the Data1 control and then change the DataSource property value of the copies of the bound controls to create a totally independent instance of a Recordset object that is derived from the same Database and TableDef objects used by Data1.

---

If you don't want to create the preceding form, you can find the project and form files in your \Ddg_vb4\32_bit\Chaptr03 folder as Datacon3.vbp and Datacon3.frm (that is, if you installed the examples from the accompanying CD-ROM using the default directory structure).

---

Using Visual Basic Code with the Data Control

The primary advantage of using Data controls is that they enable the binding of text boxes, labels, picture boxes, image frames, check boxes, and the other bound controls listed earlier in the chapter. This chapter (as well as the earlier ones) points out that in order to create example-level Visual Basic database applications when using Data controls and bound control objects, you need to write little or no Visual Basic code. Data and bound controls are advantageous at this point in the book, because these controls let you learn the basic methods of manipulating Recordset objects without becoming entangled in ancillary housekeeping code.

On the other hand, the number of data-enabled controls, although enhanced in Visual Basic 4.0, is still somewhat limited; Access's bound object group, which returns numeric values based on mutually-exclusive option button or check box selections, doesn't have a Visual Basic equivalent. There is no Visual Basic version of the versatile linked subforms that are native to Access. You must write a considerable amount of code to create effective graphs with the Graph custom control, while Access provides a Graph Wizard to help you create graphs and charts with MSGraph5. Each of these programming subjects (which Access developers might call "workarounds") is dealt with in Part III of this book, "An Introduction to Database Front-End Design."

The following sections show you how to use Visual Basic event-handling code to manipulate the record pointer, validate updates to records, and perform other simple chores associated with entry-level database applications.

Using Code to Set the Value of Data Control Properties

You can read the value of all the data-related properties of a Data control and set the value of those data-related properties (shown in Table 3.1) as read/write (RW) in run mode. The remaining properties of the Data control object are common to the majority of other Visual Basic control objects. Properties shared by all or most Visual Basic controls are not discussed in this book. The only data-related properties of the Data control that you can't set are the Database and Recordset properties. These properties are pointers to the associated Database and Recordset objects, respectively. To protect the unwary, Visual Basic doesn't enable performing operations on pointers.

You can change the source of Visual Basic's Database and Recordset objects—to which form controls are bound during run mode. Therefore, you need not specify a DatabaseName or supply a value for the Connect string (when the value is needed) in your Visual Basic code. You can supply the property value(s) in run mode when you open the form that contains the Data control. Alternatively, you can let the user enter the source information in one of the following two ways: by selecting from a text box or by choosing tables from list box entries created from the Name property of TableDef objects of the TableDefs collection.

You also can read and set the properties of bound Data controls in run mode. Therefore, it's possible to use members of the TableDefs and Fields collections to supply the value of the DataField property. You can do this for each group of bound text boxes to which you haven't assigned default DataField values in design mode. Table 3.5 lists the data-related properties of bound controls. All the properties listed in Table 3.5 are read/write in run mode.

Using Data Access Methods with Data Controls

Most database front-end applications provide a set of command buttons to manipulate the record counter, as well as to update existing records, add new records, and delete obsolete records. The following are several reasons why command buttons are preferred to graphical record selector objects (such as VCR buttons):

• You can assign shortcut key combinations (Alt+key) to command buttons, but not to VCR buttons. Accelerator keys let data entry operators use the keyboard, not a mouse, to perform all data entry functions. (Ask any veteran IBM 029 keypunch operator what he or she thinks of using a mouse or trackball.)
  
  
• For those who don't regularly use trackballs or mice, the VCR buttons are a small target. Also, VCR buttons become increasingly ugly as you make them larger. On the other hand, command buttons are an easy target for myopic users to hit with the mouse pointer.
  
  
• First, Prior (or Previous), Next, Last, Add New, Delete, Update (or Save or OK), Cancel, and Quit buttons are traditional control objects. (You break the database tradition at the risk of thwarting your career or antagonizing your clients.) The initial letter of the captions of each of these buttons differs; therefore, using the initial letter as the accelerator key combination is practical.
  
  

The following sections show you how to substitute command buttons and Move. . . methods for the VCR record selection buttons, how to avoid the No current record pitfalls (which occur when you don't use the Data control's built-in safety net for manipulating the record counter), and how to find a specific record with the Find. . . methods.

---

The BOFAction and EOFAction properties of the Data control apply only to movement of the control's record pointer by mouse clicks to the Previous Record or Next Record buttons, respectively. These properties do not affect the behavior of the Data control when programmed with VBA code.

---

Substituting Command Buttons for VCR Record Selection Controls

Figure 3.5 illustrates the design of a data entry form. The form has four command buttons, which substitute for the four VCR record-selection buttons of the Data control, plus the ubiquitous Quit button. To create the form, follow these steps:

1. Open the Datacon3.vbp application in your \Ddg_vb4\32_bit\Chaptr03 directory. Create a new project from DataCon3 by saving Datacon3.frm as Datacmd3.frm and Datacon3.vbp as Datacmd3.vbp.
   
   
2. Delete the Data2 Data control and its associated bound text boxes (Text5 through Text8) and their labels.
   
   
3. Add text boxes for the Address, City, State, Zip, Telephone, and Fax fields from the Publishers table. Add label controls for the address and telephone numbers (see Figure 3.5). Make sure that the DataSource property (in the Properties window) of each new control refers to Data1; then set the corresponding Caption, Text, and DataField properties.
   
   
4. Add five command buttons at the right of the form (as shown in Figure 3.5). To comply with the object naming conventions used in this book, type cmdFirst, cmdPrior, cmdNext, cmdLast, and cmdQuit in the Name text box of the buttons, from top to bottom.
   
   Figure 3.5. Replacing the VCR record selector buttons with command buttons.
   
   
5. Type &First, &Prior, &Next, &Last, and &Quit in the Caption text box of the buttons, from top to bottom.
   
   
6. Double-click the Visible text box of the Data1 Data control to make False replace the default value, True.
   
   
7. Double-click each command button to display the event-handler code editing window for the Click event of the button. Add the statements Data1.Recordset.MoveFirst, Data1.Recordset.MovePrevious, Data1.Recordset.MoveNext, Data1.Recordset.MoveLast, and End to the event-handler stubs cmdFirst_Click through cmdQuit_Click, respectively.
   
   
8. Run your application and test the operation of the command buttons.
   
   
9. To demonstrate the flaw in the event-handling code for the cmdPrior and cmdNext buttons, click cmdFirst and then click cmdPrior twice. The No current record runtime error dialog appears (as shown in Figure 3.6), because you attempted to move the record pointer beyond the BOF (beginning of file) position. (To view the offending code, choose the Debug button in the error dialog; the code editing window for the cmdPrior_Click event handler opens.)
   
   
10. Click the End button in the runtime error dialog or, if you displayed the code editing window, choose Run | End. Restart your application by choosing the Run | Start command. (You don't have error-handling code, so you can't continue execution of your application after an error occurs.)
    
    
11. Click cmdLast; then click the cmdNext button twice. The error message you received in step 9 also appears in this case, because you attempted to move the record pointer beyond the EOF (end of file) position. You'll correct these defects in the next section.
    
    

Figure 3.6. The error that occurs when you attempt to move beyond the beginning or end of the Recordset.

Preventing the No Current Record Error

There are several strategies you can apply to prevent the No current record problem. Visual Basic provides a very flexible set of options for almost every database operation you can imagine. Therefore, you might encounter a "choice crisis" when attempting to choose the optimal method for writing code to solve a particular problem. The following are four of the choices you can use to prevent the appearance of the No current record message:

• You can use the Validate event to execute a procedure that tests whether the value of the BOF or EOF property of the Recordset is True. If you use the preceding approach, however, clicking the Next button after clicking the Last button brings up an empty record (which this book calls the tentative append record). The tentative append record is discussed in the section titled "Updating, Adding and Deleting Records," which follows shortly. A blank tentative append record also appears when you click the First button and then click the Prior button. Blank records are quite likely to confuse a user of your application, unless the user purposely has clicked an Add New button to append a new record to the database. Therefore, the preceding strategy is not optimal.
  
  
• You can store the value of the Bookmark property of the first and last records of the Recordset in two form-level or module-level variables, strFirst and strLast. Then, test whether the Bookmark value of the current record matches strFirst in the cmdPrior_Click procedure or strLast in the cmdNext_Click procedure. This requires that you set the values of the two variables by invoking the MoveLast and MoveFirst methods in the Form_Open procedure. In a multiuser environment, the first or last record on the open form might not be the first or last record when you manipulate the record pointer. This happens if other users are making changes to the tables that underlie the Recordset simultaneously. This is a better procedure than the preceding approach, but it might suffer from concurrency problems.
  
  
• You can test the value of a field of the Recordset for which Null values are not valid. (The value of each field of a blank record is Null until data is entered in the field.) Null values cannot appear in a primary key field; the PubID field is the primary key field of the Publishers table of Biblio.mdb. If the value of PubID is Null, you know the record pointer is positioned on one of the blank records. This method works for Access and client/server databases, which both support the Null value, but not for xBase .DBF files or Paradox .DB files. You can test for empty Character fields or Numeric fields with 0 values in .DBF files, but your application would need to be altered to use different database types.
  
  
• You can test the BOF and EOF properties of the Recordset and invoke the MoveFirst or MoveLast methods if clicking the Prior or Next button causes the record pointer to land on a blank record. This is the simplest and most straightforward method of preventing the occurrence of the No current record message. You can add a Beep command to inform the user that he or she attempted an invalid action. A beep from the PC's speaker (or from the computer's sound system) is less intrusive than a message box that informs the user that a trivial misdeed has been committed.
  
  

If you choose the last technique of the preceding list, you need to alter your code for the cmdNext_Click and cmdPrior_Click event-handling procedures to contain the code that appears in Listing 3.1.

Listing 3.1. The code for preventing the No current record message from appearing.

Private Sub cmdNext_Click ()



  Data1.Recordset.MoveNext



  If Data1.Recordset.EOF Then



    Beep



    Data1.Recordset.MoveLast



  End If



End Sub



Private Sub cmdPrior_Click ()



  Data1.Recordset.MovePrevious



  If Data1.Recordset.BOF Then



    Beep



    Data1.Recordset.MoveFirst



  End If



End Sub

Choosing any of the other techniques in the preceding list requires additional code. As a rule of thumb, the quality of a procedure is inversely proportional to the square root of the number of the lines it contains (not counting comments).

Locating a Record with the Find. . . Methods

There are only sixty-some records in the Publishers table of Biblio.mdb, so finding a record that matches a particular criteria with the record selector buttons, while not a major undertaking, is somewhat inconvenient. Using record selector buttons to locate a particular record is obviously an efficient method, especially with large tables. The Find. . . method solves this problem for Recordset objects with the Dynaset type or Snapshot type. The Data control, by default, creates a Dynaset-type Recordset object. (You can't use the Find. . . method on table-type Recordset objects; indexed table-type Recordset objects require that you apply the Seek method.) Here is the syntax of the Find. . . method:

rsRecordset.FindFirst strCriteria



rsRecordset.FindNext strCriteria



rsRecordset.FindPrevious strCriteria



rsRecordset.FindLast strCriteria

rsRecordset represents any valid object reference to a Dynaset-type (rsdRecordset) or Snapshot-type (rssRecordset) Recordset object. The value of strCriteria is the WHERE clause of an SQL statement without the WHERE reserved word. Chapter 5, "Learning Structured Query Language," describes the syntax of the WHERE clause of SQL statements in detail. If you want to search for the first occurrence of a publisher located in California, use the following code fragment in the event-handler for a Find button:

Dim strCriterion As String



strCriterion = "State = 'CA'"



Data1.Recordset.FindFirst strCriterion

Notice that you need to enclose the value for which you are searching within quotation marks. Single-quote characters (') are the standard for identifying literal values in SQL statements, and single quotes eliminate the necessity of using """ or Chr(34) expressions to add double-quote characters (") to your code.

Hard-coding search literals is not an effective method, so you need a means to let the user enter the Find. . . criteria. The simplest method is to use the InputBox function to create a simple dialog that includes a text box into which the user can enter the value of strCriteria. You can create a more elegant Find dialog by opening a modal pop-up form that includes a text box, Find and Help buttons, and option buttons to choose which of the Find. . . methods to invoke. Chapter 8, "Designing a Decision-Support Front-End," includes an example of a full-featured Find dialog based on the design of the Find dialog of Access.

To implement the FindFirst method for the DataCmd3 application, follow these steps:

1. Declare strCriterion and strBookmark as form-level string variables in the Declarations section of Form1 with Dim strCriterion As String and Dim strBookmark As String statements. This enables you to use the value of strCriterion that you establish in the cmdFindFirst_Click procedure with a FindNext procedure you add later. You can use the value of strBookmark in conjunction with a Return button to return the record pointer to its position prior to the Find. . . operation.
   
   
2. Add a Find First button to Form1 and assign cmdFindFirst as the value of its Name property.
   
   
3. Double click the Find First button to open the event handler stub, cmdFindFirst_Click.
   
   
4. Add the code shown in Listing 3.2 to cmdFindFirst_Click.
   
   
5. Run DataCmd3 and click the Find First button. Type ca in the text box, as shown in Figure 3.7, and then click the OK button to find the first record for a publisher located in California.
   
   

Listing 3.2. Code to open an input box then invoke the FindFirst method.

Private Sub cmdFindFirst_Click()



  'Declare local variables



  Dim strPrompt As String



  Dim strTitle As String



  Dim strEntry As String



  'Set up the prompt and the title, then open the input box



  strPrompt = "Enter the two-character postal abbreviation " & _



              "for the state you want to find."



  strTitle = "Find First Occurrence of State"



  strEntry = InputBox(prompt:=strPrompt, title:=strTitle)



  If Len(Trim(strEntry)) = 2 Then



    'The user made an entry, set a bookmark



    strBookmark = Data1.Recordset.Bookmark



    'Create the criterion string



    strCriterion = "State = '" & UCase(strEntry) & "'"



    'Invoke the FindFirst method



    Data1.Recordset.FindFirst strCriterion



    If Data1.Recordset.NoMatch Then



      'No matching record was found



      MsgBox prompt:="No records match '" & _



                     UCase(strEntry) & "'.", _



             title:="Record Not Found"



      Data1.Recordset.Bookmark = strBookmark



    Else



      'A match was found, place the caret in the State field



      Text7.SetFocus



    End If



  End If



End Sub

Figure 3.7. The input box for entering a criterion value for the FindFirst method.

---

If you want to bypass the typing of the code examples in Listing 3.2 as well as the remaining code listings of this chapter, open Datacmd.vbp in your \Ddg_vb4\32_bit\Chaptr03 folder.

---

You can add a Find Next button that uses the same criterion value employed by the FindFirst method with the code shown in Listing 3.3.

Listing 3.3. Code for invoking the FindNext method with a previously-created criterion.

Private Sub cmdFindNext_Click ()



  'Check to see if the criterion has been entered



  If Len(strCriterion) > 0 Then



    'Invoke the FindNext method



    Data1.Recordset.FindNext strCriterion



    If Data1.Recordset.NoMatch Then



      'No matching record was found



      MsgBox prompt:="No more records match " & strCriterion, _



             Title:="Record Not Found"



      Data1.Recordset.Bookmark = strBookmark



    Else



      'A match was found, place the caret in the State field



      Text7.SetFocus



    End If



  Else



    'There was no criterion



    MsgBox prompt:="You need to enter a criterion with the " & _



                   "Find First button first", _



           Title:="No Criteria Entered"



  End If



End Sub

A better method than using the message box to advise the user that no criterion has been entered in conjunction with a FindFirst operation is to disable the Find Next button until a FindFirst operation has found a matching record. Code that enables command buttons only when their use is appropriate to the status of the application is called housekeeping code. Chapter 8 deals with housekeeping code for decision-support applications.

---

The Find. . . methods of Visual Basic applied to Recordset objects use the same syntax as and behave identically to the Find. . . methods of Access applied to Access 2+ Recordset objects of the Dynaset and Snapshot types, as well as the Dynaset and Snapshot objects of Visual Basic 3.0 and Access 1.x.

---

Updating, Adding, and Deleting Records

Figure 3.8 illustrates the set of buttons that you need to complete a basic data display and editing form. The added buttons include Update, Cancel, Add New, Delete, and Return. The Update button confirms both editing and appending new record operations. The Return button returns the record pointer to a record that you have previously identified with a bookmark.

Figure 3.8. The button set for creating a complete data entry form.

The code examples in Listing 3.4 constitute the simplest form of event-handling code for each of the buttons. Only the bare minimum of code necessary to perform the buttons' functions is included. Some combinations of procedures generate an error, such as clicking the Add New button and then clicking the Delete button. You also can generate an error if you attempt to add a new record without entering a unique numeric value for the PubID field. Code you add to the Validate event-handler, described in the next section, can detect the missing PubID problem.

Listing 3.4. Code required to provide DataCmd3 with minimal record-editing capability.

Private Sub cmdUpdate_Click ()



  'Transfer data from changed text box values to current record



Data1.Recordset.Update



End Sub



Private Sub cmdCancel_Click 



  'Cancel an AddNew or Update operation



  'Restore the value of text boxes to the original field values



  'NOTE: The Text1 text box is bound to the PubID field



  Data1.Refresh



  If IsNull(Text1.Text) Or Text1.Text = "" Then



    'Don't attempt to update controls from an aborted append



    If Len(strBookmark) > 0 Then



      'Return to the bookmarked record



      Data1.Recordset.Bookmark = strBookmark



    Else



      'Go to the last record



      Data1.Recordset.MoveLast



    End If



  End If



End Sub



Private Sub cmdAddNew_Click ()



  'Position the record pointer on the tentative append record



  Data1.Recordset.AddNew



End Sub



Private Sub cmdDelete_Click ()



  'Delete the current record with confirmation



  If MsgBox(prompt:="Are you sure you want to delete this record?", _



            buttons:=vbQuestion + vbYesNo, _



            title:="Delete Record") = vbYes Then



    'Test if the to-be-deleted record is the bookmarked record



    If (Len(strBookmark) > 0) And _



         (strBookmark = CStr(Data1.Recordset.Bookmark)) Then



      strBookmark = ""



    End If



    'Delete the record



    Data1.Recordset.Delete



    'Refresh the Recordset



    Data1.Refresh



    If Len(strBookmark) > 0 Then



      Data1.Recordset.Bookmark = strBookmark



    Else



      Data1.Recordset.MoveLast



    End If



  End If



End Sub



Private Sub cmdMark_Click ()



  'Mark the current record



  strBookmark = Data1.Recordset.Bookmark



End Sub



Private Sub cmdReturn_Click ()



  If Len(strBookmark) > 0 Then



    'Move the record pointer to the Bookmarked position



    Data1.Recordset.Bookmark = strBookmark



  Else



    Beep



  End If



End Sub

Like the examples for the Move. . . commands in the prior section, the preceding code examples in this section do not include housekeeping code to set the enabled properties of the command buttons or provide error-handling routines. Chapter 13, "Designing Online Transaction-Processing Applications," shows you how to determine the logic for command buttons that appear on data entry forms.

Although the DataCmd3 application provides only rudimentary control over record pointer positioning and editing operations, DataCmd3 demonstrates how little Visual Basic code is required to create an application that is almost usable in a commercial environment. Figure 3.9 shows the appearance of the final version of the Datacmd3.frm (Form1).

Figure 3.9. Running the DataCmd3 data entry form.

---

When you click the Add New button, you position the record pointer on the tentative append record. The tentative append record is a buffer (dedicated memory location) for a record that is automatically appended to the Recordset if the user enters data in any of the fields of the record and then moves the record pointer. The tentative append record is similar to the empty record you create with the xBase APPEND BLANK command, except that Visual Basic only appends the record if there is data in at least one field of the record.

---

Validating Data with Visual Basic Code

A new record or an edited record without the data required to maintain referential integrity and information about the real-world object to which the record refers (in this case a publishing company) is invalid and compromises the data integrity of the database. To constitute a valid record in the Publishers table, a record needs to have a unique PubID value to serve as the primary key field. The PubID value, a Long integer, of added records should be consecutive with the last (highest numbered) record in the Publishers table. In addition, a record without at least a publisher's imprint (the Name field) is invalid. Therefore, you need to test at least the value of the PubID and Name fields of a proposed update to an existing record or a newly-added record before the data in the Publishers table is altered. The Validate event and the associated Sub Validate event-handling code serve this purpose.

The Data1_Validate event-handler code of Listing 3.5 makes the tests described in the preceding paragraph and displays a message box if the PubID or Name value is missing. If either value is missing, both the intAction and fSave parameter values are set to False, preventing addition of an invalid record. The code added to the cmdAddNew_Click event-handler automatically adds 1 to the last valid value of PubID and inserts the value in the PubID text box (Text1), so you don't have to remember the last PubID number.

Listing 3.5. Using the Validate event-handler to verify the minimum data entry requirement.

Private Sub Data1_Validate (intAction As Integer, fSave As Integer)



  'Validate entries when adding new or editing existing record



  Dim strMsg As String    'Message box message



  If (intAction = vbDataActionAddNew) Or _



      (intAction = vbDataActionMoveNext) Then



    'Add new or update occurred: Text1 is PubID, Text2 is Name



    If (Text1.Text = "") Or (Text2.Text) = "" Then



      If Text1.Text = "" Then



        strMsg = "A Publisher ID number (PubID) is required."



      Else



        strMsg = "An imprint (Name) is required."



      End If



      MsgBox prompt:=strMsg, Title:="Required Field Data Missing"



      intAction = vbDataActionCancel



      fSave = False



    End If



  End If



End Sub



Private Sub cmdAddNew_Click ()



  Dim lngPubID As Long



  'Add a new record after refreshing the Dynaset



  Data1.Refresh



  'Find the last value of PubID and add 1



  Data1.Recordset.MoveLast



  lngPubID = Data1.Recordset!PubId



  'Text2 is the Name field



  If Text2.Text <> "" Then



    'Increment PubID if the last record is valid



    lngPubID = lngPubID + 1



  End If



  'Position the record pointer on the tentative append record



  Data1.Recordset.AddNew



  'Pass the PubID value to the PubID text box (Text1)



  Text1.Text = lngPubID



End Sub

---

Apply the Refresh method liberally when you add, update, or delete records of Recordsets. Using the Refresh method (except when validating an entry) ensures that your application sees only valid records and records whose data values have been altered by others in a multiuser environment.

---

Summary

This chapter introduced you to the use of the Data control and the bound control objects that you can use when you add a Data control to one of the forms of your application. Elementary examples of using Visual Basic code to position the record pointer, to add, update, and delete records, and to validate data prior to using the data to update values in tables outline the basic capability of the Data control. At this point, you've created your first data display and editing form with Visual Basic 4.0.

This chapter completes Part I of Database Developer's Guide with Visual Basic 4. You'll create more sophisticated examples of decision-support and transaction-processing applications when you reach Part III, "An Introduction to Database Front-End Design," and Part IV, "Advanced Programming with Data Access and OLE Objects." The next chapter, "Optimizing the Design of Relational Databases," introduces you to database design methodology and shows you some of the automated design tools available for Jet and client/server databases.