Microsoft KB Archive/271728

= 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 &quot;More Information&quot; 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.  Open the Northwind sample database or the NorthwindCS sample project. Click the Customers table, and then click Page on the Insert menu. In the New Data Access Page dialog box, click Autopage: Columnar, and then click OK. Save the data access page as SearchCustomers.htm.  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  On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li> On the View menu, point to Other Windows, and then click Script Outline.</li> Expand Client Objects & Events.</li> Expand the cmdFind object, and then double-click the onclick event.</li>  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 & &quot; = '&quot; & txtSearch.value & &quot;'&quot;, 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 </li> Save and then close the page.</li></ol>

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)

<div class="references_section">