How to return the item selected string value from a listbox not it's position

[Pookiemeister]

How can I modify the code below to return the listbox value and not the position number of the selected value. Ex: if the value selected in the listbox is "Apples" and "Oranges" their positions are (0) and (3), the code below would show the numbers "0 , 3" but I need it to show the word "Apples, Oranges" instead and NOT the number "0, 3" Is this possible? I hope my explanation make sense. Thank you.

Function lbxRPItems() As String
   'Returns a list of items in the listbox
   Dim lstbx As ListBox
   Dim lngItems As Long
   Dim lngItem As Long
   Dim strReturns As String
   strReturns = ""
   Set lstbx = Me!lstResponsiblePerson
   lngItems = lstbx.ItemsSelected.Count
   If lngItems > 0 Then
      For lngItem = 0 To lngItems - 1
         If lngItem = 0 Then
            strReturns = CStr(lstbx.ItemsSelected(lngItem))
         Else
            strReturns = strReturns & "," & CStr(lstbx.ItemsSelected(lngItem))
         End If
      Next
   End If
   Set lstbx = Nothing
   lbxRPItems = strReturns
End Function

Private Sub lstResponsiblePerson_AfterUpdate()
   Dim strItems As String
   strItems = lbxRPItems()
   Me!lstResponsiblePerson.Value = strItems
   MsgBox Me!lstResponsiblePerson
End Sub

 

[jackd]

What is the RowSource of your listbox?
You will likely need to identify the Column involved.

 

[Pookiemeister]

In this listbox, is a list of people's full name. Rowsource below.

Field: Full Name
Table: qryName Format
Sort:
Show: Checked
Criteria:
or:

 

[jackd]

Post the sql of your query.

 

[Pookiemeister]

SELECT [qryName Format].[Full Name]
FROM [qryName Format];

 

[Micron]

Not sure what you are trying to do. If there are 3 items selected, the zero based collection contains 3 items - 0,1 and 2. Those numbers represent the ordinal positions of the selected items in the zero based collection. If you want the data corresponding to the bound column of the listbox, you want the ItemData property for that position (e.g. lstMyListbox.ItemData(1) ) represents the value in the bound column for the 2nd list item. If you want the data from a column other than the bound column, you want the Column property of the selected item, e.g. lstMyListbox.Column(2) returns the value from the 2nd column for the selected row.

Suppose you select only the 5th row of a listbox. The ItemsSelected(0) value is 5; i.e. it is the 5th row but since there is only one item in the zero based collection, its position is (0). I think you want .ItemData, not ItemsSelected or .Column. As for the latter, you'd have to get the .Column value on each iteration of your loop.

 

[Pookiemeister]

Thank you all for your help. Thank Micron for guidance. So here is the working code that I came up with.

Function lbxRPItems() As String
   'Returns a list of items in the listbox
   Dim lstbx As ListBox
   Dim lngItems, lngItem As Long
   Dim strReturns As String
   Dim ctlList As Control, varItem As Variant, strReturn As String
   
   strReturn = ""
   Set ctlList = Me!lstResponsiblePerson
   lngItems = ctlList.ItemsSelected.Count
   If lngItems > 0 Then
      For Each varItem In ctlList.ItemsSelected
         If varItem = 0 Then
            strReturn = ctlList.ItemData(varItem)
         Else
            strReturn = strReturn & ", " & ctlList.ItemData(varItem)
         End If
      Next varItem
   End If
   Set ctlList = Nothing
   
   lbxRPItems = strReturn
End Function

I originally made this in Excel and used Excel as my database but decided to use Access instead, since I heard and seen it is easier to find items that were entered. However, it is so much harder to code in Access. To me, there seems a lot of extra steps. Anyway, It is amazing to see how well all of you guys know this so well. Any advice on how to code better in Access? Especially when using controls. Most textbooks I've seen don't cover controls in detail and they don't cover all of them. Thank you again.

 

[Micron]

It's all about familiarity and practice with the code I'd say. I do very little coding in Excel and have to research just about everything involved if I do so that makes it 'harder' for me. When I started Access vba I had to ask how to create a message box. Not sure what 'extra steps' you're referring to but here's where you might have taken a few extra steps that are not needed:
Dim lstbx As ListBox << not used
Dim lngItems, lngItem As Long << lngItem not used; lngItems is a Variant because it's data type is not expressly stated.
Dim strReturns As String << not used

Set ctlList = Me!lstResponsiblePerson <<use . not ! If you misspell the control name, code will run to that point (run time error). If you use . it won't even compile, let alone get that far. The only place I can think of where ! is necessary is when dealing with recordsets. As for style introducing extra steps, creating a string variable and setting the function to its value at the end might be an example. Conceivable that you could simply add the looped items to lbxRPItems as you go, but there's nothing wrong with either approach IMO.

The only book I ever bought was Access 2000 Power Programming by F. Scott Barker. You could probably find some pdf versions of newer books, which for the most part, will still contain a lot of current information. Nowadays, I use mostly Google for refreshers as well as this (contains every object, method and property in all of Office AFAIK). Sometimes difficult to navigate when you don't quite know the proper terms/names etc. Then again there are forums such as this if you can't find what you're looking for.