|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 10
  - 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.

Control | Property | Setting
VB.Form | Name | frmMove
| Caption | "MS Jet Move Methods"
| ClientHeight | 1470
| ClientLeft | 60
| ClientTop | 345
| ClientWidth | 5400
VB.CommandButton | Name | cmdMoveLast
| Caption | "&Last"
| Height | 300
| Left | 4080
| Top | 1080
| Width | 1200
VB.CommandButton | Name | cmdMovePrevious
| Caption | "&Previous"
| Height | 300
| Left | 2760
| Top | 1080
| Width | 1200
VB.CommandButton | Name | cmdMoveNext
| Caption | "&Next"
| Height | 300
| Left | 1440
| Top | 1080
| Width | 1200
VB.CommandButton | Name | cmdMoveFirst
| Caption | "&First"
| Height | 300
| Left | 120
| Top | 1080
| Width | 1200
VB.Label | Name | Label2
| BorderStyle | 1 `Fixed Single
| Height | 315
| Left | 120
| Top | 600
| Width | 2535
VB.Label | Name | Label1
| BorderStyle | 1 `Fixed Single
| Height | 315
| Left | 120
| Top | 180
| Width | 1575

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.

    Private Sub Form_Load()
        `
        ` open db and rs objects
        `
        strDBName = App.Path & "\..\..\data\books5.mdb"
        strRSName = "Authors"
        `
        Set ws = DBEngine.CreateWorkspace("dbTemp", "admin", "")
        Set db = ws.OpenDatabase(strDBName)
        Set rs = db.OpenRecordset(strRSName, dbOpenTable)
        `
    End Sub

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.

    Private Sub cmdSeek_Click()
        `
        ` use the seek method to locate a record
        `
        Dim strSeek As String
        `
        strSeek = InputBox("Enter an Author ID Seek Value:", "Table Seek", "10")
        strSeek = Trim(strSeek)
        `
        If strSeek <> "" Then
            rs.Seek "=", strSeek
            If rs.NoMatch = True Then
                MsgBox "Unable to locate [" & strSeek & "]", vbExclamation, "Table Seek Failed"
            Else
                ReadRow
                MsgBox "Found [" & strSeek & "]", vbInformation, "Table Seek Succeeded"
            End If
        End If
        `
    End Sub

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.

    Private Sub cmdFind_Click()
        `
        ` use the find method for non-table searches
        `
        Dim strFind As String
        `
        strFind = InputBox("Enter an Author ID to Find:", "Non-table Find", "13")
        strFind = Trim(strFind)
        `
        If strFind <> "" Then
            strFind = "AUID=" & strFind
            rs.FindFirst strFind
            `
            If rs.NoMatch = True Then
                MsgBox "Unable to locate [" & strFind & "]", vbExclamation, "Non-ÂTable Find Failed"
            Else
                ReadRow
                MsgBox "Found [" & strFind & "]", vbInformation, "Non-table Find ÂSucceeded"
            End If
        End If
        `
    End Sub

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 book.

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.

    Option Explicit
    `
    ` local enumerations

    ` recordset types
    Enum rsType
        rsTableType = dbOpenTable
        rsSnapShotType = dbOpenSnapshot
        rsDynasetType = dbOpenDynaset
    End Enum
    `
    ` button alignments
    Enum bbAlign
        bbTop = 0
        bbBottom = 1
        bbLeft = 2
        bbRight = 3
    End Enum

    `
    ` private property storage
    Private strWSName As String ` local workspace name
    Private strDBName As String ` local database name
    Private strRSName As String ` local recordset name/SQL
    Private strIndex As String ` local index name
    Private blnIndex As Boolean ` use index flag
    Private intBBAlign As Integer ` button aligment
    Private strFocus As String ` field to get first focus
    `
    Private ws As workspace
    Private db As Database
    Private rs As Recordset
    Private intRSType As rsType

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.

    Public Property Get DBName() As Variant
        DBName = strDBName
    End Property

    Public Property Let DBName(ByVal vNewValue As Variant)
        strDBName = vNewValue
    End Property

    Public Property Get RSName() As Variant
        RSName = strRSName
    End Property

    Public Property Let RSName(ByVal vNewValue As Variant)
        strRSName = vNewValue
    End Property

    Public Property Get dbObject() As Variant
        dbObject = db
    End Property

    Public Property Let dbObject(ByVal vNewValue As Variant)
        ` na
    End Property

    Public Property Get wsObject() As Variant
        wsObject = ws
    End Property

    Public Property Let wsObject(ByVal vNewValue As Variant)
        ` na
    End Property

    Public Property Get rsObject() As Variant
        rsObject = rs
    End Property

    Public Property Let rsObject(ByVal vNewValue As Variant)
        ` na
    End Property

    Public Property Get WSName() As Variant
        WSName = strWSName
    End Property

    Public Property Let WSName(ByVal vNewValue As Variant)
        strWSName = vNewValue
    End Property

    Public Property Get rsType() As rsType
        rsType = intRSType
    End Property

    Public Property Let rsType(ByVal vNewValue As rsType)
        intRSType = vNewValue
    End Property

    Public Property Get Index() As Variant
        Index = strIndex
    End Property

    Public Property Let Index(ByVal vNewValue As Variant)
        strIndex = vNewValue
    End Property

    Public Property Get IndexFlag() As Boolean
        IndexFlag = blnIndex
    End Property

    Public Property Let IndexFlag(ByVal vNewValue As Boolean)
        blnIndex = vNewValue
    End Property

    Public Property Get BtnBarAlign() As bbAlign
        BtnBarAlign = intBBAlign
    End Property

    Public Property Let BtnBarAlign(ByVal vNewValue As bbAlign)
        intBBAlign = vNewValue
    End Property

    Public Property Get RSFocus() As Variant
        RSFocus = strFocus
    End Property

    Public Property Let RSFocus(ByVal vNewValue As Variant)
        strFocus = vNewValue
    End Property

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.

    Private Sub Class_Initialize()
        `
        ` set inital values
        `
        intRSType = rsDynasetType
        strWSName = "wsTemp"
        strDBName = ""
        strRSName = ""
        `
        intBBAlign = bbTop
        `
    End Sub

    Private Sub Class_Terminate()
        `
        ` close out class
        `
        On Error Resume Next
        `
        rs.Close
        db.Close
        ws.Close
        Set rs = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
    End Sub

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.

    Public Function RSOpen(frmTemp As Object)
        `
        ` create ws, db, and rs objects
        `
        On Error GoTo LocalErr
        `
        Dim lngResult As Long
        `
        Set ws = dbengine.createworkspace(WSName, "admin", "")
        Set db = ws.OpenDatabase(strDBName)
        Set rs = db.OpenRecordset(strRSName, intRSType)
        `
        lngResult = RSInit(frmTemp)
        If lngResult = 0 Then
            lngResult = RSRead(frmTemp)
        End If
        `
        RSOpen = lngResult
        Exit Function
        `
    LocalErr:
        RSOpen = Err.Number
        `
    End 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 `Function`s instead of `Sub`s. 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.

    Public Function RSInit(frmTemp As Object)
        `
        ` clear all input controls on the form
        `
        On Error GoTo LocalErr
        `
        Dim ctlTemp As Control
        Dim strTag As String
        `
        For Each ctlTemp In frmTemp.Controls
            strTag = UCase(Trim(ctlTemp.Tag))
            If strTag <> "" Then
                ctlTemp = ""
            End If
        Next
        `
        RSInit = 0
        Exit Function
        `
    LocalErr:
        RSInit = Err.Number
        `
    End 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.

    Public Function RSLocate(FieldName As String)
        `
        ` search the designated field
        `
        On Error GoTo LocalErr
        `
        Dim strSearch As String
        `
        If blnIndex = True Then
            rs.Index = strIndex
        End If
        `
        strSearch = InputBox("Enter Search Value:", "Searching " & FieldName)
        strSearch = Trim(strSearch)
        `
        If strSearch = "" Then
            RSLocate = False
            Exit Function
        End If
        `
        If rs.Fields(FieldName).Type = dbText Then
            strSearch = "`" & strSearch & "`"
        End If
        `
        If blnIndex = True Then
            rs.Seek "=", strSearch
        Else
            rs.FindFirst FieldName & "=" & strSearch
        End If
        `
        If rs.NoMatch = True Then
            RSLocate = False
        Else
            RSLocate = True
        End If
        `
        Exit Function
        `
    LocalErr:
        RSLocate = Err.Number
        `
    End Function

`
` 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.

    Private Function RSRead(frmTemp As Object)
        `
        ` move data from recordset to form
        `
        On Error GoTo LocalErr
        `
        Dim ctlTemp As Control
        Dim strTag As String
        Dim strFldName As String
        `
        For Each ctlTemp In frmTemp.Controls
            strTag = UCase(Trim(ctlTemp.Tag))
            If strTag <> "" Then
                If IsNull(rs.Fields(strTag)) = False Then
                    ctlTemp = rs.Fields(strTag)
                End If
            End If
        Next
        `
        RSRead = 0
        Exit Function
        `
    LocalErr:
        RSRead = Err.Number
        `
    End 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.

    Private Function RSRead(frmTemp As Object)
        `
        ` move data from recordset to form
        `
        On Error GoTo LocalErr
        `
        Dim ctlTemp As Control
        Dim strTag As String
        Dim strFldName As String
        `
        For Each ctlTemp In frmTemp.Controls
            strTag = UCase(Trim(ctlTemp.Tag))
            If strTag <> "" Then
                If IsNull(rs.Fields(strTag)) = False Then
                    ctlTemp = rs.Fields(strTag)
                End If
            End If
        Next
        `
        RSRead = 0
        Exit Function
        `
    LocalErr:
        RSRead = Err.Number
        `
    End Function

    Private Function RSWrite(frmTemp As Object)
        `
        ` move values in controls to data set
        `
        On Error GoTo LocalErr
        `
        Dim ctlTemp As Control
        Dim strTag As String
        Dim lngAttrib As Long
        `
        For Each ctlTemp In frmTemp.Controls
            strTag = UCase(Trim(ctlTemp.Tag))
            If strTag <> "" Then
                lngAttrib = rs.Fields(strTag).Attributes
                If (lngAttrib And dbAutoIncrField) = 0 Then
                    If rs.Fields(strTag).DataUpdatable = True Then
                        rs.Fields(strTag) = ctlTemp
                    End If
                End If
            End If
        Next
        `
        RSWrite = 0
        Exit Function
        `
    End 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.

    Public Function RSEnable(frmTemp As Object, Toggle As Boolean)
        `
        ` toggle the controls on/off
        `
        Dim ctlTemp As Control
        Dim strTag As String
        `
        For Each ctlTemp In frmTemp.Controls
            strTag = UCase(Trim(ctlTemp.Tag))
            If strTag <> "" Then
                ctlTemp.Enabled = Toggle
            End If
            If UCase(Trim(ctlTemp.Tag)) = UCase(Trim(strFocus)) Then
                If Toggle = True Then
                    ctlTemp.SetFocus
                End If
            End If
        Next
        `
        RSEnable = 0
        Exit Function
        `
    LocalErr:
        RSEnable = Err.Number
        `
    End 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.

    Private Function RSDelete()
        `
        ` delete current record
        `
        Dim lngResult As Long
        `
        lngResult = MsgBox("Delete current record?", vbYesNo + vbQuestion, rs.Name)
        If lngResult = vbYes Then
            rs.Delete
        End If
        `
        RSDelete = 0
        Exit Function
        `
    LocalErr:
        RSDelete = Err.Number
        `
    End 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.

    Public Sub RSClose()
        `
        ` close down object
        `
        Class_Terminate
        `
    End Sub

    Private Function RSBack()
        `
        ` move back one record
        `
        If rs.BOF = True Then
            rs.MoveFirst
        Else
            rs.MovePrevious
            If rs.BOF Then
                rs.MoveFirst
            End If
        End If
        `
        RSBack = 0
        Exit Function
        `
    LocalErr:
        RSBack = Err.Number
        `
    End Function

    Private Function RSNext()
        `
        ` move to next record
        `
        If rs.EOF = True Then
            rs.MoveLast
        Else
            rs.MoveNext
            If rs.EOF Then
                rs.MoveLast
            End If
        End If
        `
        RSNext = 0
        Exit Function
        `
    LocalErr:
        RSNext = Err.Number
        `
    End Function

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.

    Public Function BBInit(frmTemp As Object)
        `
        ` initialize a button bar on the form
        `
        Dim intBtnWidth As Integer
        Dim intBtnTop As Integer
        Dim intBtnleft As Integer
        Dim intBtnHeight As Integer
        Dim intLoop As Integer
        Dim varCap As Variant
        `
        varCap = Array("&Add", "&Edit", "&Del", "&Find", "&Top", "&Next",  Â"&Back", "&Last")
        `
        ` compute btn locations
        intBtnWidth = 660
        intBtnHeight = 300
        `
        Select Case intBBAlign
            Case bbTop
                intBtnTop = 60
                intBtnWidth = (frmTemp.ScaleWidth - 60) / 8
                If intBtnWidth < 660 Then intBtnWidth = 660
                intBtnHeight = 300
            Case bbBottom
                intBtnTop = frmTemp.ScaleHeight - 360
                intBtnWidth = (frmTemp.ScaleWidth - 60) / 8
                If intBtnWidth < 660 Then intBtnWidth = 660
                intBtnHeight = 300
            Case bbLeft
                intBtnWidth = 660
                intBtnleft = 60
                intBtnHeight = (frmTemp.ScaleHeight - 60) / 8
                If intBtnHeight < 300 Then intBtnHeight = 300
            Case bbRight
                intBtnWidth = 660
                intBtnleft = frmTemp.ScaleWidth - 720
                intBtnHeight = (frmTemp.ScaleHeight - 60) / 8
                If intBtnHeight < 300 Then intBtnHeight = 300
        End Select
        `
        ` now place buttons on the form
        For intLoop = 0 To 7
            If intBBAlign = bbTop Or intBBAlign = bbBottom Then
                intBtnleft = intLoop * intBtnWidth
            Else
                intBtnTop = (intLoop * intBtnHeight) + 60
            End If
            `
            On Error Resume Next
            With frmTemp
                If intLoop <> 0 Then
                    Load .cmdbtn(intLoop)
                End If
                .cmdbtn(intLoop).Width = intBtnWidth
                .cmdbtn(intLoop).Left = intBtnleft
                .cmdbtn(intLoop).Top = intBtnTop
                .cmdbtn(intLoop).Height = intBtnHeight
                .cmdbtn(intLoop).Caption = varCap(intLoop)
                .cmdbtn(intLoop).Visible = True
            End With
        Next
        `
        BBInit = 0
        Exit Function
        `
    LocalErr:
        BBInit = Err.Number
        `
    End Function

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.

    Public Function BBEnable(frmTemp As Object, strList As String)
        `
        ` enable buttons
        `
        On Error GoTo LocalErr
        `
        Dim intLoop As Integer
        `
        strList = Trim(strList)
        `
        For intLoop = 1 To Len(strList)
            If Mid(strList, intLoop, 1) = "1" Then
                frmTemp.cmdbtn(intLoop - 1).Enabled = True
            Else
                frmTemp.cmdbtn(intLoop - 1).Enabled = False
            End If
        Next
        `
        BBEnable = 0
        Exit Function
        `
    LocalErr:
        BBEnable = Err.Number
        `
    End Function

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.

    Public Function BBProcess(frmTemp As Object, intBtn As Integer, strSearch As ÂString)
        `
        ` handle all button clicks
        `
        On Error GoTo LocalErr

        Dim lngResult As Long
        `
        Select Case intBtn
            Case 0 ` add/save/cancel
                Select Case frmTemp.cmdbtn(intBtn).Caption
                    Case "&Save" ` save new
                        lngResult = RSWrite(frmTemp)
                        If lngResult = 0 Then
                            rs.Update
                        End If
                        If lngResult = 0 Then
                            lngResult = RSInit(frmTemp)
                        End If
                        If lngResult = 0 Then
                            lngResult = RSRead(frmTemp)
                        End If
                        If lngResult = 0 Then
                            lngResult = RSEnable(frmTemp, False)
                        End If
                        If lngResult = 0 Then
                            frmTemp.cmdbtn(0).Caption = "&Add"
                            frmTemp.cmdbtn(1).Caption = "&Edit"
                        End If
                    Case "&Add" ` add new
                        rs.AddNew
                        lngResult = RSInit(frmTemp)
                        If lngResult = 0 Then
                            lngResult = RSEnable(frmTemp, True)
                        End If
                        If lngResult = 0 Then
                            frmTemp.cmdbtn(0).Caption = "&Save"
                            frmTemp.cmdbtn(1).Caption = "&Cancel"
                            BBEnable frmTemp, "11000000"
                        End If
                    Case "&Cancel" ` cancel edit
                        rs.CancelUpdate
                        frmTemp.cmdbtn(0).Caption = "&Add"
                        frmTemp.cmdbtn(1).Caption = "&Edit"
                        BBEnable frmTemp, "11111111"
                        `
                        lngResult = RSInit(frmTemp)
                        If lngResult = 0 Then
                            lngResult = RSRead(frmTemp)
                        End If
                        If lngResult = 0 Then
                            lngResult = RSEnable(frmTemp, False)
                        End If
                End Select
                Case 1 ` edit/save/cancel
                    Select Case frmTemp.cmdbtn(1).Caption
                        Case "&Save" ` save edit
                            rs.Edit
                            lngResult = RSWrite(frmTemp)
                            If lngResult = 0 Then
                                rs.Update
                            End If
                            If lngResult = 0 Then
                                lngResult = RSEnable(frmTemp, False)
                            End If
                            If lngResult = 0 Then
                                frmTemp.cmdbtn(0).Caption = "&Add"
                                frmTemp.cmdbtn(1).Caption = "&Edit"
                                BBEnable frmTemp, "11111111"
                            End If
                        Case "&Edit" ` edit existing
                            lngResult = RSEnable(frmTemp, True)
                            If lngResult = 0 Then
                                frmTemp.cmdbtn(0).Caption = "&Cancel"
                                frmTemp.cmdbtn(1).Caption = "&Save"
                                BBEnable frmTemp, "11000000"
                            End If
                        Case "&Cancel" ` cancel new
                            rs.CancelUpdate
                            frmTemp.cmdbtn(0).Caption = "&Add"
                            frmTemp.cmdbtn(1).Caption = "&Edit"
                            BBEnable frmTemp, "11111111"
                            `
                            lngResult = RSInit(frmTemp)
                            If lngResult = 0 Then
                                lngResult = RSRead(frmTemp)
                            End If
                            If lngResult = 0 Then
                                lngResult = RSEnable(frmTemp, False)
                            End If
                    End Select
                    `
                    If lngResult = 0 Then
                        lngResult = RSInit(frmTemp)
                    End If
                    If lngResult = 0 Then
                        lngResult = RSRead(frmTemp)
                    End If
                Case 2 ` delete rec
                    lngResult = RSDelete()
                    If lngResult = 0 Then
                        lngResult = RSEnable(frmTemp, False)
                    End If
                    If lngResult = 0 Then
                        lngResult = RSNext()
                    End If
                    If lngResult = 0 Then
                        lngResult = RSInit(frmTemp)
                    End If
                    If lngResult = 0 Then
                        lngResult = RSRead(frmTemp)
                    End If
                    BBEnable frmTemp, "11111111"
                Case 3 ` find
                    lngResult = RSLocate(strSearch)
                    If lngResult = True Then
                        lngResult = RSInit(frmTemp)
                    End If
                    If lngResult = 0 Then
                        lngResult = RSRead(frmTemp)
                    End If
                    BBEnable frmTemp, "11111111"
                Case 4 ` move to top
                    rs.MoveFirst
                    lngResult = RSInit(frmTemp)
                    If lngResult = 0 Then
                        lngResult = RSRead(frmTemp)
                    End If
                    BBEnable frmTemp, "11111111"
                Case 5 ` move next
                    lngResult = RSNext()
                    If lngResult = 0 Then
                        lngResult = RSInit(frmTemp)
                    End If
                    If lngResult = 0 Then
                        lngResult = RSRead(frmTemp)
                    End If
                    BBEnable frmTemp, "11111111"
                Case 6 ` move previous
                    rs.MovePrevious
                    lngResult = RSBack()
                    If lngResult = 0 Then
                        lngResult = RSInit(frmTemp)
                    End If
                    If lngResult = 0 Then
                        lngResult = RSRead(frmTemp)
                    End If
                    BBEnable frmTemp, "11111111"
                Case 7 ` move last
                    rs.MoveLast
                    lngResult = RSInit(frmTemp)
                    If lngResult = 0 Then
                        lngResult = RSRead(frmTemp)
                    End If
                    BBEnable frmTemp, "11111111"
        End Select
        `
        BBProcess = 0
        Exit Function
        `
    LocalErr:
        BBProcess = Err.Number
        `
    End Function

> 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 book 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.

Caption | Menu
&File | mnuFile
E&xit | mnuFileExit
&Lists | mnuList
&State/Prov | mnuListStProv

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.

Control | Property | Setting
VB.Form | Name | frmStProv
| Caption | "State/Province Validation Table"
| ClientHeight | 2220
| ClientLeft | 60
| ClientTop | 345
| ClientWidth | 5895
| StartUpPosition | 3 `Windows Default
VB.CommandButton | Name | cmdBtn
| Index | 0
VB.TextBox | Name | Text2
| Height | 255
| Left | 1440
| Top | 540
| Width | 2775
VB.TextBox | Name | Text1
| Height | 255
| Left | 1440
| Top | 240
| Width | 915
VB.Label | Name | Label2
| Caption | "Complete Name"
| Height | 300
| Left | 120
| Top | 600
| Width | 1200
VB.Label | Name | Label1
| Caption | "St/Prov Code"
| Height | 300
| Left | 120
| Top | 240
| Width | 1200

######

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.

    Public Sub StartProc()
        `
        ` handle initial startup of form
        `
        Set objRec = New recObject
        `
        objRec.DBName = App.Path & "\..\..\data\master.mdb"
        objRec.RSName = "StateProvList"
        objRec.rsType = rsDynasetType
        objRec.RSFocus = "StateProv"
        `
        objRec.RSOpen Me
        objRec.RSEnable Me, False
        `
        objRec.BtnBarAlign = bbBottom
        objRec.BBInit Me
        objRec.BBEnable Me, "11111111"
        `
    End Sub

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`.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.