Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Pimped is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2009
    Posts
    25

    Question Combo Box Autocomplete / Filter as you type

    Hey Guys



    I've been searching for a solution to this problem for a few months now with no exact answer, so I'm posting my first help request for this.

    What I have is a subform that adds products to an estimate, which is the parent form.

    I want to allow the user to start typing in a combo box in the subform, which is a collection of all the products in the products table and it should allow autocomplete FROM ANY PART OF THE product name.

    I say this because I have 5 columns currently displayed in the dropdown list: Main Category, Sub Category, Product Name, Colour, Size and although I would like to keep the list separated by columns, I am guessing that I will have to concetanate the fields together with a string seperator so that the required functionality will work correctly.

    I have so far found this link, but it is to do with .NET coding: h**p://social.msdn.microsoft.com/Forums/en/vblanguage/thread/97da6bbb-fa48-4d38-af87-4c8d1dbc16e7

    If someone could help me find the right solution, as I am not familiar with the API of VBA comboboxes. Even if you could just help me with the theory as to how the code would look/work so that I could build the solution piece by piece.

    Kind Regards

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    You will be lucky if you can get it to autocomplete for more than one field, as there is only on bound field per combo. What you may need is to have a text box and a list box underneath. As you type into the text box the list box is populated based on the results of the textbox. My question is why? You would have to be able to view all 5 columns in order to view the matching products.

    Another solution is to have a further combo box that tells the second combo box which field to search in. Such as:

    Look In:
    Look For:
    Look Where:

    David

  3. #3
    Pimped is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2009
    Posts
    25
    Hi David

    Thanks for the reply. Yes in that sense it would be very difficult to achieve. Let me explain my problem further.

    The subform is a datasheet view form. So there is no scope for putting the text box on that form.

    What I was hoping to do was get the recordset of all products and the 5 fields and join the fields together for each item seperated by a dash ( - )

    This way, I could put some code in VBA that would be triggered by an event that occurs as the user types and instead of autocompleting from the beginning of each item in the list, it could autocomplete from any part of the string (which is the joining of all 5 fields).

    Does this help?

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    That is one way of achieving it but has 2007 the option to autocomplete where the string entered is anywhere in the string?

    Normally combos are from start of string and listboxes are first letter only. Do not use 2007 regularly to be confident of my reply.

    David

  5. #5
    Pimped is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2009
    Posts
    25
    I have only had successful autocomplete in 2007 where it matches from the first letter and not any part of the string.

    I would ask what do you suggest, but have a read of the following first, might help

    Started the following before you replied:

    Oh before you reply, some more stuff I just thought of:

    1. When the user starts to type, the combo box should automatically drop down and only display those items that match what is being typed. Of course if nothing is type, the user can click the dropdown and every product will be displayed.

    2. Each item in the list should be matched with a product ID column

    3. When they type, the list should be filtered to show only those items where part of the string matches any part of the strings from any of the 5 fields of any product (that's a mouthful!)

    4. The dropdown which appears when the user types, should be a height and width I set as well as keep on filtering it self along with every keystroke. So I'm guessing taking a recordset clone of products and requerying that with each keystroke.


    Thanks for your posts so far, good to know there's someone out there willing to help

  6. #6
    Join Date
    Oct 2009
    Location
    Bromsgrove, West Midlands, England
    Posts
    13
    1. When the user starts to type, the combo box should automatically drop down and only display those items that match what is being typed. Of course if nothing is type, the user can click the dropdown and every product will be displayed.
    In 2003 the answer is 'no' the list does not change apart from highlight the first match of what is typed in you combo box, unless you run VBA on change or on keypress that changes your source for the combobox.

  7. #7
    Pimped is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2009
    Posts
    25
    Quote Originally Posted by Zigzag View Post
    In 2003 the answer is 'no' the list does not change apart from highlight the first match of what is typed in you combo box, unless you run VBA on change or on keypress that changes your source for the combobox.
    That's what I'm hoping to achieve: VBA takes over on keystrokes otherwise if there is nothing typed, then only display the dropdown on the click of the down arrow and show ALL products

  8. #8
    Join Date
    Oct 2009
    Location
    Bromsgrove, West Midlands, England
    Posts
    13
    What David has suggested is the best and most simple solution.

    Use a Text box and a List Box, The list box is acting as the dropdown part of you listbox, on every key stoke or change of your text box requery your list box


  9. #9
    Pimped is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2009
    Posts
    25
    Quote Originally Posted by Zigzag View Post
    What David has suggested is the best and most simple solution.

    Use a Text box and a List Box, The list box is acting as the dropdown part of you listbox, on every key stoke or change of your text box requery your list box
    I agree it is a solution, but I would obviously want to have a more cleaner solution! lol

    I guess I could add an empty, unbounded text field to the datasheet form and see if that works first before attempting to code a solution

  10. #10
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    It appears to me that the guidelines you have mentioned were not originated by yourself. It appears it comes from someone who does not know the in and outs of what Access can achieve and is purely a wish list.

    The height and width is governed by the maximum number of rows set to show in a combo box (default is 8) the width is set by the programmer if hard coded unless it attempts to show all columns that do not have a 0 width, and as such may show a horizontal scroll bar. Also it is dependant on its location on the form. If the combo is on the righ of the form then there is not going to be much room to display the results. A good developer will ensure that combo box contents do not protrude outside of the boundaries of a form. If fari to say that if you have 5 fields concatenated together onto one long string it is going to be hard to show the user which charaters in the string match the string entered in first place, unless somehow you could bolden the matching text, which you can't do in Access.

    To me this is more a training issue than a technical issue. As the developer I would say to the client, "Yes, I take on board what you say, but due to the limitations of what Access can provide this is the best solution I can offer you."

    If I were searching for a part in a parts table I would be looking for a general description.

    David

  11. #11
    Pimped is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2009
    Posts
    25
    Quote Originally Posted by dcrake View Post
    It appears to me that the guidelines you have mentioned were not originated by yourself. It appears it comes from someone who does not know the in and outs of what Access can achieve and is purely a wish list.

    The height and width is governed by the maximum number of rows set to show in a combo box (default is 8) the width is set by the programmer if hard coded unless it attempts to show all columns that do not have a 0 width, and as such may show a horizontal scroll bar. Also it is dependant on its location on the form. If the combo is on the righ of the form then there is not going to be much room to display the results. A good developer will ensure that combo box contents do not protrude outside of the boundaries of a form. If fari to say that if you have 5 fields concatenated together onto one long string it is going to be hard to show the user which charaters in the string match the string entered in first place, unless somehow you could bolden the matching text, which you can't do in Access.

    To me this is more a training issue than a technical issue. As the developer I would say to the client, "Yes, I take on board what you say, but due to the limitations of what Access can provide this is the best solution I can offer you."

    If I were searching for a part in a parts table I would be looking for a general description.

    David
    Hi David

    Yes you are right to an extent. Some of the guidelines originated from a client. The more technical wishlist, I came up with!

    Let me clear a few things up so you don't think too little of me

    1. The width of the combo box should not be an issue if the dropdown list appears while the user types, that way the physical box will not need to be as wide as the concatenated string.

    2. If the user wishes to change their selection or view what they have chosen, they could just click on the dropdown arrow and it should show their item selected/highlighted in the dropdown list.

    3. The dropdown list will not be wider than the form

    4. The height and width was an issue for me because as you said, I don't want the dropdown list to exceed the boundaries of the form, so I want the height and the width to be limited to my specifications with scrollbars attached for scrolling

    5. If it isn't possible, then I dont mind going for the textbox filtering the combo box as an alternative solution

    6. Do you think it is possible where as the user types, the dropdown list will be shown and constantly filtered as the user types until the user presses the arrow keys to move through the available products and hits enter or uses the mouse to make their selection?

  12. #12
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Here is a simple demo of what I tend to use

    David

  13. #13
    Pimped is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2009
    Posts
    25
    That is a great example of you method. Only one problem, it only looks at the beginning of a string.

    I don't know the API of VBA so I can't apply any general programming logic!

    In a sql statement pseudocode, the refreshing of the combobox would look something :

    SELECT *
    FROM Product
    WHERE ([Main Category] = *enteredString*)
    OR ([Sub Category] = *enteredString*)
    OR ([Product Name] = *enteredString*)
    OR ([Colour] = *enteredString*)
    OR ([Size] = *enteredString*)

    It's not the best examples but it shows that I want it to look from anywhere in the strings contained in those five fields.

    I know there is a programmatic way of doing this.

    So at present, the issue is searching through all 5 fields (or the joined string) and adjusting the list of the combo box to reflect the matches.

  14. #14
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Like you say it only looks at the beginning of the string, but it would be easy to modify by changing the like operator to * .... *

    David

  15. #15
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    Okay I'm back.

    So I changed the like to say where the name is: Like '*" & sText & "*'

    But when I type anything into the text box, it doesn't react. Would I have to play around with syntax or play with a trigger event?

    EDIT:

    Ignore it, forgot I had to click the dropdown to get a list of filtered results.

    I would like get the combo to show the first match as I type. Any suggestions?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. filter form based on combo box
    By lloyddobler in forum Forms
    Replies: 8
    Last Post: 09-10-2009, 07:33 AM
  2. Combo Box Filter
    By jgelpi in forum Programming
    Replies: 3
    Last Post: 07-27-2009, 12:54 PM
  3. Filter Form records with Combo Box????
    By jgelpi in forum Forms
    Replies: 0
    Last Post: 05-19-2009, 07:05 AM
  4. Replies: 0
    Last Post: 02-27-2009, 01:27 PM
  5. Applying a filter to a combo box
    By bugchaser in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 02:37 PM

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