ListBox Doesn't Show Selected Values

[TheSearcher]

Hi All,

I use the following code in many different applications to display what has already been selected from a listbox while opening a form. It always works perfectly.
However, in this particular instance, all listbox values remain unselected.
This code is on the On Open event of my form. Can anyone see what I'm doing wrong?

'*** Populate the listbox
sql1 = "SELECT tbl_ST_DDD_Modalities.Modalities "
sql1 = sql1 & "From tbl_ST_DDD_Modalities "
sql1 = sql1 & "ORDER BY tbl_ST_DDD_Modalities.SortBy;"
lst_Modalities.RowSource = sql1
lst_Modalities.Requery

'*** Grab values to display in listbox
sql1 = ""
sql1 = "SELECT tbl_Note_ST_DDD_Modalities.Modalities "
sql1 = sql1 & "FROM tbl_Note_ST_DDD_Modalities "
sql1 = sql1 & "WHERE tbl_Note_ST_DDD_Modalities.Note_Id = " & Globals.glb_NoteID
Set rs = db.OpenRecordset(sql1)

''*** Display proper Listbox values from the recordset *************************

Dim varitm As Variant
Dim SelectedID As String
Dim i As Integer
 
'loop the recordset
Do While Not rs.EOF
    SelectedID = rs("Modalities")
      
    For i = 0 To (lst_Modalities.ListCount - 1)
        If lst_Modalities.Column(0, i) = SelectedID Then
            lst_Modalities.Selected(i) = True
        End If
    Next i
    rs.MoveNext
Loop
rs.Close

 

[MajP]

How about try to debug?

For i = 0 To (lst_Modalities.ListCount - 1)
      
       Debug.print "List Value: " & lst_Modalities.Column(0, i) & "Seleced: " & selectedID
      
       If lst_Modalities.Column(0, i) = SelectedID Then
            lst_Modalities.Selected(i) = True
        End If

 

[TheSearcher]

@MajP - 2 problems: The first match "Referential signaling" doesn't get sent to True. The second issue is that "Referential signaling" is one of 18 possible options - but it never gets to any of the other options. It appears as though the NEXT isn't working.

List Value: Referential signalingSeleced: Referential signaling
List Value: Propositional/expressive behaviorsSeleced: Referential signaling
List Value: Non-verbal head raises/head loweringSeleced: Referential signaling
List Value: Implementation of environmental real objectsSeleced: Referential signaling
List Value: Eye gaze/contactSeleced: Referential signaling
List Value: Letter boardSeleced: Referential signaling
List Value: Reactive/responsive behaviorsSeleced: Referential signaling

 

[TheSearcher]

Correction - The NEXT is working. It sems to be the rs.movenext that's the issue.

 

[MajP]

To verify this match is not being Set?

List Value: Referential signaling Selected: Referential signaling

If true, a possibility is that there are spaces on one of these strings. You can add

  If trim(lst_Modalities.Column(0, i)) = trim(SelectedID) Then

I would also double check that the If is not catching the match or if it is being caught but the list is not updating

For i = 0 To (lst_Modalities.ListCount - 1)
     
       Debug.print "List Value: " & lst_Modalities.Column(0, i) & "Seleced: " & selectedID
     
       If trim(lst_Modalities.Column(0, i)) = trim(SelectedID) Then
            debug.print "Match Found "
            lst_Modalities.Selected(i) = True
        End If

 

[MajP]

one of 18 possible options - but it never gets to any of the other options. It appears as though the NEXT isn't working.

Are you positive?
Prove it by

Set rs = db.OpenRecordset(sql1)
debug.print Sql1

Now drop that SQL into Query window and verify it returns what you think.

 

[TheSearcher]

@MajP - Thank you for your help. I will try everything you suggested. I just reran the code. The code seems to be looping as it should but the matches don't get selected. There are 18 options and two that have been selected. See below.

List Value: Tactile selection Seleced: Tactile selection
List Value: Referential signaling Seleced: Tactile selection
List Value: Propositional/expressive behaviors Seleced: Tactile selection
List Value: Non-verbal head raises/head lowering Seleced: Tactile selection
List Value: Implementation of environmental real objects Seleced: Tactile selection
List Value: Eye gaze/contact Seleced: Tactile selection
List Value: Letter board Seleced: Tactile selection
List Value: Reactive/responsive behaviors Seleced: Tactile selection
List Value: Head shakes/nods Seleced: Tactile selection
List Value: Client generated handwriting Seleced: Tactile selection
List Value: Facial expressions Seleced: Tactile selection
List Value: Retrieval Seleced: Tactile selection
List Value: Vocalizations Seleced: Tactile selection
List Value: Implementation of assistive technology Seleced: Tactile selection
List Value: ASL Seleced: Tactile selection
List Value: Real object manipulation Seleced: Tactile selection
List Value: Verbalizations/approximations Seleced: Tactile selection
List Value: Implementation of visual/tactile manipulatives Seleced: Tactile selection

List Value: Tactile selection Seleced: Referential signaling
List Value: Referential signaling Seleced: Referential signaling
List Value: Propositional/expressive behaviors Seleced: Referential signaling
List Value: Non-verbal head raises/head lowering Seleced: Referential signaling
List Value: Implementation of environmental real objects Seleced: Referential signaling
List Value: Eye gaze/contact Seleced: Referential signaling
List Value: Letter board Seleced: Referential signaling
List Value: Reactive/responsive behaviors Seleced: Referential signaling
List Value: Head shakes/nods Seleced: Referential signaling
List Value: Client generated handwriting Seleced: Referential signaling
List Value: Facial expressions Seleced: Referential signaling
List Value: Retrieval Seleced: Referential signaling
List Value: Vocalizations Seleced: Referential signaling
List Value: Implementation of assistive technology Seleced: Referential signaling
List Value: ASL Seleced: Referential signaling
List Value: Real object manipulation Seleced: Referential signaling
List Value: Verbalizations/approximations Seleced: Referential signaling
List Value: Implementation of visual/tactile manipulatives Seleced: Referential signaling

 

[TheSearcher]

@MajP - As you suspected, there was a space at the end of the strings. That's why it wasn't working. All is good now. Thank you very much for your help!

 

[MajP]

1. You may want to run an update query to clean up your data by setting the new value to trim(old value)
2. In your data entry trim the data. Could do int the controls after update.
3. If these values are being imported from something then do the trim on the import process

You caught it here, but that problem could raise it head at other places.

 

[TheSearcher]

Good advice as always. I will do that. The problem was in my Insert query. A space where it shouldn't be.

For Each varitm In lst_Modalities.ItemsSelected
        sql1 = "Insert into tbl_Note_ST_DDD_Modalities (Note_Id, Client_Id, Client_Name, Modalities) "
        sql1 = sql1 & "select " & Globals.glb_NoteID & ", '" & txt_Client_Id & "', '" & txt_Client_Name & "', '" & lst_Modalities.ItemData(varitm) & " '"
        DoCmd.SetWarnings False
        DoCmd.RunSQL sql1
        DoCmd.SetWarnings True
Next varitm

 

[MajP]

Well that makes the fix a whole lot easier.