ms access database search does not work as intended

The table name is water and it has two field fname and serial.

In the field fname suppose a value 'bill' has 3 serial 1, 2, and 3. now I need when I type bill in textbox1 and click search button, the combobox1 should show the serials of 'bill' are 1,2 and开发者_运维问答 3.

I'm using ms access as db and I'm using this code but it not working.

Is there any other way to solve this?

Set rs4 = cn.Execute("select fname, serial from water where fname = '" & Text1.Text & "'")
rs4.MoveFirst
Do While Not rs4.EOF
    Combo1.AddItem rs4("serial")
    rs4.MoveNext
Loop

You could have a problem when someone enters a single-quote in to the text box. You should do something like this:

Set rs4 = cn.Execute("select fname, serial from water where fname = '" & Replace(Text1.Text, "'", "''") & "'")

Another problem you may have is if there is no match found. You're not checking to see if there is any data in the recordset object.

Combo1.Clear
Set rs4 = cn.Execute("select fname, serial from water where fname = '" & Replace(Text1.Text, "'", "''") & "'")
If not rs4.Eof Then
  rs4.MoveFirst
  Do While Not rs4.EOF
      Combo1.AddItem rs4("serial")
      rs4.MoveNext
  Loop
End If

I'm not really sure what the problem is, but your loop should look something more like this:

rs4.MoveFirst
Do While Not rs4.EOF
    Combo1.AddItem rs4("holding")
    rs4.MoveNext
Loop

Have you considered telling the compiler more explicitly what you want, something more like:

Set rs4 = cn.Execute("select fname, serial from water where fname = '" & Text1.Text & "'")
With rs4
    Do Until .EOF
        Combo1.AddItem CStr(!serial.Value)
        .MoveNext
    Loop
End With