searching!

[shuj66]

Anyone have an example of how to do a Find & Find NExt using an adodb connection???

[MoMad]

its called SQL

"SELECT * FROM Table1 WHERE Field1 LIKE '%" & SearchQuery & "%'"

Or beter yet,

SearchQuery = Split(SearchQuery, " ")
SearchQuery = Join(SearchQuery, "%")
ADO1.Connect("SELECT * FROM Table1 WHERE Field1 LIKE '%" & SearchQuery & "%'")

SQL can do alot of db stuff. Look it up.

-MoMad

[shuj66]

Gee thanks,

If I knew then I wouldn't be asking.

I did do a search but I can't seem to make it work.

I found one that uses Adodc with bound fields. How would I do the same using adodb.

Here's the one that I found:

Public Sub FindStringTroubleTicket()

Dim strFind As String
Dim intFields As Integer

On Error GoTo FindError

If Trim(txtSearch) <> "" Then
strFind = Trim(txtSearch)
With frmTicket1.Adodc1.Recordset
Do Until .EOF
'For intFields = 0 To 1
If InStr(1, frmTicket1.txtFields(0).Text, strFind, _
vbTextCompare) > 0 Then
frmTicket1.txtFields(0).SelStart = _
InStr(1, frmTicket1.txtFields(0), _
strFind, vbTextCompare) - 1
frmTicket1.txtFields(0).SelLength = Len(strFind)
frmTicket1.txtFields(0).SetFocus
Exit Sub
End If
'Next
.MoveNext
DoEvents
Loop
MsgBox "Record not found"
.MoveFirst
End With
'frmFindTicket.Top
End If

Exit Sub

FindError:

MsgBox Err.Description
Err.Clear

End Sub

Private Sub cmdCancel_Click()

Unload Me
Set frmFindTicket = Nothing
End Sub

Private Sub cmdFind_Click()

If frmTicket1.Adodc1.Recordset.RecordCount > 0 Then
frmTicket1.Adodc1.Recordset.MoveFirst
Call FindStringTroubleTicket
Else
MsgBox "RecordSet is Empty"
End If
End Sub

Private Sub cmdFindNext_Click()

With frmTicket1.Adodc1.Recordset
.MoveNext
If .EOF Then
.MoveLast
MsgBox "End of File Reached!"
Else
Call FindStringTroubleTicket
End If
End With
End Sub

[MoMad]

Oh no, you dont want that certainly... Just re-query the db and display 1 record at a time...

First set the record source to adCmdText, then you just reconnect to the db like this:

adodc1.recordsource = "SELECT * FROM Table1 WHERE field1 LIKE '%" & FindStr & "%'"
adodc1.refresh

Thats all, now you need to go through each individual recordset and highlight the word ur looking for... but this way is much faster because you dont have to go through every single record. The database will do that for you and return them, all you need to do after that is like i said, highlighting, which u did... does that help?

[ksw]

ADO has a FIND method. Try looking up "Find Method (ADO)" See if that gives you what you need.

The above suggestion will work as long as you don't need the orginal recordset anymore. Each time you execute the new statement, the recordset is re-defined to have only those records selected by the WHERE clause.

[shuj66]

My database is pretty small so I don't think time will be a factor for me.

I want it to be such that when a user enter a word and clicks find then it will go and find that and highlight the textbox on the form.The findNext will then find the next record that matches the word or number.

The code above works when I use adodc with bound fields.

So how exactly would I do the same with adodb?