column search in text box

[LMHODNETT]

I have a database that will build a special order form for customers in our store. I am having a problem with the text box I think I need to pull into the database the data I need from a table name "customer". This table gives us several columns of data that are useful in the form for special orders. My problem is that my first column is "full name" which is the key we use to find the proper customer. I have been able to type in the first letter of the last name in the full name column so it can search for all with that last name. However, what I need to be able to do is use more than one letter for this search. There are over 15,ooo name in this table and many with the last name. When I try to type a second letter now it goes to the beginning of the customers name of the second letter I typed in. I tried using combo box also nad it does allow a search of several letter so that you can get all of the las name typed in which will isolate that customer. The problem with combo box is I cannot get it recall all the data I need from the customer table for a particular customer. I hope there is a simple way to do this, because my staff to jumping all over the place run queries and such to try to isolate the customer unless it is something I can put into the form as a common function. I sure need help with this. Please advise or give comments.

Thanks,
Larry

 

[PHV]

A combobox may have many columns and the AutoExpand property set to True.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Here are modifications of an idea that work real well. In the second example I put a subform on a form. The subform is formatted to look like a list box. When you type in the search box it scrolls everytime a match is made. This effect is similiar to Mircrosoft Outlook when you use the address book. Then if I click on a record, I would have additional code to select that record. The first example just scrolls the current form. These are just some ideas that can be easily expanded, but it gives you the interface you are looking for.

Private Sub txtSearch_Change()
  Dim rs As DAO.Recordset
  Dim bk As Variant
  Dim strSearch As String
  Set rs = Me.RecordsetClone
  strSearch = "strLastName like '" & txtSearch.Text & "*'"
  rs.FindFirst (strSearch)
  If Not rs.NoMatch Then
   bk = rs.Bookmark
   Me.Bookmark = bk
  End If
  txtSearch.SelStart = Len(txtSearch.Text)
End Sub


Private Sub txtSearchTwo_Change()
  Dim rs As DAO.Recordset
  Dim bk As Variant
  Dim strSearch As String
  Set rs = Me.subFrmPersonnel.Form.RecordsetClone
  strSearch = "strLastName like '" & txtSearchTwo.Text & "*'"
  rs.FindFirst (strSearch)
  If Not rs.NoMatch Then
   bk = rs.Bookmark
   Me.subFrmPersonnel.Form.Bookmark = bk
  End If
  txtSearchTwo.SelStart = Len(txtSearchTwo.Text)
End Sub