Quick find on Access database

I have a large database with a query containing name (40 characters) and ID (6 characters). I want to be able to do a quick find by typing in the name (or parrt of the name) whcih will display all those starting with the characters typed and the associated IDs. Then wehn you double-click on the name selected you can read in the relevant record using its ID code. If I populate a text box with name and ID it takes 15 seconds as there are 6000 records. Any ideas?

Do you use SQL or recordset.find method to query the 6000 records?

I am reading from an Access query and have tried to populate the text box using recordset.Findnext and text1.Additem. If I use a dropdown combo then I can only display one field at a time.

I am reading from an Access query and have tried to populate the text box using recordset.Findnext and text1.Additem. If I use a dropdown combo then I can only display one field at a time.

Does your code scheme look anything like this? (cmdSearch is a commandbutton that does the initial search, text1 is where user types the name, SomeList is the list control you use):

Code:

---

private sub cmdSearch_Click()
  set rs = db.openrecordset( _
          "SELECT name, id FROM table "
        & "WHERE name LIKE '" & text1.Text & "*'")
  '(thus the query returns just those names that matches the typed string, not all 6000)
end sub

private sub SomeList_Dblclick()

  rs.find ("id=" & CurrentID)
  'the find only search a limited amount of records

end sub

---

if not, maybe you could try this scheme