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
Solved

Listbox selection generates error

Posted on 2014-12-10
11
138 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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 …

856 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