Link to home
Start Free TrialLog in
Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America

asked on

Listbox selection generates error

Hello,

I have an odd problem.  I've created a form that users can use to generate criteria for queries.  Basically I am constructing a where clause. based on the selections.  It works fine except when I select all the items in a list box by checking the Select All button for that list.  When I  select all the items individually, the first argument in the AddToWhere procedure has a value.  When I use the Select All check box, the first argument has a null value, even though all the items in that list have been selected via the following code .  This causes an error to be thrown.

Public Function SelectAll(lst As ListBox) As Boolean
    ' Comments:
    ' Params  : lst
    ' Returns : Boolean
    ' Modified:
    
    On Error GoTo PROC_ERR
    
    Dim lngRow As Long
  
    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next
        SelectAll = True
    End If
    
PROC_EXIT:
    Exit Function
    
PROC_ERR:
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, Me.Name & ".SelectAll"
    Resume PROC_EXIT
    
End Function

Open in new window

indiv

Call AddToWhere(Me.lstYear.Column(0), "lstYear", strYearCriteria, intArgCount)

Open in new window

ReportCriteria.jpg
Avatar of rspahitz
rspahitz
Flag of United States of America image

Just a thought...does the same problem occur if you manually select 2 items or all items?
Avatar of Juan Velasquez

ASKER

It does not occur if I manually select all items.  That's what's confusing me.
Try changing the "For" in SelectAll from
For lngRow = 0...
to
For lngRow = 1...

to see if it makes a difference...I would expect the first item to get skipped.
Avatar of Jeffrey Coachman
Make sure the Multiselect property of the listbox is set to "extended"
This works fine for me when the multiselect property is set to extended...:

Dim i As Integer

For i = 0 To Me.YourListBox.ListCount - 1
    Me.YourListBox.Selected(i) = True
Next
The multiselect property is set to "extended and the code I am using to select all the items in the listbox is

  Public Function SelectAll(lst As ListBox) As Boolean
    ' Comments:
    ' Params  : lst
    ' Returns : Boolean
    ' Modified:
    
    On Error GoTo PROC_ERR
    
    Dim lngRow As Long
    
    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True            
        Next
        
        SelectAll = True
    End If
    
PROC_EXIT:
    Exit Function
    
PROC_ERR:
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, Me.Name & ".SelectAll"
    Resume PROC_EXIT
    
End Function

Open in new window

The name of your listbox seems to be the same as the name of the property: "Multiselect"

Please change the name to something like
Me.Customer
me.

But please keep it simple..
Just try code like this  (or any of the other Experts posts) on a button on the form :

Dim i As Integer
 For i = 0 To Me.YourListBox.ListCount - 1
     Me.YourListBox.Selected(i) = True
 Next

Again, ...this very simple code works fine for me to select all the items in a listbox...
Try the function with a different list box to see if you get the same results.
Her is a simple sample, ...it works fine for me...
Database1.accdb
ASKER CERTIFIED SOLUTION
Avatar of Juan Velasquez
Juan Velasquez
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I found the solution as described