Home

Locating Records with the ADO Find Method

This sample project demonstrates using the ADO Find method to locate records in a Jet database.

Download a copy of the project.

Making the ADO Find Method Project

You should note the following limitation of the Find method, text copied from the Microsoft Knowledge Base article Q195222:

"When you attempt to use the ActiveX Data Objects (ADO) Find method to search by multiple criteria, or in other words you use AND, the following error occurs:

Error: 3001:
This application is using arguments that are of the wrong type, out of acceptable range or in conflict with one another.
Cause

The Find method is a single column operation only because the OLE DB specification defines IRowsetFind this way."


Using ' = ' As a Comparison Operator

  1. Create a DSN referring to the Northwind database. For information on creating DSNs, see these two MSDN Help topics:
  2. Open a new project in Visual Basic 6.0, and add a reference to the Microsoft ActiveX Data Objects 2.1 Library .
  3. Add a command button and text box to the form. Set properties on these controls as listed in the following table.

    Object Property Value
    Command button Name cmdFind
    Command button Caption Find record
    Text box Name txtCriteria
    Text box Text Enter Customer ID

    When you're done, the form might look like this.

    screen shot

  4. Copy the following code into the form.
    Option Explicit
    Private Sub cmdFind_Click()
        Dim adoCon As ADODB.Connection
        Dim adoRS As ADODB.Recordset
        Dim strCriteria As String
        Dim lngCurRecord As Long

        'Trap the user's search entry.
        strCriteria = "CustomerID = " & "'" & txtCriteria.Text & "'"

        'Open the Northwind Customers table.
        Set adoCon = New ADODB.Connection
        adoCon.Open "Northwind"
        Set adoRS = New ADODB.Recordset
        adoRS.Open "customers", adoCon, adOpenForwardOnly, , adCmdTable

        'Find the first record matching the criteria.
        adoRS.Find strCriteria, 1, adSearchForward

        'Provide a response to the user.
        If adoRS.EOF Then
            MsgBox "Search failed."
        Else
            'Display the customer's name, phone and fax number.
            MsgBox adoRS!ContactName & vbCrLf & adoRS!Phone & vbTab & adoRS!Fax
        End If

        'Cleanup.
        adoRs.Close
        adoCon.Close
        Set adoCon = Nothing
        Set adoRS = Nothing
    End Sub

  5. Save the project, then run it. Enter a valid customer ID, like 'bonap' or 'tortu' in the text box, then click the command button.

Using ' like ' As a Comparison Operator

You can also use like as a comparison operator. Use an asterisk in the criteria string to find one or more occurrences of a value. For example, "state like 'M*'" matches Maine and Massachusetts. You can also use leading and trailing asterisks to find a substring. For example, "state like '*as*'" matches Alaska, Arkansas, and Massachusetts.

Asterisks can be used only at the end of a criteria string, or together at both the beginning and end of a criteria string, as shown above. You cannot use the asterisk as a leading wildcard ('*str'), or embedded wildcard ('s*r').

In the project described here you can use this value in the string variable strCriteria:

 ""CustomerID like " & "'" & txtCriteria.Text & "'"" .

Save the project, then run it. Enter a value like b* or bo* in the text box, then click the command button.

Last changed 9/11/00