The built-in functionality of the Data control, which is impressive indeed, can be accessed through an application’s code with the Data-control’s methods. The simplest methods are the navigation methods, which correspond to the actions of the four buttons on the control, and they are as follows:
- MoveFirst repositions the control to the first record.
- MoveLast repositions the control to the last record.
- MovePrevious repositions the control to the previous record.
- MoveNext repositions the control to the next record.
You can use these methods to implement navigation buttons. However, you must take care 01 three special cases: (1) what happens when the Data control is positioned at the first or last record (2) what happens when the Previous button is clicked while the Data control is on the first record; and (3) what happens when the Next button is clicked while the Data control is on the last record.
VB6 at Work: The Data2 Project
The Data1 application is similar to the Data1 application, but this time we’ll use a different database. The BIBLIO database, which also comes with VisuaI Basic, is a database of book titles, publishers, and authors. We will look at the structure of this database later in the chapter, but in this application we are going to build a front end for navigating through the Titles table, which contains Title, ISBN, Description, Subject, and Comments fields, among others. The Form of the Dafa2 application is shown in Figure 17.5.
Designing the Form is straightforward. Follow these steps:
- Place a Data control and connect it to the BIBLIO database by setting its DatabaseName property to the path of the database on your system and setting its RecordSource property to the Titles table.
- Place the data-bound controls on the Form and bind them to the Data control by setting their DataSource property to Data1 and their Datafield properties to the fields they display.
The code behind the First and Last buttons is simple. It calls the corresponding navigation method of the control.
The program moves to the next record, and then it examines the EOF property. If BOP is True, we have landed on a record after the last one. The program displays a message to let the user know that the last record has been reached and then moves to the last record.
The code behind the Previous button is quite similar. It calls the MovePrevious method, and instead of the EOP property, it examines the BOP property of the Data control.
The Find Methods
In addition to the navigation methods, the Data control provides four methods for finding records in the Recordset. One of the basic operations you perform on databases is locating specific records. The following methods locate records:
- FindFirst finds the first record that meets the specified criteria.
- FindLast finds the last record that meets the specifed criteria. .
- FindNext finds the next record that meets the specified criteria.
- FindPrevious finds the previous record that meets the specified, criteria.
These methods can locate any record in the RecordSet, based on user-specified criteria. The syntax of all these methods is the same and is as follows:
RecordSet.FindFirst criteria:
The criteria argument is a string expression specifying a relation between field values and constants. The following Statement.
Data1. RecordSet. FindFirst ‘State = ‘ NY’ ‘
locates the first- record in the Recordset in which the state is NY. Notice that literals within the criteria string are delimited with single quotes” The user-specified criteria can be more complicated and can combine values of multiple fields with. logical and relational operators. For example, the following statement:
locates the first Invoice issued in 1997 With a total of $1,000 or more.
In addition to the usual relational operators, you can use the like operators, which allows you to locate records that match a pattern For example, to locate any book in the BIBLIO database with SQL in its title, use the following Find method:
RecordSet,FindFirst ‘Title LIKE ‘*SQL*”
The string ‘SQL’ is a pattern that allows any number of characters to appear before and after SQL. In other words, it matches titles such as SQL: An Introduction, Mastering SQL, or The SQL Handbook
When a Find method is called, Visual Basic locates a record that matches the criteria and repositions the Data control to this record. In your code, however, you must first examine the value of the NoMatch property, which is set to False if a record is found, and True otherwise. The following code segment shows how the Find methods are used:
The FindDemo application demonstrates how to use the Find methods a RecordSet. You can actual include this little utility in a larger application to implement a·general Find features The application is shown in Figure 17.6. The first ComboBox control you select a table, and the second ComboBox control lets you select a field; You can then type a search argument. for the operation in the search Value textbox and click the buttons to locate the records that meet the specified criteria. (The code that extracts the table names from the NWIND database and the field names of the selected table.is discussed in the section ”Mapping . Databases,” later in this chapter.)
As you can see in Figure 17.6, it is possible to specify additional fields in the search argument beyond the one selected in the Combobox. Any strings in the Search Value field must be enclosed in single quotes. Alternatively, you can write a small procedure that’replaces any double quotes found in the string with Single quotes,
If you run the FindDemo application, you’ll see column that are different from the ones shown in Figure 17.6. If you place the pointer on the dividing line between two columns in the data bound Grid control it turns into a double arrow, indicating that you can resize the column. To prepare Figure 17.6, I eliminated some column with meaningless data by setting their width to 0 and resized other column to display the data of interest (contact names and cities).
The Form of the FindDemo application contains a data-bound Grid control and a hidden Data control. The data-bound Grid control is discussed later in this chapter. For purposes of this example, all you need to know is that this control can be bound to a Data control and can display the Data control’s RecordSet in a tabular arrangement. The code that populates the two ComboBox controls accesses the values of the fields through code, and this will be explained in the section 1/Accessing Fields in RecordSets later in this chapter.
:The GenerateSQL() Function The GenerateSQL() function generates the search argument for all Find operations. The value returned by the GenerateSQL() function is used with the Find methods. After calling the FindFirst method, the program examines the NoMatch property. If it’s true, the program displays a message. If a record matching the criteria is found, the Data control is automatically repositioned at this record.
The code of the GenerateSQL() function extracts, the text from the txtsearch Value textbox, appends it to the selected field, and returns a string that is used as an argument with the Find methods. Most of the application’s code handles potential errors. All the work is done by the GenerateSQL() funvtion,which Prepares the search criteria, and it’s shown next.
