Simulate "autoselect" in ListBox

B

Bob Howard

Hi (using A2K3...),

I want to sort of simulate autoselect in a ListBox. I plan to insert an
unbound text box right above the ListBox for the user to type into. As the
user types, I want to use some VBA code behind the form to select the first
row that matches the characters typed so far (i.e., this would be only a
partial match). This would also force the ListBox contents to scroll so the
selected row is in view.

As the user types an additional character, I would refine the search ... and
so on.

And this would continue until either 1) the row the user is after is the one
selected, or 2) the the row the user is after comes into view.

Either way, the user will then be able to do what he/she needs to do with
the data in view (in the ListBox).

Can anyone point me to some VBA that would accomplish this??

Thanks in advance...

bob h.
 
L

Linq Adams via AccessMonster.com

Is there a reason why you can't use a combobox instead of a listbox? With
AutoExpand Property set to Yes, which is the default setting, it does this
automatically.
 
B

Bob Howard

I'm aware of your "combo box alternative" and the AutoExpand property. But
the nature of the application is such that a ListBox is what's needed on the
screen.

Bob
 
M

Mr B

Bob,

I recently developed an applicaiton for a client where I had much the same
situation. I really needed a list box to display the list of records but I
also needed the capability of the user to be able to type in values and have
the list box immediately filtered to the typed criteria.

In my situation I had a list of individuals names, both first and last
names. So in my case I have two text boxes above the list box. Actully my
text boxes are in the header of my form and the list box is in the details
section.

In a nutshell what I am doing is that I am creating the sql statement on the
fly that will return the filtered values for the list box. In my case the
user can use either or both of the text boxes to type a value into for
filtering the list box. I also have a command button that lets the user
clear all entries in the two text boxes. I also have a group control that
has two options for sorting the list box in asscending and descending order.

I am going to provide you with the code for my controls and hope that the
code will be at least helpful.

As an explanation, here are the names of my controls:
txtFirstName = name of text box where user can type in the first name
txtLastName = name of text box where user cna type in the last name
cmdClearAll = name of command button to clear what user types into text boxes
grpSortList = name of group control with two option buttons for sorting order
lstContacts = name of list box control

I also have the following variables defined in the declarations area of my
VBA code for the form.
Dim strActiveControl As String
This variable is being set in the "Got Focus" event of each of the text boxes.

Here is the function that is called to set the variables value
Function SetCurControlName()
strActiveControl = Me.ActiveControl.Name
End Function

Here is the code for the Got Focus event of the "txtFirstName" text box
Private Sub txtFirstName_GotFocus()
SetCurControlName
End Sub

Here is the code for the Got Focus event of the "txtLastName" text box
Private Sub txtLastName_GotFocus()
SetCurControlName
End Sub

Here is the code for the On Change event of the "txtFirstName" text box
Private Sub txtFirstName_Change()
If Me.txtFirstName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtLastName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the code for the On Change event of the "txtLastName" text box
Private Sub txtLastName_Change()
If Me.txtLastName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtFirstName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the "GetContactsList" function
Function GetContactsList()
Select Case strActiveControl
Case "txtFirstName"
strStartSql = "SELECT Contacts.ContactID, [sFirstName] & "" "" &
[slastname] & "", " _
& """ & [Contacts]![sCity] & "", "" &
[Contacts]![sStateOrProvince] AS Name FROM Contacts"
varFName = Me.txtFirstName.Text
If Me.txtLastName = "" Then
varLName = ""
Else
varLName = Me.txtLastName
End If
If varFName > "" Then
If varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varLName
& "*"") " _
& "AND ((Contacts.sFirstName) Like """ & varFName &
"*""))"
Else
strNameSql = " WHERE (((Contacts.sFirstName) Like """ &
varFName & "*"")) "
End If
ElseIf varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varLName &
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sFirstName, Contacts.sLastName;"
Case 2
strOrderSql = " ORDER BY Contacts.sFirstName DESC,
Contacts.sLastName;"
End Select
With Me.txtFirstName
.SetFocus
.SelStart = Len(varFName) + 1
End With
Case "txtLastName"
strStartSql = "SELECT Contacts.ContactID, [sLastName] & "", "" & " _
& "[sFirstname] & "", "" & [Contacts]![sCity] & "", "" &
" _
& "[Contacts]![sStateOrProvince] AS Name FROM Contacts"
varLName = Me.txtLastName.Text
If Me.txtFirstName = "" Then
varFName = ""
Else
varFName = Me.txtFirstName
End If
If varLName > "" Then
If varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"") AND ((Contacts.sFirstName)
" _
& "Like """ & varFName & "*""))"
Else
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"")) "
End If
ElseIf varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varFName &
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sLastName, Contacts.sFirstName;"
Case 2
strOrderSql = " ORDER BY Contacts.sLastName DESC ,
Contacts.sFirstName;"
End Select
With Me.txtLastName
.SetFocus
.SelStart = Len(varLName) + 1
End With
End Select

strSql = strStartSql & strNameSql & strOrderSql
With Me.lstContacts
.RowSource = strSql
.Requery
.Value = Null
End With
End Function

'end of code

Please be aware that this code is not intended to actully apply to your
situation but should only be use as a template for how to accomplish what you
want to do.

I hope this is not too confusing.
 
B

Bob Howard

Thanks. This may be exactly (or close to) what I need. I haven't dug into
the details yet, but I will shortly. Thanks again... bob

Mr B said:
Bob,

I recently developed an applicaiton for a client where I had much the same
situation. I really needed a list box to display the list of records but
I
also needed the capability of the user to be able to type in values and
have
the list box immediately filtered to the typed criteria.

In my situation I had a list of individuals names, both first and last
names. So in my case I have two text boxes above the list box. Actully
my
text boxes are in the header of my form and the list box is in the details
section.

In a nutshell what I am doing is that I am creating the sql statement on
the
fly that will return the filtered values for the list box. In my case the
user can use either or both of the text boxes to type a value into for
filtering the list box. I also have a command button that lets the user
clear all entries in the two text boxes. I also have a group control that
has two options for sorting the list box in asscending and descending
order.

I am going to provide you with the code for my controls and hope that the
code will be at least helpful.

As an explanation, here are the names of my controls:
txtFirstName = name of text box where user can type in the first name
txtLastName = name of text box where user cna type in the last name
cmdClearAll = name of command button to clear what user types into text
boxes
grpSortList = name of group control with two option buttons for sorting
order
lstContacts = name of list box control

I also have the following variables defined in the declarations area of my
VBA code for the form.
Dim strActiveControl As String
This variable is being set in the "Got Focus" event of each of the text
boxes.

Here is the function that is called to set the variables value
Function SetCurControlName()
strActiveControl = Me.ActiveControl.Name
End Function

Here is the code for the Got Focus event of the "txtFirstName" text box
Private Sub txtFirstName_GotFocus()
SetCurControlName
End Sub

Here is the code for the Got Focus event of the "txtLastName" text box
Private Sub txtLastName_GotFocus()
SetCurControlName
End Sub

Here is the code for the On Change event of the "txtFirstName" text box
Private Sub txtFirstName_Change()
If Me.txtFirstName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtLastName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the code for the On Change event of the "txtLastName" text box
Private Sub txtLastName_Change()
If Me.txtLastName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtFirstName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the "GetContactsList" function
Function GetContactsList()
Select Case strActiveControl
Case "txtFirstName"
strStartSql = "SELECT Contacts.ContactID, [sFirstName] & "" "" &
[slastname] & "", " _
& """ & [Contacts]![sCity] & "", "" &
[Contacts]![sStateOrProvince] AS Name FROM Contacts"
varFName = Me.txtFirstName.Text
If Me.txtLastName = "" Then
varLName = ""
Else
varLName = Me.txtLastName
End If
If varFName > "" Then
If varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ &
varLName
& "*"") " _
& "AND ((Contacts.sFirstName) Like """ & varFName &
"*""))"
Else
strNameSql = " WHERE (((Contacts.sFirstName) Like """ &
varFName & "*"")) "
End If
ElseIf varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varLName &
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sFirstName, Contacts.sLastName;"
Case 2
strOrderSql = " ORDER BY Contacts.sFirstName DESC,
Contacts.sLastName;"
End Select
With Me.txtFirstName
.SetFocus
.SelStart = Len(varFName) + 1
End With
Case "txtLastName"
strStartSql = "SELECT Contacts.ContactID, [sLastName] & "", "" & " _
& "[sFirstname] & "", "" & [Contacts]![sCity] & "", ""
&
" _
& "[Contacts]![sStateOrProvince] AS Name FROM Contacts"
varLName = Me.txtLastName.Text
If Me.txtFirstName = "" Then
varFName = ""
Else
varFName = Me.txtFirstName
End If
If varLName > "" Then
If varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"") AND ((Contacts.sFirstName)
" _
& "Like """ & varFName & "*""))"
Else
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"")) "
End If
ElseIf varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varFName &
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sLastName, Contacts.sFirstName;"
Case 2
strOrderSql = " ORDER BY Contacts.sLastName DESC ,
Contacts.sFirstName;"
End Select
With Me.txtLastName
.SetFocus
.SelStart = Len(varLName) + 1
End With
End Select

strSql = strStartSql & strNameSql & strOrderSql
With Me.lstContacts
.RowSource = strSql
.Requery
.Value = Null
End With
End Function

'end of code

Please be aware that this code is not intended to actully apply to your
situation but should only be use as a template for how to accomplish what
you
want to do.

I hope this is not too confusing.

--
HTH

Mr B
askdoctoraccess dot com


Bob Howard said:
I'm aware of your "combo box alternative" and the AutoExpand property.
But
the nature of the application is such that a ListBox is what's needed on
the
screen.

Bob
Click to expand...
Click to expand...
 
M

Mr B

You are quite welcome.

If you need further assistance, please just post back.

--
HTH

Mr B
askdoctoraccess dot com


Bob Howard said:
Thanks. This may be exactly (or close to) what I need. I haven't dug into
the details yet, but I will shortly. Thanks again... bob

Mr B said:
Bob,

I recently developed an applicaiton for a client where I had much the same
situation. I really needed a list box to display the list of records but
I
also needed the capability of the user to be able to type in values and
have
the list box immediately filtered to the typed criteria.

In my situation I had a list of individuals names, both first and last
names. So in my case I have two text boxes above the list box. Actully
my
text boxes are in the header of my form and the list box is in the details
section.

In a nutshell what I am doing is that I am creating the sql statement on
the
fly that will return the filtered values for the list box. In my case the
user can use either or both of the text boxes to type a value into for
filtering the list box. I also have a command button that lets the user
clear all entries in the two text boxes. I also have a group control that
has two options for sorting the list box in asscending and descending
order.

I am going to provide you with the code for my controls and hope that the
code will be at least helpful.

As an explanation, here are the names of my controls:
txtFirstName = name of text box where user can type in the first name
txtLastName = name of text box where user cna type in the last name
cmdClearAll = name of command button to clear what user types into text
boxes
grpSortList = name of group control with two option buttons for sorting
order
lstContacts = name of list box control

I also have the following variables defined in the declarations area of my
VBA code for the form.
Dim strActiveControl As String
This variable is being set in the "Got Focus" event of each of the text
boxes.

Here is the function that is called to set the variables value
Function SetCurControlName()
strActiveControl = Me.ActiveControl.Name
End Function

Here is the code for the Got Focus event of the "txtFirstName" text box
Private Sub txtFirstName_GotFocus()
SetCurControlName
End Sub

Here is the code for the Got Focus event of the "txtLastName" text box
Private Sub txtLastName_GotFocus()
SetCurControlName
End Sub

Here is the code for the On Change event of the "txtFirstName" text box
Private Sub txtFirstName_Change()
If Me.txtFirstName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtLastName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the code for the On Change event of the "txtLastName" text box
Private Sub txtLastName_Change()
If Me.txtLastName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtFirstName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the "GetContactsList" function
Function GetContactsList()
Select Case strActiveControl
Case "txtFirstName"
strStartSql = "SELECT Contacts.ContactID, [sFirstName] & "" "" &
[slastname] & "", " _
& """ & [Contacts]![sCity] & "", "" &
[Contacts]![sStateOrProvince] AS Name FROM Contacts"
varFName = Me.txtFirstName.Text
If Me.txtLastName = "" Then
varLName = ""
Else
varLName = Me.txtLastName
End If
If varFName > "" Then
If varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ &
varLName
& "*"") " _
& "AND ((Contacts.sFirstName) Like """ & varFName &
"*""))"
Else
strNameSql = " WHERE (((Contacts.sFirstName) Like """ &
varFName & "*"")) "
End If
ElseIf varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varLName &
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sFirstName, Contacts.sLastName;"
Case 2
strOrderSql = " ORDER BY Contacts.sFirstName DESC,
Contacts.sLastName;"
End Select
With Me.txtFirstName
.SetFocus
.SelStart = Len(varFName) + 1
End With
Case "txtLastName"
strStartSql = "SELECT Contacts.ContactID, [sLastName] & "", "" & " _
& "[sFirstname] & "", "" & [Contacts]![sCity] & "", ""
&
" _
& "[Contacts]![sStateOrProvince] AS Name FROM Contacts"
varLName = Me.txtLastName.Text
If Me.txtFirstName = "" Then
varFName = ""
Else
varFName = Me.txtFirstName
End If
If varLName > "" Then
If varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"") AND ((Contacts.sFirstName)
" _
& "Like """ & varFName & "*""))"
Else
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"")) "
End If
ElseIf varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varFName &
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sLastName, Contacts.sFirstName;"
Case 2
strOrderSql = " ORDER BY Contacts.sLastName DESC ,
Contacts.sFirstName;"
End Select
With Me.txtLastName
.SetFocus
.SelStart = Len(varLName) + 1
End With
End Select

strSql = strStartSql & strNameSql & strOrderSql
With Me.lstContacts
.RowSource = strSql
.Requery
.Value = Null
End With
End Function

'end of code

Please be aware that this code is not intended to actully apply to your
situation but should only be use as a template for how to accomplish what
you
want to do.

I hope this is not too confusing.

--
HTH

Mr B
askdoctoraccess dot com


Bob Howard said:
I'm aware of your "combo box alternative" and the AutoExpand property.
But
the nature of the application is such that a ListBox is what's needed on
the
screen.

Bob

Is there a reason why you can't use a combobox instead of a listbox?
With
AutoExpand Property set to Yes, which is the default setting, it does
this
automatically.
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Howard

GO IT! Thanks so much... bob

Mr B said:
You are quite welcome.

If you need further assistance, please just post back.

--
HTH

Mr B
askdoctoraccess dot com


Bob Howard said:
Thanks. This may be exactly (or close to) what I need. I haven't dug
into
the details yet, but I will shortly. Thanks again... bob

Mr B said:
Bob,

I recently developed an applicaiton for a client where I had much the
same
situation. I really needed a list box to display the list of records
but
I
also needed the capability of the user to be able to type in values and
have
the list box immediately filtered to the typed criteria.

In my situation I had a list of individuals names, both first and last
names. So in my case I have two text boxes above the list box.
Actully
my
text boxes are in the header of my form and the list box is in the
details
section.

In a nutshell what I am doing is that I am creating the sql statement
on
the
fly that will return the filtered values for the list box. In my case
the
user can use either or both of the text boxes to type a value into for
filtering the list box. I also have a command button that lets the
user
clear all entries in the two text boxes. I also have a group control
that
has two options for sorting the list box in asscending and descending
order.

I am going to provide you with the code for my controls and hope that
the
code will be at least helpful.

As an explanation, here are the names of my controls:
txtFirstName = name of text box where user can type in the first name
txtLastName = name of text box where user cna type in the last name
cmdClearAll = name of command button to clear what user types into text
boxes
grpSortList = name of group control with two option buttons for sorting
order
lstContacts = name of list box control

I also have the following variables defined in the declarations area of
my
VBA code for the form.
Dim strActiveControl As String
This variable is being set in the "Got Focus" event of each of the text
boxes.

Here is the function that is called to set the variables value
Function SetCurControlName()
strActiveControl = Me.ActiveControl.Name
End Function

Here is the code for the Got Focus event of the "txtFirstName" text box
Private Sub txtFirstName_GotFocus()
SetCurControlName
End Sub

Here is the code for the Got Focus event of the "txtLastName" text box
Private Sub txtLastName_GotFocus()
SetCurControlName
End Sub

Here is the code for the On Change event of the "txtFirstName" text box
Private Sub txtFirstName_Change()
If Me.txtFirstName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtLastName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the code for the On Change event of the "txtLastName" text box
Private Sub txtLastName_Change()
If Me.txtLastName.Text > "" Then
Me.cmdClearAll.Enabled = True
Else
If Me.txtFirstName > "" Then
Me.cmdClearAll.Enabled = True
Else
Me.cmdClearAll.Enabled = False
End If
End If
'call the "GetContactsList" function
GetContactsList
End Sub

Here is the "GetContactsList" function
Function GetContactsList()
Select Case strActiveControl
Case "txtFirstName"
strStartSql = "SELECT Contacts.ContactID, [sFirstName] & "" "" &
[slastname] & "", " _
& """ & [Contacts]![sCity] & "", "" &
[Contacts]![sStateOrProvince] AS Name FROM Contacts"
varFName = Me.txtFirstName.Text
If Me.txtLastName = "" Then
varLName = ""
Else
varLName = Me.txtLastName
End If
If varFName > "" Then
If varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ &
varLName
& "*"") " _
& "AND ((Contacts.sFirstName) Like """ &
varFName &
"*""))"
Else
strNameSql = " WHERE (((Contacts.sFirstName) Like """ &
varFName & "*"")) "
End If
ElseIf varLName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varLName
&
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sFirstName,
Contacts.sLastName;"
Case 2
strOrderSql = " ORDER BY Contacts.sFirstName DESC,
Contacts.sLastName;"
End Select
With Me.txtFirstName
.SetFocus
.SelStart = Len(varFName) + 1
End With
Case "txtLastName"
strStartSql = "SELECT Contacts.ContactID, [sLastName] & "", "" & " _
& "[sFirstname] & "", "" & [Contacts]![sCity] & "",
""
&
" _
& "[Contacts]![sStateOrProvince] AS Name FROM
Contacts"
varLName = Me.txtLastName.Text
If Me.txtFirstName = "" Then
varFName = ""
Else
varFName = Me.txtFirstName
End If
If varLName > "" Then
If varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"") AND
((Contacts.sFirstName)
" _
& "Like """ & varFName & "*""))"
Else
strNameSql = " WHERE (((Contacts.sLastName) Like """ & " _
& "varLName & "*"")) "
End If
ElseIf varFName > "" Then
strNameSql = " WHERE (((Contacts.sLastName) Like """ & varFName
&
"*""))"
Else
strNameSql = ""
End If
Select Case Me.grpSortList
Case 1
strOrderSql = " ORDER BY Contacts.sLastName,
Contacts.sFirstName;"
Case 2
strOrderSql = " ORDER BY Contacts.sLastName DESC ,
Contacts.sFirstName;"
End Select
With Me.txtLastName
.SetFocus
.SelStart = Len(varLName) + 1
End With
End Select

strSql = strStartSql & strNameSql & strOrderSql
With Me.lstContacts
.RowSource = strSql
.Requery
.Value = Null
End With
End Function

'end of code

Please be aware that this code is not intended to actully apply to your
situation but should only be use as a template for how to accomplish
what
you
want to do.

I hope this is not too confusing.

--
HTH

Mr B
askdoctoraccess dot com


:

I'm aware of your "combo box alternative" and the AutoExpand property.
But
the nature of the application is such that a ListBox is what's needed
on
the
screen.

Bob

Is there a reason why you can't use a combobox instead of a listbox?
With
AutoExpand Property set to Yes, which is the default setting, it
does
this
automatically.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word Template where user inputs <TITLE> and then that can be displayed in footers or headers [MAC] 0
Listbox search question 4
ADDING values from one listbox to another on same form 1
printing from multiselect listbox 1
Passing values of listbox to other form's combobox 1
Listbox Always Selects First Value 0
Listbox Help (remove selected item) 1
Messages showing in "Find related" but not in Conversation view 0

Top