473,324 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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
Nov 12 '05 #1
8 6565
> 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).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
Nov 12 '05 #2
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.google.c om...
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

Nov 12 '05 #3
> 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
Nov 12 '05 #4
jl*****@alumni.brown.edu (Jonathan LaRosa) wrote in message news:<e6**************************@posting.google. 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, "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


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

Bruce
Nov 12 '05 #5
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 <jl*****@alumni.brown.edu> wrote in message
news:e6**************************@posting.google.c om...
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

Nov 12 '05 #6
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*****@Telus.Net> wrote in message
news:%XTmc.6040$uN4.2535@clgrps12...
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 <jl*****@alumni.brown.edu> wrote in message
news:e6**************************@posting.google.c om...
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



Nov 12 '05 #7
"Don Leverton" <My*****@Telus.Net> wrote in message news:<%XTmc.6040$uN4.2535@clgrps12>...
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
Nov 12 '05 #8
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" <br***@aristotle.net> wrote in message
news:d3**************************@posting.google.c om...
"Don Leverton" <My*****@Telus.Net> wrote in message

news:<%XTmc.6040$uN4.2535@clgrps12>...
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

Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Skully Matjas | last post by:
I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record...
2
by: Paolo | last post by:
Friends, I have created a form named FRMNEWCLIENTS whose record source is a table named NEWCLIENTS. This table has a field named FILENUMBER. I have added on the form a combobox using the third...
5
by: deko | last post by:
I have a subform datasheet that contains a full year of records sorted by a date field. I'm trying to programmatically move the record selector on the datasheet to the first record that matches a...
1
by: sixsoccer | last post by:
I have built a database with a <Mainform> and a <Subform>. My problem is twofold. 1. My subform is set as a continuos form with AllowAddiotions set to NO (ie. a list of Issues to the client on...
7
by: Byron | last post by:
I'm looking for a way to deny a move from the current listbox item. For instance, if the user is editing the record associated with the current listbox item I want to deny a move within the...
5
lwwhite
by: lwwhite | last post by:
I'm trying to reproduce functionality I've seen in some other (non-Access) applications. On one of my forms, the master record is a document. The subform consists of a datasheet list of the topics...
7
by: Starke | last post by:
I have a table that contains an SCC # and then locations, Qty, SKU. As below SCC Lcoation qty Sku 123 adffa 3 321a1 m 123 adfa ...
3
by: lucky13 | last post by:
Dear All, I have hundered of records in Flexgrid & find a particular record is dififcult & if i want to fiind out particulart record from second column so what i want that in one text box as i...
2
by: beemomo | last post by:
Hi everyone here, Wondering if this can be perform in access using SQL or vba. VesselMovement table: RNO Date_Fix TIME VID X Y HEADING SPEED 1 ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.