ADO Find method only supports one criteria (195222)

RESOLUTION

This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: Here are four possible workarounds to this limitation.

• Use the ADO Filter property. It allows multiple criteria.
  
  -or-
• Use a more restricted Select statement to either re-create the recordset or to create an additional recordset.
  
  -or-
• Use the ADO Filter property and the Clone method. This allows you to find the correct bookmark in the clone without affecting the rows that are visible in the recordset.
  
  A code sample that illustrates this method follows:

        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
  
        'Create a variable for the Cloned Recordset.
        Dim clone_rs As ADODB.Recordset
  
        Set cn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.Recordset")
  
        With cn
         .ConnectionString = "PROVIDER=SQLOLEDB;" & _
                             "DATA SOURCE=<server>;" & _
                             "USER ID=<uid>;" & _
                             "PASSWORD=<pwd>;" & _
                             "INITIAL CATALOG=<init_cat>"
         .Open
        End With
  
        With rs
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockBatchOptimistic
         .ActiveConnection = cn
         .Open "select * from authors"
        End With
  
        'A clone recordset has some benefits :
        ' - Very little overhead, it is only an object variable containning a
        '   reference to the original recordset.
        ' - It does not require another round trip to the server.
        ' - It maintains separate but shareable bookmarks with the original.
        ' - Closing and filtering clones does not affect the original or
        '   other clones.
  
        'Create a clone of the recordset.
        Set clone_rs = Rs.Clone
        'Apply a filter to the clone using the criteria passed in.
        clone_rs.Filter = "state = 'CA' AND city = 'Oakland'"
  
        If clone_rs.EOF Or clone_rs.BOF Then
        'If criteria not found move to EOF; just as ADO's Find
         rs.MoveLast
         rs.MoveNext
        Else
        'If found, move the Recordset's bookmark to the same location as the
        'clone's bookmark.
        rs.Bookmark = clone_rs.Bookmark
        End If
  
        clone_rs.Close
        Set clone_rs = Nothing
  
        rs.Close
        cn.Close
  
        Set rs = Nothing
        Set cn = Nothing
  
        End Sub
  					

  -or-
  
  
• Create a custom Find routine. The custom Find method can be very simple with minimal features or more complicated and having all the features of the ADO Find method.
  
  A simple Multi_Find routine would be a modification of the ideas in the third workaround. For instance:

        Public Sub Multi_Find( _
         ByRef oRs As ADODB.Recordset, _
         sCriteria As String)
        '
        'This Sub Routine simulates a Find Method that accepts Multi-Find
        'Criteria. It searches columns in a recordset for specific values.
        '
        'ADO Recordset's Find Method has a limitation of single criteria
        'finds.
        'For instance:
        ' ADO Recordset's Find only accepts criteria like the following:
        '   rs.Find = "state = 'CA'"
        '
        'It generates an error if multiple criteria are passed to it:
        '   rs.Find = "state = 'CA' AND city = 'Oakland'"
        '
        'This Sub Routine has the following syntax:
        ' Multi_Find oRs, sCriteria
        'Where:
        ' oRs is the ADO Recordset object where the Find is to be done.
        ' sCriteria is a String in the same format as the Find method
        '  with the addition of multiple conditions can be provided so
        '  long as each is joined by an "AND".
        '
        'Example:
        ' Multi_Find rs, "state = 'CA' AND city = 'Oakland'"
  
        Dim clone_rs As ADODB.Recordset
        Set clone_rs = oRs.Clone
  
        clone_rs.Filter = sCriteria
  
        If clone_rs.EOF Or clone_rs.BOF Then
         oRs.MoveLast
         oRs.MoveNext
        Else
         oRs.Bookmark = clone_rs.Bookmark
        End If
  
        clone_rs.Close
        Set clone_rs = Nothing
  
        End Sub
  					

The limitations of this Multi-Find method versus the ADO Find method are that it does not support the "SkipRows", "SearchDirection", or "Start" parameters. If you want these features, you need to create a more complicated custom multi-find method. The Multi-Find method would have to parse the criteria string out properly, navigate the recordset accordingly while checking each criteria for matches.