• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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
0
Juan Velasquez
Asked:
Juan Velasquez
  • 4
  • 4
  • 3
1 Solution
 
rspahitzCommented:
Just a thought...does the same problem occur if you manually select 2 items or all items?
0
 
Juan VelasquezAuthor Commented:
It does not occur if I manually select all items.  That's what's confusing me.
0
 
rspahitzCommented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jeffrey CoachmanMIS LiasonCommented:
Make sure the Multiselect property of the listbox is set to "extended"
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Juan VelasquezAuthor Commented:
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

0
 
Jeffrey CoachmanMIS LiasonCommented:
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...
0
 
rspahitzCommented:
Try the function with a different list box to see if you get the same results.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Her is a simple sample, ...it works fine for me...
Database1.accdb
0
 
Juan VelasquezAuthor Commented:
lst.MultiSelect  is checking that the list box is set to multiselect.
At any rate, I found the problem and have added a fix. When the report is generated, each list box is checked to see it has selected items.  I added the line strYearValue = mcstrFIELD_VALUE which will be set when that list box has selected items.  I then pass this variable to the add to the addtowhere function, where it is used to tell the function that there are values to be passed.  Prevously I had been sending the listbox.column(0) .  That worked when I was manually selected each item, however when I selected all the items via code  listbox.column(0) would return null.  And that's why it was failing

Call AddToWhere(strYearValue, "lstYear", strYearCriteria, intArgCount)

Private Sub AddToWhere(FieldValue As Variant, FieldName As String, myCriteria As String, ArgCount As Integer)
        
    If FieldValue <> "" Then    'Check each field to see if there is a value entered
        If ArgCount > 0 Then    'Checks to see if more than one field has a value
            myCriteria = " and " & myCriteria '& " and "  'If more than one field has value add an 'AND' clause
        End If
        mstrMyCriteria = mstrMyCriteria & myCriteria
        ArgCount = ArgCount + 1
    End If
    
    
End Sub

Open in new window


If HasSelectedItems(Me.lstYear) Then
        'Determine if any years have been selected
        For Each varItem In Me!lstYear.ItemsSelected
            strYearCriteria = strYearCriteria & "," & Me!lstYear.ItemData(varItem) & ""
        Next varItem
        strYearCriteria = Right(strYearCriteria, Len(strYearCriteria) - 1)
        strYearValue = mcstrFIELD_VALUE
    End If

Open in new window

0
 
Juan VelasquezAuthor Commented:
I found the solution as described
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now