Searching a ADO recordset with 'Find'

[JonnyCab]

Hi all.

I am using a discounted ADO recordset and need to search it to return a value.

The recordset has two fields. If the first field of a record = cmbContract.text (The text value of a combo box)
then I want to return the second field from the same record to fill a text box on a form.

From what I hear the 'Find' command is the way to go but Im not sure how to use it. I've tried using the code below but it is obviously not in the right format

strRecord = rsContract.Find("ContractCode = " & cmbContract, , adSearchForward)

Any code samples would be much appreciated.

Cheers
Jonny

[fkauffman]

Public Sub Main()
FindX
End Sub

Public Sub FindX()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim mark As Variant
Dim count As Integer

count = 0
cnn.Open "DSN=Pubs; Provider=MSDASQL; uid=sa; pwd=;"
rst.Open "SELECT title_id FROM titles", cnn, _
adOpenStatic, adLockReadOnly, adCmdText

' The default parameters are sufficient to search forward
' through a Recordset.

rst.Find "title_id LIKE 'BU%'"

' Skip the current record to avoid finding the same row repeatedly.
' The bookmark is redundant because Find searches from the current
' position.

Do While rst.EOF <> True 'Continue if last find succeeded.
Debug.Print "Title ID: "; rst!title_id
count = count + 1 'Count the last title found.
mark = rst.Bookmark 'Note current position.
rst.Find "title_id LIKE 'BU%'", 1, adSearchForward, mark
Loop

rst.Close
cnn.Close
Debug.Print "The number of business titles is " & count

End Sub

[pardede]

I think your problem is only some missing quotes:

strRecord = rsContract.Find("ContractCode = '" & cmbContract & "'"

[JonnyCab]

Thanks pardede & fkauffman.

After looking at a few examples in MSDN documentation I realised that I was missing the single quotes required to identify a string. I used the following code.

rsContract.MoveFirst
rsContract.Find ("ContractCode = '" & cmbContract & "'")
txtSequence = rsContract!ContractSeq
cmbACode = rsContract!Activity

You were right on the button pardede.

Cheers

Jonny