Listbox ItemsSelected returns NULL item on first call - but not if debugging - not a DoEvent problem

I think you will find that (perhaps because) the listbox is unbound, the underlying values have not been updated. Perhaps a poor analogy but a bit like going round the supermarket and putting things in your basket - but they aren't yours until you've been through checkout

adding a Requery to your cmd code solves the problem

Code: 
 Dim strIN As String
    strIN = ""
    
    Dim ct As Control
    Requery
    For Each ct In Me.Controls
 
GregDataReno said:
Here's the truth test to prove that <listbox>.ItemData(i) is NULL yet a value is returned on a second call:
Click to expand...

I can not reproduce this (from your Post #11).

However, your basic methodology is flawed here anyway.

Instead of looping the whole listbox and testing for which items .Selected = True (which gets thrown with .ColumnHeads), you should just focus on the .ItemsSelected collection.

Loop that to get the indices of the selected items only (ignoring all the other items).

Here are a couple of generic functions to get the selected values from any listbox. You pass the listbox to the function, optionally a column index if you want to get the values in a different column from the bound column.

You can also optionally pass a callback function if you need to transform the data in the list (eg quote the values or format dates).

The first version collects the values in to an array, since sometimes you may need to work with an array of your values.

The second outputs a delimited string (or null if there are no selections). It uses the array version to build the list and then the Join() function to create the delimited list. By default it will add brackets around the whole value list, but this is optional.

Add the following functions to a standard module:
Code: 
Option Compare Database
Option Explicit

'***************************************************************************************
' Function  : SelArray
' DateTime  : 22-06-2005 12:34
' Author    : dm
' Purpose   : produces an array of values from a multi-select listbox
' Params    :
' LstPass   : the listbox object whose selections you want
' ColIdx    : index of the column in the listbox from which to collect the values. 0-based; default = bound column
' Callback  : the name of a public function to apply to each value, eg to add formatting, quote escaping etc
'***************************************************************************************
'
Public Function SelArray(LstPass As ListBox, _
                         Optional ColIdx As Integer = -1, _
                         Optional Callback As String) As Variant

  Dim arr() As Variant, i As Integer

  With LstPass
    If ColIdx < 0 Then ColIdx = .BoundColumn - 1
    If .ItemsSelected.Count > 0 Then
      ReDim arr(.ItemsSelected.Count - 1)
      For i = 0 To .ItemsSelected.Count - 1
        If Len(Callback) Then
          arr(i) = Application.Run(Callback, .Column(ColIdx, .ItemsSelected(i)))
        Else
          arr(i) = .Column(ColIdx, .ItemsSelected(i))
        End If
      Next i
      SelArray = arr
    Else
      SelArray = Null
    End If
  End With

End Function

'***************************************************************************************
' Function  : SelString
' DateTime  : 22-06-2005 12:34
' Author    : dm
' Purpose   : produces an string of delimited values from a multi-select listbox
' Params    :
' LstPass   : the listbox object whose selections you want
' ColIdx    : index of the column in the listbox from which to collect the values. 0-based; default = bound column
' Callback  : the name of a public function to apply to each value, eg to add formatting, quote escaping etc
' Delim     : Delimiter to separate the values. Default is a comma
' Wrap      : If True the result will be enclosed in brackets.
'***************************************************************************************
'
Public Function SelString(LstPass As ListBox, _
                          Optional ColIdx As Integer = -1, _
                          Optional Callback As String, _
                          Optional Delim As String = ",", _
                          Optional Wrap As Boolean = True) As Variant
                        
  Dim selVals As Variant
 
  SelString = Null
  selVals = SelArray(LstPass, ColIdx, Callback)
  If IsArray(selVals) Then
    SelString = Join(selVals, Delim)
    If Wrap Then
      SelString = "(" & SelString & ")"
    End If
  End If

End Function


Function SQLStr(vIn As Variant, _
                Optional blUseNull As Boolean = True, _
                Optional blWrap As Boolean = True, _
                Optional Delim As String = "'") As String
 
  Dim ret As String
 
  If Not IsNull(vIn) Then
    ret = Replace(CStr(vIn), Delim, Delim & Delim)
  Else
    If blUseNull Then
      ret = "NULL"
      blWrap = False
    End If
  End If
  If blWrap Then ret = Delim & ret & Delim
  SQLStr = ret
 
End Function

Then, in your form's cmdLoadQuery_Click() event you can use:
Code: 
Private Sub cmdLoadQuery_Click()
    On Error GoTo CleanError

  Dim selVals As Variant, strIn As String, strFilter As String
  Dim ct As Control

  For Each ct In Me.Controls
    If ct.ControlType = acListBox Then
      If ct.ItemsSelected.Count > 0 Then
        selVals = SelString(ct, , "SQLStr")
        If Not IsNull(selVals) Then
          strIn = "[" & ct.Recordset.Fields(0).Name & "] IN " & selVals
        End If
        strFilter = strFilter & IIf(Len(strFilter), IIf(oJoin = 1, " AND ", " OR "), vbNullString) & strIn
      End If
    End If
  Next ct
  MsgBox "Selected: " & strFilter

  Dim strWhere As String
  strWhere = strFilter
  With DoCmd
    .OpenQuery "vAllSuppliers"
    .SetFilter wherecondition:=strWhere
  End With

CleanExit:
    Application.Echo True
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    On Error GoTo 0
    Exit Sub

CleanError:
  
    Application.Echo True
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    MsgBox "Error " & VBA.Err.Number & " (" & VBA.Err.Description & ") in Function " & Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0) & " of Module " & Application.VBE.ActiveCodePane.CodeModule, VBA.vbExclamation
    Resume CleanExit

    Resume ' Never gets here - interactive debugging purposes only
End Sub
 
Last edited:
This is definitely a bug, and a very strange one. Because I can fix this with simply putting a debug.print statement in there. Only discovered that by accident.
Code: 
If .Selected(i) = True Then
    Debug.Print .ItemData(i)
    strIN = strIN & dblQuote(.ItemData(i)) & ","
 End If

That works. So my assumption was it had to be a timing issue. I added do events and it did nothing.

I tried this by assigning it a variable and it failed in the same way.
Code: 
dim x as string
...
If .Selected(i) = True Then
    x = .itemData(i)  ' same error
    strIN = strIN & dblQuote(x)) & ","
 End If

Then I assumed it had to be due to passing itemData(i) by reference to dblQuote. I changed that to byval and still the same error.

For sure you should be using the itemSelected, but that does not change the fact this is a bug and should work. In fact I rewickered this with itemsselected and it still fails without adding a debug.print.

Code: 
 Dim vItm As Variant
 For Each vItm In lst.ItemsSelected
    ' Debug.Print lst.ItemData(vItm)
     strIN = strIN & dblQuote(lst.ItemData(vItm)) & ","
 Next vItm

Never seen anything like this. I thought it had to do with the Column headers, but if you remove them the problem still repeats. I even tried replacing itemData with column(0,i) and same issue.

In all cases it appears the item is selected and in the itemselected collection, but the itemdata returns null.
The fact that debug.print before calling itemdata fixes it, tells me it is some kind of bug / timing issue.
 
Now this makes no sense, but I believe the cause may be the call to the recordset property.
If I replace this line with a hardwired line it works
Code: 
'strFilter = strFilter & "[" & lst.Recordset.Fields(0).Name & "] IN (" ' . "[CompanyName] IN ("
 strFilter = strFilter & "[CompanyName] IN ("

So I did this and it seems to work, suggesting that call to the recordset causes the bug.
Code: 
  bFirstPass = False
     Dim fldName As String
     Dim rs As DAO.Recordset
     Set rs = CurrentDb.OpenRecordset(ct.RowSource)
     fldName = rs.Fields(0).Name
     rs.Close
     strFilter = strFilter & "[" & fldName & "] IN (" ' . "[CompanyName] IN ("

I can say I have seen very strange repeatable issues when working with a control recordset.
 
I don't [*think* I] see the bug - what happens?
Click to expand...
I am on 365, most current Access version.
If I open the form and select an item then hit the query button I get an error in the call to .itemdata(i)
This still happens if I loop itemsselected.
Even though the item is selected and passes the if check, the call to itemdata(i) returns null.
Even more bizarre if you hover over it at the error it shows a value returned.
Code: 
For i = 0 To .ListCount - 1
    If .Selected(i) = True Then
             strIN = strIN & dblQuote(.ItemData(i)) & ","
      End If
 Next i

It is not an issue with the function because any assignment fails

Code: 
dim x as string
x = .itemdata(i)

but you can for some reason put a debug.print .itemdata(i) and it fixes the issue.
 
June7 said:
I tested the code I suggested with your file and it worked perfectly until I opened it this morning to try again. Will look at some more.
Click to expand...
No this fails in the exact same way as described. Or at least on my version of Access it does.

However Requerying the listbox as @CJ_London points out will solve the problem. I missed that. Not that doing that explains much.
However I am guessing like he said on this first time through the items are selected, but the item data is some how not yet assigned.
Now why calling debug.print would then fix it, I have no idea. Maybe somehow that causes a requery.
 
Last edited:
Sorry, I deleted my earlier post. Thought I did it quick enough no one would notice.
Testing again and don't know why I thought it was working. I did close and reopen form for testing code.
However, seems MajP has hit the nail.
I am using A2021.
 
I have seen slightly similar issues.
One is here with some strange disconnects between the recordset and rowsource when trying to clear a listbox where the recordset is "manually" assigned.
https://www.access-programmers.co.uk/forums/threads/how-do-you-clear-a-listbox.325210/page-3

I have some code in my find as you type comboboxes and listboxes that references the recordset property of the control. Often the recordset property is nothing until after the user selects a value even if it has a row source and values displayed. Same kind of issue where on the first attempt to select a value fails. In that case I do not think a requery solved the issue. I actually assign the recordset in code. Something like

Code: 
dim rs as dao.recordset
if ctl.recordset is nothing then
  set rs = currentdb.openrecordset (ctl.rowsource)
  set ctl.recordset = rs
end if

What is bizarre I tried that on this example and it did nothing, but the requery does.

Bottom line. Before accessing an unbound listbox itemdata / Column value, requery the listbox / combobox before the first use.
 
Last edited:
cheekybuddha said:
I can not reproduce this (from your Post #11).

However, your basic methodology is flawed here anyway.

Instead of looping the whole listbox and testing for which items .Selected = True (which gets thrown with .ColumnHeads), you should just focus on the .ItemsSelected collection.

Loop that to get the indices of the selected items only (ignoring all the other items).

Here are a couple of generic functions to get the selected values from any listbox. You pass the listbox to the function, optionally a column index if you want to get the values in a different column from the bound column.
...
Click to expand...
Thanks Cheeky, this is a significant contribution to solving my problem. I'll continue to work on what does appear to be a bug or perhaps a VBA codebase anomaly. I note further contributions here overnight - thanks to all.

(I did initially iterate only the .Selected array, but switched to stepping over .ItemsSelected in subsequent versions - can't remember why I changed it - prob through frustration)

(My code example is for a small testing database where in due course the data will end up in SQL Server and Fabric for deeper analysis.)

Cheers
 
Thanks Cheeky, this is a significant contribution to solving my problem. I'll continue to work on what does appear to be a bug or perhaps a VBA codebase anomaly. I note further contributions here overnight - thanks to all.
Click to expand...
@GregDataReno
I am assuming you did not read anything I wrote. As I pointed out looping the itemsselected is not a solution to the problem. I provided the code that will recreate this issue even if you loop the itemsselected. IT IS completely repeatable.

THE SOLUTION IS as previously mentioned :
Requery any unbound listbox or combobox before calling the itemdata or column property.
 
MajP said:
@GregDataReno
I am assuming you did not read anything I wrote. As I pointed out looping the itemsselected is not a solution to the problem. I provided the code that will recreate this issue even if you loop the itemsselected. IT IS completely repeatable.

THE SOLUTION IS as previously mentioned :
Requery any unbound listbox or combobox before calling the itemdata or column property.
Click to expand...
I read all responses, but that does not mean immediately adopting them, so perhaps not safe to assume things without evidence.

I said 'a significant contribution' because it informs a more durable solution. But the 'bug' remains, and I quote 'bug' because it's is more a code anomaly with, as you've demonstrated, a single line solution. But requiring a work-around in a language some 30 years old and with millions of users is really a 'bug' - in my opinion.

I'll will get to try out your suggestion shortly - now that household power has been restored and my UPS's are charging.

Cheers.
 
I said 'a significant contribution' because it informs a more durable solution
Click to expand...
Might be interested in this then.

I can build and combine many types of conrols to build filters with single lines of code for each control.
Here is all my code to combine both of the listboxes into a single filter.

Code: 
 Dim fltrSup As String
  Dim fltrCon As String
  Dim strWhere As String
  Dim andor As CombineFilterType
 
 
  If Me.oJoin = 1 Then andor = ct_And Else andor = ct_OR
  fltrSup = GetFilterFromControl(Me.lstSuppliers)
  fltrCon = GetFilterFromControl(Me.lstContacts)
  strWhere = CombineFilters(andor, fltrSup, fltrCon)
 
  MsgBox strWhere

Doe not matter how many controls I have or what data type or which column you want to use. Each additional control is a single line of code.
See discussion

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...
www.access-programmers.co.uk
 

Attachments

This is a question / rumination in hopes of further understanding this oddity.

On a bound form, you can navigate and have a Form_Current event after navigation. Even if you don't have any code in the Form_Current event, the form's bound elements update so we know the MSACCESS.EXE part of the _Current event occurred. On an unbound form, the _Current event doesn't happen and none of the other things like _BeforeUpdate & _AfterUpdate happen either. You can't even look at the recordset for an unbound form because the unbound form doesn't have that property.

We are talking about a control, not a form, but I would bet that MS doesn't have totally separate code for recordsets based on the object that owns them. I.e. recordset handing is the same for forms, controls, and lists. For a bound control, this NULL problem has not been reported. For bound controls, we don't see a control_Current event because Access doesn't expose an event corresponding to what happens when a .RowSource navigates and its properties update & become current. Yet after the fact, we can examine the control's recordset properties to see things like the recordset's positional information, and see that it changes.

Is it possible that because this case is unbound, the behind-the-scenes (non-exposed) events aren't happening either, analogous to unbound forms? Which perhaps is why a manual .Requery seems to fix the problem.

As previously noted, it is not a problem fixed by DoEvents - perhaps because there were no planned events to do? Since Access is not open-source, we don't know what happens with Debug.Print, but could guess that the debug support code, in order to find the current value of a control, forces a .Requery for that particular control. Yes, it is speculative, and maybe it doesn't make sense.

As a wild-eyed guess, try creating a control to act as the .ControlSource for your lists, even if you make that control invisible. See if that fixes the problem. If so, it would pinpoint that the problem is related to the difference between bound and unbound list-oriented controls that have both a .ControlSource AND a .RowSource. As to whether it is a bug or a feature, I leave that to posterity.
 
The_Doc_Man said:
This is a question / rumination in hopes of further understanding this oddity....

.RowSource. As to whether it is a bug or a feature, I leave that to posterity.
Click to expand...
I think you're definitely on the right track with this.

The fact that there's some kind of behind-the-scenes refresh between calls on the ListBox, or when running as debug, makes it nearly impossible to analyse, made worse by hovering the property returns a value - it's as if the hovering is doing a kind of requery (not the actual 'requery').

I note across the interwebs there is quite a bit of chat about anomalies when working with unbound ListBox controls - eg add/delete - and these issues probably have a similar origin, but it's the origin that's hard to reveal.

Time to update my "Tips and Tricks" to always requery unbound ListBoxes before calls to content.

Thanks all for contributions - community at its best.

Greg
 
June7 said:
don't know why I thought it was working.
Click to expand...
I think I know why.
When I download the file from #9, I don't have any problem. No error at all.

2025-04-21_11-26-09.gif



But if I open the form in design view, Edit something and save the form, then the error start showing.
I assume when you download and tested it first, it was ok. Then you checked the code, and may have saved the form.

Note : Even after saving, I see error only when I select "American Imports Inc." & "Old York Foods, Inc."
 
Last edited:
KitaYama said:
I think I know why.
When I download the file from #9, I don't have any problem. No error at all.

[snip]

But if I open the form in design view, Edit something and save the form, then the error start showing.
I assume when you download and tested it first, it was ok. Then you checked the code, and may have saved the form.

Note : Even after saving, I see error only when I select "American Imports Inc." & "Old York Foods, Inc."
Click to expand...
That's bizarre - and reflecting the problems we're having in this post.
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Multiselect listbox loop showing there isn't data
Replies
4
Views
603
pbaldy
pbaldy
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
221
Mike Krailo
Mike Krailo
mloucel
after Menu click, open a form and that form opens a specific one.
Replies
6
Views
426
mloucel
mloucel
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
1,123
Pat Hartman
P
B
Filtering from a multiselect listbox with other filters
Replies
5
Views
787
bonzitre
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom