Site hosted by Angelfire.com: Build your free website today!

'; zhtm += '

' + pPage + ''; zhtm += ''; window.popUpWin.document.write(zhtm); window.popUpWin.document.close(); // Johnny Jackson 4/28/98 } //--> Visual Basic 6 Database How-To -- Ch 2 -- Accessing a Data-base with Data Access Objects

www.Sir FreeDom.com.Ar

Visual Basic 6 Database How-To

- 2 -
Accessing a Data-base with Data Access Objects

How do I...

The Data control provides a means of quickly developing database applications with little or no code, but it limits your access to the underlying database. The Microsoft Jet database engine exposes another method of working with data-bases: Data Access Objects (DAO). Although using DAO requires more coding than using the Data control, it offers complete programmatic access to every-thing in the database, as well as significantly greater flexibility. This chapter shows you how to use Jet Data Access Objects to perform common database operations.

All the examples in this chapter use Microsoft Access (.MDB) database files. The Jet engine can also access other PC-based databases such as dBASE, FoxPro, Paradox, and Btrieve, as well as Open Database Connectivity (ODBC) data sources. The techniques shown can be used with any database that Visual Basic can access through Jet. Chapter 6, "Connecting to an ODBC Server," and Chapter 7, "SQL Server Databases and Remote Data Objects," show how to access ODBC and SQL Server databases.

2.1 Browse and Update a Recordset with Data Access Objects

Browsing and updating records are two of the most basic database operations. In this How-To, you will use unbound controls and Data Access Objects to browse and update a recordset.

2.2 Validate Data Entered into Data Access Objects

Users make data entry errors, and robust applications anticipate and trap those errors. This How-To shows how to trap and respond to user errors when you're using Data Access Objects.

2.3 Allow Users to Undo Changes They've Made in Data Access Objects

Users expect to be able to undo changes they make while they are working. In this How-To, using Data Access Objects and unbound controls, you will learn how to enable users to undo changes.

2.Add and Delete Records by Using Data Access Objects

Inserting and deleting records are common database activities. In this How-To, you will learn to use Data Access Objects to add and delete records.

2.5 Use Unbound Controls to Update Fields in Data Access Objects

Simple text boxes are not the only user interface tools available when you're using unbound controls. In this How-To, you will build a generic form that can run an ad hoc query, display the results, and enable the user to select a record.

2.6 Find Records by Using Index Values in Data Access Objects

Indexes can substantially speed up access to records. This How-To shows how you can leverage indexes for performance.

2.7 Determine How Many Records Are in a Dynaset- or Snapshot-Type Recordset

If you need to know how many records are in your recordset, this How-To will show you how to get that number.

2.8 Handle Data Access Object Errors

Although Jet is a robust database engine, many things can go wrong when working with a database. This How-To shows you how to handle Data Access Object errors gracefully.

2.9 Access Excel Worksheets by Using Data Access Objects

An interesting capability of the Microsoft Jet engine is used to access Excel worksheets. In this How-To, we view and manipulate an Excel worksheet as if it were an ordinary database file.

2.1 How do I...

Browse and update records by using Data Access Objects?

PROBLEM

Bound recordsets with Data controls are fine for many purposes, but the Data control has significant limitations. I can't use indexes with bound controls, and I can't refer to the Data control's recordset if the Data control's form is not loaded. How can I browse a recordset without using bound controls?

Technique

Visual Basic and the Microsoft Jet database engine provide a rich set of Data Access Objects that give you complete control over your database and provide capabilities beyond what you can accomplish with bound controls.

If you've worked with other databases, and in particular Structured Query Language (SQL), you might be accustomed to dividing the database programming language into Data Definition Language (DDL) and Data Manipulation Language (DML). Although Jet provides programmatic access to both structure and data, DAO makes no clear distinction between the two. Some objects, such as the Recordset, are used strictly for data manipulation, whereas others, such as the TableDef object, act in both data definition and data manipulation roles. Figure 2.1 shows the DAO hierarchy.

Figure 2.1. The Data Access Object hierarchy.

In most cases, you will be using DAO to manage data. There are four general types of data operations:

Retrieve records
Insert new records
Update existing records
Delete records

DAO provides the recordset object for retrieving records and both the recordset object and the Execute method for inserting, updating, and deleting records.

Before you can do anything useful with DAO, you must open a database. In most cases, this is as simple as using the OpenDatabase method of the default workspace. The following code fragment shows some typical code used to open an Access .MDB file and create a recordset:

` Declare database variable
Dim db As Database
Dim rs As Recordset
` Open a database file and assign it to db
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\MyDB.MDB")
` Create a recordset
Set rs = db.OpenRecordset("MyQuery", dbOpenDynaset, , dbOptimistic)

If you are working with a secured database, you will need to take a few extra steps to provide a valid username and password to the database engine so that a secured workspace object can be created. Refer to Chapter 11, "The Windows Registry and State Information," for more information.

Moving Within an Unbound Recordset

When you use the Data control on a bound form, you rarely have to code in order to move operations. The user clicks on the navigational buttons, and the Data control executes a move internally. Only in the case of a delete do you need to code a move operation (see How-To 1.4).

When you use unbound controls, you must refresh the data displayed on the form with your code. The recordset object provides four methods to facilitate this task: MoveFirst, MovePrevious, MoveNext, and MoveLast.

When you use MovePrevious, you should always check to make sure that the movement has not placed the record pointer before the first record in the recordset. Do this by checking the value of the recordset's BOF property. If BOF is True, you're not on a valid record. The usual solution is to use MoveFirst to position the pointer on the first record. Similarly, when you use MoveNext, you should check to make sure that you're not past the last record in the recordset by checking the EOF property. If EOF is True, use MoveLast to move to a valid record. It's also a good idea to ensure that the recordset has at least one record by making sure that the value of the recordset's RecordCount property is greater than zero.

Updating Records

You can update the values in table-type or dynaset-type recordsets. Updating records in a recordset is a four-step procedure:

: 1. Position the record pointer to the record you want to update.
: 2. Copy the record's values to an area of memory known as the copy buffer by invoking the recordset object's Edit method.
: 3. Change the desired fields by assigning values to the Field objects.
: 4. Transfer the contents of the copy buffer to the database by invoking the recordset object's Update method.

Steps

Open and run the project HT201.VBP. The form shown in Figure 2.2 appears. Use the navigation buttons at the bottom of the form to browse through the records in the recordset. You can change the data by typing over the existing values.

: 1. Create a new project called HT201.VBP. Use Form1 to create the objects and properties listed in Table 2.1, and save the form as HT201.FRM.

Table 2.1. Objects and properties for Form1.

OBJECT Property Setting

Form Name Form1

Caption Unbound Browser

CommandButton Name cmdMove

Caption |<

Index 0

CommandButton Name cmdMove

Caption <

Index 1

CommandButton Name cmdMove

Caption >

Index 2

CommandButton Name cmdMove

Caption >|

Index 3

TextBox Name txt

Index 0

TextBox Name txt

Index 1

TextBox Name txt

Index 2

TextBox Name txt

Index 3

Label Name Label1

Caption &Title

Label Name Label2

Caption &Year Published

Label Name Label3

Caption &ISBN

Label Name Label4

Caption &Publisher ID

: 2. Create the menu shown in Table 2.2.

Table 2.2. Menu specifications for Form1.

CAPTION Name Shortcut Key

&File mnuFile

----E&xit mnuFileExit

&Data mnuData

----&Save Record mnuDataSaveRecord Ctrl+S

Figure 2.2. The Unbound Browser form.

: 3. Add READINI.bas to your project. READINI.bas looks for a file named VBDBHT.ini in the Windows directory with the following text:

[Data Files]
BIBLIO=<path to biblio directory>

: The path should point to the Chapter 2 subdirectory of the directory where you installed the files from the CD setup program. The standard module uses the GetPrivateProfileString API function to obtain the path to the sample database.
: You can also use the copy of Biblio.mdb that was installed with Visual Basic, although it is possible that there are differences in the file. If your copy has the same database structure, you can update the .ini file to point to your existing copy and delete the copy in the Chapter 2 subdirectory.
: 4. Add a class module to the project, name it CTitles, and save the file as HT201.CLS.
: 5. Add the following code to the declarations section of CTitles. Several private variables are declared, including database and recordset object variables, a flag to track changes to the record, strings to hold the property values, and public enumerations for record movement and class errors.

Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Note: It's up to the client to save
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Flags
` dirty flag
Private mblnIsDirty As Boolean
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
    FirstRecord = 1
    LastRecord = 2
    NextRecord = 3
    PreviousRecord = 4
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
    ErrInvalidMoveType = vbObjectError + 1000 + 11
    ErrNoRecords = vbObjectError + 1000 + 12
End Enum

ENUMERATIONS

Enumerations are a feature in Visual Basic that enables you to define publicly available constants within a class module. If you did any work with class modules in Visual Basic 4.0 or earlier, you might have been frustrated by the need to provide a standard "helper" module for any class that used public constants. This type of workaround has been replaced with public enumerations. Note, however, that you can assign only long integer values in an enumeration. No means exist for making strings or other types of constants public in classes.

: 6. Add the following code to the Class_Initialize and Class_Terminate event procedures in CTitles. Class_Initialize opens the database and creates a recordset, generating an error if no records exist. Class_Terminate closes the object variables opened by Class_Initialize.

Private Sub Class_Initialize()
    ` open the database and recordset
    Dim strDBName As String
    ` Get the database name and open the database.
    ` BiblioPath is a function in READINI.BAS
    strDBName = BiblioPath()
    Set mdb = DBEngine.Workspaces(0).OpenDatabase(strDBName)
    ` Open the recordset.
    Set mrs = mdb.OpenRecordset( _
        "Titles", dbOpenDynaset, dbSeeChanges, dbOptimistic)
    ` Raise an error if there is no data
    If mrs.BOF Then
        RaiseClassError ErrNoRecords
    End If
    ` fetch the first record to the properties
    GetCurrentRecord
End Sub
Private Sub Class_Terminate()
` cleanup - note that since a Class_Terminate error
` is fatal to the app, this proc simply traps and
` ignores any shutdown errors
` that's not a great solution, but there's not much
` else that can be done at this point
` in a production app, it might be helpful to log
` these errors
    ` close and release the recordset object
    mrs.Close
    Set mrs = Nothing
    ` close and release the database object
    mdb.Close
    Set mdb = Nothing

End Sub

: 7. Add the private RaiseClassError method to CTitles. This is a simple switch that sets the Description and Source properties for errors raised by the class.

Private Sub RaiseClassError(lngErrorNumber As CTitlesError)
` Note: DAO errors are passed out as-is
    Dim strDescription As String
    Dim strSource As String
    ` assign the description for the error
    Select Case lngErrorNumber
        Case ErrInvalidMoveType
            strDescription = "Invalid move operation."
        Case ErrNoRecords
            strDescription = _
                "There are no records in the Titles table."
        Case Else
            ` If this executes, it's a coding error in
            ` the class module, but having the case is
            ` useful for debugging.
            strDescription = _
                "There is no message for this error."
    End Select
    ` build the Source property for the error
    strSource = App.EXEName & ".CTitles"
    ` raise it
    Err.Raise lngErrorNumber, strSource, strDescription
End Sub

: 8. Add the GetCurrentRecord method. This procedure fetches the data from the recordset and writes the values to the private module-level variables used by the property procedures.

Private Sub GetCurrentRecord()
` Get current values from the recordset
    ` a zero length string is appended to
    ` each variable to avoid the Invalid use of Null
    ` error if a field is null
    ` although current rules don't allow nulls, there
    ` may be legacy data that doesn't conform to
    ` existing rules
    mstrISBN = mrs![ISBN] & ""
    mstrTitle = mrs![Title] & ""
    mstrYearPublished = mrs![Year Published] & ""
    mstrPubID = mrs![PubID] & ""
End Sub

: 9. Add the private UpdateRecord method. This procedure writes the module-level variables to the recordset. The error handler in this procedure clears any changes that were made to the field values.

Private Sub UpdateRecord()
` DAO Edit/Update
On Error GoTo ProcError
    ` inform DAO we will edit
    mrs.Edit
    mrs![ISBN] = mstrISBN
    mrs![Title] = mstrTitle
    mrs![Year Published] = mstrYearPublished
    mrs![PubID] = mstrPubID
    ` commit changes
    mrs.Update
    ` clear dirty flag
    mblnIsDirty = False
    Exit Sub
ProcError:
    ` clear the values that were assigned
    ` and cancel the edit method by
    ` executing a moveprevious/movenext
    mrs.MovePrevious
    mrs.MoveNext
    ` raise the error again
    Err.Raise Err.Number, Err.Source, Err.Description, _
        Err.HelpFile, Err.HelpContext
End Sub

: 10. Add the Property Let and Property Get procedures for the Title, YearPublished, ISBN, and PubID properties of the class. The Property Get procedures simply return the values of the module-level variables. The Property Let procedures assign the new values to the module level variables and set the mblnIsDirty flag.

Public Property Get Title() As String
    Title = mstrTitle
End Property
Public Property Let Title(strTitle As String)
    mstrTitle = strTitle
    ` set the dirty flag
    mblnIsDirty = True
End Property
Public Property Get YearPublished() As String
    YearPublished = mstrYearPublished
End Property
Public Property Let YearPublished(strYearPublished As String)
    mstrYearPublished = strYearPublished
    ` set the dirty flag
    mblnIsDirty = True
End Property
Public Property Get ISBN() As String
    ISBN = mstrISBN
End Property
Public Property Let ISBN(strISBN As String)
    mstrISBN = strISBN
    ` set the dirty flag
    mblnIsDirty = True
End Property
Public Property Get PubID() As String
    PubID = mstrPubID
End Property
Public Property Let PubID(strPubID As String)
    mstrPubID = strPubID
    ` set the dirty flag
    mblnIsDirty = True
End Property

: 11. Add the IsDirty Property Get procedure. This property returns the current value of the mblnIsDirty flag. Note that this is a read-only property. There is no corresponding Property Let procedure.

Public Property Get IsDirty() As Boolean
` pass out the dirty flag
    IsDirty = mblnIsDirty
End Property

: 12. Add the Move method. This method moves the current pointer for the recordset based on the lngMoveType parameter. The values for lngMoveType are defined by the CTitlesMove enumeration in the header section. This method is a simple wrapper around the various move methods of the underlying recordset object.

Public Sub Move(lngMoveType As CTitlesMove)
` Move and refresh properties
    Select Case lngMoveType
        Case FirstRecord
            mrs.MoveFirst
        Case LastRecord
            mrs.MoveLast
        Case NextRecord
            mrs.MoveNext
            ` check for EOF
            If mrs.EOF Then
                mrs.MoveLast
            End If
        Case PreviousRecord
            mrs.MovePrevious
            ` check for BOF
            If mrs.BOF Then
                mrs.MoveFirst
            End If
        Case Else
            ` bad parameter, raise an error
            RaiseClassError ErrInvalidMoveType
    End Select
    GetCurrentRecord
End Sub

More on Enumerations
Declaring the lngMoveType parameter as CTitlesMove instead of as a long integer illustrates another benefit of using enumerations. If a variable is declared as the type of a named enumeration, the code editor provides a drop-down list of available constants wherever the variable is used.
: 13. Add the SaveRecord method. This method tests the mblnIsDirty flag and updates the current record if necessary.

Public Sub SaveRecord()
` save current changes
    ` test dirty flag
    If mblnIsDirty Then
        ` update it
        UpdateRecord
    Else
        ` record is already clean
    End If
End Sub

: 14. Add the following code to the declarations section of Form1. A private object variable is created for the CTitles class, as well as constants for the control arrays and a status flag used to prevent the txt_Change events from writing the property values in the class during a refresh of the data.

Option Explicit
` CTitles object
Private mclsTitles As CTitles
` These constants are used for the various control arrays
` command button constants
Const cmdMoveFirst = 0
Const cmdMovePrevious = 1
Const cmdMoveNext = 2
Const cmdMoveLast = 3
` text box index constants
Const txtTitle = 0
Const txtYearPublished = 1
Const txtISBN = 2
Const txtPubID = 3
` refresh flag
Private mblnInRefresh As Boolean

: 15. Add the Form_Load event procedure. Form_Load creates the mclsTitles object and loads the first record. If an error occurs, the error handler displays a message and unloads the form, which terminates the application.

Private Sub Form_Load()
` create the mclsTitles object and display the first record
On Error GoTo ProcError
    Dim strDBName As String
    Screen.MousePointer = vbHourglass
    ` create the CTitles object
    Set mclsTitles = New CTitles
    ` fetch and display the current record
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    ` An error was generated by Visual Basic or CTitles.
    ` Display the error message and terminate gracefully.
    MsgBox Err.Description, vbExclamation
    Unload Me
    Resume ProcExit
End Sub

: 16. Add the Query_Unload event procedure. This saves the current record before unloading the form. If an error occurs, the error handler gives the user the option of continuing (with loss of data) or returning to the form.

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` save the current record
    mclsTitles.SaveRecord
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    ` an error here means the record won't be saved
    ` let the user decide what to do
    Dim strMsg As String
    strMsg = "The following error occurred while _
             "attempting to save:"
strMsg = strMsg & vbCrLf & Err.Description & vbCrLf
    strMsg = strMsg & "If you continue the current operation, " _
    strMsg = strMsg & "changes to your data will be lost."
    strMsg = strMsg & vbCrLf
    strMsg = strMsg & "Do you want to continue anyway?"
    If MsgBox(strMsg, _
        vbQuestion Or vbYesNo Or vbDefaultButton2) = vbNo Then
        Cancel = True
    End If
    Resume ProcExit
End Sub

: 17. Add the cmdMove_Click event procedure. This event saves the current record, requests the record indicated by the Index parameter from the mclsTitles object, and refreshes the form.

Private Sub cmdMove_Click(Index As Integer)
` move to the desired record, saving first
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` save the record
    mclsTitles.SaveRecord
    ` move to the indicated record
    Select Case Index
        Case cmdMoveFirst
            mclsTitles.Move FirstRecord
        Case cmdMoveLast
            mclsTitles.Move LastRecord
        Case cmdMoveNext
            mclsTitles.Move NextRecord
        Case cmdMovePrevious
            mclsTitles.Move PreviousRecord
    End Select
    ` refresh display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: 18. Add the txt_Change event procedure. This event writes the control values to the properties unless the mblnInRefresh flag is set. The flag tells the event procedure that the form is being refreshed and that the property values in mclsTitles do not need to be set.

Private Sub txt_Change(Index As Integer)
` update property values if required
On Error GoTo ProcError
    Dim strValue As String
    Screen.MousePointer = vbHourglass
    ` fetch the value from the control
    strValue = txt(Index).Text
    ` check first to see if we're in a GetData call
    ` assigning the property values while refreshing
    ` will reset the dirty flag again so the data will
    ` never appear to have been saved
    If Not mblnInRefresh Then
        ` update the clsTitles properties
        Select Case Index
            Case txtTitle
                mclsTitles.Title = strValue
            Case txtYearPublished
                mclsTitles.YearPublished = strValue
            Case txtISBN
                mclsTitles.ISBN = strValue
            Case txtPubID
                mclsTitles.PubID = strValue
        End Select
    End If
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: 19. Add the mnuFileExit_Click event. This procedure unloads the form. The rest of the unload logic is contained in the QueryUnload event procedure.

Private Sub mnuFileExit_Click()
    ` shut down
    ` work is handled by the Query_Unload event
    Unload Me
End Sub

: 20. Add the mnuData_Click and mnuDataSaveRecord_Click event pro-cedures. The mnuData_Click event toggles the enabled flag for the mnuDataSaveRecord menu control based on the IsDirty flag of the mclsTitles object. The mnuDataSaveRecord_Click procedure saves
the current record.

Private Sub mnuData_Click()
` set enabled/disabled flags for menu commands
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` save enabled only when dirty
    mnuDataSaveRecord.Enabled = mclsTitles.IsDirty
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub
Private Sub mnuDataSaveRecord_Click()
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` save it
    mclsTitles.SaveRecord
    ` refresh display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: 21. Add the private GetData procedure. GetData is used to refresh the controls based on the property values in mclsTitles and sets the mblnInRefresh flag so that the properties aren't changed again by txt_Change.

Private Sub GetData()
` display the current record
    ` set the mblnInRefresh flag so that the txt_Change event
    ` doesn't write the property values again
    mblnInRefresh = True
    ` assign the values to the controls from the properties
    txt(txtTitle).Text = mclsTitles.Title
    txt(txtYearPublished).Text = mclsTitles.YearPublished
    txt(txtISBN).Text = mclsTitles.ISBN
    txt(txtPubID).Text = mclsTitles.PubID
    ` clear the refresh flag
    mblnInRefresh = False
End Sub

How It Works

When the form loads, it creates the object variable for the CTitles class and displays the first record. The user can navigate among the records by clicking the move buttons, and the logic in the form and the CTitles class saves changes if the record is dirty.

With placement of the data management logic in the class module and the user interface logic in the form, a level of independence between the database and the user interface is created. Several different forms can be created that all use the same class without duplicating any of the data management logic. Additionally, changes made to the underlying database can be incorporated into the class without requiring changes to the forms based on it.

Comments

The beauty of encapsulating all the data access code in a class module isn't fully revealed until you have an application that enables the user to edit the same data using more than one interface. If, for example, you display a summary of detail records in a grid and also provide a regular form for working with the same data, most of the code for managing that data will be shared in the class modules. Each form that presents the data will only need to have code that controls its own interface.

2.2 How do I...

Validate data entered into Data Access Objects?

Problem

I need to verify that data entered is valid before I update the database. How can I do this with Data Access Objects and unbound controls?

Technique

Databases, tables, and fields often have various business rules that apply to the data. You can apply rules by writing code to check the values of the data in unbound controls before you write the changes to the underlying tables.

Class modules are most often used to handle the data-management logic for unbound data--adding a layer of separation between the database schema and the user interface logic. Normally, class modules should not handle any user interaction (unless, of course, the class is specifically designed to encapsulate user interface components), so instead of generating messages, classes raise errors if a validation rule is violated. The error is trapped by the user interface and handled in whatever manner is appropriate.

Steps

Open and run HT202.VBP. Tab to the Year Published text box, delete the year, and attempt to save the record. Because the Year Published is required, an error message is displayed, as shown in Figure 2.3. Experiment with some of the other fields to examine other rules and the messages that are displayed.

Figure 2.3. The Validating Browser form.

: 1. Create a new project called HT202.VBP. This project adds to the project developed for How-To 2.1 by adding validation code to the CTitles class module. The form and the READINI.bas module for this project are unchanged from the previous How-To, with the exception of the form caption, which was changed to Validating Browser. Changes to the class module are shown in the steps that follow.
: 2. Add the following code to the declarations section of the class module. The modified enumeration, shown in bold, defines the errors that can be raised by the class. The rest of the declarations section is unchanged from the previous How-To.

Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Flags
` dirty flag
Private mblnIsDirty As Boolean
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
    FirstRecord = 1
    LastRecord = 2
    NextRecord = 3
    PreviousRecord = 4
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
    ErrMissingTitle = vbObjectError + 1000 + 1
    ErrMissingYear = vbObjectError + 1000 + 2
    ErrMissingISBN = vbObjectError + 1000 + 3
    ErrInvalidYear = vbObjectError + 1000 + 4
    ErrMissingPubID = vbObjectError + 1000 + 5
    ErrNonNumericPubID = vbObjectError + 1000 + 6
    ErrRecordNotFound = vbObjectError + 1000 + 10
    ErrInvalidMoveType = vbObjectError + 1000 + 11
    ErrNoRecords = vbObjectError + 1000 + 12
End Enum

: 3. Modify the RaiseClassError procedure, as shown. This is a simple switch that sets the Description and Source properties for errors raised by the class. Several new cases in the Select Case block are dedicated to assigning descriptions to validation errors.

Private Sub RaiseClassError(lngErrorNumber As CTitlesError)
` Note: DAO errors are passed out as-is
    Dim strDescription As String
    Dim strSource As String
    ` assign the description for the error
    Select Case lngErrorNumber
        Case ErrMissingTitle
            strDescription = "The Title is required."
        Case ErrMissingYear
            strDescription = "The Year Published is required."
        Case ErrMissingISBN
            strDescription = "The ISBN number is required."
        Case ErrInvalidYear
            strDescription = "Not a valid year."
        Case ErrMissingPubID
            strDescription = "The Publisher ID is required."
        Case ErrNonNumericPubID
            strDescription = "The Publisher ID must be numeric."
        Case ErrRecordNotFound
            strDescription = "The record was not found."
        Case ErrInvalidMoveType
            strDescription = "Invalid move operation."
        Case ErrNoRecords
            strDescription = _
                "There are no records in the Titles table."
        Case Else
            ` If this executes, it's a coding error in
            ` the class module, but having the case is
            ` useful for debugging.
            strDescription = "There is no message for this error."
    End Select
    ` build the Source property for the error
    strSource = App.EXEName & ".CTitles"
    ` raise it
    Err.Raise lngErrorNumber, strSource, strDescription
End Sub

: 4. Add the IsValid property. This property takes an optional argument, blnRaiseError, which defaults to False and controls the procedure logic. If the flag is True, an error is generated when a validation rule is violated. Code within the class sets the flag to True so that errors are raised when rules are violated. Forms would normally ignore this optional parameter and simply test for a True or False value in the property.

Public Property Get IsValid _
  (Optional blnRaiseError As Boolean = False) As Boolean
` test the data against our rules
` the optional blnRaiseError flag can be used to have the
` procedure raise an error if a validation rule is
` violated.
    Dim lngError As CTitlesError
    If mstrISBN = "" Then
        lngError = ErrMissingISBN
    ElseIf mstrTitle = "" Then
        lngError = ErrMissingTitle
    ElseIf mstrYearPublished = "" Then
        lngError = ErrMissingYear
    ElseIf Not IsNumeric(mstrYearPublished) Then
        lngError = ErrInvalidYear
    ElseIf mstrPubID = "" Then
        lngError = ErrMissingPubID
    ElseIf Not IsNumeric(mstrPubID) Then
        lngError = ErrNonNumericPubID
    End If
    If lngError <> 0 Then
        If blnRaiseError Then
            RaiseClassError lngError
        Else
            IsValid = False
        End If
    Else
        IsValid = True
    End If
End Property

: 5. Modify the SaveRecord method to call IsValid. This method tests the mblnIsDirty flag and updates the current record if necessary. Before UpdateRecord is called, the procedure calls the IsValid procedure, setting the blnRaiseError parameter to True so that any rule violations are raised as errors in the form. The only difference between this version of SaveRecord and the version shown in How-To 2.1 is the call to IsValid and the relocation of the call to UpdateRecord so that it is called only if IsValid returns True.

Public Sub SaveRecord()
` save current changes
    ` test dirty flag
    If mblnIsDirty Then
        ` validate, raise an error
        ` if rules are violated
        If IsValid(True) Then
            ` update it
            UpdateRecord
        End If
    Else
        ` record is already clean
    End If
End Sub

How It Works

Data validation logic is handled entirely within the CTitles class--no special coding is required in the form beyond the normal error handlers that trap errors and display a simple message about the error. The rules applied for validation can be as simple or as complex as the application requires.

Only minor modifications to the class were required to implement data validation. The error enumeration and the corresponding RaiseClassError procedure were expanded to include validation errors, the IsValid procedure was added to perform the validation tests, and a few lines of code were changed in the SaveRecord procedure.

Comments

Not all database rules require you to write code to perform data validation. The Jet database engine can enforce some--or possibly all--of your rules for using the properties of tables and fields or relational constraints. How-To 4.5 shows you how to use these objects and properties to supplement or replace validation code.

2.3 How do I...

Allow users to undo changes they've made in Data Access Objects?

Problem

Users sometimes make data entry errors while working. How can I allow users to undo changes they've made to data in unbound controls using Data Access Objects?

Technique

A few additional lines of code in the class module handling data management for your database can implement an undo feature. Because data is not updated in bound controls until you explicitly update it with your code, you can restore the original values by reloading them from the underlying recordset. The class module handles restoring the original values from the recordset and assigning them to the property values. The form only needs to read the data from the property procedures and write the property values to the controls.

Steps

Open and run the project HT203.VBP and the form shown in Figure 2.4 appears. Change the data in any control on the form, and use the Undo command on the Edit menu to restore the original value.

Figure 2.4. The Undo Browser form.

: 1. Create a new project called HT203 .VBP. This project is based on the example developed for How-To 2.1 and also includes the READINI.bas module and CTitles.cls module. Menu controls and supporting code have been added to the form developed for How-To 2.1 to support the undo operation, and a public method has been added to the class module. If you have worked through How-To 2.1, you can copy or modify your existing files. If not, refer to the steps in that example to create the foundation for this project.
: 2. Change the caption of Form1 to Undo Browser, and add the menu shown in Table 2.3.

Table 2.3. Menu specifications for the Edit menu.

CAPTION Name Shortcut Key

&Edit mnuEdit

----&Undo mnuEditUndo Ctrl+Z

NOTE By convention, the Edit menu is placed to the immediate right of the File menu.

: 3. Add the mnuEdit_Click and mnuEditUndo_Click events to Form1.
The mnuEdit_Click event checks the current record state and toggles the enabled property of the mnuEditUndo control based on the value of the IsDirty property of mclsTitles. The mnuEditUndo_Click procedure calls the UndoRecord method of the mclsTitles object and refreshes the form. This is the only new code in the form.

Private Sub mnuEdit_Click()
` enable/disable undo command based on current dirty flag
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` toggle based on dirty flag
    mnuEditUndo.Enabled = mclsTitles.IsDirty
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub
Private Sub mnuEditUndo_Click()
` undo changes
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` undo changes
    mclsTitles.UndoRecord
    ` refresh the display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: 4. Add the UndoRecordmethod to the CTitles class. UndoRecord clears the mblnIsDirty flag and restores the original values from the recordset to
the private variables used for the property procedures by calling the GetCurrentRecord procedure.

Public Sub UndoRecord()
` clear changes and refresh properties
    ` clear dirty flag
    ` but do not clear new flag
    mblnIsDirty = False
    ` refresh the current values from the recordset
    GetCurrentRecord
End Sub

How It Works

Each of the property procedures that represent fields in the CTitles class sets the module-level mblnIsDirty flag. This flag is then used to toggle the enabled property of the Undo command on the Edit menu. When the user selects Undo, the form calls the UndoRecord method of the class and refreshes the controls on the form. The UndoRecord method needs only to restore the private module-level variables with the data that is still unchanged in the recordset.

Comments

The standard TextBox control supports a field level undo capability with the built-in context menu. By adding some code, you could also implement a field-level undo command for any field you display, regardless of the type of control that is used.

2.How do I...

Add and delete records by using Data Access Objects?

Problem

Viewing and editing existing records are only half of the jobs my users need to do. How do I add and delete records using unbound controls and Data Access Objects?

Technique

The recordset object provides the AddNew and Delete methods for inserting and deleting records. When you are using unbound controls on a form, adding a record is a five-step process:

: 1. Clear (and if necessary save) the current record so that the user can enter data for the new record.
: 2. Call the AddNew method of the recordset.
: 3. Write the values from the controls to the Fields.
: 4. Call the Update method of the recordset.
: 5. Restore the record pointer to the newly added record.

Deleting a record is a two-step process:

: 1. Call the Delete method of the recordset.
: 2. Move to a valid record and display it.

ADD AND DELETE USER INTERFACES

Microsoft Access and (if properly configured) the Data control enable users to add new records by navigating to the end of the recordset and clicking the Next Record button. Although this two-step procedure might seem obvious to most programmers and database developers, it is not at all obvious to most users. Users will be looking for something clearly labeled as a command that will give them a new record with which to work. Rather than emulate this confusing idiom, this example uses a New Record command on the Data menu.

The same reasoning applies to deleting records. Provide a clearly labeled menu command or button the user can click to perform a delete.
In short, don't aggravate your users by burying common operations with obscure procedures in the interface.

Steps

Open and run the project HT204 .VBP. To add a new record, select the Data | New Record menu command, as shown in Figure 2.5. To delete a record, choose the Delete Record command.

Figure 2.5. The Add and Delete form.

: 1. Create a new project called HT204.VBP. This project is based on the example developed for How-To 2.1. Only the changes to that project are described here. Refer to the steps in the original project for the complete details of creating the form, the CTitles class module, and the READINI.bas module.
: 2. Change the caption of Form1 to Add and Delete, and add the New Record and Delete Record commands (shown in bold) to the Data menu, as shown in Table 2.4.

Table 2.4. Menu specifications for Form1.

CAPTION Name Shortcut Key

&File mnuFile

----E&xit mnuFileExit

&Data mnuData

----&New Record mnuDataNewRecord Ctrl+N

----&Save Record mnuDataSaveRecord Ctrl+S

----&Delete Record mnuDataDeleteRecord Ctrl+X

: 3. Modify the Form_Load event procedure to enable the user to create a new record if the table is empty when the form is initially loaded. Only the code in the error handler has changed.

Private Sub Form_Load()
` create the mclsTitles object and display the first record
On Error GoTo ProcError
    Dim strDBName As String
    Screen.MousePointer = vbHourglass
    ` create the CTitles object
    Set mclsTitles = New CTitles
    ` fetch and display the current record
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    ` An error was generated by Visual Basic or CTitles.
    ` Check for the "No Records" error and if so
    ` just provide a new record.
    Select Case Err.Number
        Case ErrNoRecords
            mclsTitles.NewRecord
            Resume Next
        Case Else
            ` Some other error
            ` Display the error message and terminate gracefully.
            MsgBox Err.Description, vbExclamation
            Unload Me
            Resume ProcExit
    End Select
End Sub

: 4. Add the event procedures for mnuData_Click and its submenu items mnuDataNewRecord_Click, mnuDataSaveRecord_Click, and mnuDataDeleteRecord_Click. The mnuData_Click event toggles the enabled flag for the mnuDataSaveRecord menu control based on the IsDirty flag of the mclsTitles object. The mnuDataNewRecord_Click event calls the NewRecord method of the mclsTitles object. The mnuDataSaveRecord_Click procedure saves the current record by calling the mclsTitles.SaveRecord method. The DeleteRecord method of mclsTitles is called by mnuDataDeleteRecord_Click. A special trap is used in the error handler. If the delete leaves the recordset empty, a new record is created.

Private Sub mnuData_Click()
` set enabled/disabled flags for menu commands
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` save enabled only when dirty
    mnuDataSaveRecord.Enabled = mclsTitles.IsDirty
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: The mnuData_Click event procedure is unchanged from the example in How-To 2.1.

Private Sub mnuDataNewRecord_Click()
` set up a new record
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` save existing first
    mclsTitles.SaveRecord
    ` get a new record
    mclsTitles.NewRecord
    ` refresh display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub
Private Sub mnuDataSaveRecord_Click()
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` save it
    mclsTitles.SaveRecord
    ` refresh display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub
Private Sub mnuDataDeleteRecord_Click()
` delete the current record
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    mclsTitles.DeleteRecord
    ` refresh display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    Select Case Err.Number
        Case ErrNoRecords
            ` last record was deleted
            ` Create a new record
            mclsTitles.NewRecord
            Resume Next
        Case Else
            ` inform
            MsgBox Err.Description, vbExclamation
            Resume ProcExit
    End Select
End Sub

: 5. Revise the declarations section of CTitles to include the new mblnIsNew flag, shown in bold in the following listing. This flag is True if the current data is a new record and False if it is an existing record.

Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Note: It's up to the client to save
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Flags
` dirty flag
Private mblnIsDirty As Boolean
` new record flag
Private mblnIsNew As Boolean
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
    FirstRecord = 1
    LastRecord = 2
    NextRecord = 3
    PreviousRecord = 4
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
    ErrInvalidMoveType = vbObjectError + 1000 + 11
    ErrNoRecords = vbObjectError + 1000 + 12
End Enum

: 6. Create the AddNewRecord method. This sub does the real work of inserting the record into the database. First the AddNew method is called, then the values from the properties are written to the fields, and finally the Update method is called to commit the changes. After a record is inserted using AddNew and Update, the current record is undefined. Setting the recordset's Bookmark property to the special LastModified bookmark restores the current record pointer to the new record.

Private Sub AddNewRecord()
` DAO AddNew/Update
    ` inform DAO we are going to insert
    mrs.AddNew
    ` write the current values
    mrs![ISBN] = mstrISBN
    mrs![Title] = mstrTitle
    mrs![Year Published] = mstrYearPublished
    mrs![PubID] = mstrPubID
    ` update the record
    mrs.Update
    ` return to the new record
    mrs.Bookmark = mrs.LastModified
    ` clear new flag
    mblnIsNew = False
    ` clear dirty flag
    mblnIsDirty = False
End Sub

: 7. Add the IsNew property. This flag indicates whether the current record is a new record (one that has not been added to the recordset) by returning the value of the private mblnIsNew flag.

Public Property Get IsNew() As Boolean
` pass out the new flag
    IsNew = mblnIsNew
End Property

: 8. Add the NewRecord method. This method clears the current values of the private variables used for the field properties and sets the mblnIsNew flag used by the IsNew property.

Public Sub NewRecord()
` clear the current values for an insert
` NOTE: the flags work so that if a new
` record is added but not changed, you
` can move off of it or close with no
` prompt to save
    ` assign zero-length strings to the properties
    mstrISBN = ""
    mstrTitle = ""
    mstrYearPublished = ""
    mstrPubID = ""
    ` set the new flag
    mblnIsNew = True
End Sub

: 9. Add the DeleteRecord method. DeleteRecord calls the Delete method of the recordset object and then uses the MovePrevious method to reset the current record to a valid record pointer. If MovePrevious takes the recordset to BOF (before the first record) a MoveFirst is executed.

Public Sub DeleteRecord()
` DAO delete
    ` delete the record
    mrs.Delete
    ` clear new and dirty flags
    mblnIsDirty = False
    mblnIsNew = False
    ` reposition to a valid record
    mrs.MovePrevious
    ` check for BOF
    If mrs.BOF Then
        ` could be empty, check EOF
        If Not mrs.EOF Then
            mrs.MoveFirst
        Else
            ` empty recordset, raise error
            ` the client must decide how to
            ` handle this situation
            RaiseClassError ErrNoRecords
        End If
    End If
    GetCurrentRecord
End Sub

: 10. Add the SaveRecord method. This method tests the mblnIsDirty flag and updates the current record, if necessary. If the record is new, the data is committed to the table using the AddNewRecord procedure. Existing records are updated using the UpdateRecord procedure.

Public Sub SaveRecord()
` save current changes
    ` test dirty flag
    If mblnIsDirty Then
        ` test new flag
        If mblnIsNew Then
            ` add it
            AddNewRecord
        Else
            ` update it
            UpdateRecord
        End If
    Else
        ` record is already clean
    End If
End Sub

How It Works

The CTitles class handles all the data processing with the database engine and provides a lightweight wrapper around the AddNew, Update, and Delete methods of the Recordset object. The form exposes these features in the user interface by providing menu commands for each data operation and handling save and error trapping logic.

Encapsulating all the recordset management logic in the class module means that all that code can be easily reused in other forms based on the same data.

Comments

A complete application might not necessarily have a one-to-one correspondence between tables and class modules. The classes should reflect the object model for the application and its data, not the database schema itself. The database underlying a complex application might have tables that are not reflected in the object model, such as tables used only for supplying lookup values to lists, or tables that are not directly represented, such as junction tables in many-to-many relationships.

2.5 How do I...

Use unbound controls to update fields in Data Access Objects?

Problem

Using Data Access Objects to build data entry forms with unbound controls works well in most situations, but sometimes the data is difficult for the user to work with. I need to extend the user interface of my application to provide alternative methods of finding and choosing records.

TECHNIQUE

The nature of database applications is that users must often deal with less-than-obvious values, such as foreign keys and coded data. Instead of forcing the user to remember arbitrary key values and data codes, you can alter the user interface to provide lists of values rather than simple text boxes.

NOTE Chapter 4, "Designing and Implementing a Database," discusses foreign keys and table relationships.

Foreign key values can represent a special problem because the lookup tables for the keys are often quite large. Populating a list with all the available values can seriously damage the performance of the application. Additionally, because this is such a common operation, a generic tool for working with this type of data saves significant coding effort.

Using a simple form and a ListView control, you can build a generic tool that can display the results of an ad hoc query and return a key value for the record selected by the user.

Steps

Open and run the project HT205.VBP. Select Data|Find Publisher and enter SAMs. Click OK to display the results. The form shown in Figure 2.6 appears.

Figure 2.6. The Lookup Browser form.

: 1. Create a new project called HT205 .VBP. This example extends the project developed for How-To 2.1 to provide a means for the user to search for an identifier value for a publisher. Refer to the steps in How-To 2.1 for complete details on building the base application. Only the changes to the original project are shown in the steps that follow.
: 2. Change the caption of Form1 to Lookup Browser. Modify the Data menu as shown in Table 2.5, adding the Find Publisher command (shown in bold).

Table 2.5. Menu specifications for Form1.

CAPTION Name Shortcut Key

&File mnuFile

----E&xit mnuFileExit

&Data mnuData

----&Save Record mnuDataSaveRecord Ctrl+S

----&Find Publisher mnuDataFindPublisher Ctrl+F

: 3. Add the following code to Form1 as the mnuDataFindPublisher_Click event. This event uses an input box and the FSearchResults form to build and display the results of an ad hoc query. The user is prompted
to enter all or part of the name of a publisher. The value entered is used to build a SQL statement that is passed to the FSearchResults form. The FSearchResults form handles the balance of the selection process and sets its KeyValue property before it returns control to the mnuDataFindPublisher_Click event.

Private Sub mnuDataFindPublisher_Click()
` Use the FSearchResults form to find a pub id
On Error GoTo ProcError
    Dim strPrompt As String
    Dim strInput As String
    Dim strSQL As String
    Dim fSearch As FSearchResults
    strPrompt = "Enter all or the beginning of _
                "the publisher name:"
    strInput = InputBox$(strPrompt, "Search for Publishers")
    If strInput <> "" Then
        ` search
        strSQL = "SELECT * FROM Publishers " & _
            "WHERE Name LIKE `" & strInput & "*';"
        Set fSearch = New FSearchResults
        ` Note: Search method does not return
        ` until the user dismisses the form
        fSearch.Search "PubID", strSQL, Me
        If Not fSearch.Cancelled Then
            txt(txtPubID).Text = fSearch.KeyValue
        End If
    End If
ProcExit:
    ` release the search form reference
    Set fSearch = Nothing
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit

End Sub

: 4. Create a new form, name it FSearchResults, and save the form. Table 2.6 shows the objects and properties for the form.

Table 2.6. Objects and properties for the FSearchResults form.

OBJECT Property Value

ListView Name lvwResults

View 3 - lvwReport

LabelEdit 1 - lvwManual

CommandButton Name cmd

Index 0

Caption OK

Default True

CommandButton Name cmd

Index 1

Caption Cancel

Cancel True

: 5. Add the following code to the declarations section of FSearchResults. The two constants are used for the index to the CommandButton control array. The mblnCancelled flag is used to indicate that the user chose the Cancel button. The mvntKeyValue holds the value (typically the primary key field, but any field can be specified when the Search method is called) for the key field. The mintItemIdx holds the index into the SubItems collection of the ListView control for the key value.

Option Explicit
` This form will run an ad hoc query and
` display the results in the list view control
` command button array constants
Const cmdOK = 0
Const cmdCancel = 1
` cancel property
Private mblnCancelled As Boolean
` selected key value
Private mvntKeyValue As Variant
` subitem index for key value
Private mintItemIdx As Integer

: 6. Add the cmd_Click event procedure for the CommandButton control array. This event procedure sets the private flag indicating whether the user chose OK or Cancel and hides the form. Because the form is displayed modally, control returns to whatever procedure created the form after this procedure exits.

Private Sub cmd_Click(Index As Integer)
    If Index = cmdOK Then
        mblnCancelled = False
    Else
        mblnCancelled = True
    End If
    Me.Hide
End Sub

: 7. Add the lvwResults_ItemClick event procedure. When Visual Basic fires the ItemClick event for a ListView control, it passes the clicked ListItem object as a parameter. The mintItemIdx variable set by the Search method is used to retrieve the value of the key field from the SubItems collection and assign it to the mvntKeyValue variable, where it can be read by the KeyValue property procedure.

Private Sub lvwResults_ItemClick(ByVal Item As ComctlLib.ListItem)
On Error GoTo ProcError
    mvntKeyValue = Item.SubItems(mintItemIdx)
ProcExit:
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: 8. Add the Cancelled and KeyValue property procedures. Each procedure returns the values assigned to the module-level variables. These properties are checked by the procedure that created the instance of the form to see what button the user chose and the key value of the record selected.

Public Property Get Cancelled()
    Cancelled = mblnCancelled
End Property
Public Property Get KeyValue() As Variant
    KeyValue = mvntKeyValue
End Property

: 9. Add the following code to FSearchResults as the Search method. The method takes three parameters: a key field name used to return a value in the KeyValue property, a SQL statement used to create the list of results, and a parent form reference used by the Show method. The method builds the results list by first creating a recordset object based on the SQL statement provided. If there are records to display, it iterates the Fields collection of the recordset to generate a set of column headers for lvwResults (determining the index of the key field in the process). After the column headers for the list have been created, the method enters a Do loop and iterates the records in the recordset. For each record, the fields are iterated and their values are placed into the appropriate SubItem of the ListView. In addition to the list of fields, the lvwResults ListView control shows the ordinal position in the results for each record.

Public Sub Search( _
    strKeyField As String, _
    strSQLStatement As String, _
    frmParent As Form)
` run the specified query and populate the
` listview with the results
    Dim strDBName As String
    Dim lngOrdRecPos As Long
    Dim db As Database
    Dim rs As Recordset
    Dim fld As Field
    strDBName = BiblioPath()
    Set db = DBEngine(0).OpenDatabase(strDBName)
    Set rs = db.OpenRecordset(strSQLStatement, _
        dbOpenDynaset, dbReadOnly)
    ` test for no records
    If Not rs.EOF Then
        ` create the ordinal position column
        lvwResults.ColumnHeaders.Add , "Ordinal", "Record"
        ` set width
        lvwResults.ColumnHeaders("Ordinal").Width = 600
        ` create the columns in the listview
        For Each fld In rs.Fields
            lvwResults.ColumnHeaders.Add , fld.Name, fld.Name
            ` best guess column width
            lvwResults.ColumnHeaders(fld.Name).Width _
                = 150 * Len(fld.Name)
            If fld.Name = strKeyField Then
                ` mark the item index for later retrieval
                mintItemIdx = fld.OrdinalPosition + 1
            End If
        Next    ` field
        ` populate the list
        Do
            ` increment the ordinal position counter
            lngOrdRecPos = lngOrdRecPos + 1
            ` add the item
            lvwResults.ListItems.Add _
                lngOrdRecPos, , CStr(lngOrdRecPos)
            ` add the fields to the rest of the columns
            For Each fld In rs.Fields
                lvwResults.ListItems(lngOrdRecPos). _
                SubItems(fld.OrdinalPosition + 1) = _
                fld.Value & ""
            Next    ` field
            ` go to next record
            rs.MoveNext
        Loop While Not rs.EOF
        ` clean up
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        ` show modally
        Me.Show vbModal, frmParent
    Else
        ` no data, treat as a cancel
        mblnCancelled = True
        MsgBox "No matching records found.", vbInformation
        Me.Hide
    End If
End Sub

How It Works

The FSearchResults form provides a generic tool for running an ad hoc query, displaying its results, and returning a key value selected by the user. The benefit of using a generic search form is that the form can be easily reused in any situation in which this type of lookup is required. This form can be added with minimal impact on the original design or performance of the main data entry form, but it still provides the user with a more advanced method of entering the PubID foreign key field.

The form works by filling a ListView control using a completely generic population routine in the Search method. When the user selects a record and dismisses the form by clicking OK, a private module-level variable retains the selected item, which can then be read from the KeyValue property.

Comments

Over time, you might build a significant library of generic components such as this lookup form. If the tools are properly designed and not coupled too tightly to any particular application or database, you might be able to bundle them together in an ActiveX DLL, which can then be included in future applications without having to return to the source code.

2.6 How do I...

Find records by using index values in Data Access Objects?

Problem

I know that indexes can be used to speed up database operations. How can I take advantage of indexes in my application?

Technique

When a table-type recordset has a current index (either the primary key or another index you have designated), you can use the Seek method to find records based on the indexed values. To use the Seek method, provide it with an argument that matches each field in the index. When the Seek method executes, if it finds at least one record matching the index values, it positions the record pointer to the first matching record and sets the NoMatch property of the recordset to False. If Seek does not find a matching record, it sets the NoMatch property to True; the current record is then undefined, which means that you can't be sure where the record pointer is pointing.

When you use Seek, specify not only the values for the key index fields but also the comparison criterion that Seek is to use. You provide the comparison criterion as the first argument to the Seek method, and you provide it as a string value. In the majority of cases, you will specify that Seek is to match the index value exactly; you do this by specifying a comparison criterion of =. You can also specify < > for not equal, > for greater than, < for less than, >= for greater than or equal to, or <= for less than or equal to.

Steps

Open and run the project HT206.VBP. Use the Index command on the Data menu to select the ISBN index. Browse forward in the recordset a few records, and copy the ISBN number from the form to the clipboard. Using the MoveFirst button, return to the first record; then select Data|Seek to display the input box shown in Figure 2.7. Paste the value you copied into the input box, and click OK. The record with the matching ISBN number is displayed.

Figure 2.7. The Seek form.

: 1. Create a new project called HT206 .VBP. This example adds the capability to find records using the Seek method to the basic browser application developed in How-To 2.1. The form and class module contain minor changes and some added code to support the Seek operation. The READINI.bas module is unchanged. Only the code that has been added or modified for this project is shown in the steps that follow.
: 2. Change the caption of Form1 to Seek, and modify the Data menu as shown in Table 2.7. The Index command, its submenu items, and the Seek command (all shown in bold) are new in this project.

Table 2.7. Menu specifications for Form1.

CAPTION Name Shortcut Key

&File mnuFile

----E&xit mnuFileExit

&Data mnuData

----&Save Record mnuDataSaveRecord Ctrl+S

----&Index mnuDataIndex

--------&ISBN mnuDataIndexName

--------&Title mnuDataIndexName

----S&eek mnuDataSeek

: 3. Modify the declarations section of Form1 as shown. Index constants have been added for the mnuDataIndexName control array.

Option Explicit
` CTitles object
Private mclsTitles As CTitles
` These constants are used for the various control arrays
` command button constants
Const cmdMoveFirst = 0
Const cmdMovePrevious = 1
Const cmdMoveNext = 2
Const cmdMoveLast = 3
` text box index constants
Const txtTitle = 0
Const txtYearPublished = 1
Const txtISBN = 2
Const txtPubID = 3
` index constants
Const idxISBN = 0
Const idxTitle = 1
` refresh flag
Private mblnInRefresh As Boolean

: 4. Modify the mnuData_Click event procedure to toggle the enabled flag for the mnuDataSeek menu control if an index has been chosen.

Private Sub mnuData_Click()
` set enabled/disabled flags for menu commands
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` seek enabled only if index is set
    If Len(mclsTitles.IndexName) Then
        mnuDataSeek.Enabled = True
    Else
        mnuDataSeek.Enabled = False
    End If
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: 5. Add the following code to Form1 as the mnuDataIndexName_Click and mnuDataSeek_Click events. The mnuDataIndexName_Click procedure assigns the index to use for the Seek operation and manages the check marks on the menu. The mnuDataSeek_Click procedure prompts the user for search criteria and attempts to locate the value provided by calling the Seek method of mclsTitles.

Private Sub mnuDataIndexName_Click(Index As Integer)
` set the current index
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    ` set the index
    Select Case Index
        Case idxISBN
            ` assign the index
            mclsTitles.Index = IndexISBN
            ` set up menu check marks
            mnuDataIndexName(idxTitle).Checked = False
            mnuDataIndexName(idxISBN).Checked = True
        Case idxTitle
            ` assign the index
            mclsTitles.Index = IndexTitle
            ` set up menu check marks
            mnuDataIndexName(idxTitle).Checked = True
            mnuDataIndexName(idxISBN).Checked = False
    End Select
    ` refresh display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub
Private Sub mnuDataSeek_Click()
` seek a record
On Error GoTo ProcError
    Dim strMsg As String
    Dim strResult As String
    Screen.MousePointer = vbHourglass
    ` prompt for a value
    strMsg = "Enter a value to search for:"
    strResult = InputBox$(strMsg)
    ` seek for the record
    mclsTitles.SeekRecord strResult
    ` refresh display
    GetData
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description, vbExclamation
    Resume ProcExit
End Sub

: 6. Modify the declarations section of the CTitles class to include the new enumeration for the index and to modify the error enumeration for new error messages.

Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
    FirstRecord = 1
    LastRecord = 2
    NextRecord = 3
    PreviousRecord = 4
End Enum
` Index constants
Public Enum CTitlesIndex
    IndexISBN = 0
    IndexTitle = 1
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
    ErrRecordNotFound = vbObjectError + 1000 + 10
    ErrInvalidMoveType = vbObjectError + 1000 + 11
    ErrNoRecords = vbObjectError + 1000 + 12
    ErrInvalidIndex = vbObjectError + 1000 + 13
End Enum

: 7. Change the Class_Initialize procedure so that a table-type recordset is created rather than a dynaset-type recordset. Only the table-type recordset supports the Seek method. The only thing required to change to a table-type recordset is to change the dbOpenDynaset constant to dbOpenTable.

Private Sub Class_Initialize()
    ` open the database and recordset
    Dim strDBName As String
    ` Get the database name and open the database.
    ` BiblioPath is a function in READINI.BAS
    strDBName = BiblioPath()
    Set mdb = DBEngine.Workspaces(0).OpenDatabase(strDBName)
    ` Open the recordset.
    Set mrs = mdb.OpenRecordset( _
        "Titles", dbOpenTable, dbSeeChanges, dbOptimistic)
    ` Raise an error if there is no data
    If mrs.BOF Then
        RaiseClassError ErrNoRecords
    End If
    ` fetch the first record to the properties
    GetCurrentRecord
End Sub

: 8. The RaiseClassError procedure has had new sections added to the Select...Case block for the newly added errors.

Private Sub RaiseClassError(lngErrorNumber As CTitlesError)
` Note: DAO errors are passed out as-is
    Dim strDescription As String
    Dim strSource As String
    ` assign the description for the error
    Select Case lngErrorNumber
        Case ErrRecordNotFound
            strDescription = "The record was not found."
        Case ErrInvalidMoveType
            strDescription = "Invalid move operation."
        Case ErrNoRecords
            strDescription = "There are no records " _
                & "in the Titles table."
        Case ErrInvalidIndex
            strDescription = "Invalid Index Name."
        Case Else
            ` If this executes, it's a coding error in
            ` the class module, but having the case is
            ` useful for debugging.
            strDescription = "There is no message for this error."
    End Select
    ` build the Source property for the error
    strSource = App.EXEName & ".CTitles"
    ` raise it
    Err.Raise lngErrorNumber, strSource, strDescription
End Sub

: 9. Add the IndexName and Index properties. The IndexName property is used to determine the current index for the recordset. The Index property changes the index based on the value provided in the lngIndex parameter.

Public Property Get IndexName() As String
    IndexName = mrs.Index
End Property
Public Property Let Index(lngIndex As CTitlesIndex)
` unlike the field values, this is validated when assigned
    Dim vntBookmark As Variant
    ` save a bookmark
    vntBookmark = mrs.Bookmark
    ` assign the index
    Select Case lngIndex
        Case IndexISBN
            mrs.Index = "PrimaryKey"
        Case IndexTitle
            mrs.Index = "Title"
        Case Else
            ` invalid, raise an error
            RaiseClassError ErrInvalidIndex
    End Select
    ` return to old record
    mrs.Bookmark = vntBookmark
End Property

: 10. Add the SeekRecord method. This method stores a bookmark and seeks for the value passed in the strValue parameter. If a matching record is found, it is fetched. If no matching record is found, the saved bookmark is used to restore the record pointer to the original position.

Public Sub SeekRecord(strValue As String)
` seek to the indicated record based on the current index
    Dim vntBookmark As Variant
    ` mark the current record
    vntBookmark = mrs.Bookmark
    ` seek, the first operator is the comparison,
    ` the following represent the field(s) in the index
    mrs.Seek "=", strValue
    ` check for match
    If Not mrs.NoMatch Then
        ` found it, now fetch it
        GetCurrentRecord
    Else
        ` not found, return to prior location
        mrs.Bookmark = vntBookmark
        ` raise the not found error
        RaiseClassError ErrRecordNotFound
    End If
End Sub

How It Works

The Seek method takes two or more parameters. The first parameter specifies the comparison operator (normally =), and the following parameters are the values for the fields in the index. The Index Property Let procedure in the CTitles class enables you to assign the current index for the recordset, and SeekRecord searches for a value. Both procedures use bookmarks to store records and, if necessary, return to the original record.

Comments

You cannot set indexes or use the Seek method with dynaset- or snapshot-type Recordset objects. To find a record in a dynaset or snapshot recordset, use one of the Find methods: FindFirst, FindNext, FindPrevious, or FindLast. Because these methods do not use indexes, they are much slower than Seek operations with table-type recordsets. You also cannot use Seek on remote server tables because these cannot be opened as table-type recordsets.

In most cases, it is much faster to create a new dynaset- or snapshot-type recordset than to use either a Find or the Seek method. You do this by building a SQL statement that includes a WHERE clause specifying the records you want to retrieve. If the database engine can find a useful index for the query, it uses that index to speed up the query.

See Chapter 3, "Creating Queries with SQL," for details on creating SQL statements, and Chapter 4, "Designing and Implementing a Database," for more information on choosing and defining indexes.

2.7 How do I...

Determine how many records are in a dynaset- or snapshot-type recordset?

Problem

I need to know how many records are in a recordset I've created. For table-type recordsets, this is easy--I just use the value of the RecordCount property. But when I try this with a dynaset- or snapshot-type recordset, I can't predict what value will be returned. Sometimes it's the correct count, while other times it's not. How can I reliably determine the number of records in a dynaset- or snapshot-type recordset?

Technique

The system tables in a Microsoft Access database include information about the number of records in every table in the database. As records are added or deleted, the table is continuously updated by the Jet engine. You can determine the number of records in the table at any time by checking the RecordCount property of the TableDef. Unlike a table, dynaset- and snapshot-type recordsets are temporary recordsets. You can't obtain a record count by checking a TableDef.

You can retrieve the RecordCount property of a dynaset- or snapshot-type recordset, but the value it returns depends on several factors in addition to the number of records actually in the recordset. The only way the Jet engine can determine how many records are in a dynaset- or snapshot-type recordset is by counting them. To count them, the Jet engine has to move through the records, one by one, until it reaches the end of the recordset. When the Jet engine creates a dynaset- or snapshot-type recordset, however, it does not automatically count the records because counting the records in a large recordset could take a long time. If you retrieve the RecordCount property immediately after you create a dynaset- or snapshot-type recordset, therefore, you're guaranteed to get back one of two values: 0 if the recordset is empty or 1 if the recordset has at least one record.

To get an accurate count, your code must tell the Jet engine to count the records. Do this by executing the recordset's MoveLast method. After a MoveLast, you can retrieve the RecordCount with the confidence that the value is accurate. In the case of a dynaset-type recordset, if you add or delete records, the Jet engine keeps track of them for you, and any subsequent looks at RecordCount will give you the correct current count.

Steps

Open and run the project HT207.VBP. You will see the form shown in Figure 2.8. This form shows the number of records in the BIBLIO.MDB Authors table. The first box reports the number of records reported immediately after Authors is opened as a table-type recordset. The second box shows the number of records reported immediately after a dynaset is opened with the SQL statement SELECT Au_ID FROM Authors--a statement that returns a dynaset consisting of the entire table, the same set of records that are in the table-type recordset reported in the first box. The third box shows the record count from the dynaset after its MoveLast method has been used. Note that the first and third boxes have the same number (which might be different on your system). The second box, reporting the dynaset record count before the MoveLast, shows a count
of 1.

Figure 2.8. The Record Counts form.

: 1. Create a new project called HT207 .VBP. Create the objects and properties listed in Table 2.8, and save the form as HT207.FRM.

Table 2.8. Objects and properties for Form1.

OBJECT Property Setting

Form Name Form1

Caption Record Counter

CommandButton Name cmdExit

Cancel True

Default True

Caption Exit

Label Name lblTable

Alignment 2 (Center)

BorderStyle 1 (Fixed Single)

Label Name lblDynasetCreate

Alignment 2 (Center)

BorderStyle 1 (Fixed Single)

Label Name lblDynasetMoved

Alignment 2 (Center)

BorderStyle 1 (Fixed Single)

Label Name Label1

Caption Records reported in the

BIBLIO.MDB Authors table

recordset:

Label Name Label2

Caption Records in the BIBLIO.MDB SELECT

Au_ID FROM Authors dynaset immediately after creation.

Label Name Label3

Caption Records in the BIBLIO.MDB SELECT

Au_ID FROM Authors dynaset after using the MoveLast method.

: 2. Add READINI.BAS to your project.
: 3. Add the Form_Load event procedure. Form_Load opens the database, opens table and dynaset recordsets, and gets record counts for both recordsets. It then uses MoveLast to go to the end of the dynaset and gets the record count again. Finally, Form Load inserts each of the record counts into Label controls on the form.

Private Sub Form_Load()
On Error GoTo ProcError
    Dim strDBName As String
    Dim strSQL As String
    Dim db As Database
    Dim rsTable As Recordset
    Dim rsDynaset As Recordset
    ` Get the database name and open the database.
    ` BiblioPath is a function in READINI.BAS
    strDBName = BiblioPath()
    Set db = DBEngine.Workspaces(0).OpenDatabase(strDBName)
    Set rsTable = db.OpenRecordset("Authors", dbOpenTable)
    lblTable = rsTable.RecordCount
    strSQL = "SELECT Au_ID FROM Authors"
    Set rsDynaset = db.OpenRecordset(strSQL, dbOpenDynaset)
    lblDynasetCreate = rsDynaset.RecordCount
    rsDynaset.MoveLast
    lblDynasetMoved = rsDynaset.RecordCount
ProcExit:
    On Error Resume Next
    ` clean up
    rsDynaset.Close
    rsTable.Close
    db.Close
    Exit Sub
ProcError:
    MsgBox Err.Description
    Resume ProcExit
End Sub

: 4. Insert the following code as the Click event of cmdExit:

Private Sub cmdExit_Click()
    Unload Me
End Sub

How It Works

The table-type recordset enables you to check the RecordCount property immediately after it is created, but with a snapshot- or dynaset-type recordset, you must first access all the records before you can obtain an accurate count. This is typically done via the MoveLast method.

Comments

One common reason for wanting an accurate record count is to use the count as the control for a For...Next loop to cycle through the entire recordset, as in this code fragment:

myRecordset.MoveLast
n = myRecordset.RecordCount
myRecordset.MoveFirst
for i = 1 to n
    ` do something
    myRecordset.MoveNext
next i

The difficulties with getting and keeping accurate record counts make it inadvisable to use code like this--especially in shared data environments where the record count of a table can change the instant after you retrieve it. This fragment illustrates a more reliable way to accomplish the same goal:

myRecordset.MoveFirst
Do While Not myRecordset.EOF
    ` do something
    myRecordset.MoveNext
Loop

This loop executes until the last record in the recordset has been processed (when myRecordset.EOF becomes True), and it does not depend on a potentially unstable record count.

2.8 How do I...

Handle Data Access Object errors?

Problem

When I access a database through Visual Basic, I have limited control over the environment. A user might move a database file, or another program might have made unexpected changes to the database. I need my programs to be able to detect errors that occur and handle them in the context of the program. How do I accomplish this task with Visual Basic?

Technique

When an error occurs in a Visual Basic program, control passes to error-handling logic. Unless you have enabled an error handler, Visual Basic uses its default handler, which displays a message about the error--one that is sometimes useful, but often not--and terminates the application.

Clearly, the default handling is not acceptable. Fortunately, Visual Basic provides tools that you can use to build your own error traps and handlers. Although any Visual Basic application should trap and handle runtime errors, it is especially important in database applications in which many error conditions can be expected to occur. Visual Basic error-trapping is enabled with the On Error statement. The On Error statement takes two basic forms:

On Error Goto label
On Error Resume Next

In the first form, when a runtime error occurs, Visual Basic transfers control of the application to the location specified by label. In the second form, Visual Basic continues execution with the line following the line in which the error occurred. When an error trap is enabled and an error occurs, Visual Basic performs the action indicated by the most recent On Error statement in the execution path. Listing 2.1 shows a hypothetical call tree and several variations of how error handlers are enabled and activated.

Listing 2.1. Trapping errors.

Sub SubA()
    ...other code
    SubB
End Sub
Sub SubB()
On Error Goto ProcError
  SubC
ProcExit:
    Exit Sub
ProcError:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    Resume ProcExit
End Sub
SubC
    ...other code
    SubD
End Sub
SubD()
On Error Resume Next
    ...code
End Sub

Understanding the path of execution in this code fragment is important to comprehending how error handlers are activated:

In SubA no error handler exists. If an error occurs before the call to SubB, Visual Basic's default handler displays a message and terminates the application.
SubB uses the On Error Goto form. If an error occurs in this routine, control is transferred to the statement following the ProcError label.
SubC has no error handler. However, when it is called from SubB, the error handler in SubB is still enabled, so an error in SubC also transfers control to the statement following the ProcError label in SubB.
SubD uses the On Error Resume Next form. If an error occurs in this procedure, code within the procedure needs to detect and handle the error.

WARNING Errors in Class_Terminate events and most form and control events are fatal to your application if untrapped, so it is especially important that you include error-handling logic in these procedures.

Errors generated by Visual Basic and by components of an application--including the Jet database engine--are associated with error numbers. You can obtain the error number, which tells you the nature of the error, by reading the Number property of the Err object. You can read additional information about the error from the Description property. After you know the type of error, you can take appropriate action to handle it. This is typically done with a Select Case block.

NOTE For backward compatibility, Visual Basic still supports the outdated Err and Error statements and functions. However, any new code should use the Err object.

The code fragment in Listing 2.2 illustrates how you might handle some common errors that occur in a multiuser environment. (See Chapter 11, "The Windows Registry and State Information," for a complete discussion of working with a multiuser database application.)

Listing 2.2. Error handling.

Sub DAOCode()
On Error Goto ProcError
    ...code
ProcExit:
    Exit Sub
ProcError
    Dim strMsg As String
    Select Case Err.Number
        Case 3197
            ` Another user changed the data since the last time
            ` the recordset was updated
            strMsg = "The data in this record was changed by " & _
                  "another user." & _
                  vbCrLf & "Do you want to overwrite those changes?"
            If MsgBox(strMsg, vbYesNo or vbQuestion or vbDefaultButton2) _
                  = vbYes Then
                ` VB only generates the error on the first attempt
                ` Resume re-executes the line that caused the error
                Resume
            Else
                ` refresh the existing data
                rs.Requery
                DisplayData
                Resume ProcExit
            End If
        Case 3260
            ` locked by another user
            strMsg = "The record is currently locked by another user."
            ` control continues at end of block
        Case Else
            ` default
            strMsg = "Error: " & Err.Number & vbCrLf & Err.Description
    End Select
    MsgBox strMsg, vbExclamation
    Resume ProcExit
End Sub

An error handler must execute a statement that clears the error. Table 2.9 list the methods of clearing an error.

Table 2.9. Statements that clear an error.

STATEMENT Effect

Resume Re-executes the line that generated the error

Resume Next Resumes execution at the line that follows the line that generated the error

Resume label Resumes execution at the line following the named label

Resume number Resumes execution at the line with the indicated number

Exit Sub Exits immediately from the current subroutine

Exit Function Exits immediately from the current function

Exit Property Exits immediately from the current property

On Error Resets error-handling logic

Err.Clear Clears the error without otherwise affecting program execution

End Terminates execution of the program

ERRORS IN CLASS MODULES

It is good programming practice to separate data management code in class modules from user interface code in forms. To maintain this separation, it is important that you do not simply display a message if an error occurs in a class module. Two types of error situations can occur in a class module.

A class can detect an error condition (such as the violation of a validation rule). In this case, the class module should call the Raise method of the Err object and set the Number, Description, Source, and--if a help file is available--the appropriate help properties.

A class can also trap errors raised by the database engine. These can be much more difficult to handle. The sheer number of possible errors makes it impractical in most applications to reassign and describe these errors, so most applications simply regenerate them.

In either case, the code in the class module should, if possible, attempt to correct the error before raising it.

Steps

Open and run the project HT208.VBP. Three errors will occur in succession. For each, the message reporting the error gives you the error number, error description, and line number where the error occurred. Figure 2.9 shows the first of these errors.

Figure 2.9. The HT208 error message.

: 1. Create a new project called HT208.VBP. Use Form1 to create the objects and properties listed in Table 2.10, and save the form as HT208.FRM.

Table 2.10. Objects and properties for Form1.

OBJECT Property Setting

Form Name Form1

Caption Errors

: 2. Add the file READINI.BAS to your project.
: 3. Add the following code as the Load event of Form1. This code generates three errors. Line 20 generates an error because there is no such table as No Such Table. Line 40 generates an error because there is no such field as No Such Field. Line 60 generates an error because the Year Published field requires a numeric value. Each error causes execution to branch to the label LoadError. The code beginning with LoadError displays an informative message and then executes a Resume Next. The Resume Next transfers execution back to the line following the line that caused the error.

Private Sub Form_Load()
On Error GoTo ProcError
    Dim db As Database
    Dim dbName As String
    Dim rs As Recordset
    Dim s As String
    ` Get the database name and open the database.
    ` BiblioPath is a function in READINI.BAS
5   dbName = BiblioPath()
10  Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)
20  Set rs = db.OpenRecordset("No Such Table", dbOpenTable)
30  Set rs = db.OpenRecordset("Titles", dbOpenTable)
40  s = rs![No Such Field]
50  rs.Edit
60  rs![Year Published] = "XYZ"
70  rs.Update
80  End
Exit Sub
ProcError:
    MsgBox "Error: " & Err.Number & vbCrLf & _
        "Line: " & Erl & vbCrLf & _
        Err.Description, vbExclamation
Resume Next
End Sub

NOTE Line numbers are used here to help illustrate the error handler. Few programmers actually use them in production code, although they can be helpful for debugging.

How It Works

When Visual Basic encounters a runtime error, it transfers control of the application to the error-handling code you specify by using the On Error statement. You have a choice of inline handling using On Error Resume Next or centralized handling using On Error Goto. Either way, it's up to you to determine the type of error generated and the appropriate action to take for that error.

ERRORS COLLECTION
It is possible for a single statement in DAO code to generate several errors. You can examine these errors by iterating the DAO Errors collection, as shown in the following code fragment:
For Each Error In Errors
    Debug.Print Err.Number & " - " & Err.Description
Next ` Error

Comments

Hundreds of potential runtime errors can occur in a Visual Basic application, and you are unlikely to be able to anticipate all of them. With experience and careful coding, you can be prepared for the most common problems, but you should expect that from time to time your application will encounter a situation for which you did not explicitly plan. In this situation it is important for your general error-handling code to offer as much information as possible to the user and to provide the opportunity to correct the problem.

You might also find it useful to write errors to a log file. Examining an error log provides you with information you can use to build more robust error-handling procedures.

2.9 How do I...

Access Excel worksheets by using Data Access Objects?

Problem

I have Excel worksheets that my company created. I need to incorporate this data into my own applications. I want to both display and manipulate the data from these Excel worksheets. How do I work with these Excel files in my Visual Basic projects?

Technique

By using the Microsoft Jet engine, you can access Excel worksheets as if they were actually Access databases. As with accessing other types of ISAM files with the Jet engine, there are a few restrictions with accessing Excel worksheets:

You cannot delete rows.
You cannot delete or modify cells that contain formulas.
You cannot create indexes.
You cannot read encrypted Excel files, even when using the correct password (PWD parameter) in the Connect string.

You can add records to a worksheet or edit standard cells (those without formulas).

When opening an ISAM database with the OpenDatabase method, you must provide a valid ISAM type. In addition to this, if you want to use the first row of the Excel document as field names, you can specify a parameter HDR equal to Yes. (HDR stands for header.) If you set HDR to No, the first row of the Excel worksheet is included as a record in the recordset, as in this example:

Set db = DBEngine.Workspaces(0).OpenDatabase("C:\VB6DBHT\" & _
                                  "CHAPTER02\HowTo09\WidgetOrders.XLS", _
                                  False, False, "Excel 8.0; HDR=NO;")

Notice that when accessing Excel worksheets, unlike with other ISAM formats, you must specify the filename in the OpenDatabase method.

When opening a recordset from an Excel ISAM, you must specify the sheet name as the recordset name, followed by a dollar sign ($), as in this example:

WorkSheetName = "Sheet1"
Set rs = db.OpenRecordset(WorkSheetName & "$", dbOpenTable)

Here, rs is a recordset variable.

Steps

Open and run the ExcelDAO project. You should see a form that looks like the one shown in Figure 2.10. This application enables you to view a Microsoft Excel worksheet file in a ListView control. If you click the Add or Edit buttons, the form expands so that you can edit the contents. Clicking OK or Cancel when editing a record either saves or discards your changes, respectively. By clicking the View in Excel button, you can view the worksheet in Microsoft Excel (assuming that you have Microsoft Excel; it is not included on the distribution CD-ROM with this book). If you make changes in Excel and click the Refresh button on the form, the ListView control repopulates with the updates.

Figure 2.10. The ExcelDAO project.

: 1. If you have not already installed the Excel IISAM, reinstall Visual Basic with the Custom Setup option, and specify the Excel IISAM in setup.
: 2. Create a new project and call it ExcelDAO.
: 3. Go to the Project|Components menu item in Visual Basic, and select Microsoft Windows Common Controls 6.0 from the list. This selection enables you to use the ListView common control that comes with Windows 95.
: 4. Add the appropriate controls so that the form looks like that shown in Figure 2.11.
: 5. Edit the objects and properties of Form1, as shown in Table 2.11, and then save it as frmWidgetOrders.

Figure 2.11. The ExcelDAO form in design mode.

Table 2.11. Objects and properties for the Widget Orders project.

OBJECT Property Setting

Form Name frmWidgetOrders

Caption Widget Orders

Height 3390

List view Name lstvWidgetOrders

View 3 `vwReport

Command button Name cmdAdd

Caption &Add

Command button Name cmdEdit

Caption &Edit

Command button Name cmdView

Caption &View in Excel

Command button Name cmdRefresh

Caption &Refresh

Command button Name cmdClose

Caption &Close

Cancel True

Default True

Command button Name cmdOk

Caption &Ok

Command button Name cmdCancel

Caption &Cancel

Text box Name txtOrderNum

Text box Name txtProductID

Text box Name txtProductDesc

Text box Name txtQuantity

Text box Name txtUnitPrice

Label Name lblOrderNum

Caption Order Num

Label Name lblProductID

Caption Product ID

Label Name lblProductDesc

Caption Product Description

Label Name lblQuantity

Caption Quantity

Label Name lblUnitPrice

Caption Unit Price

: 6. Enter the following code in the declarations section of your project. The variables and constants included here are form level and can be accessed by any code within this form. The integer, m_nState, holds the value of one of the two constants defined. This variable states whether the user has selected to add a new record or edit the current one. The m_oSelItem object variable of type ComctlLib.ListItem is an object that holds the value of a selected item in the list view. The last two variables hold the path, filename, and worksheet name of the Excel file to be used in this project.

Option Explicit
` form-level variables used to hold the database and recordset
Private db As Database
Private rs As Recordset
` form-level constant values used to indicate the current state
Private Const ADD_RECORD = 0
Private Const EDIT_RECORD = 1
` form-level variables used to save the current state, and 
` selected list item
Private m_nState As Integer
Private m_oSelItem As ComctlLib.ListItem
` form-level variables used to store the file path and sheet name
` of the Excel file used in the app
Private m_sFilePath As String
Private m_sSheetName As String

: 7. Add the following code to the Form_Activate event to get the path and filename of the Excel XLS file to be used. This information is stored in the form-level variable m_sFilePath. The worksheet name is stored in the m_sSheetName form-level variable. Finally, the PopulateListView routine is called. The file is opened and read here.

Private Sub Form_Activate()
    ` allow app to paint screen
    DoEvents
    ` get paths and names of files used in app
    m_sFilePath = DataPath & "\Chapter02\WidgetOrders.xls"
    m_sSheetName = "Sheet1$"
    ` populate the list view control
    PopulateListView
End Sub

: 8. Add the following code to clear the text boxes and display the lower portion of the form for editing, or in this case, adding a new record. The last line of code sets the m_nState variable to ADD_RECORD, indicating that the user is adding a new record.

Private Sub cmdAdd_Click()
    ` clear all the text boxes
    txtOrderNum = ""
    txtProductID = ""
    txtProductDesc = ""
    txtQuantity = ""
    txtUnitPrice = ""
    ` show the bottom of the form and set the state to add so we 
    ` know how to save the record later
    ShowBottomForm
    m_nState = ADD_RECORD
End Sub

: 9. The cmdEdit_Click event is used when the user wants to edit the current record. The database will already be open from the PopulateListView routine. This event walks through each record in the recordset until the selected item's order number matches the record's order number. After the record is found, the text boxes on the lower portion of the form are populated with the values in the corresponding fields. The Total Price field is left out because we will calculate it ourselves from the given information. After the text boxes are populated, the form is lengthened by the call to ShowBottomForm, and the m_nState variable is set to EDIT_RECORD, indicating that the user has chosen to edit the current record.

Private Sub cmdEdit_Click()
    ` we cannot use indexes with Excel files, so we must 
    ` transverse the recordset until the record matches the 
    ` selected item, then populate the text boxes with the records 
    ` values
    With rs
        .MoveFirst
        While (.Fields("Order Number") <> m_oSelItem.Text)
            .MoveNext
        Wend
        txtOrderNum = .Fields("Order Number")
        txtProductID = .Fields("Product ID")
        txtProductDesc = .Fields("Product Description")
        txtQuantity = .Fields("Quantity")
        txtUnitPrice = .Fields("Unit Price")
    End With
    ` show the bottom of the form and set the state to editing so 
    ` we know how to save the record later
    ShowBottomForm
    m_nState = EDIT_RECORD
End Sub

: 10. With the cmdView command button, the user can view the worksheet used in this project in Excel. To enable the user to view the file in Excel, the database must be closed first. This is done by setting the rs and db objects to nothing. The Shell command then calls Excel with the m_sFilePath variable, which holds the path and filename of the Excel worksheet. The ExcelPath function is included in the ReadINIFile module installed from the distribution CD-ROM.

Private Sub cmdView_Click()
    ` set the recordset and database to nothing because Excel will 
    ` not be able to successfully open the file if not
    Set rs = Nothing
    Set db = Nothing
    ` open Excel with the file
    Shell ExcelPath & " """ & m_sFilePath & """", vbNormalFocus
End Sub

: 11. If the user edits the worksheet in Excel while the Widget Orders project is open, the changes will not be apparent in the application. Use the Refresh button to repopulate the information from the worksheet. Enter the code for cmdRefresh_Click:

Private Sub cmdRefresh_Click()
    ` force a repopulation of the list view (use when the user has 
    ` made changes in Excel to the file)
    PopulateListView
End Sub

: 12. Now enter the following code for the Close button and the Form_Unload event to successfully end the application:

Private Sub cmdClose_Click()
    ` always use Unload Me instead of End
    Unload Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
    ` it is good practice to set all objects to nothing
    Set m_oSelItem = Nothing
    ` this is equivalent to closing the recordset and the database
    Set db = Nothing
    Set rs = Nothing
End Sub

: 13. The OK button is used to save the information after a user has edited the current record or added a new one. The event can determine the proper recordset method to use from the m_nState variable, either AddNew or Edit. The information for each field is saved, and the Total Price field is calculated from the Unit Price and the Quantity. After the save, the list view is repopulated, and the bottom of the form is hidden from the user. The code for the button is as follows:

Private Sub cmdOk_Click()
    ` edit or add new is confirmed, save the values of the text 
    ` boxes this would be a good place to code validation for each 
    ` field
    With rs
        If (m_nState = ADD_RECORD) Then
            .AddNew
        Else
            .Edit
        End If
        .Fields("Order Number") = txtOrderNum
        .Fields("Product ID") = txtProductID
        .Fields("Product Description") = txtProductDesc
        .Fields("Quantity") = txtQuantity
        .Fields("Unit Price") = txtUnitPrice
        .Fields("Total Price") = txtUnitPrice * txtQuantity
        .Update
    End With
    ` repopulate the listview with the changes; then hide the 
    ` bottom of the form
    PopulateListView
    HideBottomForm
End Sub

: 14. The cmdCancel_Click event simply hides the bottom half of the form so that the user cannot edit the record. Because the recordset has not been set to AddNew or Edit yet, we need do nothing further.

Private Sub cmdCancel_Click()
    ` edit or add new was canceled, hide the bottom of the form
    HideBottomForm
End Sub

: 15. The following routines show the bottom of the form, hide the bottom of the form, and set the Enabled property of all input controls but the ListView to the appropriate state:

Private Sub ShowBottomForm()
    ` lengthen the height of the form and enable the proper 
    ` controls
    Me.Height = 4350
    SetObjects False
End Sub
Private Sub HideBottomForm()
    ` shorten the height of the form and enable the proper 
    ` controls
    Me.Height = 3390
    SetObjects True
End Sub
Private Sub SetObjects(StateIn As Boolean)
    ` set Enabled property for controls on top of form
    cmdAdd.Enabled = StateIn
    cmdEdit.Enabled = StateIn
    cmdRefresh.Enabled = StateIn
    cmdView.Enabled = StateIn
    cmdClose.Enabled = StateIn
    ` set Enabled property for controls on bottom of form
    txtOrderNum.Enabled = Not StateIn
    txtProductID.Enabled = Not StateIn
    txtProductDesc.Enabled = Not StateIn
    txtQuantity.Enabled = Not StateIn
    txtUnitPrice.Enabled = Not StateIn
    cmdOk.Enabled = Not StateIn
    cmdCancel.Enabled = Not StateIn
End Sub

: 16. The PopulateListView routine is the core of this project because it opens the database and sets the worksheet to a recordset. This routine also reads the database TableDefs collection to define the column headers for the list view, as well as populate the entire list view with the recordset. This first part of the routine adds a column for each field in the TableDefs(m_sSheetName).Fields collection. The second part of the routine steps through each record in the recordset and adds a list item for each record. Enter the code for the PopulateListView routine:

Private Sub PopulateListView()
    Dim oField As Field
    Dim nFieldCount As Integer
    Dim nFieldAlign As Integer
    Dim nFieldWidth As Single
    Dim oRecItem As ListItem
    Dim sValFormat As String
    ` this might take a noticeable amount of time, so before we do 
    ` anything change the mouse pointer to an hourglass and then 
    ` hide the bottom of the form
    Screen.MousePointer = vbHourglass
    HideBottomForm
    ` open the database (this might already be open; however, if 
    ` the user has just started the app or selected the `View in 
    ` Excel' button, then the database and recordset would be set 
    ` to nothing
    Set db = OpenDatabase(m_sFilePath, False, False, _
        "Excel 8.0;HDR=YES;")
    Set rs = db.OpenRecordset(m_sSheetName)
    With lstvWidgetOrders
        ` clear the list view box in case this is a refresh of the 
        ` records
        .ListItems.Clear
        .ColumnHeaders.Clear
        ` using the For Each statement as compared to the For To 
        ` statement is technically faster, as well as being 
        ` easier to understand and use
        For Each oField In db.TableDefs(m_sSheetName).Fields
            ` align currency fields to the right, all others to 
            ` the left
            nFieldAlign = IIf((oField.Type = dbCurrency), _
                              vbRightJustify, vbLeftJustify)
            ` our product description field is text, and the 
            ` values in this field are generally longer than their 
            ` field name, so increase the width of the column
            nFieldWidth = TextWidth(oField.Name) _
                         + IIf(oField.Type = dbText, 500, 0)
            ` add the column with the correct settings
            .ColumnHeaders.Add , , oField.Name, _
                                   nFieldWidth, _
                                   nFieldAlign
        Next oField
    End With
    ` add the records
    With rs
        .MoveFirst
        While (Not .EOF)
            ` set the new list item with the first field in the 
            ` record
            Set oRecItem = lstvWidgetOrders.ListItems.Add(, , _
                CStr(.Fields(0)))
            ` now add the rest of the fields as subitems of the 
            ` list item
            For nFieldCount = 1 To .Fields.Count - 1
                ` set a currency format for fields that are 
                ` dbCurrency type
                sValFormat = IIf(.Fields(nFieldCount).Type = 
                             dbCurrency, _
                                  "$#,##0.00", _
                                  "")
                ` set the subitem
                oRecItem.SubItems(nFieldCount) = _
                            Format$("" & .Fields(nFieldCount), _
                            sValFormat)
            Next nFieldCount
            .MoveNext
        Wend
    End With
    ` by setting the last record item to the selected record item 
    ` form variable, we can assure ourselves that a record 
    ` is selected for editing later
    Set m_oSelItem = oRecItem
    ` remember to set object variables to nothing when you are 
    ` done
    Set oRecItem = Nothing
    Set oRecItem = Nothing
    Screen.MousePointer = vbDefault
End Sub

How It Works

In this project, an Excel worksheet file is opened and used to create a recordset object to populate a ListView control. The code for accessing the recordset is the same as that of accessing Microsoft Access databases, using AddNew and Edit to alter the underlying ISAM data file. However, Excel worksheet rows cannot be deleted; therefore, the Delete method of a recordset is unavailable.

In the PopulateListView routine, the project uses the TableDef object of the database object to access the table definition of the Excel worksheet. Within this object, there is a collection of fields through which the project loops, adding a column header for each, using the fields' Name, Width, and Align properties.

After the column header collection of the list view is populated with the field names in the Excel worksheet, the ListView control is populated with the records. Accessing these records is the same as accessing other Jet database records. A list item is set for each record with the first field of the record. After this, the subitems of the list item are populated with the remaining fields in the current record.

This application also uses a trick to gain more space when necessary. The form is elongated when either the Add button or the Edit button is clicked, allowing room for a record editing area. After the record is saved or canceled, the form resumes its normal size.

Another feature of this project is that the user can view the worksheet in Excel by using the Shell method to start another application. By using this option, the user can load a worksheet, edit its changes, and then switch to Excel to see the results. Even if the user changes the worksheet within Excel while the project is still running (as long as the file is saved and the user clicks the Refresh button), the ListView control will be repopulated with the correct, up-to-date information.

Comments

It is possible to open ranges of an Excel worksheet using the Microsoft Jet Engine. To do this, replace the worksheet name in the OpenRecordset method with the range of cells you want returned, as in this example:

Set rs = db.OpenRecordset("B1:H12")

In this example, "B1:H12" is the range on which you want to create a recordset, from the specified Excel worksheet.

*OBJECT*	*Property*	*Setting*
Form	Name	Form1
	Caption	Unbound Browser
CommandButton	Name	cmdMove
	Caption	\|<
	Index	0
CommandButton	Name	cmdMove
	Caption	<
	Index	1
CommandButton	Name	cmdMove
	Caption	>
	Index	2
CommandButton	Name	cmdMove
	Caption	>\|
	Index	3
TextBox	Name	txt
	Index	0
TextBox	Name	txt
	Index	1
TextBox	Name	txt
	Index	2
TextBox	Name	txt
	Index	3
Label	Name	Label1
	Caption	&Title
Label	Name	Label2
	Caption	&Year Published
Label	Name	Label3
	Caption	&ISBN
Label	Name	Label4
	Caption	&Publisher ID

*CAPTION*	*Name*	*Shortcut Key*
&File	mnuFile
----E&xit	mnuFileExit
&Data	mnuData
----&Save Record	mnuDataSaveRecord	Ctrl+S

*OBJECT*	*Property*	*Value*
ListView	Name	lvwResults
	View	3 - lvwReport
	LabelEdit	1 - lvwManual
CommandButton	Name	cmd
	Index	0
	Caption	OK
	Default	True
CommandButton	Name	cmd
	Index	1
	Caption	Cancel
	Cancel	True

*STATEMENT*	*Effect*
Resume	Re-executes the line that generated the error
Resume Next	Resumes execution at the line that follows the line that generated the error
Resume label	Resumes execution at the line following the named label
Resume number	Resumes execution at the line with the indicated number
Exit Sub	Exits immediately from the current subroutine
Exit Function	Exits immediately from the current function
Exit Property	Exits immediately from the current property
On Error	Resets error-handling logic
Err.Clear	Clears the error without otherwise affecting program execution
End	Terminates execution of the program

www.Sir FreeDom.com.Ar

Visual Basic 6 Database How-To

- 2 - Accessing a Data-base with Data Access Objects

2.1 Browse and Update a Recordset with Data Access Objects

2.2 Validate Data Entered into Data Access Objects

2.3 Allow Users to Undo Changes They've Made in Data Access Objects

2.Add and Delete Records by Using Data Access Objects

2.5 Use Unbound Controls to Update Fields in Data Access Objects

2.6 Find Records by Using Index Values in Data Access Objects

2.7 Determine How Many Records Are in a Dynaset- or Snapshot-Type Recordset

2.8 Handle Data Access Object Errors

2.9 Access Excel Worksheets by Using Data Access Objects

2.1 How do I...

Browse and update records by using Data Access Objects?

Table 2.1. Objects and properties for Form1.

Table 2.2. Menu specifications for Form1.

End Sub

2.2 How do I...

Validate data entered into Data Access Objects?

2.3 How do I...

Allow users to undo changes they've made in Data Access Objects?

Table 2.3. Menu specifications for the Edit menu.

2.How do I...

Add and delete records by using Data Access Objects?

Table 2.4. Menu specifications for Form1.

2.5 How do I...

Use unbound controls to update fields in Data Access Objects?

TECHNIQUE

Table 2.5. Menu specifications for Form1.

End Sub

Table 2.6. Objects and properties for the FSearchResults form.

2.6 How do I...

Find records by using index values in Data Access Objects?

Table 2.7. Menu specifications for Form1.

2.7 How do I...

Determine how many records are in a dynaset- or snapshot-type recordset?

Table 2.8. Objects and properties for Form1.

2.8 How do I...

Handle Data Access Object errors?

Listing 2.1. Trapping errors.

Listing 2.2. Error handling.

Table 2.9. Statements that clear an error.

Table 2.10. Objects and properties for Form1.

2.9 How do I...

Access Excel worksheets by using Data Access Objects?

Table 2.11. Objects and properties for the Widget Orders project.

- 2 -
Accessing a Data-base with Data Access Objects