move to a particular record in a list box?

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, "SearchSessionsbox" is the search text
box)

For intCurrentRow = 0 To ctlList.ListCount - 1
If ctlList.Column(1, intCurrentRow) Like
(CStr(Me.SearchSessionsbox) & "*") Then
' NEED CODE HERE TO MAKE THE LIST BOX JUMP TO THAT ENTRY
' maybe something like this:
ctlList.SetFocus (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

re: move to a particular record in a list box?

> I want to add functionality that does the following:[color=blue]
> 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.[/color]

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).Controls(strCtl)
End Function

The recordset:

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

re: move to a particular record in a list box?

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" <jlarosa@alumni.brown.edu> wrote in message
news:e6a1ce64.0405061321.4084ff66@posting.google.c om...[color=blue]
> 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, "SearchSessionsbox" is the search text
> box)
>
> For intCurrentRow = 0 To ctlList.ListCount - 1
> If ctlList.Column(1, intCurrentRow) Like
> (CStr(Me.SearchSessionsbox) & "*") Then
> ' NEED CODE HERE TO MAKE THE LIST BOX JUMP TO THAT ENTRY
> ' maybe something like this:
> ctlList.SetFocus (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[/color]

re: move to a particular record in a list box?

> Not trying to be a "smart-ass" ... but isn't that what a combo-box does ...[color=blue]
> 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..[/color]

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

re: move to a particular record in a list box?

jlarosa@alumni.brown.edu (Jonathan LaRosa) wrote in message news:<e6a1ce64.0405061321.4084ff66@posting.google. com>...[color=blue]
> 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, "SearchSessionsbox" is the search text
> box)
>
> For intCurrentRow = 0 To ctlList.ListCount - 1
> If ctlList.Column(1, intCurrentRow) Like
> (CStr(Me.SearchSessionsbox) & "*") Then
> ' NEED CODE HERE TO MAKE THE LIST BOX JUMP TO THAT ENTRY
> ' maybe something like this:
> ctlList.SetFocus (intCurrentRow)
> End If
> Next intCurrentRow[/color]

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

Bruce

re: move to a particular record in a list box?

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 cmdRequeryListbox_Click()

Dim strFilter
Dim MySQL As String
Dim whr As String

strFilter = Me.txtFilter

MySQL = ""
MySQL = MySQL & "SELECT YourTableName.YourFieldName FROM YourTableName "

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

whr = whr & "WHERE (((YourTableName.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 <jlarosa@alumni.brown.edu> wrote in message
news:e6a1ce64.0405070504.65045b87@posting.google.c om...[color=blue][color=green]
> > Not trying to be a "smart-ass" ... but isn't that what a combo-box does[/color][/color]
....[color=blue][color=green]
> > without the need for a seperate textbox...?
> > Let me qualify that ... a combobox with it's AutoExpand Property set to[/color][/color]
True[color=blue][color=green]
> > (which is the default) will do that..[/color]
>
> 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[/color]

re: move to a particular record in a list box?

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_Click()
On Error GoTo Err_cmdListIndex_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.SetFocus
' Force ListBox to start from the top
Me.List2.ListIndex = 1

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

Exit_cmdListIndex_Click:
Exit Sub

Err_cmdListIndex_Click:
MsgBox Err.Description
Resume Exit_cmdListIndex_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.Name@Telus.Net> wrote in message
news:%XTmc.6040$uN4.2535@clgrps12...[color=blue]
> Hi Jon,
>
> I don't think Bruce's method will do what he thinks it will ... but I[/color]
could[color=blue]
> be wrong ... go ahead and try.
>
> I think it will indeed "select" the first row that it fits the[/color]
criteria, but[color=blue]
> I doubt that it will scroll to it? AFAIK, there is no way to[/color]
automatically[color=blue]
> scroll to the desired row.
>
> Now I'm thinking that you may be able to limit the number of rows that[/color]
do[color=blue]
> get displayed in the list, and eliminate (or at least reduce) the need[/color]
for[color=blue]
> scrolling .
>
> This could be done by building an SQL string in code, and then using[/color]
that[color=blue]
> 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 cmdRequeryListbox_Click()
>
> Dim strFilter
> Dim MySQL As String
> Dim whr As String
>
> strFilter = Me.txtFilter
>
> MySQL = ""
> MySQL = MySQL & "SELECT YourTableName.YourFieldName FROM YourTableName[/color]
"[color=blue]
>
> whr = ""
> If Len(strFilter) > 0 Then 'If the user has typed something to[/color]
narrow[color=blue]
> the search
>
> whr = whr & "WHERE (((YourTableName.YourFieldName ) Like "
>
> 'There are two ways to go here ... "Contains" OR "Begins With"
> 'eg "*pat*" would give you "Patrick Fitzsimons" AND "Simon[/color]
Fitzatrick"[color=blue]
> ' 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 <jlarosa@alumni.brown.edu> wrote in message
> news:e6a1ce64.0405070504.65045b87@posting.google.c om...[color=green][color=darkred]
> > > Not trying to be a "smart-ass" ... but isn't that what a combo-box[/color][/color][/color]
does[color=blue]
> ...[color=green][color=darkred]
> > > without the need for a seperate textbox...?
> > > Let me qualify that ... a combobox with it's AutoExpand Property[/color][/color][/color]
set to[color=blue]
> True[color=green][color=darkred]
> > > (which is the default) will do that..[/color]
> >
> > 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[/color][/color]
ability[color=blue][color=green]
> > to select multiple values. which means the combo box idea is out.
> >
> > thanks,
> > jon[/color]
>
>[/color]

re: move to a particular record in a list box?

"Don Leverton" <My.Name@Telus.Net> wrote in message news:<%XTmc.6040$uN4.2535@clgrps12>...[color=blue]
> 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.[/color]

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

re: move to a particular record in a list box?

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 cmdSelectListRow_Click()

Dim intCurrentrow As Integer

For intCurrentrow = 0 To ctlList.ListCount - 1

If ctlList.Column(0, intCurrentrow) Like (CStr(Me.SearchSessionsbox) &
"*") Then
ctlList.Selected(intCurrentrow) = True
Else
ctlList.Selected(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" <bruce@aristotle.net> wrote in message
news:d3b3c84d.0405100903.687bcca3@posting.google.c om...[color=blue]
> "Don Leverton" <My.Name@Telus.Net> wrote in message[/color]
news:<%XTmc.6040$uN4.2535@clgrps12>...[color=blue][color=green]
> > Hi Jon,
> >
> > I don't think Bruce's method will do what he thinks it will ... but I[/color][/color]
could[color=blue][color=green]
> > be wrong ... go ahead and try.
> >
> > I think it will indeed "select" the first row that it fits the criteria,[/color][/color]
but[color=blue][color=green]
> > I doubt that it will scroll to it? AFAIK, there is no way to[/color][/color]
automatically[color=blue][color=green]
> > scroll to the desired row.[/color]
>
> 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[/color]