Need help with this search code.... 1

[thetambarineman]

Hello all,

the following is my code for searching through a database..
I realise that this is a bit of an over-kill for searching through databases - but its the way i learned way back in vb3-and old habits die hard!!!

Anyway-the problem is that it doesnt seem to be looping around again if a record is found

'this is called from the code..
'what it does is checks all of the string in a DB for your search string and matches it hopefully!!
'The only thing is that its only finding 1 record-then stopping despite there being loads of similiar records which match the search criteria!!!

Sub systemsearch(sysdata As Data, sysfield As String, syssearchdata As String, syssearchlist As ListBox)

recsfound = 0
sysdata.Recordset.MoveFirst

Do While sysdata.Recordset.EOF = False
startpos = 1
fieldlen = Len(sysdata.Recordset(sysfield))
stringlen = Len(syssearchdata)

Do Until startpos > (fieldlen - stringlen) + 1
matchstring = Mid$(sysdata.Recordset(sysfield), startpos, stringlen)

If UCase(matchstring) = UCase(syssearchdata) Then
syssearchlist.AddItem (sysdata.Recordset(sysfield))
recfound = MsgBox("Record located..", vbInformation + vbOKOnly, "Records located&quot

continueflag = False
Do Until continueflag = True
DoEvents
Loop
End If
startpos = startpos + 1
Loop

If sysdata.Recordset.EOF = False Then sysdata.Recordset.MoveNext
Loop

If found = False Then
reply = MsgBox("No records found.. Please refine your search", vbInformation + vbOKOnly, "No records found&quot
End If

End Sub


Thanks in advance!!

And excellent improvements to the site-it looks really well!

Paul

 

[Ruairi]

Definitely overkill. I think the reason it's stopping is because continueflag is never set to true. Anyway, i'd just cut out the whole mess and replace it with a single Do Loop like this.

RecsFound = 0 
sysdata.Recordset.MoveFirst 

Do Until sysdata.Recordset.EOF 
   If InStr(Ucase(sysdata.Recordset(sysfield)), Ucase(syssearchdata)) Then 
      RecsFound = RecsFound + 1 
      syssearchlist.AddItem sysdata.Recordset(sysfield) 
      MsgBox "Record Found" 
   End If 
   sysdata.Recordset.MoveNext 
Loop 

If RecsFound = 0 Then MsgBox "No Records Found"

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[thetambarineman]

Ruairi,

Thanks so much for that-it has been bugging me for a while now!! the only thing that now remains is another little niggle... when i get the search completed the results are displayed in a list box-the user must then click the appropriate search result to display the record on another form... The way i have of doing this is inefficient-can you suggest a way to do this???

Thanks again, and thanks in advance..

Paul..

 

[chiph]

In SQL Server you can do the following:
[tt]SELECT PATINDEX('%hello%', message)
FROM emailbody
WHERE sender_id = 'FooBar';
[/tt]
This searches the message column in table emailbody for any rows containing the word "hello", and was sent by user "FooBar".

Chip H.

 

[thetambarineman]

Id have preferred to stay away from sql - its not where my experience lies.. is there anyway to assign an element in a list box a variable - then pass that variable when the user clicks it???