Solved

Listbox selection generates error

Posted on 2014-12-10
11
139 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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