?
Solved

Listbox selection generates error

Posted on 2014-12-10
11
Medium Priority
?
143 Views
Last Modified: 2015-02-23
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
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40491753
Just a thought...does the same problem occur if you manually select 2 items or all items?
0
 

Author Comment

by:chtullu135
ID: 40491797
It does not occur if I manually select all items.  That's what's confusing me.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40491883
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40491902
Make sure the Multiselect property of the listbox is set to "extended"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40491906
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
 

Author Comment

by:chtullu135
ID: 40491913
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40492017
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 40492034
Try the function with a different list box to see if you get the same results.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40492036
Her is a simple sample, ...it works fine for me...
Database1.accdb
0
 

Accepted Solution

by:
chtullu135 earned 0 total points
ID: 40492042
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
 

Author Closing Comment

by:chtullu135
ID: 40625333
I found the solution as described
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question