CODEWITHSUNDEEP
vbams-accessdaorecordset
Mosh Yaz
Mosh Yaz

Reputation: 1

Choosing a value from a recordset using MS-Access VBA

I'd like to create a search textbox for names. I'm using a DAO record set to get records that are like the name entered. When returning more than one record, I'd like to show the user the results and have him to choose the a name (record) from the list.

Dim dbs As DAO.Database
Dim Rs As DAO.Recordset
Dim strSQL As String
Dim SQL As String

Set dbs = CurrentDb()

Dim diverFirstName As String
diverFirstName = CStr(Me.txtSearchFirst) + "*"

strSQL = " SELECT tblDiver.* " & _
         " FROM tblDiver " & _
         " WHERE tblDiver.firstName LIKE '" & diverFirstName & "' ;"

Set Rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Rs.MoveLast

If Rs.RecordCount > 1 Then
     *** code to display a table with the returned records so that the user can choose here ***
Exit Sub

How should I approach this?

Upvotes: 0

Views: 914

Answers (1)

JNevill
JNevill

Reputation: 50263

You cycle through recordset results with:

rs.moveFirst
do until rs.eof
    'add to a listbox or something
    me.listbox1.additem rs.fields("firstname").value & " " & rs.fields("lastname").value

    rs.moveNext
loop

Upvotes: 1

Related Questions