Excel frontend with Access backend that utilise a combobox/textbox combination to filter clients

  • Hi

    I'm putting this question under Excel, although Access is also involved.

    I have an Excel frontend, that has a userform that can be used to display client information. The Excel pulls data from an Access database that has a ClientCode field and a ClientName field (amongst many many other field).

    This is what I have a problem with (steps will explain):

    1. The userform has a textbox and combobox next to one another. Upon opening the userform the combobox should automatically be filled with the names of all clients (this should be queried from the Access database automatically via a recordset if possible)

    2. The client can then start typing client names in the combobox, and the combobox should filter to corresponding clients as the user types. It should display the top 5-10 options if possible.

    3. The user can then select the client he wants.

    4. The corresponding client code is than put in the textbox when the client name is selected in the combobox.

    If a combobox is not the correct control to use I would gladly accept someone correcting me. Also, is it possible to change/manipulate combobox settings in such a way that it can be used as a filterable (is this a word?) control to get the desired result.

    I thought of pulling the ClientCode and ClientName from the Access database, then putting it in a hidden sheet and creating a (dynamic?) named range, assign that to the combobox, and then run a VLookup from the userform to get the client code for the textbox.

    BUT before I do that I want to know if there is a more clever way of achieving the above before resorting to my proposed solution. Just thinking - if more than once control is necessary to get the desired result that is fine. I suppose in such a situation some of the controls can be hidden and would not even bother the user.

    Too many ideas without knowing my options...

    I do not have any code for this yet since I need to know my options before I can proceed. I've also searched the internet for similar problems people posted about, but could not find a single one that utilises Excel frontend with Access backend (or I just did not search with the correct phrases etc).

    Thank you for any assistance!



  • wesselsteyl

    Changed the title of the thread from “Excel frontend with Acess backend that utilise a combobox/textbox combination to filter clients” to “Excel frontend with Access backend that utilise a combobox/textbox combination to filter clients”.