How to select an item (value) in a listbox using VBA?

[LindaLooUK]

Hi folks

First post here so I hope you'll be gentle with me!

In plain English, I want to select an item in a listbox that has a specified value when I open the form that contains this listbox. Most of the help I've found describes selecting records based on their index value but I want to select an item according to the actual value that's displayed in the listbox.

For example, the first number below is the index value, the 2nd is the number I'm interested in and the 3rd is another column in the listbox:

0 1 12345
1 2 33256
2 4 36280

I'm using the OpenArgs property to pass the value (a long integer primary key) to the opening form but I don't know the syntax or correct terminology for ensuring the specified row is selected in the listbox. At present, using the above example, if I want to select the row with the value 2, it's selecting the 3rd row rather than the 2nd.

Sorry to ramble but I do hope you can help me with this...it's driving me crazy!!

 

[essaytee]

Check this link, should clue you up on columns of a listbox.

Basically though, to extract a particular column of a listbox, it's as follows:

=Forms!FormName!ListboxName.Column(Column Number)

such as

=Forms!Customers!CompanyName.Column(1)

or reduced to

=Me.ListboxName.Column

Column counting starts at zero.

 

[LindaLooUK]

Many thanks for the prompt response Steve but I'm not sure how this helps me...sorry if I'm being completely dim here!

The column property allows you to specify a column and then (optionally) a row number but as I don't know which row the desired value appears in how would I specify this?

 

[essaytee]

Many thanks for the prompt response Steve but I'm not sure how this helps me...sorry if I'm being completely dim here!

The column property allows you to specify a column and then (optionally) a row number but as I don't know which row the desired value appears in how would I specify this?

Assuming a simple listbox, whereby only one item can be selected, when you click an item in the listbox then the row is already known.

To test this, add a command button to your form, in the click event enter the following (or similar, your control names):

Private Sub Command4_Click()
    Dim strTest As String
    strTest = Me.lstText.Column(2)
    Debug.Print "strTest = " & strTest
    MsgBox "strTest = " & strTest
 End Sub

For the above example, my listbox control name is lstTest. The listbox has three columns. I elected to extract the third column data, which is .Column(2) as the first column is zero.

At this point we are just retrieving the data value of the third column.

Get this bit working first then additional info to follow, if need be.

 

[LindaLooUK]

Assuming a simple listbox, whereby only one item can be selected, when you click an item in the listbox then the row is already known.

Thanks for persisting with this Steve.
However, the listbox I'm referring to does not have a row already selected as I don't know which row the record I'm interested in is located! I'm trying to get Access to select the row based on a specific unique value in column 1.

 

[essaytee]

Thanks for persisting with this Steve.
However, the listbox I'm referring to does not have a row already selected as I don't know which row the record I'm interested in is located! I'm trying to get Access to select the row based on a specific unique value in column 1.

Okay, the process is, you'll need a text box, enter the search criteria, then a code snippet to check against the recordset of the listbox. On successfully finding the row of the recordset the listbox row can be selected.

I'll provide further later, when I have time to nut it out properly for you. Let me know if I'm off the mark.

 

[LindaLooUK]

Many thanks for the kind offer, that would be really great if you don't mind.

 

[LindaLooUK]

Doh! I've just tried this again and it actually works fine without the need to go through hoops! I just pass the lngID (primary key) to the form that contains the listbox in the OpenArgs property and then use that value in the OnOpen event of the form:

lstListbox = lngID

The listbox is bound to the lngID field.

Thank you for being so helpful and my apologies for wasting your time.

 

[essaytee]

Thanks for reporting back and I'm glad you resolved your issue.