Extended Listbox

[padrepio]

Hi

I'm using the following code and it works initially when a few records in a listbox (ListFrom) are selected but then I'm greeted with 'invalid use of null' when I run it a second time. The strange thing is that when I set the Multi Select property of the listbox to 'Extended', the code works!!!

Other functions are called in the procedure that sets a field status to false and the row source filters out the list, I don't think that it's causing the issue.

Dim v As Variant
Dim strWhere As String
 
For Each v In Me.ListFrom.ItemsSelected
       strWhere = Me.ListFrom.ItemData(v)
               Next

The big problem is that I need the multi select property to be set to 'Simple' - is there any way around this?

 

[pbaldy]

What event is that in? I can't see why it would work once and not the second time. You realize that your code only populates the variable with the last item selected, not all of them? Here's how it would look:

http://www.baldyweb.com/multiselect.htm

 

[Galaxiom]

.. it works initially when a few records in a listbox (ListFrom) are selected but then I'm greeted with 'invalid use of null' when I run it a second time.

strWhere = Me.ListFrom.ItemData(v)

This code line will fail as soon as a Null is encountered in the bound column of a selected row.

The strange thing is that when I set the Multi Select property of the listbox to 'Extended', the code works!!!

There is no difference in ItemsSelected between Simple and Extended.

I expect the variation is because you are making different selection each time you test. The different behaviour would be a coincidence.

 

[arnelgp]

its unfortunate (i think itz a perpetual bug)
that the succeeding inspection to SelectedItems,
it is being reset.

what i would suggest then is to use Collection object
to save the List items that have been selected.

whether you use Simple, Extended does not matter here.

put this code in the ListFrom listbox's AfterUpdate Event:

Dim colItems As Collection
Dim strWhere As String

Private Sub ListFrom_AfterUpdate()

    Dim varItem As Variant
    Dim i As Integer
    
    ' initialise the collection first
    If (colItems Is Nothing) Then _
        Set colItems = New Collection
        
    If colItems.Count = 0 Then
        ' there is no data yet in the collection, so add it
        colItems.Add Me.ListFrom.ItemData(Me.ListFrom.ListIndex), Me.ListFrom.ListIndex & ""
        
    Else
        ' we have items in collection.
        ' check if the list item is already
        ' in the collection, therefore, the
        ' user want to remove it.
        On Error Resume Next
        varItem = colItems.Item(Me.ListFrom.ListIndex & "")
        If Err.Number <> 0 Then
            ' list item is not in collection so we add it
            colItems.Add Me.ListFrom.ItemData(Me.ListFrom.ListIndex), Me.ListFrom.ListIndex & ""
            
        Else
            ' list item is already in the collection, so we remove it.
            colItems.Remove Me.ListFrom.ListIndex & ""
            
        End If
        On Error Goto 0
    End If
    
    ' reset strWhere
    strWhere = vbNullString
    
    ' check if there is any items in the collection
    If colItems.Count > 0 Then
        ' update the strWhere
        For i = 1 To colItems.Count
            strWhere = "," &  colItems.Item(i) & strWhere
        Next
        ' remove the leading comma(,) from the string
        strWhere = Mid(strWhere, 2)
    End If
    ''''''''''''''''''''''
    ' this is a test portion
    ' uncomment it to see the result
    '''''''''''''''''''''''
    'For i = 1 To colItems.Count
    '    Debug.Print colItems.Item(i)
    'Next
    'Debug.Print "--------------------------"
End Sub

remember to set to Nothing the collection object
when you unload the form:

Private Sub Form_Unload()
Set colItems = Nothing
End Sub

 

[pbaldy]

its unfortunate (i think itz a perpetual bug)
that the succeeding inspection to SelectedItems,
it is being reset.

I can't replicate that problem, repeated tests of the listbox continue to return the selected items as expected. How do you replicate this "bug"?

 

[arnelgp]

my mistake. i was thinking the other post when the selected items are being reset only to find iut that there is code that is running ti alter and add items to the list.

si maybe this us the case again.

 

[padrepio]

Apologies for the tardy response, thanks for all the feedback on what I now realise was quite limited information - I usually post after several hours of attempts and fatigue takes over.

I've tested the code, run from a command button, on approximately 7 items within the listbox and I can categorically state that the same items are selected, consistently in the same order and only the 'Extended' Multi Select property ensures that the error doesn't arise.

As I’ve said before, I require the ‘Simple’ Multi Select Option. I’ve thrown all the pertinent code together (had been referencing functions within the main command button onclick event).

Slight variation on previous code posted, offending line below is:

strWhere3 = Me.ListFrom.ItemData(v3)

Have tried MajP's suggestion, works for Extended but not Simple Multi-Select property so code certainly remains useless. Have yet to try arnelgp's suggestion as I was holding out for an adjustment on the existing. Any further thoughts?

Dim dbs2 As DAO.Database
Dim sStudentName, sStatusOut As String
Dim v2 As Variant
 
Set dbs2 = CurrentDb()
 ‘Following section inserts records into table (no issues here) 
For Each v2 In Me.ListFrom.ItemsSelected
       sStudentName = Me.ListFrom.Column(2, v2)
       sStatusOut = -1
      
       dbs2.Execute "INSERT INTO StudentSignInDetailsTBL (nameofstudent,statusout)VALUES ('" & sStudentName & "', '" & sStatusOut & "')"
   
Next 
Set dbs2 = Nothing
 
‘next stage, filtering out ListFrom listbox – the problem
 
    Dim dbs As DAO.Database
Dim strWhere3 As String
Dim v3 As Variant
Set dbs = CurrentDb()
 
For Each v3 In Me.ListFrom.ItemsSelected
       strWhere3 = Me.ListFrom.ItemData(v3)
       dbs.Execute "UPDATE tbstudents SET StatusOut = True WHERE studentid=" & Me.ListFrom.ItemData(v3)
Next
  Set dbs = Nothing
 
 Me.ListTo.Requery
    Me.ListFrom.Requery
    Me.Requery

 

[pbaldy]

Can you attach the db here with instructions on how to recreate the error? As Galaxiom mentioned previously that line would error with a Null in the listbox, and I can't think of any way that simple and extended would differ when it comes to items selected.

 

[padrepio]

Will do Paul, won't be until tomorrow though. Got to get some sleep

 

[padrepio]

Ok, so I can't sleep. Here goes:

1. Open SearchPupilSignOutForm, the ListFrom listbox Multi Select property is already set to Extended and the code should run fine.
2. Select all items in ‘ListFrom’ from the top down with the exception of Lee Rixon.
3. Click on the command button (101) – I’ve inserted Msgbox’s at different places to help highlight what is going on.
4. You should be left with Lee Rixon on the list – select his name and click Cmd101 and his name should also then transfer to the other listbox.
5. Now for the problem, change the multi select property of ListFrom to Simple
6. Reset the data by opening tbPupils table and unticking the StatusOut boxes
7. Delete all records in the PupilSignInDetailsTBL table.
8. Re-open SearchPupilSignOutForm and you should have the same contents in the listboxes as at 1. above.
9. Follow steps 2, 3 ad 4 above and the problem should arise at step 4.

It’s probably something silly that I’m doing wrong, I’ve only just noticed that some records are being transferred into the PupilSignInDetailsTBL with null values in the ‘NameofPupil field when the code goes awry.

 

[pbaldy]

Here's your key, from Help:

If the MultiSelect property is set to Extended, requerying the list box clears any selections made by the user.

As is, there are, for lack of a better term, phantom selections being held onto (somebody can probably explain that better than I). Your solution is to clear selections after executing your SQL. I think you can do your work in one loop, then clear in the second. Try this quick and dirty attempt:

    Dim v3                    As Variant
    Dim dbs2                  As DAO.Database
    Dim sPupilName, sStatusOut As String
    Dim v2                    As Variant

    If Me.ListFrom.ItemsSelected.Count = 0 Then
        MsgBox "A record must be selected"
        Exit Sub
    End If

    Set dbs2 = CurrentDb()

    For Each v2 In Me.ListFrom.ItemsSelected

        sPupilName = Me.ListFrom.Column(2, v2)
        sStatusOut = -1

        dbs2.Execute "INSERT INTO PupilSignInDetailsTBL (nameofpupil,statusout)VALUES ('" & sPupilName & "', '" & sStatusOut & "')"

        dbs2.Execute "UPDATE tbpupils SET StatusOut = True WHERE pupilid=" & Me.ListFrom.ItemData(v2)
    Next v2

    For Each v3 In Me.ListFrom.ItemsSelected
        Me.ListFrom.Selected(v3) = False
    Next v3

    Me.ListTo.Requery
    Me.ListFrom.Requery
    Me.Requery

    Set dbs2 = Nothing

 

[padrepio]

Thank you Paul, I'll give this a shot after work

 

[pbaldy]

No problem. Worked for me in a brief test using the steps in post 11.

 

[padrepio]

Thanks Paul, excellent result

 

[pbaldy]

Happy to help!