Looking for suggestion on how to collect a variety of multiple selections from a list (1 Viewer)

[Cark]

I really don't know where to start with this, but will try to explain the situation as best I can below, but by all means ask me as many questions as it requires to get us looking into a nice solution:

Goal:
To collect a set of "Position Selections" for each Part Number in my database.

What does each bit of data look like?:
Part Number = Alphanumeric typically below 32 characters e.g SDFWER-2342
Positions = Alphanumeric with punctuation like full-stops or dashes, varying from between 1 and 12 characters e.g No.2

What does the overall data look like?:
Each Part Number will have at least one entry for Position, however there will be plenty of cases where a Part Number will have multiple Positions selected.

How many Positions are there in the list?

The attached Excel file is an export of all the current positions being used (1,703 ), however going forward I will want to refine this list down to the most commonly used ones. I envisage there being approximately 300 commonly used ones.

How will I want the end user to use the Form?:

I will want the user to enter a Part Number and then be able to make a selection from the list of Positions and be able to multi-select Positions into a Selected bucket etc.

Is any of this possible? I currently don't have any examples which are anywhere near close to working and I think would just cause confusion, but if you want me to mock something up, just ask. The attached ListboxExamples database has 3 Pick Lists that I think could provide a start point for ideas, however with 300 entries in a list, it could be messy and slow for a user to scroll through the 300 entries. Is there any way to add a search feature / autocomplete?

 

[NauticalGent]

Look into what is called cascading comboboxes...

 

[isladogs]

Actually you can't use cascading combos for the two fields as a combo only allows a single selection.
However the solution needed is very similar.
Use a combo for the PartNumber followed by a multi-select listbox for the Position filtered to the selected PartNumber.

If you are concerned about the number of items in the PartNumber combo, you could either break it down into 'parts' using cascading combos for that. See this example for selecting a UK postcode from a list of around 2.6 million using 5 cascading combos https://www.access-programmers.co.uk/forums/showthread.php?t=302126
….or use a 'search feature' to filter the records displayed e.g. see http://allenbrowne.com/ser-32.html

 

[Cark]

I get how the combo box would work for the "Part Number" aspect, however I am struggling to get my head around how I would then do the "Position" aspect? It might be that I didn't explain it well in the beginning.

When initially setting up a new "Part Number" in the database, I will want the user to pick somewhere between 1 and typically 10 positions from the big master list of "Positions". This master list will need to be available to be picked from on all "Part Numbers". I will then want these 10 position selections to be saved against the particular "Part Number".

Is a Combo Box and Multi-Select List Box still the best option?

 

[isladogs]

Yes
You will need code to loop through the selections made in your listbox and then save each to your table as a separate record.
You could use code similar to that shown for a report in http://allenbrowne.com/ser-50.html
Hope that helps

 

[Cark]

Thanks I will have a bash at it.