set limit of selections of a multi select list box

[pavlos]

Hi,

I use the following code to implement a multi select list box. I tried to modified it (by using a loop) so that the user cannot select more than 5 items in the list box and probably to take a message the limit of selections is reached.

Any help?


Private Sub List15_AfterUpdate()
Dim rst As Recordset
Dim varItem As Variant

'--- empty temp table in preparation to receive new requests
CurrentDb.Execute ("DELETE * from _temp;")

'--- open the _temp table
Set rst = CurrentDb.OpenRecordset("_temp")

'--- loop through all selected clients in the list box
' adding their names to the _temp table
For Each varItem In List15.ItemsSelected
rst.AddNew
rst!client = List15.ItemData(varItem)
rst.Update
Next varItem

'--- close the table
rst.Close
Set rst = Nothing

'--- refresh the form or could put a docmd to run a report, open a form, etc
Me.Refresh
End Sub

 

[WayneRyan]

Pavlos,

Private Sub List15_AfterUpdate()
Dim rst As Recordset
Dim varItem As Variant
Dim i As Integer

i = 1

'--- empty temp table in preparation to receive new requests
CurrentDb.Execute ("DELETE * from _temp;")

'--- open the _temp table
Set rst = CurrentDb.OpenRecordset("_temp")

'--- loop through all selected clients in the list box
' adding their names to the _temp table
For Each varItem In List15.ItemsSelected
   If i < 6 Then
      rst.AddNew
      rst!client = List15.ItemData(varItem)
      rst.Update
   End If
   i = i + 1
   Next varItem

'--- close the table
rst.Close
Set rst = Nothing

'--- refresh the form or could put a docmd to run a report, open a form, etc
Me.Refresh
End Sub

Wayne

 

[pavlos]

Thanks Wayne, it works!
Indeed, any selection after the fifth is not registered in table _temp, however, it is still highlighted in the list box and this can confuse. How can I modify the multi select list box so that it does not to allow more than 5 selections (let’s say out of 20) at the same time?

 

[WayneRyan]

Pavlos,

You can use the OnClick event of your Listbox. Use the same code, but don't
put them in the table, and if i > 5 then:

Show a messagebox
Exit Sub

Wayne

 

[pavlos]

Thanks again Wayne,

I did use the code in OnClick event, the list box still allows (highlights) more than 5 items to be selected.

Also, you suggest not to put the selected names in the table. why? I use these names to plot a chart.