DAO to ADO question about "Find" and "Move"

[mmitchell]

I am having problems converting the floowing code to ADO. Specifically with the FindFirst and Move.

I have serached but don't fully understand when to use "Seek" and when to use "Find" and also what options I have to include with them.

Dim blnFound As Boolean
Dim lngWOIDSub As Long, lngWOID As Long, strSQL As String
 
 Dim cnn As New ADODB.Connection
  Dim rstItemsOrdrd As New ADODB.Recordset
  Dim rstKitItems As New ADODB.Recordset
  
lngWOIDSub = Me.WOIDSub
lngWOID = Me.WOID
blnFound = False

strSQL = "SELECT [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum FROM [T_SetupSheetHistoryCombinationsDetail] " & _
        "WHERE ((([T_SetupSheetHistoryCombinationsDetail].WOID)=" & lngWOID & ") AND " & _
        "(([T_SetupSheetHistoryCombinationsDetail].WOIDSub)=" & lngWOIDSub & "));"


' Open the connection
    cnn.Open CurrentProject.Connection
    
If rstItemsOrdrd.BOF = False Then
    rstItemsOrdrd.MoveFirst
End If

Do Until rstItemsOrdrd.EOF = True
    rstKitItems.[COLOR=Red]FindFirst[/COLOR] "[KitItemPartNum]=""" & rstItemsOrdrd.Fields(0) & """"
    If rstKitItems.[COLOR=Red]NoMatch[/COLOR] = True Then
        blnFound = True
        Exit Do
    End If
    rstItemsOrdrd.[COLOR=Red]MoveNext[/COLOR]
Loop

 

[FoFa]

This is my stored ADO example I use (OK it comes from an ASP page), but a couple minor changes and it works in Access.

FIND Method:

Sub ADOFindRecord()

   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"

   ' Open the recordset
   rst.Open "Customers", cnn, adOpenKeyset, adLockOptimistic

   ' Find the first customer whose country is USA
   rst.Find "Country='USA'"

   ' Print the customer id's of all customers in the USA
   Do Until rst.EOF
      Debug.Print rst.Fields("CustomerId").Value
      rst.Find "Country='USA'", 1
   Loop

   ' Close the recordset
   rst.Close

End Sub

SEEK Method:

Sub ADOSeekRecord()

   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"

   ' Open the recordset
   rst.Open "Order Details", cnn, adOpenKeyset, adLockReadOnly, _
      adCmdTableDirect

   ' Select the index used to order the data in the recordset
   rst.Index = "PrimaryKey"

   ' Find the order where OrderId = 10255 and ProductId = 16
   rst.Seek Array(10255, 16), adSeekFirstEQ

   ' If a match is found print the quantity of the order
   If Not rst.EOF Then
   Debug.Print rst.Fields("Quantity").Value
   End If

   ' Close the recordset
   rst.Close

End Sub

Because Seek is based on an index, it is important to specify an index before searching. In the previous example, this is not strictly necessary because Microsoft Jet will use the primary key if an index is not specified.