Database Access with the Data Control

This topic provides a brief introduction to developing database applications with Visual Basic through a series of nine step-by-step exercises. It should be noted that these exercises use the Data control with other controls bound to it. The Data control does a lot for you "behind the scenes" and you may be tempted to use it in your applications, but be advised that the Data control is rarely used in professional applications – the norm is to write your own database access code so that you have complete control over the process. These exercises are worthwhile in that you can see what data bound controls are all about, but again be advised that they should not be used for professional application development. For further elucidation on why data bound controls are "evil", read this article.

The intrinsic Data control is geared toward MS-Access 97 and earlier databases, although a later VB service pack added connectivity for Access 2000 databases. These articles use the two sample Access databases provided with Visual Basic (BIBLIO.MDB and NWIND.MDB). These databases are provided in Access 97 format. On a default installation of VB6, these databases can be found in the folder: C:\Program Files\Microsoft Visual Studio\VB98.

To do these exercises, you should make a folder into which you will copy the two database files mentioned above. Then, within the folder you have created, make separate subfolder for each exercise, one level below the root of your folder. The DatabaseName property for the Data control in these exercises assumes that the database file resides one directory level above the folder in which the exercise project files reside.

EXERCISE 1

Connecting to an Access Database Using the VB Data Control

STEPS:

1.         Open a new Visual Basic project.

2.         Put a data control (an intrinsic control, located in the VB toolbox) on the form and set the properties as follows:

Property	Value
(Name)	datAuthors
Caption	Use the arrows to view the data
Connect	Access (default)
DatabaseName	..\biblio.mdb
DefaultType	UseJet (default)
RecordSource	Authors (choose from list)

Notes:  When you use the Data Control in a project, the properties that must be set are DatabaseName and RecordSource, in that order.  DatabaseName is the name of the database you want to use, and the RecordSource is the name of the table in that database that you want to use.

3.         On your form, create a text box for each field in the Authors table, with labels.  (If you were to open the database in Access, you would see that the three fields of the Authors table are Au_ID, Author, and Year Born.)  Set the properties of the three textboxes as follows:

Name	DataSource	DataField
txtAuthID	datAuthors	Au_ID
txtAuthor	datAuthors	Author
txtYearBorn	datAuthors	Year Born

            In addition, set the Enabled property of txtAuthID to False.

When you want a control (such as a text box) to display data from a database, the properties that must be set are DataSource and Datafield.  The DataSource is the name of the data control on the form (it should already be configured), and the DataField is the name of the particular field in the database that should be displayed in the control (this field will be in the table that was chosen for the RecordSource of the data control).

            At this point, your form should resemble the screen-shot below:

4.         Save and run the project.  Use the arrows on the data control to scroll through the data.

5.         On any record, change the data in the author name or year born field.  Move ahead, then move back to the record you changed.  Note that your changes remain in effect.  The data control automatically updates a record when you move off of the record.

Note that this exercise demonstrated that you can create a simple but functional application that allows the user to browse through the rows of a database table (or result set) and to update rows in that table without writing any code.

EXERCISE 2

Using Navigation Buttons with a Data Control

In the previous exercise, you saw that by clicking specific buttons of the data control, you could move to the first, previous, next, or last record.  What is happening is that the data control is automatically invoking specific methods of the recordset object: namely the MoveFirst, MovePrevious, MoveNext, and MoveLast methods.  You can also invoke these methods through code, which is what this exercise demonstrates.

STEPS:

1. Copy the files from Exercise #1 into a new folder and open the VBP file in the new folder.

2.         Set the Visible property of the data control (datAuthors) to False.

3.         Make four command buttons with the following properties:

Name	Caption
cmdMoveNext	Next Record
cmdMoveLast	Last Record
cmdMovePrevious	Previous Record
cmdMoveFirst	First Record

At this point, your form should resemble the screen-shot below:

4.         Put the following four lines of code in the appropriate Click events for the buttons:

Event	Code
cmdMoveNext_Click	datAuthors.Recordset.MoveNext
cmdMoveLast_Click	datAuthors.Recordset.MoveLast
cmdMovePrevious_Click	datAuthors.Recordset.MovePrevious
cmdMoveFirst_Click	datAuthors.Recordset.MoveFirst

5.         Save and run your program.

6.         Move to the last record and then click the Move Next button twice.  What happens?  (We will fix this in Exercise 4.)

EXERCISE 3

Using the BOFAction and EOFAction Properties of the Data Control

Note:    EOF (End Of File) is a Boolean property of the recordset object that becomes true when an attempt is made to move forward past the last record in a recordset.

            BOF (Beginning Of File) is a Boolean property of the recordset object that becomes true when an attempt is made to move backward past the first record in a recordset.

STEPS:

1.         Copy the files from Exercise #1 into a new folder and open the VBP file in the new folder.

2. Click once on the data control and make sure that the following properties are set:

            BOFAction = 0 – Move First

      EOFAction = 0 – Move Last

3.         Run the program and notice what happens when you use the arrows to “move previous” when you are on the first record already, or “move next” when you are already on the last record.

End the program, and set the data control properties as follows:

            BOFAction = 1 – BOF

      EOFAction = 1 – EOF

Notice what happens to the arrows on the data control when you try to move past the last or first record.

4.         Now set the EOFAction property to 2 – AddNew.

5.         Run the program; click the >| arrow on the data control to move to the end of the records; then click on the > arrow to move to the next record (which does not exist).

6.         A blank record should appear.  Type some data in all the fields (the author ID will be entered automatically), then move to a previous record.  Move back to the last record to verify that your data is still there.

7.         End the program, then start it again.  Verify that the data you entered is still in the database.

Note that this exercise demonstrated that you can create a simple but functional application that not only allows the user to browse through the rows of a database table and to update rows in that table, but also allows the user to add a new record to that table without writing any code.

EXERCISE 4

Using the EOF and BOF Properties with Navigation Buttons

STEPS:

1.         Copy the files from Exercise #2 into a new folder and open the VBP file in the new folder.

2.         When the user clicks on the MoveNext button, and there is no next record, your code should stay on the same record (the last one).

Put the following code in the cmdMoveNext_Click() event:

            datAuthors.Recordset.MoveNext

      If datAuthors.Recordset.EOF = True Then

                datAuthors.Recordset.MoveLast

      End If

FYI: Instead of Recordset.MoveLast, you could use MoveFirst to let the user loop around to the first record.

3.         Put similar code in the cmdMovePrevious_Click() event.  In this case, you will be checking for Recordset.BOF = True.

4.         Save and run the project and test it thoroughly.

EXERCISE 5

Adding, Updating and Deleting Records

In previous exercises, you saw that with the data control, changes to a record are automatically updated when the user moves off of that record – this is the Update method of the recordset object of the data control at work.  You also saw that, by setting the EOFAction of the data control to "2 – AddNew", the data control will invoke the AddNew method of the recordset object, which causes all the bound controls to be cleared so that the user can enter data.  In addition to being invoked automatically through the data control, the Update and AddNew methods can also be invoked through code.  The recordset object also has a Delete method, which can only be invoked through code – it cannot be accessed automatically through the data control.

This exercise shows you how to invoke the Update, AddNew, and Delete methods of the recordset object through code.

STEPS:

1.         Copy the Exercise #4 files into a new folder and open the VBP file.

2.         Add three more buttons to the form and set their properties as follows:

Name	Caption	Enabled
cmdNewRecord	New Record	True
cmdSaveRecord	Save Record	False
cmdDeleteRecord	Delete Record	True

Your form should resemble the following:

3. When the user clicks on New Record, your program should enable the Save Data button and disable the others.  Put the following code in the cmdNewRecord_Click() event:

            datAuthors.Recordset.AddNew

      cmdSaveRecord.Enabled = True

            cmdMoveFirst.Enabled = False

      cmdMoveLast.Enabled = False

      cmdMovePrevious.Enabled = False

      cmdMoveNext.Enabled = False

      cmdDeleteRecord.Enabled = False

      cmdNewRecord.Enabled = False

4.         Save and run the program to make sure the buttons are enabled and disabled correctly.

5.         When the user clicks on the Save button to save the data that was entered, the Update method should be called and the buttons should be redisplayed.  Place the following code in the cmdSaveRecord_Click() event:

            datAuthors.Recordset.Update

      cmdSaveRecord.Enabled = False

            cmdMoveFirst.Enabled = True

      cmdMoveLast.Enabled = True

      cmdMovePrevious.Enabled = True

      cmdMoveNext.Enabled = True

      cmdDeleteRecord.Enabled = True

      cmdNewRecord.Enabled = True

6. Something to watch out for with the Delete method is that when a record is deleted, the recordset is no longer pointing to a valid record, but the data from the deleted record still remains in the controls.  If the user attempted to update the record at that point, a run-time error would occur.  To prevent this from happening, you should move the user to a valid record immediately following the delete.

Another issue is that if you attempt to delete a record that has a related record in another table, the Jet (Access) database engine will not allow the delete, and a run-time error will occur.  If you don't trap the error, the program will crash.

Finally, it is good programming practice to ask the user to confirm any destructive action.

Place the following code, which addresses the above-mentioned issues,  in the cmdDelete_Click() event:

    On Error GoTo Delete_Error

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

               vbQuestion + vbYesNo + vbDefaultButton2, _

              "Confirm") = vbNo Then

        Exit Sub

    End If

    'delete the current record

    datAuthors.Recordset.Delete

    'move to a valid record

    cmdMoveNext_Click

    Exit Sub

Delete_Error:

    ' This error will occur if you attempt to delete an author that is related to

    ' another table in the biblio.mdb database ...

    MsgBox "This record cannot be deleted. Error code = " _

           & Err.Number & vbCrLf & Err.Description, _

           vbCritical, "Cannot Delete"

7.         Save and test your program to make sure all functions work.

EXERCISE 6

Using the Validate Event

This exercise will add the following functionality to the form you created in Exercise 5:

· Validating the user's input when they update an existing record or add a new record to the database.

· An Undo feature which will enable the user to cancel changes that they make to a record.

This exercise introduces the Validate event of the data control, as well as the CancelUpdate and UpdateControls methods of the data control, the Bookmark and LastModified properties of the recordset object, and the DataChanged property of bound controls.

The Validate event of the data control occurs prior to a record Move and prior to an Update, Delete, Unload, Close, or setting of a Bookmark.  This means that any code contained in the Validate event procedure will be executed prior to the execution of a statement containing one of these methods.  For example, if somewhere in your program you code the statement datAuthors.Recordset.Update, when VB encounters this statement, any code in the Validate event will be executed first.

The Validate event takes in two VB-provided arguments: Action and Save. (For example, the Validate event procedure header for this exercise will look like this:

Private Sub datAuthors_Validate(Action As Integer, Save As Integer). 

The Action argument tells you which particular action (MoveFirst, Update, etc.) caused the Validate event to fire.  The value of Action can be tested by comparing it to a literal integer value (1, 2, 3, etc.) or with its corresponding VB predefined constant (vbDataActionMoveFirst, vbDataActionUpdate, etc.).  In addition, whatever action triggered the Validate event can be cancelled if you set the value of Action to zero (or to the predefined constant vbDataActionCancel) prior to exiting the Validate event procedure.  The values of Action and their corresponding predefined constants are as follows:

Constant	Value	Description
vbDataActionCancel	0	Cancel the operation when the Sub exits
vbDataActionMoveFirst	1	MoveFirst method
vbDataActionMovePrevious	2	MovePrevious method
vbDataActionMoveNext	3	MoveNext method
vbDataActionMoveLast	4	MoveLast method
vbDataActionAddNew	5	AddNew method
vbDataActionUpdate	6	Update operation (not UpdateRecord)
vbDataActionDelete	7	Delete method
vbDataActionFind	8	Find method
vbDataActionBookmark	9	The Bookmark property has been set
vbDataActionClose	10	The Close method
vbDataActionUnload	11	The form is being unloaded

The Save argument is a Boolean value indicating whether or not bound data has changed. You can set Save to False to prevent VB from saving changes to a record.

DataChanged is a run-time only Boolean property available with data-bound controls (such as the textboxes you have been using), typically used in the Validate event.  You would typically use DataChanged as a first test to see if a particular field needs further validation (if the user did not change the data in a field, why bother doing further validation on that field?)  The logic structure would look something like the following:

      If txtMyField.DataChanged Then

          If (something is wrong with txtMyField) Then

                  MsgBox "Invalid data in this field"

          End If

      End If

The CancelUpdate method is used to cancel any pending updates resulting from an Edit or AddNew operation. For example, if a user invokes the Edit or AddNew method and hasn't yet invoked the Update method, CancelUpdate cancels any changes made after Edit or AddNew was invoked.

The UpdateControls  method gets the current record from a Data control's Recordset object and displays the appropriate data in controls bound to a Data control.  This method is typically used to restore the contents of bound controls to their original values, as when a user makes changes to data and then decides to cancel the changes.  This method does not cause the Validate event to fire.  Similarly, the UpdateRecord method (not used in this exercise) saves the current contents of bound controls to the database during the Validate event without triggering the Validate event again.

Bookmark is a property of the Recordset object that contains a binary string identifying the current record. If you assign the Bookmark value to a variable and then move to another record, you can make the earlier record current again by setting the Bookmark property to that string variable.

LastModified is a property of the Recordset object that returns a bookmark indicating the most recently added or changed record.

STEPS:

1. Copy your files from Exercise 5 into a new folder.

2. Place two new command buttons on your form and set their properties as follows:

Name	Caption	Enabled
cmdUndo	Undo	True
cmdCancelNew	Cancel New Record	True

            At this point, your form should resemble the following:

3. Place the following statements in the general declarations section of your form:

Private blnAddingNewRecord As Boolean

Private blnValidationError As Boolean

4. Code the event procedure for the new cmdUndo button, which consists of only one statement:

datAuthors.UpdateControls

5.         Modify the cmdNewRecord_Click() event procedure to look like the following (new statements are shown in bold):

            datAuthors.Recordset.AddNew

If blnValidationError Then Exit Sub

      cmdSaveRecord.Enabled = True

cmdCancelNew.Enabled = True

            cmdMoveFirst.Enabled = False

      cmdMoveLast.Enabled = False

      cmdMovePrevious.Enabled = False

      cmdMoveNext.Enabled = False

      cmdDeleteRecord.Enabled = False

cmdNewRecord.Enabled = False

cmdUndo.Enabled = False

blnAddingNewRecord = True

Explanation

When the user initiates the process to add a record, the code invokes the AddNew method, which will cause the Validate event to fire, which will set the blnValidationError flag.  This will catch the case where the user has modified the current record (and has made errors) and then clicks the "New Record" button.  In that case the error is flagged, the AddNew is canceled, and the user must correct the problem with the current record.

If everything's OK, we enable the "Cancel New" button so that they can cancel the process, and disable the "Undo" button, because that is only applicable when the user is changing, not adding a record.  We also set the blnAddingNewRecord flag to true, which will be tested in the Validate event (shown in a few steps below).

6.         Modify the cmdSaveRecord_Click() event procedure to look like the following (new statements are shown in bold):

            datAuthors.Recordset.Update

            If blnValidationError Then Exit Sub

            ' make the new record the current record

            datAuthors.Recordset.Bookmark _

                    = datAuthors.Recordset.LastModified

      cmdSaveRecord.Enabled = False

      cmdCancelNew.Enabled = False

            cmdMoveFirst.Enabled = True

      cmdMoveLast.Enabled = True

      cmdMovePrevious.Enabled = True

      cmdMoveNext.Enabled = True

      cmdDeleteRecord.Enabled = True

      cmdNewRecord.Enabled = True

cmdUndo.Enabled = True

blnAddingNewRecord = False

Explanation

When the user initiates a save for a newly added record, the Update method is invoked (with the statement datAuthors.Recordset.Update).  This will cause the Validate event to fire, where we will set the blnValidationError flag.  When the Validate event terminates, control resumes with the If statement, where that flag is tested.  If there is an error, we want to exit the sub early, which will let the user continue working on the current record to correct the problem.  Otherwise, if the flag is False, that means that all validation checks passed and we can continue on our merry way.

The statement

datAuthors.Recordset.Bookmark = datAuthors.Recordset.LastModified

            causes the newly added record to become the current record.  This is necessary if you want to see the data for the new record on the form after you add it, because the AddNew method and subsequent Update method does not cause the newly added record to become the "current" record.  Therefore, without this statement, the record that was current before you invoked the "New Record" operation would be displayed, and you would have to go to the last record to see the newly added record.

            Since this event completes the operation of adding a new record, the cmdCancelNew button is disabled, the cmdUndo button is enabled and the blnAddingNewRecord flag is set to False.

7.         Code the Validate event procedure for the datAuthors data control. Recall that this event fires prior to a record Move and prior to an Update, Delete, Unload, Close, or setting of a Bookmark.  Since Validate is the default procedure for a data control, you can get the procedure header by double-clicking on the data control.  The code is shown below:

Private Sub datAuthors_Validate(Action As Integer, Save As Integer)

    If Action = vbDataActionBookmark Then Exit Sub

    If Action = vbDataActionDelete Then Exit Sub

    'check to see if a valid author id is entered:

    If txtAuthor.DataChanged Or blnAddingNewRecord Then

        If Trim$(txtAuthor) = "" Then

            MsgBox "Author name must be entered"

            txtAuthor.SetFocus

            GoTo CancelValidateAction

        End If

    End If

    'check to see if a valid year is entered

    If txtYearBorn.DataChanged Or blnAddingNewRecord Then

        If Val(txtYearBorn) = 0 Then

            MsgBox "Invalid year"

            txtYearBorn.SetFocus

            GoTo CancelValidateAction

        End If

    End If

    blnValidationError = False

    Exit Sub

CancelValidateAction:

    blnValidationError = True

    Action = vbDataActionCancel

    Save = False

End Sub

8.         Code the Click event procedure for the cmdCancelNew command button. If the user wants to cancel the adding of a new record, the CancelUpdate method should be used; then the UpdateControls method is used to restore the controls on the form to the values of the current record (the record that was displayed on the form prior to the initiation of the AddNew operation).  The code is shown below:

Private Sub cmdCancelNew_Click()

    'cancel the AddNew

    datAuthors.Recordset.CancelUpdate

    datAuthors.UpdateControls

    'enable & disable the appropriate buttons

    cmdSaveRecord.Enabled = False

    cmdCancelNew.Enabled = False

    cmdMoveFirst.Enabled = True

    cmdMoveLast.Enabled = True

    cmdMovePrevious.Enabled = True

    cmdMoveNext.Enabled = True

    cmdDeleteRecord.Enabled = True

    cmdNewRecord.Enabled = True

    cmdUndo.Enabled = True

    blnAddingNewRecord = False

End Sub

9.         Save, run and test the program.  Make sure you understand what the code is doing.

EXERCISE 7

Using the Find Methods

The Recordset object has methods FindFirst, FindLast, FindNext, and FindPrevious.  You can use these to search for a particular record in the Recordset.

The syntax is

datControl.Recordset.FindFirst criteria

where criteria is a string item consisting of a field name, a relational (comparison) operator, and a value.  It is essentially the same as a SQL WHERE clause without the word WHERE. The comparison operators that can be used are =, >, <, >=, <=, <>, Like, Between, and In.  The value on the right-hand side of the comparison operator must conform to the following rules:

            string values must be enclosed in single quotes

            numeric values are not enclosed in quotes

            date values must be enclosed in #'s (pound signs)

If the criteria is expressed in a literal string, that string must be enclosed in double quotes.  Typically, you must use VB's string-handling functions (especially the "&" for concatenation) to get the desired results.

Examples:

datBooks.Recordset.FindFirst "ISBN = '123-456-789-0' "

      datMembers.Recordset.FindNext "Amount > 100"

      datMembers.Recordset.FindNext "DateOfBirth < #1/1/1950#"

      datBooks.Recordset.FindNext "Amount > " & txtAmount.Text

      datMembers.Recordset.FindNext "FirstName = '" & txtName.Text & "'"

The next example assumes that the variable dtmBirthDay is of the Date data type:

      datMembers.Recordset.FindNext  _

"DateOfBirth < #" & Format$(dtmBirthDay, "mm/dd/yyyy") & "#"

In this exercise, the user selects both the field name and the relational operator from combo boxes, then enters the search value in a textbox.  The criteria for the Find methods are thus formed by statements similar to the following:

If cboField.Text = "Author" Then

        strQuote = "'"

Else

        strQuote = ""

        txtCriteria.Text = Val(txtCriteria.Text)

End If

      strCriteria = _

          cboField.Text & " " & cboRelOp.Text & _

          " " & strQuote & txtCriteria.Text & strQuote

datAuthors.Recordset.FindFirst strCriteria

Additional Notes:

· If the name of the field in the database table has spaces in its name, you must put square brackets around the field name, as in the following example:

      datBooks.Recordset.FindFirst "[Pay Rate] > 30000"

· For string values, if there is the possibility that the search string will contain an apostrophe, an extra measure should be taken to "double" the apostrophes in the string – otherwise, the apostrophe embedded in the string will be interpreted as the end of the string and a syntax error will most likely result. The easiest way to provide this "insurance" against embedded apostrophes is to use the Replace$ function on the string in question to replace any occurrences of a single apostrophe with two apostrophes:

      datProducts.Recordset.FindFirst _

                  "ProductName = '" & Replace$(strSearchText, "'", "''") & "'"

For example, if strSearchText contained "Chef Anton's Cajun Gumbo", the criteria in the above statement would evaluate to

ProductName = 'Chef Anton''s Cajun Gumbo'

and the double apostrophe in "Anton''s" would be correctly interpreted by the SQL parser as a single apostrophe.

In this particular example, if the Replace function was NOT used (i.e., you simply coded

   "ProductName = '" & strSearchText & "'"

for the criteria, the result would be

ProductName = 'Chef Anton's Cajun Gumbo'

which would result in an error: the SQL parser would interpret the criteria to be "Chef Anton" with extraneous characters ("s Cajun Gumbo") at the end.

The Recordset has a NoMatch property.  It is set to False to begin with.  If you use a Find method and a record is not found, then the NoMatch property is set to True.  You should use this property to determine whether or not a record was found. If a match is found, NoMatch will be set to True, and the found record becomes the current record.

STEPS:

1. Copy the files from Exercise 6 into a new folder.  Follow steps 2 through 4 to get your form to resemble the following:

2.         Add four command buttons to the form and set their properties as follows:

Name	Caption	Enabled
cmdFind	F&ind	True
cmdFindAgain	Find &Again	False
cmdCancelFind	Cance&l Find	False
cmdGo	&Go!	False

3.         Add two combo boxes and set their properties as follows (Note: you can set the List property of a listbox or combo box in the Properties box by typing in values and pressing Ctrl+Enter - this action is analogous to using the AddItem method in code).

Name	Style	Enabled	List
cboField	2 - Dropdown List	False	Au_Id Author [Year Born]
cboRelOp	2 - Dropdown List	False	= > < >= <= <> Like

4.         Add a textbox called txtCriteria and set its Enabled property to False.

5.         Place the following statements in the Form_Load event:

cboField.Text = cboField.List(0)

cboRelOp.Text = cboRelOp.List(0)

6.         Code the Click events for the combo boxes (to provide smoother navigation for the user).  The Enabled tests are necessary because the Form_Load event assigns the first element of the their respective List property arrays to their respective text properties, which automatically invokes the Click event, which will normally perform a SetFocus.  SetFocus cannot be invoked on a disabled control; nor can it be invoked prior to completion of a Form Load.

Private Sub cboField_Click()

    If cboField.Enabled Then

        cboRelOp.SetFocus

    End If

End Sub

Private Sub cboRelOp_Click()

    If cboRelOp.Enabled Then

        txtCriteria.SetFocus

    End If

End Sub

7.         To ensure that strBookmark always references the current record, place this statement

strBookmark = datAuthors.Recordset.Bookmark

            at the end of each of these event procedures:

            cmdMoveFirst_Click

            cmdMoveLast_Click

            cmdMoveNext_Click

            cmdMovePrevious_Click

This is necessary to ensure that we have a pointer to a valid record available in case the Find method fails, which leaves the current record pointer undefined.

8.         Place the following code in the cmdFind_Click event (disables navigating and updating controls and enables "Find" controls):

blnFirstFind = True

txtAuthor.Enabled = False

txtYearBorn.Enabled = False

cmdMoveFirst.Enabled = False

cmdMoveLast.Enabled = False

cmdMovePrevious.Enabled = False

cmdMoveNext.Enabled = False

cmdDeleteRecord.Enabled = False

cmdNewRecord.Enabled = False

cmdUndo.Enabled = False

cmdSaveRecord.Enabled = False

cmdCancelNew.Enabled = False

cmdFind.Enabled = False

cmdCancelFind.Enabled = True

cmdGo.Enabled = True

cboField.Enabled = True

cboRelOp.Enabled = True

txtCriteria.Enabled = True

9.         Place the following code in the cmdGo_Click event (prepares criteria argument for the Find methods, then performs the Find):

    Dim strQuote    As String

    Dim strCriteria As String

    If cboField.Text = "Author" Then

        strQuote = "'"

    Else

        strQuote = ""

        txtCriteria = Val(txtCriteria)

    End If

    strCriteria = _

        cboField.Text & " " & cboRelOp.Text & _

        " " & strQuote & txtCriteria & strQuote

    With datAuthors.Recordset

        If blnFirstFind Then

            blnFirstFind = False

            .FindFirst strCriteria

        Else

            .FindNext strCriteria

        End If

        If .NoMatch Then

            MsgBox "Data not found"

            .Bookmark = strBookmark

            cmdFind_Click

            cmdFindAgain.Enabled = False

        Else

            cmdFindAgain.Enabled = True

            cboField.Enabled = False

            cboRelOp.Enabled = False

            txtCriteria.Enabled = False

            cmdGo.Enabled = False

        End If

    End With

10.        In the cmdFindAgain_Click event, place the following line (performs the same actions as the cmdGo_Click event):

            cmdGo_Click

11.        Place the following code in the cmdCancelFind_Click() event (enables the navigation and updating controls, disables the Find controls):

    txtAuthor.Enabled = True

    txtYearBorn.Enabled = True

    cmdMoveFirst.Enabled = True

    cmdMoveLast.Enabled = True

    cmdMovePrevious.Enabled = True

    cmdMoveNext.Enabled = True

    cmdDeleteRecord.Enabled = True

    cmdNewRecord.Enabled = True

    cmdUndo.Enabled = True

    cmdSaveRecord.Enabled = True

    cmdCancelNew.Enabled = True

    cmdFind.Enabled = True

    cmdCancelFind.Enabled = False

    cmdGo.Enabled = False

    cboField.Enabled = False

    cboRelOp.Enabled = False

    txtCriteria.Enabled = False

12.        Run the program and test the Find operations using different fields, relational operators, and search strings.

EXERCISE 8

Using the MSFlexGrid Control

The MSFlexGrid can be used to display multiple records in a grid.  It cannot be used to edit data or add and delete records.  Follow the steps in this exercise to create a Categories and Products master/detail form similar to the screen-shot shown below:

STEPS:

1.         Place two data controls on the form and set their properties as follows: 

Name	DatabaseName	RecordSource	Visible
datCategories	..\NWIND.MDB	Categories	True
datProducts	..\NWIND.MDB	(leave blank)	False

            Set the Caption property of datCategories to "Use Arrow Buttons to Navigate Records".

2.         Place three textboxes and one OLE control on the form.  Set the DataSource property for each of these four controls to datCategories.

Set the DataField property for these controls to CategoryID, CategoryName, Description, and Picture respectively. 

Set the Enabled property of the Category ID textbox to False.  For the Description text box, set its MultiLine property to True and set its ScrollBars property to 2 – Vertical.

Add appropriate label controls to accompany the textboxes, and group these four controls into a frame, as shown in the screen-shot.

3.         If necessary, add the MSFlexGrid to your toolbox from the Components item of the Project menu.  Place an MSFlexGrid control on your form, and set its properties as follows:

Property	Value
AllowUserResizing	1 – flexResizeColumns
DataSource	datProducts
FixedCols	0

            Enclose the grid in a frame, as shown in the screen-shot.

4. Place the following line of code in the Form_Load event:

datCategories.Refresh

            This will force the controls bound to datCategories to be populated sooner than they would otherwise, and will also trigger the Reposition event of the datCategories control.  (The Reposition event of a data control occurs whenever a Move method is executed against that control.)

5. Place the following code in the datCategories_Reposition event:

          datProducts.RecordSource _

                      = "SELECT * FROM Products " _

                & "WHERE CategoryID = " & txtCatID

          datProducts.Refresh

6.         Save and run the program.

EXERCISE 9

Using the DBGrid Control

This exercise uses the DBGrid control, which goes a step further than the MSFlexGrid in that allows the user to add, update, and delete records.

The DBGrid control does not install automatically when you install Visual Basic 6.0, but it is available on the VB 6/VS 6 CD. To install the DBGrid control, perform the following steps.

· Locate these three files on your VB6 installation CD: DBGRID32.OCX, DBGRID32.DEP, and DBGRID.REG. All three files should be located in this folder: D:\COMMON\TOOLS\VB\CONTROLS (where "D:" is the drive letter of your CD-ROM drive). If you cannot locate your installation CD, you may download these three files here.

· Copy these three files to your Windows "System" directory (the default system directory varies depending on the OS being used: On Windows 2000 and NT the default is WINNT\SYSTEM32; on XP the default is WINDOWS\SYSTEM32, and on 95, 98, and ME it is WINDOWS\SYSTEM).

· Double-click on DBGRID.REG to register the DBGRID32.OCX. You should now see the control (under the name "Microsoft Data Bound Grid Control 5.0 (SP3)") when you access the Components dialog by selecting Project|Components within VB. (If not, click on the “Browse…” button in the Components dialog and locate the DBGRID32.OCX file by browsing to the folder in which you copied it previously.)

Once you have installed the DBGrid control, proceed with the steps of Exercise 9:

STEPS:

1. Copy your files from Exercise 8 to a new folder and open the project.

2. Delete the MSFlexGrid control.

3. Out of  Project | Components, include the Microsoft Data Bound Grid Control.

4. Place a DBGrid on your form in the position previously occupied by the flex grid.

5. Set the DBGrid's properties as follows:

Property	Value
AllowAddNew	True
AllowDelete	True
AllowUpdate	True
DataSource	datProducts

6. The code can remain as is.  Save and run the program; experiment by using the grid to add, update, and delete records.  While this application is certainly not bullet-proof, you'll find that built-in error-checking will prevent most "illegal" actions attempted by the user (such as trying to change the primary key, changing a foreign key to a non-existent value, or deleting a record with a related record in another table).

Download the project files for these exercises here.