Access 2003 VBA: Return only the index of the last item selected in a ListBox

0
votes

I will preface this with saying, this is my first time using listboxes and earlier posts were criticized for lacking detail. So, all help is greatly appreciated and I hope this is enough information without being overkill.

Currently, I have a listbox updating a junction table with an on click event (iterates through selected items and if they are not in the table it adds them). The list box is also updated by an option group (based on the option group value a query populates the list with the appropriate items and they are selected/highlighted based on the junction table). Also, when items are a "sub-category" the "category" is also selected. This functions perfectly until I ask it to do more...

Problem 1: I need to differentiate "categories" of items from each other. So, I have included a blank item to the list box to add a space between categories. When the blank items are present the listbox does not update the junction table properly and vice versa.

Problem 2: My users want to be able to deselect the "category" under certain circumstances. This is fine, just de-select the "category" after the "sub-category" is selected. However, the "category" is re-selected whenever the listbox is clicked again because it iterates through all entries.

Perceived solution for both problems: Return only the index of the item (de)selected and manipulate accordingly. Is this possible? If so, how?

OR: Should I take a different approach?

1
ms-accessvba
Is there some compelling reason why you've chosen to use an unbound listbox instead of the normal bound subform with combo boxes? – David-W-Fenton
Mostly it was just the first idea I thought to try... Other considerations: the query populates the listbox with a subset of items based on the particular users preferences. The data needs to be stored in the same spot, but all items are NOT applicable to all users. Is it still possible to replicate the multiselect property with subforms and comboboxes? – Eric D. Johnson
@David-W-Fenton Would you be so kind as to point me to a reference for creating the bound subform with combo boxes as you suggest. Thank you for your time. – Eric D. Johnson
I don't have any pointers to existing tutorials on this. When the rowsource of a combo/listbox is dependent on other data, you assign the rowsource at runtime when you arrive on the record that has the data that determines what should be in the rowsource. In a parent form/subform layout, you'd put that code in the OnCurrent of the parent record, setting the rowsource of the combo box in the subform, limited according to the restrictions applicable to the parent record. – David-W-Fenton

1 Answers

0
votes

One can think of a list or combo box as a low-resource sub-form. Conversely, one can use a sub-form to take care functions usually handled with a list or combo box.

I don't understand all that you're trying to do, but I do know that "up-sizing" to a sub-form is usually the answer when a simple control (for whatever reason) can't cover the need.