none
Combo Box Auto Complete RRS feed

  • 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?
    Wednesday, August 16, 2017 5:13 PM

All replies

  • 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...



    • Edited by .theDBguy Friday, August 18, 2017 2:57 PM
    Wednesday, August 16, 2017 5:31 PM
  • 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.
    Wednesday, August 16, 2017 7:16 PM
  • 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

    Thursday, August 17, 2017 7:41 AM
  • 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

    Thursday, August 17, 2017 11:25 AM
  • 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 MSDNFSF@microsoft.com.

    Friday, August 18, 2017 8:10 AM
    Moderator
  • 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!

    Friday, August 18, 2017 2:57 PM