Microsoft KB Archive/271728

Knowledge Base

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

Article ID: 271728

Article Last Modified on 6/23/2005

---

APPLIES TO

• Microsoft Access 2000 Standard Edition

---

This article was previously published under Q271728

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

You can use a drop-down list on a data access page to find a matching record for a particular field based on the selection that is made in the list. By using a text box and script similar to what is in the "More Information" section of this article, you can modify the page so that you can search on any field on the page. This article shows you how to implement this generic search functionality.

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)

REFERENCES

For additional information about finding a record on a page using a dropdown list, click the article number below to view the article in the Microsoft Knowledge Base:

247823 ACC2000: How to Find a Record from a Drop-down List in a Data Access Page

Additional query words: search

Keywords: kbdap kbdapscript KB271728

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.