Wat is the best way to design a table and an input form to select data from a lookup table with 200 items listed, where the user can select 0, i, or several items ?
Novice
Windows 11 
Access 2021
Wat is the best way to design a table and an input form to select data from a lookup table with 200 items listed, where the user can select 0, i, or several items ?
Virtually Inert Person
Windows 10 Access 2016
What is best for you is not necessarily best for someone else. Given the number of list items (200) I think I would use 2 listboxes - one for the complete list and one to show the selected items. Might seem odd to some, but what it does is allow you to scroll through very large lists and not lose track of the items you've selected because they're visible in the second box as you scroll through the first listbox. If you change your mind about a selected item, it's also easy to remove it from the selected items without having to scroll in the first listbox to find it and unselect it. This requires a form with 2 listboxes and buttons that move or copy between the listboxes, so code is required.
The design of the table isn't really a factor - unless by lookup table (OK) you really mean a table with lookup fields (not really OK).
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
VIP
Windows 10 Access 2010 32bit
Selecting several items would require combobox to be bound to a multi-value field. Advise NOT to use multi-value fields.
Having a second listbox as suggested by Micron would be helpful but still have to scroll through 200 items (or type first letter of item to move down list).
Perhaps cascading comboboxes would be appropriate. They do have their own issues to be dealt with.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Virtually Inert Person
Windows 10 Access 2016
True 'nuff. But how did combos and lookup fields come into this? Or were you just providing your usual good advice?
Maybe FAYT (Find As You Type) combo boxes to jump through the lists?
As I see it, cascading combos won't eliminate the issue you mention about having to scroll through 200 items in the first list (if that's how it would be. Who knows, maybe only 50). Perhaps FAYT with cascading combos though? I venture to say that more info about the OP's design and/or goals would be helpful.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
VIP
Windows 10 Access 2010 32bit
Just brainstorming. As you noted, really need more info.
Combos don't require scrolling. If you have some idea of what the list contains, just typing and AutoExpand accomplishes a lot.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Lookup Table For Listbox Input
Posting Permissions
Reply With Quote