Searching with VBA

[raindrop3]

hello,

I have a little problem. I have one table and I want the user to search if a value already exist. He/she types the value in a textbox and then push a button what starts the code. However, my code only find the first record of the table.

Can someone help me with this 'loop-problem'?

Thanks in advantage,

Albert.


Here goes my simplified code:

Dim rstthumbs As Recordset
Dim dbs As Database

Set dbs = CurrentDb()

Set rstthumbs = dbs.OpenRecordset("SELECT naam FROM Thumbnails")

zoeknaam.SetFocus 'this is the textbox
If rstthumbs("naam") = zoeknaam.Text Then
MsgBox "Gevonden"
Else
MsgBox "Niet gevonden"
End If

 

[DMC]

Try this,

Dim rstthumbs As Recordset
Dim dbs As Database
Dim Txtz as Control

Set dbs = CurrentDb()

Set rstthumbs = dbs.OpenRecordset("SELECT naam FROM Thumbnails")

set Txtz = me!zoeknaam 'use the ME! if the control is on the same form

rstthumbs.movefirst ' start of recordset

do until rstthumbs.eof = true 'start loop and do until end of recordset unless a match is found

If rstthumbs("naam") = Txtz Then goto Found

Else rstthumbs.movenext

end loop

not_found:

MsgBox "Niet gevonden"
exit sub


Found:
MsgBox "Gevonden"
exit sub

end sub

This should loop you through the recordset until it gets to the end, whats more if there are no records in the recordset it will give you the 'niet gevonden' message. you do not have to set the focus on your text box to refer to it.

Hope this helps

Cheers

DMC

 

[raindrop3]

THANKS A LOT DMC!

It works great. Now I'm out of trouble!

thanks again.

Albert.

PS. There was missing a 'loop'. The code below is how I use it now:

Dim rstthumbs As Recordset
Dim dbs As Database
Dim Txtz As Control

Set dbs = CurrentDb()

Set rstthumbs = dbs.OpenRecordset("SELECT naam FROM Thumbnails")

Set Txtz = Me!zoeknaam 'use the ME! if the control is on the same form

rstthumbs.MoveFirst ' start of recordset

Do Until rstthumbs.EOF = True 'start loop and do until end of recordset unless a match is found

If rstthumbs("naam") = Txtz Then GoTo Found
rstthumbs.MoveNext
Loop 'THIS LOOP!
not_found:
MsgBox "Niet gevonden"
Exit Sub


Found:
MsgBox "Gevonden"
Exit Sub

End Sub