Search within access listbox rowsource

[bubai]

I have placed an Unbound listbox on my bound form. The listbox's rowsource is an SQL query. Is it possible to Search within the listbox rowsource and find the ordinal position of a row?
There is a common field between my form's RecordSource and the listbox's rowsource which is "Pay_ID". So whenever my form moves to a record I would like to search the listbox's rowsource by the form's "Pay_ID" control value and get the ordinal position and select the listbox using the Me.lstBankPay.Selected(x) = True formula. I have tried the following method but it does not seem to give accurate result.

'Dim rsR As Recordset
'Set rsR = Me.lstBankPay.Recordset
'On Error Resume Next
'rsR.FindFirst "Pay_ID = " & Me.Pay_ID
'If Not rsR.NoMatch Then
'Me.lstBankPay.Selected(rsR.AbsolutePosition - 1) = True
'End If
'rsR.Close
'Set rsR = Nothing

[June7]

Listbox is bound to Pay_ID field? A bound single-select lisbox will already put focus on item saved into field.

I don't really understand what you want to accomplish with this code.

[bubai]

Listbox is bound to Pay_ID field? A bound single-select lisbox will already put focus on item saved into field.

ListBox is Unbound.
But its Rowsource query has a field named "Pay_ID" and in my bound form I also have a control named "Pay_ID". I want to search the listbox by this field.

[Welshgasman]

I believe you would need to cycle through the listbox and set the relevant row as selected if found?

Most times, the process is the other way around?, you select something in the list/combo and a record is found, not the other way around?

[bubai]

I believe you would need to cycle through the listbox and set the relevant row as selected if found?

Could you help me with the code?
I guess I have to put the code to the OnCurrent event of the form.

[Welshgasman]

Could you help me with the code?
I guess I have to put the code to the OnCurrent event of the form.

Why not use Google? That is always my first point of call. Someone has generally wanted to do it before me.

From https://www.google.com/search?q=set+...hrome&ie=UTF-8

you will get

https://stackoverflow.com/questions/...-list-box-item

Yes, current would be a good place.

[June7]

Is this on a Single View form?

Dim i As Integer
With Me.lstBankPay
    For i = 0 To .ListCount - 1
        If .Column(0, i) = Me.Pay_ID Then
            .Selected(i) = True
            Exit For
        End If
    Next
End With

Did you step debug your code? I did and it works when I remove the - 1. AbsolutePosition is zero-based.

[moke123]

I think what your looking for is - under the forms OnCurrent event is

Me.lstBankPay = nz(Me.Pay_ID,0)

[June7]

Yep, as simple as that, doh!

Don't need Nz().

[moke123]

Yep, as simple as that, doh!

Don't need Nz().

Depends whether there is any other code dependant on the listbox not being null, like when moving to a new record.

[bubai]

I think what your looking for is - under the forms OnCurrent event is

Me.lstBankPay = nz(Me.Pay_ID,0)

Wow!! It's working like charm.
But how come it is selecting the listbox without a .Selected property?

[June7]

It's not selecting the listbox, just setting value of listbox.

.Selected just sets a listbox item but doesn't put focus on the control.

If you want to set focus on listbox, use: Me.lstBankPay.SetFocus

[bubai]

It's not selecting the listbox, just setting value of listbox.

.Selected just sets a listbox item but doesn't put focus on the control.

If you want to set focus on listbox, use: Me.lstBankPay.SetFocus

Many thanks for your help.

[moke123]

But how come it is selecting the listbox without a .Selected property?

I believe the .selected property is primarily used in multiselect listboxes and the ItemsSelected collection.

for instance the code below iterates through the ItemsSelected collection and sets them to false(unselected)
You would use this to clear a multiselect listbox.

        For Each varItem In lst.ItemsSelected
            lst.Selected(varItem) = False
        Next

This iterates through the listbox items and selects all the items in a multiselect listbox.

        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next