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
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
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.
0
FordraidersAuthor Commented:
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
0
mmipsCommented:
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...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Éric MoreauSenior .Net ConsultantCommented:
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.)
0
FordraidersAuthor Commented:
mmips,
Do i keep my present code or try what emoreau suggested along with yours?
fordraiders
0
Éric MoreauSenior .Net ConsultantCommented:
You better forget bookmarks since it is not a feature present in every driver!
0
FordraidersAuthor Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
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      
               
0
FordraidersAuthor Commented:
emoreau,
It doesn't like all the quotes.
adodc1.Recordset.Find "custnum = ' " & replace(txtSearch2.Text, " ' " ," '' ") & " ' " 
Is this correct/

fordraiders
0
Éric MoreauSenior .Net ConsultantCommented:
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
0
FordraidersAuthor Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
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.
0
FordraidersAuthor Commented:
emoreau,
One last thing.
With this

msgbox adodc1.recordset!CustNum
Can I add the word.  "Found"
Ready to close out.
fordraiders
0
Éric MoreauSenior .Net ConsultantCommented:
Sure:

msgbox adodc1.recordset!CustNum & " found"

You could also keep an indicator telling that you found at least one record or not like this:
dim intFound as integer

'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  

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
emoreau,
I have Never seen the message box displayed like this before.
Thanks for the info.
fordraiders
0
FordraidersAuthor Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
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
0
FordraidersAuthor Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
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.


0
FordraidersAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.