List: Multiple Select without Control Key and counter of selected items

Can I select multiple lines in list field without holding control key?

I also need reset button and if possible (not that important but would be nice) counter of selected items
IlijaKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hi IlijaK,

> Can I select multiple lines in list field without holding control key?

If MultiSelect = Extended...
Well, you could hold down the Shift key, but that would select all the items between "point A" and "point B", rather
than the Ctrl key allowing you to pick and choose individual items.  Other than that, yes, I think you are stuck with
Ctrl.

If MultiSelect = Simple
Items are selected by clicking on them or with the Space bar

> I also need reset button

Me!ListBoxName.Requery

> and if possible (not that important but would be nice) counter of selected items

Put an unbound textbox on your form, with controlsource of
=SelectedInListbox("NameOfForm", "NameOfListbox")

Add this code to a regular (NOT form or report) module:

Function SelectedInListBox(FormName As String, LbName As String)

    SelectedInListBox = Forms(FormName)!Controls(LbName).ItemsSelected.Count

End Function

Will work in Access 2002 and 2003; not sure about earlier versions...

Regards,

Patrick
IlijaKAuthor Commented:
Patrich, thank you for the quick reply:

If MultiSelect = Simple works great! Thank you

But I had trouble with reset button - nothing happens (all the selected items remain selected)
of course I changed ListBoxName to the proper name

and I need a bit more help on counter :(

Regards,
Ilija
Patrick MatthewsCommented:
Ilija,

I'll have to look further into "reset".

To get your counter:

1) Add the function to a VB module (hit Alt+F11 to get to the VB Editor; select Insert|Module from the menu;
paste my code in the module)

2) Go to your form, add a textbox, and rightclick it to bring up properties.  Go to the controlsource (on data
tab), and enter (assuming form name is "MyForm" and the listbox is named "MyList"):
=SelectedInListbox("MyForm", "MyList")
That should give a count of the number of items selected

Patrick
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

harfangCommented:
Hello,

It's easier that than, in fact.

To get a "selection count", create a textbox with this control source:

    = lstTheList.ItemsSelected.Count

To reset the selection in the list, use this code:

Private Sub cmdResetList_Click()
    Dim varI
    For Each varI In lstTheList.ItemsSelected
        lstTheList.Selected(varI) = False
    Next varI
    Me.Recalc   ' needed for the text box with selection count...
End Sub

Hope this helps (and sorry to barge in, Patrick)
(°v°)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
Not at all, Markus.  Simpler = better :)

Patrick
IlijaKAuthor Commented:
Thanks Patrick and Harfang

you solved my problem - I'll have to split the points :)


BTW Harfang if I dont trouble you too much how to modify the code not to reset the selected but to selecta all items in the list

of course lstTheList.Selected(varI) = True does not work
harfangCommented:
Ah, that would be:

Private Sub cmdSelectAll_Click()
    Dim lngI As Long
    With lstTheList
        For lngI = 0 To .ListCount - 1
            .Selected(lngI) = True
        Next lngI
    End With
End Sub

The loop in the previous code sample only loops through selected items, this one loops through all items.

Cheers!
(°v°)
IlijaKAuthor Commented:
Tried the code it works but in 10min on 3GHz computer it didn't finish going through the list (10 000 items) :(
any way to speed it up? or open the list already selected?
 I'll increase the points of course
Patrick MatthewsCommented:
Ilija,

I am assuming you are using the ListBox to help users make selection criteria for something,
probably a report or query.

If you have that many items in your list, rather than "select all" that way, I would suggest instead that
you have an Option Group that asks whether to "select all" or to "select one or more".

If "select all" is chosen, disable the ListBox, and let your query use a simplified query.  If not, then
enable the ListBox, and use the more complicated WHERE clause.

Is this at all helpful?

Patrick
harfangCommented:
Sorry, there is no way to speed it up.

The problem is that you are using the wrong tool. There isn't anything useful you can possibly do with a selection among 10000 items in a listbox control. For instance, you can't save the selection over sessions, and with such a list, the user might need a couple of days to finalize her selection. Really, navigating and selecting items in such a list is a user's nightmare.

Incidentally, it's also a programmers nightmare, are you are starting to discover. Will you later have to write a query based on the selection of 8342 items in that list? It will be a looooooong query to write, execute and use...

As a rule of thumb: if you have more that 100 items, it's no longer suited for a listbox.

Alternative:

Add a yes/no field to the underlying table and display the list as a subform. Queries to "select all" and "deselect all" will run in a flash (simple update queries) and you have many more options:
* filter/sort the list in various ways to help the selection
* tools to select items by criteria
* collaborate with other users to create the selection

Also, this type of selection will be actually saved in the field, so that one can continue working on it the next day.

If you care to explain a little bit about the setup and purpose of the list, we might have other ideas.

Cheers!
(°v°)
IlijaKAuthor Commented:
Thank you very much for your thorough and fast replies!
IlijaKAuthor Commented:
I can manage select all by using MultiSelect = Extended and shift key
it will suffice for now :)
harfangCommented:
I'm glad it worked out. Good luck with your application.
(°v°)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.