move to a particular record in a list box?

[Jonathan LaRosa]

Hi all,

I have a list box that is populated with enough records such that
there are scroll bars and a user will have to scroll through the box
to see all of the records.

I want to add functionality that does the following:
the user types a few letters (like "th") into a text box and clicks a
button and the list box moves down to that row.

Code would look something like this:
("ctlList" is the list box, "SearchSessions box" is the search text
box)

For intCurrentRow = 0 To ctlList.ListCou nt - 1
If ctlList.Column( 1, intCurrentRow) Like
(CStr(Me.Search Sessionsbox) & "*") Then
' NEED CODE HERE TO MAKE THE LIST BOX JUMP TO THAT ENTRY
' maybe something like this:
ctlList.SetFocu s (intCurrentRow)
End If
Next intCurrentRow

The problem with that code is SetFocus() doesn't take any parms.

Anyone know how I can do this?

Thanks in advance,
Jon

[deko]

> I want to add functionality that does the following:

the user types a few letters (like "th") into a text box and clicks a
button and the list box moves down to that row.

You might try a query, a function, and a recordset. I know this works with
a datasheet, not sure about list...

The query would look something like this:

SELECT MyField, Record_ID
FROM tblMyTable WHERE ( MyString Like QryPrm("MyForm" ,"MyTxtBox") );

The function:

Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String)
As Variant
QryPrm = Forms(strFrm).C ontrols(strCtl)
End Function

The recordset:

Dim lngId As Long
Dim rst As DAO.Recordset
lngId = Nz(DLookup("Rec ord_ID", "qryMyQry") , 0)
Set rst = Me.RecordsetClo ne
rst.FindFirst "[Record_ID] = " & lngId
If rst.NoMatch Then MsgBox "No matching records"
Me.Form.Bookmar k = rst.Bookmark

[Don Leverton]

Hi Jonathan,

Not trying to be a "smart-ass" ... but isn't that what a combo-box does ...
without the need for a seperate textbox...?
Let me qualify that ... a combobox with it's AutoExpand Property set to True
(which is the default) will do that..

Don
"Jonathan LaRosa" <jl*****@alumni .brown.edu> wrote in message
news:e6******** *************** ***@posting.goo gle.com...

Hi all,

I have a list box that is populated with enough records such that
there are scroll bars and a user will have to scroll through the box
to see all of the records.

I want to add functionality that does the following:
the user types a few letters (like "th") into a text box and clicks a
button and the list box moves down to that row.

Code would look something like this:
("ctlList" is the list box, "SearchSessions box" is the search text
box)

For intCurrentRow = 0 To ctlList.ListCou nt - 1
If ctlList.Column( 1, intCurrentRow) Like
(CStr(Me.Search Sessionsbox) & "*") Then
' NEED CODE HERE TO MAKE THE LIST BOX JUMP TO THAT ENTRY
' maybe something like this:
ctlList.SetFocu s (intCurrentRow)
End If
Next intCurrentRow

The problem with that code is SetFocus() doesn't take any parms.

Anyone know how I can do this?

Thanks in advance,
Jon

[Jonathan LaRosa]

> Not trying to be a "smart-ass" ... but isn't that what a combo-box does ...

without the need for a seperate textbox...?
Let me qualify that ... a combobox with it's AutoExpand Property set to True
(which is the default) will do that..

yes, this is what a combo box does. except there is a requirement
that i did not mention that forces the use of a list box - the ability
to select multiple values. which means the combo box idea is out.

thanks,
jon

[Bruce]

jl*****@alumni. brown.edu (Jonathan LaRosa) wrote in message news:<e6******* *************** ****@posting.go ogle.com>...

Hi all,

I have a list box that is populated with enough records such that
there are scroll bars and a user will have to scroll through the box
to see all of the records.

I want to add functionality that does the following:
the user types a few letters (like "th") into a text box and clicks a
button and the list box moves down to that row.

Code would look something like this:
("ctlList" is the list box, "SearchSessions box" is the search text
box)

For intCurrentRow = 0 To ctlList.ListCou nt - 1
If ctlList.Column( 1, intCurrentRow) Like
(CStr(Me.Search Sessionsbox) & "*") Then
' NEED CODE HERE TO MAKE THE LIST BOX JUMP TO THAT ENTRY
' maybe something like this:
ctlList.SetFocu s (intCurrentRow)
End If
Next intCurrentRow

Setting ctlList.Selecte d(intCurrentRow ) = True will scroll to and
select that item in the list.

Bruce

[Don Leverton]

Hi Jon,

I don't think Bruce's method will do what he thinks it will ... but I could
be wrong ... go ahead and try.

I think it will indeed "select" the first row that it fits the criteria, but
I doubt that it will scroll to it? AFAIK, there is no way to automatically
scroll to the desired row.

Now I'm thinking that you may be able to limit the number of rows that do
get displayed in the list, and eliminate (or at least reduce) the need for
scrolling .

This could be done by building an SQL string in code, and then using that
SQL as the Row Source for the listbox.

Try this, and see if it suits your needs...
(You will have to edit Table, Field, and Control Names ... )

*************** *************** *************
Private Sub cmdRequeryListb ox_Click()

Dim strFilter
Dim MySQL As String
Dim whr As String

strFilter = Me.txtFilter

MySQL = ""
MySQL = MySQL & "SELECT YourTableName.Y ourFieldName FROM YourTableName "

whr = ""
If Len(strFilter) > 0 Then 'If the user has typed something to narrow
the search

whr = whr & "WHERE (((YourTableNam e.YourFieldName ) Like "

'There are two ways to go here ... "Contains" OR "Begins With"
'eg "*pat*" would give you "Patrick Fitzsimons" AND "Simon Fitzatrick"
' or "pat*" would return "Patrick Fitzsimons" only
'--- this example is for "Contains ---"

whr = whr & " '*' "
whr = whr & " & "
whr = whr & Chr$(34) & strFilter & Chr$(34)
whr = whr & " & "
whr = whr & " '*' "
whr = whr & "))"

MySQL = MySQL & whr 'Insert the WHERE into the SQL string

End If

MySQL = MySQL & ";"
'Debug.Print MySQL

Me.lstFiltered. RowSource = MySQL

End Sub
*************** *************** *************

HTH,
Don

Jonathan LaRosa <jl*****@alumni .brown.edu> wrote in message
news:e6******** *************** ***@posting.goo gle.com...

Not trying to be a "smart-ass" ... but isn't that what a combo-box does .... without the need for a seperate textbox...?
Let me qualify that ... a combobox with it's AutoExpand Property set to True (which is the default) will do that..

yes, this is what a combo box does. except there is a requirement
that i did not mention that forces the use of a list box - the ability
to select multiple values. which means the combo box idea is out.

thanks,
jon

[Stephen Lebans]

Don you can use the ListIndex property to simulate the standard ListBox
TopIndex property.

http://www.lebans.com/List_Combo.htm#ScrollListbox
Scroll a ListBox to a specific row. Emulates the VB ListBox TopIndex
property. You can alter the code to easily have the selected row display
as the first or last row as well. The example code is placed behind a
Command Button.

' *** CODE START
Private Sub cmdListIndex_Cl ick()
On Error GoTo Err_cmdListInde x_Click

' Always make NumRows an odd number
' if you want selected Row to be in the
' middle of the ListBox.

' NumRows is the number of completely visible rows in the ListBox Const
NumRows = 7
' Row we want displayed in middle of ListBox.
Dim intDesiredRow As Integer

' Arbitrarily select the 24th row.
intDesiredRow = 24
' ListBox must have the Focus
Me.List2.SetFoc us
' Force ListBox to start from the top
Me.List2.ListIn dex = 1

' Force the Scroll offset we desire
Me.List2.ListIn dex = intDesiredRow + (NumRows / 2)
' Now select the row without further scrolling
Me.List2.ListIn dex = intDesiredRow

Exit_cmdListInd ex_Click:
Exit Sub

Err_cmdListInde x_Click:
MsgBox Err.Description
Resume Exit_cmdListInd ex_Click

End Sub
' ***CODE END


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Don Leverton" <My*****@Telus. Net> wrote in message
news:%XTmc.6040 $uN4.2535@clgrp s12...

Hi Jon,

I don't think Bruce's method will do what he thinks it will ... but I could be wrong ... go ahead and try.

I think it will indeed "select" the first row that it fits the criteria, but I doubt that it will scroll to it? AFAIK, there is no way to automatically scroll to the desired row.

Now I'm thinking that you may be able to limit the number of rows that do get displayed in the list, and eliminate (or at least reduce) the need for scrolling .

This could be done by building an SQL string in code, and then using that SQL as the Row Source for the listbox.

Try this, and see if it suits your needs...
(You will have to edit Table, Field, and Control Names ... )

*************** *************** *************
Private Sub cmdRequeryListb ox_Click()

Dim strFilter
Dim MySQL As String
Dim whr As String

strFilter = Me.txtFilter

MySQL = ""
MySQL = MySQL & "SELECT YourTableName.Y ourFieldName FROM YourTableName "
whr = ""
If Len(strFilter) > 0 Then 'If the user has typed something to narrow the search

whr = whr & "WHERE (((YourTableNam e.YourFieldName ) Like "

'There are two ways to go here ... "Contains" OR "Begins With"
'eg "*pat*" would give you "Patrick Fitzsimons" AND "Simon Fitzatrick" ' or "pat*" would return "Patrick Fitzsimons" only
'--- this example is for "Contains ---"

whr = whr & " '*' "
whr = whr & " & "
whr = whr & Chr$(34) & strFilter & Chr$(34)
whr = whr & " & "
whr = whr & " '*' "
whr = whr & "))"

MySQL = MySQL & whr 'Insert the WHERE into the SQL string

End If

MySQL = MySQL & ";"
'Debug.Print MySQL

Me.lstFiltered. RowSource = MySQL

End Sub
*************** *************** *************

HTH,
Don

Jonathan LaRosa <jl*****@alumni .brown.edu> wrote in message
news:e6******** *************** ***@posting.goo gle.com...

Not trying to be a "smart-ass" ... but isn't that what a combo-box

does

... without the need for a seperate textbox...?
Let me qualify that ... a combobox with it's AutoExpand Property

set to

True (which is the default) will do that..

yes, this is what a combo box does. except there is a requirement
that i did not mention that forces the use of a list box - the ability to select multiple values. which means the combo box idea is out.

thanks,
jon

[Bruce]

"Don Leverton" <My*****@Telus. Net> wrote in message news:<%XTmc.604 0$uN4.2535@clgr ps12>...

Hi Jon,

I don't think Bruce's method will do what he thinks it will ... but I could
be wrong ... go ahead and try.

I think it will indeed "select" the first row that it fits the criteria, but
I doubt that it will scroll to it? AFAIK, there is no way to automatically
scroll to the desired row.

It's not a matter of me 'thinking' it will, it's a matter of me having
tested it and demonstrated that it will, else I would not have posted
it without the caveat "I think this should work..." . I did not test
it with a multiselect listbox, however, but I _think_ that should work
as well :) There could definitely be situations in which the listbox
could not display all of the selected items simultaneously however. I
think Don's solution in which one limits the actual display of items
in the listbox to those which meet your criteria is preferred where
multiple items need to be selected.

Bruce

[Don Leverton]

Hi Bruce,

I'm sorry, I meant no offence.

In fact I was attempting to be polite, because I had already tried something
almost identical to what you had posted, and found that while the row was
indeed "selected", it was still out of view.

*************** *************** *************** **
Private Sub cmdSelectListRo w_Click()

Dim intCurrentrow As Integer

For intCurrentrow = 0 To ctlList.ListCou nt - 1

If ctlList.Column( 0, intCurrentrow) Like (CStr(Me.Search Sessionsbox) &
"*") Then
ctlList.Selecte d(intCurrentrow ) = True
Else
ctlList.Selecte d(intCurrentrow ) = False
End If

Next intCurrentrow

End Sub
*************** *************** *************** **
I thought I must have missed something, so I tried it again ... with the
same result.

Then I followed up on your comment about not trying the Multi-Select ...
When I changed my Multi-Select property from "Extended" to "None", it does
do as you say it will!
(I tried "Simple", too ... but I still see the same behaviour as "Extended")

Regards,
Don

"Bruce" <br***@aristotl e.net> wrote in message
news:d3******** *************** ***@posting.goo gle.com...

"Don Leverton" <My*****@Telus. Net> wrote in message

news:<%XTmc.604 0$uN4.2535@clgr ps12>...

Hi Jon,

I don't think Bruce's method will do what he thinks it will ... but I could be wrong ... go ahead and try.

I think it will indeed "select" the first row that it fits the criteria, but I doubt that it will scroll to it? AFAIK, there is no way to automatically scroll to the desired row.

It's not a matter of me 'thinking' it will, it's a matter of me having
tested it and demonstrated that it will, else I would not have posted
it without the caveat "I think this should work..." . I did not test
it with a multiselect listbox, however, but I _think_ that should work
as well :) There could definitely be situations in which the listbox
could not display all of the selected items simultaneously however. I
think Don's solution in which one limits the actual display of items
in the listbox to those which meet your criteria is preferred where
multiple items need to be selected.

Bruce