Best way to select item from table with many items

[sstiebinger]

Thanks for taking a look...



I'm trying to build a tool to create quotations, in the quotation I want to add specific items to the quote, however my list of possible items is very large (several thousand items.)
I've looked at using cascading combo boxes to narrow based on categories and sub-categories, but I'd rather not have to create a bunch of cascading combos...

I can't think that I'm the first person to have this need... what are the best practices for what I'm trying to do?

Thanks.

[June7]

A combobox with several thousand items might not be too many, however, you might find this of interest http://allenbrowne.com/ser-32.html

[Missinglinq]

Allen Browne's hack, that June7 has given you a link to, is great when you're dealing with thousands of possible selections from a Combobox...for things like account numbers, ID numbers, etc. But I'm guessing that if you have thousands of possible items, it's really not reasonable to expect your end users to know the exact names of each of these items, which will be necessary if you use a single Combobox! My guess is that, like it or not, you're going to have to use Cascading Comboboxes so that the users can at least have some idea of what they're looking for, going by a general category. For this type of situation, this is best practice!

Linq ;0)>

[June7]

However, be aware that cascading (dependent) combo/list boxes with lookup alias don't work nice in Datasheet or Continuous form.

[CJ_London]

here is a link for cascading combos which works for datasheet and continuous forms

http://www.access-programmers.co.uk/...d.php?t=275155

[sstiebinger]

You are correct that the end-user won't necessarily know the part number....
What I imagine is something much like an explorer tree, where I have broad categories, with subcategories under them, with the part under those... but all in a single combo box... so I can start typing the main category which will jump right to that, then I can continue on "drilling down" to the part.
Is this possible in Access?

[CJ_London]

you can use a subform rather than a combobox, as you type, the subform reduces the number of options. For this to be effective, the fields you are searching on need to be indexed. So for example you type A, the list in the subform will reduce to any item which as A in any column, then type B, again the list is reduced to any row where there is AB in any column, etc

on your form have an unbound text control (we'll call it quickFind)

on your form have a subform in datasheet view with a recordsource which covers all your parts and shows the various categories and part names - we'll call it sfData

back to quickFind - in the OnChange event put code with builds a filter for the subform and applies it - something like

dim tmpstr as string
dim fltrstr as string
dim ctrl as control

tmpstr = quickFind.Text
For Each ctrl In sfData.Form
    if ctrl.controltype=acTextbox then
        fltrStr = fltrStr & "([" & ctrl.ControlSource & "] like '*" & tmpstr & "*') OR "
    End If
Next ctrl
fltrStr = Left(fltrStr, Len(fltrStr) - 4)

sfData.Form.Filter = fltrStr
sfData.Form.FilterOn = True

then in your subform oncurrent event, you need a bit of code which selects the record you have clicked on

[sstiebinger]

I'm beginning to see how this would work... but not knowing the part number makes this unpractical as-is.

What I'd like to do is start typing, and get to a heading, such as controller, or accessory, then under each of those main headings I would have the parts that are classified as accessories, or possibly even a sub category to accessories (Cogs, Widgets, Sprockets) and have those part numbers under there...

In QuickBooks (Which I really don't care for, but has a usable example in this instance) the parts list is arranged like...

Control Panels
    PanelA
    PanelB
    PanelC
Accessories
    Panel A Accessories
        PAAccessory1
        PAAccessory2
    Panel B Accessories
        PBAccessory1
    GeneralAccessory1
    GeneralAccessory2

And so on.

Is there a way to do that in access?

[CJ_London]

And so on.

Is there a way to do that in access?

you have a number of examples which you seem to be ignoring. Suggest you try them and see if they fit the bill. If they don't come back with why, if they do, say thanks

[sstiebinger]

I'm sorry if I'm giving the impression that I'm ignoring the solutions provided....

What I saw in the other examples would be great if I knew the part number I'm looking for but my user may not know what the part number "GeneralAccessory1" is, so spelling it won't do him any good... but he know's it's an accessory, so if he spells "Accessory" and then has those accessories available to choose from, that's the functionality I'm looking for.

Like the quickbooks example above, that's in a single list, but I can just type access and it will jump right to accessories... It's sorted alphabetically by the category, then alphabetically under each category, etc... but not just 1 gargantuan list of parts, because I have thousands of parts from dozens of vendors in dozens of categories... it's not likely that I will know the part number I'm looking for... only the category of part it is.

Again, I appreciate the answers I've been given, and they're helping me to define my question, which I concede was not as specific as it needed to be.
I thank you for your patience in assisting with this problem.

[June7]

Options have been offered. You need to explore them. Build form and test.

[CJ_London]

You clearly haven't understood how either solution works, otherwise you wouldn't be saying

if I knew the part number I'm looking for but my user may not know what the part number "GeneralAccessory1" is

so if he spells "Accessory" and then has those accessories available to choose from, that's the functionality I'm looking for

Personally, quickbooks with perhaps a hundred codes is one thing. but I wouldn't want to be the user who types in 'accessory' and then has to scroll down 1000 items looking for the one they want.

To get what you want you need to learn about binary trees and nested sets so you know how to inset different levels. Without knowing your data structure, I can't advise further. You can also investigate the activeX listview control

good luck with your project

[ssanfu]

In a different life, I started creating an Impacts database, mostly for my own edification.

While the Quickbooks example might be what you want, it took someone to set up the tables.

Combo boxes have an AutoExpand Property that allows fills the text box portion of a combo box with a value from the combo box list that matches the characters you enter as you type in the combo box.

My example has a form with cascading combo boxes and a form with cascading list boxes. Maybe it will help...

[sstiebinger]

Ajax,

You're absolutely correct; I jumped to conclusions based on your description of the code and did not evaluate the code itself.
I apologize, it devalued your time in putting together the answer.

I'm working through how to use your suggestion with my data set, and ssanfu's forms for that cascading approach have given me some insight as well, although not what I originally intended.

Thank you again for your suggestions, and I'll definitely try harder not to jump to conclusions on this board. There is clearly a lot of talent here, given freely, and I want to be respectful of that.