ACC2000: How to Find a Record in Any Field on a Data Access Page (271728)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

1. Open the Northwind sample database or the NorthwindCS sample project.
2. Click the Customers table, and then click Page on the Insert menu.
3. In the New Data Access Page dialog box, click Autopage: Columnar, and then click OK.
4. Save the data access page as SearchCustomers.htm.
5. In Design view, add the following control to SearchCustomers.htm, and then set the properties of the control as follows:

     Text Box 
     -------------
     Id: txtSearch
   
     Label
     -----------------------------------------------------------
     NOTE: The Id should match the Id of the label associated with the 
     text box added above.
   
     Id: Text0_Label 
     InnerText: Search For...
   
     Command Button 
     --------------
     Id: cmdFind
     Value: Find
   					

6. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
7. On the View menu, point to Other Windows, and then click Script Outline.
8. Expand Client Objects & Events.
9. Expand the cmdFind object, and then double-click the onclick event.
10. Type the following code between the SCRIPT tags:

    '-----------------------------------------------------------------------
    'This routine searches all fields in the defaultrecordset for something
    'entered by a user in a Search text box. It passes through each field
    'the recordset until it finds a match.
    '-----------------------------------------------------------------------
    dim i           'Counter variable
    dim rs          'ADO recordset object
    dim fld         'ADO field object
    dim FieldCount  'Number of fields in the recordset
    	
    FieldCount = MSODSC.DefaultRecordset.Fields.Count
    
    'This will return the default recordset on the page
    'in this case, the Customers table.
    set rs = MSODSC.DefaultRecordset
    	
    for i = 0 to FieldCount - 1
        'get a field object
        set fld = rs.Fields(i)
    
        '0 = Skip no records
        '1 = Search forward
        '1 = Start with the first record
        rs.Find fld.name & " = '" & txtSearch.value & "'", 0, 1, 1
    
        'Check for EOF.  If  at EOF but have not exhausted
        'all the fields, then reset to the first position in the 
        'recordset.  Otherwise, if a match was found, exit the loop.
        if rs.EOF then 
            rs.MoveFirst
        else
            exit for
        end if
    next
    
    'Clean up.
    set fld = nothing
    set rs = nothing
    					

11. Save and then close the page.

To test this page, view the SearchCustomers.htm page in Microsoft Internet Explorer 5 or later. Enter the following values in the search text box, and then click Find after each entry:

• Mexico (located in the Country field)
• Owner (located in the ContactTitle field)
• Hanna Moos (located in the ContactName field)