asked on

Getting ListBox items into a variable in VBA

I have a form that has a ListBox (called “Article”, and with no Control Source) containing only 4 items that I have typed in.
When the user selects on of these items I want the selected item to be assigned to a string value in my VBA.

I have tried using [Article] and Article.value each of which always contain NULL regardless of what value actually shows in the ListBox.

I have also tried to include a ‘Control Source’ (to a field called [Article]) which always loads the associated table with only the first item from the ListBox.

Any ideas?

S.A.L.F

As long as its a ActiveX control you can use:-

ListBox1.List(ListBox1.ListIndex)

This will return the currently selected list value.

bartvd

With this code you get the first item selected

ListBox.ItemData(ListBox.ItemsSelected.Item(0))

als315

If your listbox have name Article, in afterupdate event for it you can get it's value:

Private Sub Article_AfterUpdate()
MsgBox Me.Article
End Sub

usatrfe

ASKER

I'm using Access 2007:-

ListBox1.List (as Article.List) is not valid (in suggetion ListBox.ItemData(ListBox.ItemsSelected.Item(0))

MsgBox Me.Article returns only NULL.

ListBox.ItemData(ListBox.ItemsSelected.Item(0)) returns Runtime error 2480

Any other ideas?

S.A.L.F

I've just tested the same conditions and Article.Value returns the currently selected list value.

What error are you getting when you try this method?

usatrfe

ASKER

Runtime erro 94 Invalid use of NULL

als315

Can you upload your form with this listbox?

S.A.L.F

I suspect that the problem is not how you are accessing the control. Would you be able to post the code so that we can diagnose it please?

peter57r

Sounds like you have set the listbox to be multi-select.
That would return a null value.

Do you need multi-select? If not then set it to none. You will then get the selected value.

usatrfe

ASKER

multi-select was set to NONE.

Here is the file.

UnitSerNo.accdb

ASKER CERTIFIED SOLUTION

S.A.L.F

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peter57r

You need to move the code into the afterupdate event procedure.

Nothing is selected at the point of 'gotfocus'