Combo Box Auto Complete

Question

I am trying to create a combo box that has a Client ID and the name of the Client. But I want to be able to type and search by the name but save the ID. What can I do?

Answer 1

You could try something like:

Row Source: SELECT ClientID, ClientName FROM tblClients ORDER BY ClientName

Bound Column: 1

Column Count: 2

Column Widths: 0"

Hope it helps...

Answer 2

I currently have one working similar to this but I doesn't let me autocomplete the name it lets me autocomplete the ID. Is there a way for it to autocomplete the name.

Answer 3

Hi nk1188,

How do you set the combo box? Does the combo box show the ID or the name?

Based on DBbug's Row Source, I set the combo box like below that could let me autocomplete the name.

Column Count:2

Column Width:0";1"

Best Regards,

Terry

Answer 4

If the client names are personal names with separate FirstName and LastName columns I'd recommend the following setup for the control:

RowSource:     SELECT ClientID, LastName & ", " & FirstName FROM Clients ORDER BY LastName, FirstName;

BoundColumn:   1
ColumnCount:    2
ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

This would allow you to enter the client's last name and progressively go to the first match as each character is entered.  However, selecting a person purely by name is not really satisfactory, as personal names can be duplicated (I worked with two Maggie Taylors).  To differentiate between clients of the same name, further details can be shown in another column or columns of the combo box's list, e.g. the client's address.  You'll find an example in NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the opening form includes a combo box which shows the contact's address as a second column.  The address data is concatenated by means of the following function, which is adapted from the CanShrinkLines function published by Microsoft:

Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
 
    ' Pass this function a character or characters
    ' to be used as the separator followed by the values to be combined
    ' For example: strFullName =
    ' ConcatValues(" ",FirstName, MiddleName,LastName)
    
    Dim X As Integer, strLine As String
    
    For X = 0 To UBound(arrVals)
        If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
          strLine = strLine & strSeparator & arrVals(X)
        End If
    Next
      
    ' remove leading separator character(s)
    ConcatValues = Mid(strLine, Len(strSeparator) + 1)
 
End Function

---

Ken Sheridan, Stafford, England

Answer 5

Hi nk1188,

you had mentioned that," I want to be able to type and search by the name "

something like below?

Code:

Private Sub Combo0_Change()
Me.Combo0.RowSource = ""
Dim strSQL As String
strSQL = "SELECT Country from tblCountry1 where Country like '" & Me.Combo0.Text & "*'"
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.EOF
   Debug.Print rs("Country")
   Me.Combo0.AddItem (rs("Country").Value)
   rs.MoveNext
Loop
End Sub

here I only use 1 column. you can try to use 2 columns and display one column to user.

you can modify the code as per your requirement.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

I currently have one working similar to this but I doesn't let me autocomplete the name it lets me autocomplete the ID. Is there a way for it to autocomplete the name.

Hi,

What were the settings for your combo's properties? For example, can you please show us what is in the following properties?

Row Source

Bound Column

Column Count

Column Widths

Thanks!