Solved

Listbox selection generates error

Posted on 2014-12-10
11
141 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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