Finding Matching Records in Data Access Pages

In a scenario such as the above, it is possible to populate a combo-box control with all the available customer identifiers and pick the one for the customer in question to get the information. Sometimes for security reasons this solution may not be acceptable, in which case the customer information representative would just type the information provided by the customer into an empty textbox, and the event raised by this action is used to find the information.

In Data Access Pages, the data is retrieved in the form of tables and queries constituting the "Field List" of the Data Access Page. The "Field List" objects can be dragged and dropped onto the design pane of the DAP. The data is in the form of a record set which can be set to represent the DefaultRecordset for the MSODSC control that controls the data on the DAP. Also the various objects on the DAP are part of the client objects, with rich properties and events. It is possible to program these using the JavaScript (Jscript's) DOM model, or using the ECMA Script.

This tutorial looks at the implementation of finding records in a table using both the Combo-box and the text-box control as the source of the text used for matching. This tutorial was prompted in part by the questions posed to some of the article blogs at this site.

Creating a Data Access Page using the Wizard

It will be useful for the reader to review the following articles in this site so that the present article will be little easier to understand:

• Data Access Page Based on a Parametric Query
• Scripting a Data Access Page: a Simple Example
• Using Microsoft Script Editor for Data Access Pages
• Creating Data Access Pages from Scratch
• Data Access Pages: What they are and how to create one 
• Manipulating ADO Recordsets 

For purposes of illustration a new blank data base project is created into which the Employees table from an existing Northwind database will be imported. You will find the Northwind database in the Office folder of your computer. In the first example, the DAP will use a textbox control in addition to the various field objects. This textbox is used to find the keyword typed into this box; once the keyword is found, all the column elements for that row will be returned and displayed on the DAP. In the second case combo-boxes reveal existing entries for a keyword;  when a particular keyword is selected, the entire row will be retrieved.

Using a textbox to find matching records

Create a TestEmployees.htm DAP

The design view of the Employees table is shown in the following picture.

The DAP is created using the "Create data access page by using wizard" shortcut in the DapCntrls database which has just one table called "Employees." The fields listed below were chosen from among the various fields (Columns) present. There was no particular reason for this choice.

• EmployeeID
• LastName
• FirstName
• BirthDate
• Country

Design View of TestEmployees.htm

This created page, "TestEmployees.htm," is shown in design view together with its "FieldList" as shown in the next picture.

Now add a textbox control from the "Tools" to the "Section: HeaderEmployees" just below the Employee information as shown in the next picture. The picture also shows the textbox's ID being changed to "IdEmp." The textbox's label was changed to "Search by EmployeeID." Now when it is properly coded, when you view this page, type in a number and step out, the Employee information displayed should reflect the EmployeeID you typed into this box.

The code page and the code to search the EmployeeID

Now open up the Microsoft Script Editor as shown with all elements showing at the top of this picture. The "Document Outline" window shows all the objects on this DAP relating to the data-related objects as well as any control element you placed on the page (in this case, a textbox was placed).

Now scroll down until you come to the textbox whose ID is IdEmp as shown in the next picture.

Since this is a textbox, the onchange event is a natural event that is invoked when you type something into the textbox and step out of it. Double click the onchange event in the "Document Outline." This opens up a region in the Microsoft Script Editor and places a vbscript-related tag for that particular element as shown.

Now what is required is to code in such a way that the EmployeeID field in the recordset will be searched until the text typed in the box whose id is "IdEmp" is found. The following code does just that. The script is vbscript and it is written for the "onchange" event of the object whose Id is "IdEmp." The language syntax is Microsoft's, which can be recognized by the "document.all.item()" method and is IE specific. As mentioned previously, the data in the DAP is passed to the MSODSC's default recordset. This is all that is needed to find all the information for this EmployeeID.

Testing this code

Open the page in preview mode, which will require you to save it first. This page is already saved as "TestEmployees.htm." When this is opened the employee information for the employee whose IdEmp =1 will be showing, and the textbox will be empty. Type a number (there are only nine employees in this table) and step out of the box and you will see the record for the employee displayed as shown. The picture as shown is for the number 6 typed into the textbox.

Using Combo boxes to find records

Since the previous example has been described in considerable detail, only some relevant screen shots will be shown and the coding will be similar to the previous example except that instead of a textbox there will be two combo-boxes whose properties will be set in such a way that they get populated by the EmployeeID in one, and by FirstName in the other. Again the code will be written to those elements by following up their ids in the "Document Outline."

The design view of Employees.htm

The next picture shows the design view of the Employee.htm which has a different recordset. Two combo-boxes have been added to this page, the same way the textbox was added in the previous example. Their labels have been changed to reflect what is achieved by picking something from the combo-box. The picture also shows the setting for one of the combo-boxes (EmployeeID). The drop-down list has an ID property. The combo-box gets tied to the EmployeeID field and since its source is Recordset:Employees, when the page is shown in preview, the combo-box gets populated by the EmployeeID. In a similar way, the FirstName combo-box gets populated by the Employee First Names.

The code for finding the specified FirstName

The FirstName combo box has been given an id, Ename. The next picture shows the vbscript code for the onchange event of this object. When you write the code make sure the id matches correctly as shown. Again the routine for finding the Ename is the same except that the combo-box's onchange event is used and the code is written for this event. The Find() method can take three other optional arguments.

Testing this code

Open the page using the View menu option. By choosing a FirstName or an EmployeeID name, you can bring up the record corresponding to that FirstName or the EmployeeID as shown for the Employee whose FirstName is Steven. It may be noticed that the navigation control is hidden, which you can do in the design. However the navigation will be effective if you want to use because it is bound to the recordset.

Summary

It is quite easy to find records bound to Data Access Pages using the Recordset's Find() method as shown in this article. If you need to hide data from prying eyes you may just have an empty textbox, but now you need to enter the correct information. No validation has been set for the textbox, which you must include to prevent runtime error in your code. Both examples could be implemented in the same DAP. It was shown separately in this tutorial for reasons of clarity.