Results 1 to 4 of 4
  1. #1
    MiniG is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    4

    How to make ComboBox autocomplete search on multiple columns

    With Access 2016, I have a dropdown combo box with a row source built from a query combining 4 different columns.
    Code:
    SELECT STUDENT_NAME, INTERNAL_FILE_ID, STUDENT_CODE, STUDENT_ID FROM STUDENT;
    STUDENT_ID is of Data Type "Number", and the other three columns are "Short Text".

    As it is currently configured, Access will try autocomplete any text typed in the combo box matching a potential entry in the first column of my row source, in this example, STUDENT_NAME. If a user click on an option from the dropdown, the STUDENT_NAME value will be added in the combo box. This feature is great, but I would like to know if it's possible to code in VBA a function (or use a built-it settings, if it exists) that would extend this feature and propose to the user to select any option from the dropdown matching either the STUDENT_NAME, INTERNAL_FILE_ID or STUDENT_CODE (I am not interest with the STUDENT_ID for this feature).

    As an example, if someone types "John S", it would autocomplete as "John Smith", and if someone types "0342", it would check and present all entry with a STUDENT_CODE starting with "0342". The same goes for the INTERNAL_FILE_ID, which is an alphanumerical string, ex: J8912.



    Thanks

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Only way I can think about is:
    1. Have RowSource property for combo like:
    Code:
    SELECT STUDENT_ID, STUDENT_NAME  & ", " & INTERNAL_FILE_ID & ", " & STUDENT_CODE FROM STUDENT ORDER BY 2;
    2. Set [ControlSource = "STUDENT_ID", BoundColumn = 1, ColumnCount = 2, and ColumnWidths = "0, 2.5" (or "0; 2.5" , I'm not sure).
    3. Add an unbound combo like cbbSearchOrder with BoundColumn = 1 and with RowSource like:
    Code:
    1; name/internal/code; 2; name/code/internal; 3; internal/name/code; 4; internal/code/name; 5; code/name/internal; 6; code/internal/name
    4. In AfterUpdate event of cbbSearchOrder change Rowsource of your combo like:
    Code:
    ...
    IF me.cbbSearchOrder = 1 THEN
         me.cbbMyCombo.RowSource = "SELECT STUDENT_ID, STUDENT_NAME  & ", " & INTERNAL_FILE_ID & ", " & STUDENT_CODE FROM STUDENT ORDER BY 2;"
    ELSEIF me.cbbSearchOrder = 2 THEN
         me.cbbMyCombo.RowSource = "SELECT STUDENT_ID, STUDENT_NAME & ", " & STUDENT_CODE  & ", " & INTERNAL_FILE_ID FROM STUDENT ORDER BY 2;"
    ELSEIF me.cbbSearchOrder = 3 THEN
         me.cbbMyCombo.RowSource = "SELECT STUDENT_ID, INTERNAL_FILE_ID & ", " & STUDENT_NAME  & ", " & STUDENT_CODE FROM STUDENT ORDER BY 2;"
    ELSEIF me.cbbSearchOrder = 4 THEN
         me.cbbMyCombo.RowSource = "SELECT STUDENT_ID, INTERNAL_FILE_ID  & ", " & STUDENT_CODE & ", " & STUDENT_NAME FROM STUDENT ORDER BY 2;"
    ELSEIF me.cbbSearchOrder = 5 THEN
         me.cbbMyCombo.RowSource = "SELECT STUDENT_ID, STUDENT_CODE & ", " & STUDENT_NAME  & ", " & INTERNAL_FILE_ID FROM STUDENT ORDER BY 2;"
    ELSEIF me.cbbSearchOrder = 6 THEN
         me.cbbMyCombo.RowSource = "SELECT STUDENT_ID, STUDENT_CODE  & ", " & INTERNAL_FILE_ID & ", " & STUDENT_NAME FROM STUDENT ORDER BY 2;"
    ELSE
    END IF
    Me.cbbMyCombo.Requery
    Now user estimates search order when current one is not liked, and then enters the search string

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would think you could also rewrite the combo sql with the OnChange event but you'd need something to signal which column you're trying to base the search on. If that was an option button for example, your WHERE clause would be related to whatever field that button represented and would include whatever is being typed into the combo. If you alter the option choice, you'd reset the combo.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Yorkers is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    1
    Is there a situation where you may deliberately want to reset the combo?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-06-2016, 09:25 PM
  2. Replies: 8
    Last Post: 09-29-2014, 01:56 PM
  3. how to make autocomplete field from history
    By tushargsm in forum Forms
    Replies: 1
    Last Post: 01-18-2012, 09:51 AM
  4. ComboBox with multiple columns
    By rkm360 in forum Forms
    Replies: 10
    Last Post: 05-14-2010, 01:19 PM
  5. ComboBox Autocomplete
    By AdrianKitchen in forum Forms
    Replies: 3
    Last Post: 10-13-2009, 11:12 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums