Vba get value from listbox column
TEST YOUR SMARTS
Which of the following retains the information it's storing when the system power is turned off?
Submit »
88% of IT pros got this right.
Challenge
×
Get answers from your peers
along with millions of IT pros who visit Spiceworks.
Join Now
Access 2003 Col 1 = Primary Key Record Identifier Text
For Each varItem In ListBox.ItemsSelected
SelectedItemID = ListBox.ItemData(varItem)
Name = ListBox ????? This is where everything breaks down.
Next varItem
Best Answer
Datil
OP
kevinhughes2
May 14, 2019 at 13:10 UTC
Assuming its a 2 column list box and the value you seek is in the second column:
Should do the trick.
View this "Best Answer" in the replies below »
6 Replies· · ·
Datil
OP
Best Answer
kevinhughes2
May 14, 2019 at 13:10 UTC
Assuming its a 2 column list box and the value you seek is in the second column:
Should do the trick.
2
· · ·
Serrano
OP
greg28818
This person is a verified professional.
Verify your account
to enable IT peers to see that you are a professional.
Here is some code I use for getting the multiple values selected from a list box. I put them into a table and then use that table in a query. You can reference the other column's in a combo box with the reference Me.ComboBox.Column(2) (just change the number to be whatever column you need). I assume it is similar for a listbox but I have not tried to reference multi column listbox before. Text
...AfterUpdate
'Check if filter table is empty if not call multi, if empty set row source to ALL
Call MultiSelect("tblFilter_CaseLabel", "frmCaseLabels", "lstFilter")
Sub ListBox_MultiSelect(tblName, frmName, lstName) 'table to write selection to, name of form
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
rs.Open "tblFilter_CaseLabel", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
' rs.Open tblName, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Dim frm As Form, ctl As Control
Dim varItm As Variant
F = "Forms!frmCaseLabels"
c = "frm!" & lstName
Set frm = "Forms!frmCaseLabels"
Set ctl = c
For Each varItm In ctl.ItemsSelected
rs.AddNew
rs(0) = ctl.ItemData(varItm)
rs.MoveNext
Next varItm
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub
1
· · ·
Jalapeno
OP
markberry
May 14, 2019 at 13:28 UTC
kevinhuges2 thanks! I worked on that for hours and couldn't figure it out. It wasn't for not Googling! But nobody seemed to reference how to get that second column.
0
· · ·
Jalapeno
OP
markberry
May 14, 2019 at 13:36 UTC
greg28818 said "
Me.ComboBox.Column(2) (just change the number to be whatever column you need)."
This was the problem on ALL the other articles I found on the subject,
Me.ComboBox.Column(2) only references the Second Column of the list box, it doesn't address the Selected Item. As kevinhuges2 pointed out, you need the Index of the Current Selected Item like this: Text
For Each varItem In ListBox.ItemsSelected
varName = ListBox.Column(1, varItem).
Next varItem
The magic sauce was in the second parameter to the Column property (1, varItem) That says "get the Column property for the currently referenced Item from the list of selected items in the For Each Loop. It's really odd to me that nobody seemed to mention this in any of the 20 or 30 posts I've read over the past few days about this. ...
0
· · ·
Serrano
OP
greg28818
This person is a verified professional.
Verify your account
to enable IT peers to see that you are a professional.
I got ya now. I thought when you wanted multiple values, that you wanted multiple selected values from the list box and any other columns from the selected values. So then you could use a little bit of both. Glad you got your answer.
0
· · ·
Datil
OP
kevinhughes2
May 14, 2019 at 14:02 UTC
There are times when SO devolves into pedantic nonsense debates ... but heres one. https://stackoverflow.com/questions/4649000/retrieve-column-values-of-the-selected-row-of-a-multicolumn-access-listbox
1
This topic has been locked by an administrator and is no longer open for commenting. To continue this discussion, please ask a new question. |