First item selected not showing up in text string

[Gina Maylone]

Hello all!



This is a continuation of a previous post. I have an unbound listbox "Needs", and in the onclick event have the following code which works great if I have more than one item selected, but if I only have one item selected, it does not put it in the text box "ServiceText". Thoughts? TIA!

Private Sub Needs_BeforeUpdate(Cancel As Integer)
On Error GoTo errHandler
Dim ctl As Control
Dim strStart As String, strEnd As String
Dim aryList As String
Dim varSelected As Variant

  If Me.ServiceText = "" Then
  Me.ServiceText = Me.csvtext
  End If
  
Set ctl = Me!Needs
'  Me.Needs = Me.Needs.ItemData(0)
  Select Case ctl.ItemsSelected.Count
   Case Is = 0
      MsgBox "You haven't selected anything"
      Me.ServiceText = ""
      
   Case Is = 1
      aryList = ctl.Column(1, ctl.ItemsSelected(0))
   Case Is > 1
      For Each varSelected In ctl.ItemsSelected
          aryList = aryList & ctl.Column(1, varSelected) & ", "
      Next varSelected
      aryList = Left(aryList, Len(aryList) - 2)
      strStart = Left(aryList, InStrRev(aryList, ",") - 1)
      strEnd = Mid(aryList, InStrRev(aryList, ",") + 2)
      aryList = strStart & " and " & strEnd
      Me.ServiceText = aryList
      
End Select
Exit Sub
errHandler:
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub

[ranman256]

There is a LOT of code here. Usu you have a list box of values and the user picks one and it is assigned to the field.
A bound list box plugs the value into the field using the combo bound column.
NO code.

what does all your code do that the above method cant?

[JamesDeckert]

this line is only in the case is >1
Me.ServiceText = aryList
it should also be in the
case is = 1

[Gina Maylone]

There is a LOT of code here. Usu you have a list box of values and the user picks one and it is assigned to the field.
A bound list box plugs the value into the field using the combo bound column.
NO code.

what does all your code do that the above method cant?

Yea, I need more than one selection written to a string separated by commas with the last selection preceded by "and". It's used in another process. Multi select list boxes do not save that way.

Thanks.

[Gina Maylone]

this line is only in the case is >1
me.servicetext = arylist
it should also be in the
case is = 1

perfecto!!! Duh! Thank you jamesdeckert!!!