[RESOLVED] Finding names using LIKE

[Michael_Kamen]

Hi,

I'm using this code to list all names from a table into a listbox:

Dim path As String
Dim strQuery As String
Dim i As Integer
Dim r As Integer
path = App.path & "\persons.mdb"
Set db = OpenDatabase(path)
strQuery = "SELECT * FROM Names where Name Like '" & txtSearch.Text & "*'"
Set rs = db.OpenRecordset(strQuery, dbOpenDynaset)
r = rs.RecordCount
If r <> 0 Then
List1.Clear
For i = 0 To r
List1.AddItem rs.Fields("Name").Value
rs.MoveNext
Next
End If
rs.Close
End Sub

The code above is run in the change event of my search textbox.
Say I have two usernames in the table: "Pete" and "Peter".
It works fine if I type like "Pe" or "Pet" or "Pete", they are listed in the listbox. But as soon as I type "Peter" I get the error "No current Record".
What am I doing wrong?

[peet]

the problem is probably the recordcount.

it is not correct unless you do a movelast movefirst before counting... MS does it again

rs.MoveLast
        rs.MoveFirst

after that, the recordcount will be correct.

[peet]

this is how I would do it

Set db = OpenDatabase(path)
    strQuery = "SELECT * FROM Names where Name Like '" & txtSearch.Text & "*'"
    
    Set rs = db.OpenRecordset(strQuery, dbOpenDynaset)
    If rs.RecordCount > 0 Then
        List1.Clear        
        While Not rs.EOF
            List1.AddItem rs.Fields("Name").Value
            rs.MoveNext
        Wend
    End If
    rs.Close

this way I do not have to rely on the recordcount

[Michael_Kamen]

Thanx peet