Using that FIND command with ADODB

[VBfreak]

This is the syntax the words in brackets are optional but I had problem leaving blanks so I used them.


Recordset.Find(Criteria As String, [SkipRecords As Long], [SearchDirection As SearchDirectionEnum = adSearchForward], [Start])

Criteria is a string with operators like 'EMP_NAME = DAVE' and you can use wild card operators as well like ? and *.

SkipRecords is self-explanatory

SearchDirection is self-explanatory

Start is where to start like adBookmarkFirst then you must logicaly use adSearchForward since you are at the first record of the Recordset.

Make sure that your recordset cursor type is not adForwardOnly meaning that you can only advance in the record set.

Hope this will help

Wherever possible I avoid the .FIND method because it is too dang slow. I use sql SELECT instead.

[steviep]

Anyone know how to use that find command with ADODB to search through your open database???

[steviep]

How do you use find select with sql?

Whatever your .FIND criteria is you can phrase as a SELECT statement, e.g., Recordset.FIND "FIELD = " & value can be rephrased "SELECT Recordset.* WHERE (FIELD = " & value. Then execute the SELECT phrase as a command.EXECUTE or recordset.OPEN(sql)

.FIND is a sequential search going forward or backward according to the parameters VBfreak mentioned. SELECT is much more direct utilizing table indices.

[jbuck]

Hi steviep,
The following code works for me!

If strLastName <> "" Then
'Search forward for the CustomerID
datPrimaryRS.Recordset.Find "LAST_NAME = '" & strLastName & "'", 0, adSearchForward

'If the record isn't found, we will be at the end of the recordset.

The strLastName is just a string from a text box and this code is placed under the click area of a command button.
Jim
'So, reposition the recordset back to the record we came from and search backwards.
If datPrimaryRS.Recordset.EOF Then
datPrimaryRS.Recordset.Bookmark = mybkmark
datPrimaryRS.Recordset.Find "LAST_NAME = '" & strLastName & "'", 0, adSearchBackward

'If we don't find the record this time, it doesn't exist.
'So, reposition the recordset back to the record we came from and tell the user.
If datPrimaryRS.Recordset.BOF Then
datPrimaryRS.Recordset.Bookmark = mybkmark
MsgBox "Record Not Found"
End If
End If
End If