asked on

ADO-Searching - not looking at all records

I'am using the following off this command to call a string variable and search the current adodc1 recordset and find matches as I keep pressing the command button.
When it comes to the last match it says "Search Ended"
Then it runs another routine.
WHAT IT IS NOT DOING:
If I have 20 records in the database and start the search on record...5.
This code will only search records 6 -20.
It overlooks 1-5.
Any suggestions
fordraiders

Private Sub cmdFindNam_Click()
' GOES TO LABEL ON FORM TO SHOW SEARCHING ON CURRENT DATABASE
frmDesc.lblFiltered.Enabled = True
' SEARCH ON CUSTOMER NUMBER
adodc1.Recordset.Find "custnum = '" & txtSearch2.Text & "'", 1

If adodc1.Recordset.EOF Or adodc1.Recordset.BOF Then
If frmDesc.adodc1.Recordset.RecordCount >= 1 Then
If frmDesc.adodc1.Recordset.RecordCount = 0 Then
Else
frmDesc.adodc1.Recordset.MoveFirst
MsgBox "Search Ended"
frmDesc.lblFiltered.Enabled = False
' CALL TO FRMsEARCH FOR BIGGER RESULT SET.
Call fndSearch2
Exit Sub
End If
With frmDesc.adodc1.Recordset
.MoveNext
End With
End If
End If

Exit Sub
End Sub

Éric Moreau

Place a MoveFirst before your Find. Find doesn't start from the beginning automatically since you can find the same criteria again which will move to the next record corresponding to the criteria 'til the EOF.

Fordraiders

ASKER

emoreau,
frmDesc.adodc1.Recordset.MoveFirst
I placed a movefirst before the find.
It moves to the first match but does not execute after that.
It did cycle through if a match was found on record2 and I started on 6.
but not beyond that.
say... I had another match on 5.
It stoped on record 2.
fordraiders

mmips

Use the bookmark or absolute position property to save your current location then start your search with the current position set to the book mark + 1. Then save that location and continue the search from ther + 1 or until EOF then you can move to first to wrap around if necessary...The find method takes a current position as the second parameter and will start search from there...

Éric Moreau

Did you look at the syntax of find?

Here is what I got from the help. In that, you will see that the second parameters (SkipRows) can be used with a value of one to skip the current row. So normally, the first you use the Find method, you put a 0 in this parameter. All other times, you put 1 to skip the one just found.

====COPIED FROM VB HELP FILE====

Find Method

Searches a Recordset for the record that satisfies the specified criteria. If the criteria is met, the recordset position is set on the found record; otherwise, the position is set on the end of the recordset.

Syntax

Find (criteria, SkipRows, searchDirection, start)

Parameters

criteria A String containing a statement that specifies the column name, comparison operator, and value to use in the search.

SkipRows An optional Long value, whose default value is zero, that specifies the offset from the current row or start bookmark to begin the search.

searchDirection An optional SearchDirectionEnum value that specifies whether the search should begin on the current row or the next available row in the direction of the search. Its value can be adSearchForward or adSearchBackward. The search stops at the start or end of the recordset, depending on the value of searchDirection.

start An optional Variant bookmark to use as the starting position for the search.

Remarks

The comparison operator in criteria may be ">" (greater than), "<" (less than), "=" (equal), ">=" (greater than or equal), "<=" (less than or equal), "<>" (not equal), or "like" (pattern matching).

The value in criteria may be a string, floating point number, or date. String values are delimited with single quotes (for example, "state = 'WA'"). Date values are delimited with "#" (number sign) marks (for example, "start_date > #7/22/97#").

If the comparison operator is "like", the string value may contain "*" (one or more occurrences of any character) or "_" (one occurrence of any character). (For example, "state like M_*" matches Maine and Massachusetts.)

Fordraiders

ASKER

mmips,
Do i keep my present code or try what emoreau suggested along with yours?
fordraiders

Éric Moreau

You better forget bookmarks since it is not a feature present in every driver!

Fordraiders

ASKER

emoreau,
Thanks, I have stared at this help file for quite a while before.
I have th latest MSDN OCT 1999.
I have tried to use a varient bookmark.
I have tried adSEARCHFORWARD, ADSEARCHBACKWARD.
Thanks
fordraiders

Éric Moreau

Maybe your code should look something like this:

'Looking for the first
adodc1.recordset.movefirst
adodc1.Recordset.Find "custnum = '" & replace(txtSearch2.Text,"'","''") & "'"

do until adodc1.recordset.eof
msgbox adodc1.recordset!FieldName
'Looking for the next one
adodc1.recorRecordset.Find "custnum = '" & replace(txtSearch2.Text,"'","''") & "'", 1
loop

Fordraiders

ASKER

emoreau,
It doesn't like all the quotes.
adodc1.Recordset.Find "custnum = ' " & replace(txtSearch2.Text, " ' " ," '' ") & " ' "
Is this correct/

fordraiders

Éric Moreau

The Replace will help you in the case your user inputs something containing single quotes. When using that kind of words, we need to double quotes in the words to ensure that it will be interpreted correctly by ADO.

If your custnum is numeric, you can use this:
adodc1.Recordset.Find "custnum = " & txtSearch2.Text

Fordraiders

ASKER

emoreau,
It is text.

'Looking for the first
adodc1.recordset.movefirst
adodc1.Recordset.Find "custnum = '" & replace(txtSearch2.Text," ' "," " " ") & " ' "

do until adodc1.recordset.eof
msgbox adodc1.recordset!FieldName
'Looking for the next one
adodc1.recorRecordset.Find "custnum = '" & replace(txtSearch2.Text," ' ", " " " " " ) & " ' ", 1
loop

This works!!
Thanks
Now this is totally off what I have.
Why is Replace working.
fordraiders

Éric Moreau

You include an error in the Replace. You have to replace single quotes (second parameter) by 2 single quotes (third parameter) - not by 2 double quotes and do not include spaces. So it should look like this:
adodc1.Recordset.Find "custnum = '" & replace(txtSearch2.Text,"'","''") & " ' "

If your custnum doesn't have any single quotes, you can take it out for the test purpose but remember it when you will be searching string containing it later.

Fordraiders

ASKER

emoreau,
One last thing.
With this

msgbox adodc1.recordset!CustNum
Can I add the word. "Found"
Ready to close out.
fordraiders

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

emoreau,
I have Never seen the message box displayed like this before.
Thanks for the info.
fordraiders

Fordraiders

ASKER

Emoreau,
I need some additional help.
The code I accepted as you answer.
Is it possible to search on more than one field at a time?
I need to search at least 3 more fields.
Using same criteria.
Extra fields would be:
custnum2
custnum3
custnum4

If so , can you place code.
Will apply additional 100 points.

Thanks
fordraiders

Éric Moreau

Sorry but this way, you can't.

Find has this serious limitation of allowing only one criteria.

If I remember well, the Filter method of the recordset doesn't have this limitation. So maybe can you filter for custnum2, custnum3, ... and then use find!

Another way (the one I usually prefer) would be to restrict the numbers of rows returned in your recordset by using the appropriate Where clause in your SQL query:
SELECT * FROM TableA
WHERE CustNum1 = 1
AND CustNum2 = 2
AND CustNum3 = 12345

Fordraiders

ASKER

Emoreau,
I tried the following and it works
Let me know if this is wise.

Looking for the first
adodc1.recordset.movefirst
adodc1.Recordset.Find "custnum = '" & replace(txtSearch2.Text,"'","''") & "'"

do until adodc1.recordset.eof
intFound = intFound + 1
msgbox adodc1.recordset!CustNum & " found"
'Looking for the next one
adodc1.recorRecordset.Find "custnum = '" & replace(txtSearch2.Text,"'","''") & "'", 1
loop

Looking for the second
adodc1.recordset.movefirst
adodc1.Recordset.Find "custnum2 = '" & replace(txtSearch2.Text,"'","''") & "'"

do until adodc1.recordset.eof
intFound = intFound + 1
msgbox adodc1.recordset!CustNum2 & " found"
'Looking for the next one
adodc1.recorRecordset.Find "custnum2 = '" &
replace(txtSearch2.Text,"'","''") & "'", 1
loop

Looking for the third
adodc1.recordset.movefirst
adodc1.Recordset.Find "custnum3 = '" & replace(txtSearch2.Text,"'","''") & "'"

do until adodc1.recordset.eof
intFound = intFound + 1
msgbox adodc1.recordset!CustNum3 & " found"
'Looking for the next one
adodc1.recorRecordset.Find "custnum3 = '" & replace(txtSearch2.Text,"'","''") & "'", 1
loop

if intFound = 0 then
msgbox "nothing found!!!"
end if

I know this is repaeting code but it works.
Thanks
foprdraiders

Éric Moreau

I thought you wanted to search for them all at once!

>Let me know if this is wise.
It is always dependable of your process!

In some cases, you have to go that way, in other you have other ways. Always try to reduce the number of lines in your recordset. This is one of the first rule (if not THE first) when dealing with performance.

Fordraiders

ASKER

emoreau,
If I were returning records in a flexgrid. I would have no problem.
But this is searching and returning "record by record" on the form itself.
Similiar to Access97 filter by form.

Unless you know a way like in Access97; to filter by form method.
That would return all at once.
and the press the movenext button to see each record.

Thanks
fordraiders