• Creating Database Programs with Visual Basic Code
• • Why Use Code Instead of the Data Control?
  • Searching for a Record
    • Using Move to Navigate Recordsets
      • Figure 10.1.
  • Listing 10.1. Coding the form-level variables.
  • Listing 10.2. Opening the database and a Dynaset.
  • Listing 10.3. Coding the cmdMove events.
    • Using Seek on Table Recordsets
  • Listing 10.4. Coding the cmdSeek_Click event.
    • Figure 10.2.
  • Using Find on Non-Table Recordsets
  • Listing 10.5. Coding the cmdFind_Click event.
    • Figure 10.3.
  • Creating Your Own Bound Controls
    • Preparing the Data Entry Form
  • Listing 10.6. Coding the global variables.
    • Coding the Property Handling Routines
  • Listing 10.7. Coding the property-handling routines.
  • Listing 10.8. Coding the Class_Initialize and Class_Terminate events.
    • The RSOpen Routine
  • Listing 10.9. Coding the RSOpen function.
    • The RSInit Routine
  • Listing 10.10. Coding the RSInit function.
    • The RSLocate Routine
  • Listing 10.11. Coding the RSLocate routine.
    • The RSRead Routine
  • Listing 10.12. Coding the RSRead function.
    • The RSWrite Routine
  • Listing 10.13. Coding the RSWrite function.
    • The RSEnable Routine
  • Listing 10.14. Coding the RSEnable function.
    • The RSDelete Routine
  • Listing 10.15. Coding the RSDelete function.
    • Other Record Routines
  • Listing 10.16. Coding the RSClose, RSBack, and RSNext routines.
  • Creating Your Own Button Bar Routines
    • The BBInit Routine
  • Listing 10.17. Coding the BBInit routine.
    • The BBEnable Routine
  • Listing 10.18. Coding the BBEnable routine.
    • The BBProcess Routine
  • Listing 10.19. Coding the BBProcess routine.
  • Creating a Data Entry Form with the Library Routines
    • Figure 10.4.
  • Modifying the Master Form
    • Figure 10.5.
  • Building the State/Province List Form
    • Figure 10.6.
  • Listing 10.20. Coding the form-level variables.
  • Listing 10.21. Coding the StartProc routine.
  • Listing 10.22. Coding the Form_Load routine.
    • Figure 10.7.
    • Figure 10.8.
  • Summary
  • Quiz
  • Exercise

Day 10

Creating Database Programs with Visual Basic Code

Today you'll learn how to create complete database entry forms using Visual Basic code instead of the data control. You'll learn how to open a database, establish a Recordset, and prepare a data entry form to allow records to be added, edited, and deleted. You'll also learn how to create a generic record locate routine to use with any data entry form, as well as how to create a set of command buttons to handle all data entry functions. You'll learn about the Visual Basic methods you can use to locate single records and about the Seek method for table objects and the Find and Move methods that you can apply to all Recordsets.

All the routines you create today are generic and portable. You write these routines in an OLE Server library module that you can use in your future database projects. For the lesson today, you'll add these library routines to a new form for the CompanyMaster database project you started last week. When you finish today's exercises, you'll be able to build a fully functional data entry form with less than 30 lines of Visual Basic code.

Why Use Code Instead of the Data Control?

Before jumping into the code routines, you should know the difference between writing data entry programs with the Visual Basic data control and writing them without the Visual Basic data control. There are advantages and disadvantages to each method.

The advantage of using the data control is that you can quickly put together solid data entry forms without writing much Visual Basic code. This method works well for small, one-time projects that need to be completed quickly. The disadvantage of using the data control is that once the project is completed, it is not always easy to modify the data entry form or adapt the finished form for another data entry project. Also, forms built using the data control are not always easy to debug or maintain because most of the action goes on in the data control itself. If you think your project needs to be modified or maintained by other programmers, the data control might not be your best choice.

The advantage of using complete Visual Basic code to produce data entry forms is that you have total control over all aspects of the process. You decide when to open the database and Recordset, and you control the record read and write operations, too. This capability can be a real advantage in multiuser settings where increased traffic can cause locking conflicts in programs that use the data control. Another advantage of using Visual Basic code for your data entry forms is that you can create generic code that you can reuse in all your database projects. When you have a fully debugged set of data entry routines, you can quickly create new forms without much additional coding. Because the forms rely on generic routines, they are also easy to modify and maintain in the future.

The primary drawback for using Visual Basic code to create data entry forms is that you have to handle all processes yourself; you can assume nothing. For example, locating and updating a single record in a data table requires that you account for all of the following processes:

• Opening the database
• Opening the Recordset
• Locating the requested record
• Loading the input controls from the Recordset
• Handling all user actions during the data entry process
• Writing the updated controls back to the Recordset

Add the possibility of user errors and database errors, and you have a good bit of responsibility! And you haven't even seen what you need to do to add a new record to the table or delete an existing one. You also need a way for the user to browse the data. Remember that dropping the data control means your form does not automatically display the VCR-style navigation arrows.

Despite this added responsibility, writing your data entry forms with Visual Basic code gives you much greater control over the process and can result in a form that is easy for both programmers and users to deal with. Even though you have to do a good bit of coding to create new data management routines, you can place most of this new code in an OLE Server DLL that can be reused in future projects with a minimum amount of coding.

Searching for a Record

Before you create the generic data entry routines, you need to examine an important topic, record searching. Up until now, we have only touched on this issue. You can use one of several methods to search for a record in a Recordset; some are faster than others. Using the most effective method in your Visual Basic programs can make your programs seem fast and solid. Using an ineffective search method can make your program seem slow.

The Visual Basic data-access object interface is a set-oriented interface. It is designed and tuned to quickly return a set of multiple records that meet your search criteria. However, a major part of data entry processing involves key-oriented searches. These are searches for a single, specific record that needs to be updated. Visual Basic offers the following three different approaches to handling key-oriented searches:

• The Move methods: You can use these methods to browse records one by one (commonly called "walking the dataset"). The Move methods allow you to use Visual Basic code to move from one record to the next in the dataset.
• The Seek method: You can use this method to perform an indexed search of the dataset to find the first record that meets your criteria. This search method is the fastest one provided by Visual Basic, and it can only be applied to Recordsets that are opened tables. Dynasets and Snapshots cannot use the Seek method.
• The Find methods: You can use these methods to locate a single record in the dataset that meets a set of criteria you establish. This criteria is similar to the SQL WHERE clause you learned about in Day 8, "Selecting Data with SQL." The Find methods perform a sequential search of the dataset to locate the first record that meets your criteria.

Using Move to Navigate Recordsets

The Move methods offer the most basic form of record searching. There are four methods you can apply to the Recordset object:

• MoveFirst: This method moves the record pointer to the first record in the dataset. This method is the same as clicking the double-headed arrow on the left side of the data control.
• MovePrevious: This method moves the record pointer to the record just before the current record. This method is the same as clicking the single-headed arrow on the left side of the data control.
• MoveNext: This method moves the record pointer to the record just after the current record. This method is the same as clicking the single-headed arrow on the right side of the data control.
• MoveLast: This method moves the record pointer directly to the last record in the dataset. This method is the same as clicking the double-headed arrow on the right side of the data control.

To practice using these methods, start a new Visual Basic project. Save the form as FRMMOVE.FRM and the project as PRJMOVE.VBP. Table 10.1 contains a list of controls to add to the form. Refer to Figure 10.1 as a guide as you lay out the form.

Figure 10.1. Laying out the frmMove form.

Table 10.1. Controls for project PRJMOVE.VBP.

After laying out the form, you need to add the code. Enter Listing 10.1 in the general declarations section of the form. This code declares all the form-level variables you use in the project.

Listing 10.1. Coding the form-level variables.

Option Explicit
`
` form-level vars
`
Dim strDBName As String
Dim strRSName As String
Dim ws As Workspace
Dim db As Database
Dim rs As Recordset 

Listing 10.2 shows the code that opens the database and then opens a Dynaset for your use. Add this code to the Form_Load event.

Listing 10.2. Opening the database and a Dynaset.

This routine initializes the database and Recordset name variables and then creates the related data objects. Performing this step is similar to setting the DatabaseName, RecordSource, and RecordsetType properties of the data control.

You need to create a Sub procedure to handle the process of reading the current record and loading the data into the form controls. Create a Private Sub procedure called ReadRow and then add the following code to the routine:

Public Sub ReadRow()
    `
    ` fill controls with current value
    `
    Label1.Caption = rs.Fields(0)
    Label2.Caption = rs.Fields(1)
    `
End Sub

This routine copies the first column in the current row of the Recordset to the first form control and then copies the second column of the Recordset to the second form control.

You need to create code for each of the four command buttons on the form. Each button needs to perform two tasks:

• Reposition the pointer as requested
• Read the data from the new current row

The four code pieces in Listing 10.3 do these tasks. Enter the code in that corresponds to the command button into the Click event of that command button. For example, enter rs.MoveFirst and ReadRow into the Click event of the cmdMoveFirst command button. Then enter rs.MoveLast and ReadRow into the cmdMoveLast command button, and so on.

Listing 10.3. Coding the cmdMove events.

Private Sub cmdMoveFirst_Click()
    `
    rs.MoveFirst
    ReadRow
    `
End Sub

Private Sub cmdMoveLast_Click()
    `
    rs.MoveLast
    ReadRow
    `
End Sub

Private Sub cmdMoveNext_Click()
    `
    rs.MoveNext
    ReadRow
    `
End Sub

Private Sub cmdMovePrevious_Click()
    `
    rs.MovePrevious
    ReadRow
    `
End Sub 

You need to add two more routines to finish up the project. The following code forces the first record onto the screen at startup. Add this code to the Form_Activate event:

Private Sub Form_Activate()
    `
    cmdMoveFirst_Click
    `
End Sub

The last bit of code performs a safe close of the database at the end of the program. Add this code to the Form_Unload event:

Private Sub Form_Unload(Cancel As Integer)
    `
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    `
End Sub

Save the form as FRMMOVE.FRM and save the project as PRJMOVE.VBP. When you run the project, you can click the buttons in order to walk the dataset. This project operates the same as the data control arrow buttons.

---

NOTE: If you click the First button and then immediately click the Previous button, you get a runtime error. This error is caused by attempting to read past the beginning of the dataset. Later today, you'll create a routine that prevents this error from occurring in your programs.

---

The project you created in this section is a good example of how you can provide users with a way to browse the dataset on a form. In the next section, you see how to give your users the ability to search for a particular record in the dataset.

Using Seek on Table Recordsets

The fastest way to locate a specific record is to use the Seek method on a table object. The Seek method performs an indexed search for the first occurrence of the record that matches the index criteria. This search uses the type of index used by ISAM-type databases. Indexed searches are easy to perform and are very fast.

Modify the PRJMOVE.VBP project to illustrate index searching by adding another button to the form. Set the button's Name property to cmdSeek and its Caption property to &Seek. Next, add Listing 10.4 to the cmdSeek_Click event.

Listing 10.4. Coding the cmdSeek_Click event.

Listing 10.4 does three things. First, it prompts the user to enter a value for which to search. Second, the code confirms that the user entered a value and then performs the Seek operation. After performing the Seek operation, the code uses the NoMatch method to get the results of the Seek operation (this is the third operation performed in this routine). The results of the search are then posted in a message box. If the search was successful, the new record is loaded into the form controls.

To make this routine work, you have to make a few changes to code in the Form_Load event. Change vbOpenDynaset to vbOpenTable, and then add the following line to the end of the routine, just after the OpenRecordset line:

rs.Index = "PrimaryKey" ` set index property

Now save and run the project. This time, click the Seek button. When the dialog box appears, accept the default value of 10 and click OK. You should see a message telling you that the search was successful (see Figure 10.2).

Figure 10.2. The results of the Seek method on a table object.

---

TIP: You can use other comparison values besides = with the Seek method. You can use <, <=, =, >=, or > as a comparison value.

---

Although Seek is the fastest search method, you can apply it only to Recordsets opened as table objects. If you want to locate a specific record in a Dynaset or Snapshot, use one of the Find methods.

Using Find on Non-Table Recordsets

Because Dynaset and Snapshot objects do not use indexes, you cannot use the Seek method to search for specific records within them. The Find method is used to locate specific records in non-table objects (Dynasets and Snapshots). The Find method is a sequential search; it starts at the beginning of the dataset and looks at each record until it finds one that matches the search criteria. Although this method is not as fast as Seek, it is still faster than using the Move methods to handle this operation within your own Visual Basic code.

The syntax for the Find methods is almost identical to the SQL WHERE clause (covered in Day 8). The search string consists of a field (or set of fields) followed by a comparison operator (=,<>, and so on) and a search value (for example, MyRS.FindFirst "Au_id=13").

There are actually four Find methods: FindFirst, FindPrevious, FindNext, and FindLast. The FindFirst method starts its search from the beginning of the file. The FindLast method starts its search from the end of the file and works its way to the beginning. You can use the FindPrevious and FindNext methods to continue a search that can return more than one record. For example, if you are looking for all the records that have their ZipCode column set to 99999, you could use the FindFirst method to locate the first record and then use the FindNext method to continue the search forward until you reach the end of the dataset. Similarly, you can use the FindLast and FindPrevious methods to perform continued searches starting at the end of the dataset. Although the FindNext and FindPrevious methods are available, it is usually better to create a new Recordset using the Find criteria if you expect to locate more than one record that meets the criteria.

Modify the PRJMOVE.VBP project to illustrate the Find method by adding another button to the project. Set the button's Name property to cmdFind and its Caption property to F&ind. Next, add the code in Listing 10.5 to the cmdFind_Click event.

Listing 10.5. Coding the cmdFind_Click event.

Listing 10.5 is almost identical to the code used in the cmdSeek_Click event (refer to Listing 10.4). Notice that you have to build the criteria string to include the name of the field you are searching. Because the Find method can be applied to any field (or fields) in the table, you must supply the field in the search criteria.

Before saving the project, comment out the line in the Form_Load event that sets the index. Also, change dbOpenTable to dbOpenSnapshot. Now save and run the project. When you click the Find button, enter 13 in the input box. You should see a message telling you that the Find operation was successful (see Figure 10.3).

Figure 10.3. The results of the non-table Find method.

Notice that if you click the Seek button, you eventually get an error message. You cannot apply a Seek method to a non-table object. Also, you cannot apply a Find method to a table object. Later, you'll learn how to write a single locate routine that is smart enough to figure out which search method to use for your Recordset object.

Creating Your Own Bound Controls

Up to this point, you have been creating your Visual Basic database programs by using the data control as the heart of the system. After learning about the Microsoft JET data engine and covering some basics on searching techniques, you are now ready to create an OLE Server library that allows you to build solid data entry forms without using the data control. The rest of this day is devoted to constructing this OLE Server library.

---

NOTE: A finished version of the RecObject OLE Server library is contained in the RECOBJECT.CLS class file on the CD that comes with this guide.

---

There is a series of operations that must be handled for any data entry system. First, let's outline these operations, and then you can use that outline as a guide in constructing your library functions. The following is a list of common operations used in almost all data entry forms:

• RSOpen: This routine opens a database and selects a set of records for processing.
• RSInit: This routine initializes the data entry form and prepares the on-form controls for reading and writing data records.
• RSLocate: This routine provides a front end for performing Seek and Find operations on the dataset.
• RSRead: This routine reads the selected record and loads the on-form controls with the contents of the data fields.
• RSEnable: This routine handles the enabling and disabling of the input controls to manage user updates to the data form.
• RSWrite: This routine copies the values from the data entry form back to the dataset for storage.
• RSDelete: This routine gives the user the power to delete the current record from the dataset.

In addition to the record-handling routines, you also build a set of routines to design and manage a command button toolbar. This toolbar provides access to basic data entry functions such as add, edit, delete, and locate, as well as the four browse actions: first, next, previous, and last moves. These three additional routines handle the actions that involve the command buttons:

• BBInit: This routine creates the button set on your form. You can place the button set on the top, bottom, left, or right side of the form.
• BBEnable: This short routine enables you to temporarily disable selected buttons on the bar to make sure the user does not mistakenly invoke a search action in the middle of an update action.
• BBProcess: This routine is the heart of the data entry form. It links the button set with the previously mentioned record functions to provide a complete, customized data entry form for your applications.

You design these routines to work with any dataset you select, as well as any form layout you choose, using any input controls (not just the Visual Basic data-bound controls). Also, you construct the routines as a set of methods within the standalone OLE Server. That way, you can add the record-handling routines to all your future programming projects.

Finally, the OLE Server library has a handful of properties that you can use to control the behavior of the record-processing routines. The following is a list of the properties you need with the OLE Server:

• WSName: This property is the local workspace name.
• DBName: This property is the database name.
• RSName: This property is the Recordset name or SQL statement.
• RSType: This property is the value to indicate a table, Dynaset, or Snapshot object.
• Index: This property is the index name to use for table objects.
• IndexFlag: This property is a True/False toggle. When this property is True, use the Seek method.
• BBAlign: This property is the alignment value that controls the appearance of the control buttons on the form.
• Focus: This property is the field that gets first focus when you are adding or editing a record.

In the following sections, you go through the process of building the code library. After the library is built, you build a simple form to add to the CompanyMaster project. This form uses all the library functions covered in this section.

Preparing the Data Entry Form

The routines we have designed make a few assumptions about how your data entry forms are constructed. These assumptions are very general and result in a solid, if not flashy, data entry form. After completing these routines, you might want to modify the library functions to add additional features and options that suit your particular data entry needs.

For each data entry form you design using these routines, you need to stay within the following guidelines:

• Each data entry form corresponds to a single dataset. Following this guideline is easy when you are dealing with table-type datasets. You can design a single form for each table. If you need to perform data entry on a set of columns that are the result of a multiple-table SQL JOIN operation, you can use the dataset produced by the JOIN as the basis for the data entry form.
• Each data entry form contains a single command button named cmdBtn. You must set its Index property to 0 to indicate that it is part of a control array. All the routines you build expect this command button.
• Every column in the dataset row that requires data entry is represented by a single textbox control on the form. The control and the field are related by placing the column name in the Tag property of the input control. This procedure enables you to bind your input controls to your dataset.

After incorporating these guidelines, you can lay out your forms in any manner you like.

Begin this project by building the library of record-handling functions. Start a new Visual Basic 5.0 ActiveX DLL project. Set the class name to recObject by filling the Name property of the class module.

---

TIP: Be sure to set the Option Explicit option to On for this project. This option forces you to declare all variables before they are used in your program. Using the Option Explicit setting helps reduce the number of program bugs you create as you enter these routines.

---

Before you begin the heavy coding, complete the declaration section of the library routine. Enter Listing 10.6 at the top of the module.

Listing 10.6. Coding the global variables.

The first two enumerated values in Listing 10.6 are used throughout the routines to indicate the types of datasets and the location of the button bar set on the form. The rest of the values represent local storage for public properties of your OLE Server class. After you have entered the code in Listing 10.6, save the module as RECOBJECT.CLS.

Coding the Property Handling Routines

Now that you've created the local storage for the properties, you can use the Tools | Add Procedure menu option to create Public property procedures, too. Listing 10.7 shows the code for all the property-handling routines in the library. Use the Property names as a guide in creating the properties with the Tools | Add Procedure menu and then enter a associated code into each of the Property Let and Get methods.

Listing 10.7. Coding the property-handling routines.

Next you need to add the code for the Class_Initialize and the Class_Terminate events. See Listing 10.8 for the code for these two events.

Listing 10.8. Coding the Class_Initialize and Class_Terminate events.

Now that you have dealt with the properties, you're ready to start coding the main record-handling routines. The next several sections contain the code for all the record-handling routines.

The RSOpen Routine

The RSOpen routine handles the opening of an existing database and the creation of a Recordset to hold the selected records. Enter Listing 10.9 into the class module. Be sure to include the Function declaration line. Visual Basic supplies the End Function line automatically.

---

TIP: You should save your work after entering each coding section to ensure that you do not lose much work if your computer suffers an unexpected crash.

---

Listing 10.9. Coding the RSOpen function.

This routine accepts the user's form as a parameter, uses the property values to create a complete database and Recordset connection, and then initializes the data entry form and fills it with the first record in the dataset.

Another new twist here is that almost all the routines in this library are declared as Functions instead of Subs. These functions return an integer value that indicates whether any errors occurred during the operation. This value gives you a very easy way to check for errors from within Visual Basic code. Note that any error number returned by the Visual Basic code is sent back to the user's program for handling. This is a simple way to pass internal errors out of the class module into the caller's routine.

---

TIP: You should comment out the On Error lines of your program while you are first entering the Visual Basic code. When the error trap is on, even simple typing errors set it off. During the construction phase, you want the Visual Basic interpreter to halt and give you a full error message. When you are sure you have eliminated all the programming bugs, you can activate the error handlers by removing the comment mark from the On Error program lines.

---

The RSInit Routine

The RSInit routine clears out any stray values that might exist in the form controls that you are binding to your data table. Remember that you can bind a form control to a dataset column by placing the name of the column in the Tag property of the field. This routine checks that property and, if it contains information, initializes the control to prepare it for receiving dataset values. Enter the code in Listing 10.10 as a new function.

Listing 10.10. Coding the RSInit function.

This routine contains a simple loop that checks all the controls on the form to see whether they are bound to a dataset column. If they are, the control is initialized.

The RSLocate Routine

The RSLocate routine prompts the user to enter a value to use as a search criteria on the Recordset. The routine is smart enough to use the Seek method for table objects and the Find method for non-table objects. Add the routine in Listing 10.11 to your module.

Listing 10.11. Coding the RSLocate routine.

Notice that if you set the IndexFlag property to True in this routine, the routine uses the Seek method instead of a sequential Find method. Also note the check for a text-type search field. If the target field to search has a dbText data type, the search values are enclosed in single quotes.

The RSRead Routine

Now you get one of the important routines! The RSRead routine takes values from the current record of the dataset and loads them into controls on the form. This is done by checking all the controls on the form for a nonblank Tag property. If a control has a value in the Tag property, the routine assumes that the value is a column name for the dataset. The value in this column is then copied from the dataset into the form control. Add this new routine (shown in Listing 10.12) to your library. Note that this routine is built as a Private Function. You do not want external programs to be able to invoke this function directly.

Listing 10.12. Coding the RSRead function.

This routine and the next routine (RSWrite) are the heart of the record-handling functions. When you understand how these routines work, you can build your own customized routines for handling dataset read and write operations.

The RSWrite Routine

The routine in Listing 10.13 performs the opposite function of RSRead (see Listing 10.12). Again, it's a simple loop through all the controls on the form. If a control is bound to a data column, the value in the control is copied to the dataset column for storage.

---

NOTE: Before you can write to a dataset, you need to invoke the Edit or AddNew methods. After the write operation, you must invoke the Update method to save the changes. You handle these operations in the button set routines later in today's lesson.

---

Listing 10.13. Coding the RSWrite function.

An added feature in this routine deserves mention. Because Visual Basic does not allow you to write to COUNTER data type fields, this routine checks the Attributes property of each bound column before attempting an update. If the field is a COUNTER data type, the routine does not attempt to write data to the column. Note again that the RSWrite routine is built as a Private Function. This function can be executed only by other methods within your OLE Server class.

The RSEnable Routine

To simplify the management of data entry routines, your form allows users to update form controls only after they select the Edit or Add buttons on a form. The RSEnable routine gives you an easy way to turn on or off the Enabled property of all the bound controls on your form. You call this routine often from your button set routines. Add Listing 10.14 to the library.

Listing 10.14. Coding the RSEnable function.

Notice that the RSEnable routine checks the Focus property to see which input field should get the initial focus on the form.

The RSDelete Routine

The RSDelete routine performs a delete operation on the selected data record. But before committing the deed, the user is given a chance to reverse the process. Add Listing 10.15 to the library.

Listing 10.15. Coding the RSDelete function.

Other Record Routines

You need three more routines to complete the record-handling portion of the library. RSClose handles the final closing of the record-handling routines; RSBack and RSNext provide a safe way to process Visual Basic MovePrevious and MoveNext operations without encountering end-of-file errors from Visual Basic. Add these three routines, which are provided in Listing 10.16, to the library.

Listing 10.16. Coding the RSClose, RSBack, and RSNext routines.

You have just completed the record-handling portion of the library. There are only three routines left to build. These three routines provide the button set that users see when they perform data entry operations on your form.

Creating Your Own Button Bar Routines

The next three routines handle all the operations needed to add a complete set of command buttons to your data entry form. You can use this set for any data entry form that provides the basic add, edit, delete, find, and browse operations needed for most data entry routines.

Warning: To make these routines work with your programs, you must add a single command button to your form called cmdBtn. Its Index property must be set to 0 to indicate that it is part of a control array. The details of constructing a working form are covered in the "Creating a Data Entry Form with the Library Routines" section of this lesson.

The BBInit Routine

The BBInit routine builds the details of the command button array and places that array on your data entry form. You must first place a single command button on the target form with its Name property set to cmdBtn and its Index property set to 0. This routine creates seven more command buttons, sets their captions and sizes, and places the button set on the top, bottom, left, or right side of the form. You control this feature by setting the BtnBarAlign property you defined earlier. Add this routine (in Listing 10.17) to the OLE Server library module that contains the record-handling routines.

Listing 10.17. Coding the BBInit routine.

Listing 10.17 uses the data form's dimensions to calculate the location and size of the command buttons in the button set. You create a working example of this form in the section "Creating a Data Entry Form with the Library Routines."

The BBEnable Routine

The BBEnable routine is a short routine that allows you to toggle the Enabled property of the command buttons in the button set. This routine is used to turn on or off selected buttons during edit or add operations. Add the routine in Listing 10.18 to the library.

Listing 10.18. Coding the BBEnable routine.

The routine works by accepting a series of eight 1s and 0s. Each position in the eight-byte string represents one of the button bar buttons. If the value is set to 1, the button is enabled. If the value is set to 0, the button is disabled.

The BBProcess Routine

The BBProcess routine handles all the button actions initiated by the user and makes many calls to the other routines in the library. This routine is the high-level method of the class module; it is also the most involved routine in this library. It might look intimidating at first glance. But, after you inspect the first several lines, you see a pattern developing. More than half of the routine is devoted to handling the browse buttons (First, Back, Next, and Last). The rest is used to handle the add, edit, find, and delete operations. Enter Listing 10.19 into the library.

Listing 10.19. Coding the BBProcess routine.

---

NOTE: The routine in Listing 10.19 is the last library function you'll be adding. Be sure to save the updated library file to disk before exiting Visual Basic.

---

Several aspects of Listing 10.19 need review. First, because you are using a command button array, all operations are dependent on which button was pushed. The outer Select Case structure handles the action. The comment lines show what each button is labeled. However, the captions (and functions) of the first two buttons (Add and Edit) can change during the course of the data entry process. Therefore, these two options have an additional Select Case to check the caption status of the selected button.

There are a great number of If..End If blocks in the code. These blocks are present because you are constantly checking the results of previous actions. They clutter up the code a bit, but they provide solid error-checking capability and program flow control.

Each main section of the outer Select Case performs all the operations needed to complete a user action. For example, the very first set of operations in the routine is the completion of the save operation for an Add command. If you ignore the constant checks of the nResult variable, you see that the essence of this section of the code is as follows:

• Write the record to the dataset (RSWrite)
• Commit the changes (rs.Update)
• Initialize the form controls (RSInit)
• Read the current record into the form (RSRead)
• Disable data entry in the fields (RSEnable False)
• Reset the command button labels and enable all the buttons

The save operation is the most complicated process. The locate, delete, and browse operations are much easier to accomplish and require less coding. The key to remember here is that you are providing all the user-level processes of the data control in this set of Visual Basic code. Although it seems to be a large code piece, you can use it in all your Visual Basic projects once you have it on file.

Compile the OLE Server library so you can use it later. Select File | Make prjRecObject.DLL from the main Visual Basic menu and compile the DLL. In future projects, all you need to do is add a reference to this new object, and you'll be ready to create complete data entry forms with very little coding.

Creating a Data Entry Form with the Library Routines

Now that you have a solid library set for creating data entry forms, you can build a new form for the CompanyMaster project. To do this, you add a new form to the CompanyMaster project. This form is a simple validation list that you can use to validate input for other portions of the project.

If you haven't done it yet, start Visual Basic and load the MASTER.VBP project. This project is a copy of the project you built last week. The first thing you must do is add a reference to the prjRecObject.DLL in the CompanyMaster project. Select Project | References from the main menu and then locate and select prjRecObject.DLL (see Figure 10.4).

Figure 10.4. Locating and selecting the prjRecObject OLE Server DLL.

---

NOTE: The CD that ships with the guide has a completed version of the library. The CompanyMaster that ships with the CD may also have a reference to the old prjRecObject.DLL instead of a pointer reference to your newer version. If you receive errors loading the CompanyMaster project, ignore them and load the new prjRecObject.DLL as planned.

---

Modifying the Master Form

Before you add the new form, you need to add a short menu to the CompanyMaster main form. You use this menu to call the new form. Open the frmMaster form and add the menu items listed in Table 10.2. You can also refer to Figure 10.5 as a guide for building the menu.

Figure 10.5. Adding items to the menu.


Table 10.2. Menu items for the frmMaster form.

After building the menu, enter the following code for the Exit menu item:

Private Sub mnuFileExit_Click()
    cmdExit_Click   ` do the exit!
End Sub

This code calls the existing routine that handles the program exit.

Now you need to add the line of code that calls the new form you are going to create. Enter the following code for the State/Prov menu item:

Private Sub mnuListStProv_Click()
    frmStProv.Show 1
End Sub

This code calls the new form and forces it to display as a modal form. Because it is modal, users cannot change the focus within their project until they safely exit this form.

Building the State/Province List Form

Now that the housekeeping is done, you can build the new form. Use Table 10.3 and Figure 10.6 as guides as you lay out the new validation form.

Table 10.3. Controls for the State/Province list form.

Figure 10.6. Laying out the State/Province form.

Next, add the code fragments that make this data entry form work. You only have a few items to add because you're using the prjRecObject library you built earlier in this lesson. Add Listing 10.20 to the declaration section of the form.

Listing 10.20. Coding the form-level variables.

Option Explicit
`
Dim objRec As Object
Dim lngResult As Long 

Create a Sub procedure to handle opening the database and creating the Recordset. Add the new routine in Listing 10.21 to the form.

Listing 10.21. Coding the StartProc routine.

Listing 10.21 initializes the top-level record object and then sets several properties of the new object before executing the RSOpen and RSEnable methods. Then the routine goes on to initialize and enable the button bar for the form.

Next, you need to add code to the Form_Load event that starts this whole process. Enter the code in Listing 10.22 in the Form_Load event window of the form.

Listing 10.22. Coding the Form_Load routine.

Private Sub Form_Load()
    `
    ` set field tags for data binding
    Text1.Tag = "StateProv"
    Text2.Tag = "Description"
    `
    ` call routine to start recObject library
    StartProc
    `
End Sub 

In Listing 10.22, you set the Tag properties of the two textboxes that are used for data entry, and then you call StartProc to start up the local copy of recObject.

Now you need to add the routine that makes the buttons call all of the library routines. Add the following code to the cmdBtn_Click event of the form:

Private Sub cmdBtn_Click(Index As Integer)
    `
    ` handle all button selections
    `
    objRec.BBProcess Me, Index, "StateProv"
    `
End Sub

This code is called every time you click any of the eight buttons on the data entry form. The BBProcess routine determines which button was pressed and performs the appropriate actions. Note that you are sending the BBProcess method three parameters: the data entry form, the index value that tells you which button was pressed, and the Search field to use if the user has pressed the Find button.

You need to add a few more lines of code to this form before you are done. First, add code that enables the buttons to automatically resize each time the form is resized. Add the following code to the Form_Resize event:

Private Sub Form_Resize()
    `
    objRec.BBInit Me
    `
End Sub

Finally, add the following code to the Form_Unload event to ensure a safe close of the database when the program ends:

Private Sub Form_Unload(Cancel As Integer)
    `
    objRec.RSClose
    `
End Sub

Save the new form as FRMSTPROV.FRM, and run the project. When the main form comes up, select Lists | StateProv from the menu to start the new form. Your form should look like the one shown in Figure 10.7.

Notice that the button set appears on the bottom of the form. This placement was handled automatically by the library routines. Resize the form to see how the button bar automatically adjusts to the new form shape. Finally, click the Add button to add a new record to the State/Province table. You see the input controls become enabled and most of the button bar becomes disabled (see Figure 10.8).

Figure 10.7. Running the new State/Province Validation form.

Figure 10.8. Adding a new record to the State/Province table.


You can enter values in both fields and then click the Save button or the Cancel button to undo the add operation. Click Cancel for now. Test out the form by clicking the Browse and Find buttons. Add a record, edit it, and then delete it. You now have a fully functional data entry form, and you added less than 30 lines of Visual Basic code to the master form!

Summary

Today you learned how to write data entry forms using Visual Basic code. These topics were covered: record search routines, the creation of a procedure library to handle all data entry processes, and the creation of a working data entry form for the CompanyMaster project.

You learned how to perform single-record searches using the three search methods:

• The Move methods for browsing the dataset
• The Seek method for indexed table objects
• The Find methods for non-table objects (Dynasets and Snapshots)

You created an OLE Server library to handle adding, editing, deleting, reading, writing, and locating records in datasets. These routines were written as a generic DLL that you can insert into all Visual Basic programs you write in the future.

You used the new library to add a new form to the CompanyMaster database project. This new form reads a dataset and enables the user to update and browse the table. This new data entry form was built using less than 30 lines of Visual Basic code.

Quiz

To review the material you learned in this chapter, respond to the following questions and check your answers against the ones provided in Appendix C.

1. What are the advantages and disadvantages of using the data control rather than code to manage Visual Basic database applications?

2. What is the main advantage of using code to produce data entry forms?

3. Which approach to searching for a data record--the Move, Find, or Seek method--most resembles the SQL WHERE clause?

4. On what kind of Recordsets can the Seek method be used to search for records?

5. What are the four Move methods that you can apply to the Recordset object?

6. Which of the Find methods starts its search from the beginning of the Recordset? Which of the Find methods starts its search from the end of the Recordset?

7. Which item do you use to remember a specific location in a dataset?

8. What is the fastest search method to locate a record in a dataset?

9. How do you create a control array in Visual Basic?

10. What method(s) do you need to invoke prior to using the Update method to write to a dataset?

Exercise

Assume that you complete the CompanyMaster application and add the State/Province form as discussed in this lesson. After distributing this application to your users, you quickly discover that they are having trouble obtaining zip codes for the companies they enter. You decide to help them by adding a form to this application that lists zip codes and their city equivalents.

Use code to modify the CompanyMaster application so that users can select an item from the List menu (call this item ZipCity) that displays zip codes (field name of Zip) and city (field name of City). Use Visdata to add a data table (ZipCity) to MASTER.MDB.