Re-position bound listbox to a specific record?

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a simple form that includes a listbox control that is bound to a
SELECT query. The functionality of the listbox control is such that as the
user presses a key, the first record matching that key is moved to (e.g.,
pressing "S" moves to "Smith, Jane"). However, as the user presses
additional keys (e.g., "SMI"), the first record matching each individual
record is moved to (e.g., "Smith, Jane", "Martin, Bill", "Irving, Alan").

Below the listbox, I've added an unbound textbox and a Find button. I'd like
to give the user the ability to type in one or more characters, press the
Find button, and have the listbox move to the first record matching the
entire string.

Is there a method of the listbox or recordsource that will allow me to do
this?
 
Hi Chris,

Basically you just need to filter the listbox based on the contents of the
textbox, here's one way to do it using the Change event of the textbox:

Private Sub Text1_Change()
dim strSQL as string
Const strBaseSQL As String = "Select Custid, Custname from Customer "
strSQL = strBaseSQL & "Where Custname like """ _
& Me.Text1.Text & "*"" Order by Custname;"
Me.lstMyList.RowSource = strSQL
End Sub

Adjust the table and field names accordingly.
 
Thanks, Sandra. What if I don't want to filter the data--just move the
pointer to the first matching record?

Sandra Daigle said:
Hi Chris,

Basically you just need to filter the listbox based on the contents of the
textbox, here's one way to do it using the Change event of the textbox:

Private Sub Text1_Change()
dim strSQL as string
Const strBaseSQL As String = "Select Custid, Custname from Customer "
strSQL = strBaseSQL & "Where Custname like """ _
& Me.Text1.Text & "*"" Order by Custname;"
Me.lstMyList.RowSource = strSQL
End Sub

Adjust the table and field names accordingly.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have a simple form that includes a listbox control that is bound to a
SELECT query. The functionality of the listbox control is such that as the
user presses a key, the first record matching that key is moved to (e.g.,
pressing "S" moves to "Smith, Jane"). However, as the user presses
additional keys (e.g., "SMI"), the first record matching each individual
record is moved to (e.g., "Smith, Jane", "Martin, Bill", "Irving, Alan").

Below the listbox, I've added an unbound textbox and a Find button. I'd
like to give the user the ability to type in one or more characters,
press the Find button, and have the listbox move to the first record
matching the entire string.

Is there a method of the listbox or recordsource that will allow me to do
this?
Click to expand...
Click to expand...
 
Hi Chris,

In that case you have to do the search by iterating through the items in the
listbox. Assuming that the CustName field is the second column in the
listbox rowsource, the following loops through the listbox and compares
compares the values in the listbox to whatever is entered in the textbox.
This also assumes that the listbox rowsource is sorted according to this
column.

Private Sub Text2_Change()
Dim inti As Integer
Dim fEnd As Boolean
inti = 0
Do Until fEnd
If Left(Me.List0.Column(1, inti), Len(Me.Text2.Text)) = Me.Text2.Text
Then
fEnd = True
Me.List0.SetFocus
Me.List0.ListIndex = inti
' put focus back to the textbox
Me.Text2.SetFocus
Me.Text2.SelStart = Len(Me.Text2 & "")
Else
inti = inti + 1
If inti > Me.List0.ListCount Then
fEnd = True
End If
End If
Loop

End Sub


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thanks, Sandra. What if I don't want to filter the data--just move the
pointer to the first matching record?

Sandra Daigle said:
Hi Chris,

Basically you just need to filter the listbox based on the contents
of the textbox, here's one way to do it using the Change event of
the textbox:

Private Sub Text1_Change()
dim strSQL as string
Const strBaseSQL As String = "Select Custid, Custname from
Customer " strSQL = strBaseSQL & "Where Custname like """ _
& Me.Text1.Text & "*"" Order by Custname;"
Me.lstMyList.RowSource = strSQL
End Sub

Adjust the table and field names accordingly.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have a simple form that includes a listbox control that is bound
to a SELECT query. The functionality of the listbox control is such
that as the user presses a key, the first record matching that key
is moved to (e.g., pressing "S" moves to "Smith, Jane"). However,
as the user presses additional keys (e.g., "SMI"), the first record
matching each individual record is moved to (e.g., "Smith, Jane",
"Martin, Bill", "Irving, Alan").

Below the listbox, I've added an unbound textbox and a Find button.
I'd like to give the user the ability to type in one or more
characters, press the Find button, and have the listbox move to the
first record matching the entire string.

Is there a method of the listbox or recordsource that will allow me
to do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top