Recipe 2.8 Make a Simple "Searching" List Box
2.8.1 Problem
You'd like
to create a text box/list box combination like the one in Windows
Help. As you type in the text box portion of the control, you want
the list box to scroll to match whatever's been
typed so far. You know you could use a combo box for this, but the
combo box keeps closing up. You want something
that's permanently open.
2.8.2 Solution
Entering a portion of the value they're looking for
and seeing the matches displayed as users type is an excellent way to
find specific values in a list. You get the best of both worlds: the
functionality of a combo box and the "permanently
open" look of a list box.The key to implementing this
functionality is the text box's Change event. Every
time the text in the text box changes, the code
you'll use will automatically find the matching
value in the associated list box. You'll be able to
call a function that will handle all the work for you. In addition,
because searching through indexed tables is so much faster than
walking through dynasets (the results of running a query or a SQL
expression), this solution offers two solutions to this problem: one
for list boxes that are bound to tables and another for list boxes
that are bound to queries or SQL expressions. Figure 2-15 shows frmSearchFind in action.
Figure 2-15. Using Incremental Search on frmSearchFind
The methods you'll find in this solution apply only
to bound list boxes.To test out the functionality, open the database
02-08.MDB and then open either frmSearchFind or
frmSearchSeek. As you type in the text box, you'll
see the associated list box scroll to match what
you've typed. If you backspace to delete some
characters, the list box will still match the characters that remain
in the text box. When you leave the text box or click on an item in
the list box, you'll see the full text of the chosen
item in the text box. The functionality is the same no matter which
form you use. frmSearchSeek will look up items faster, though,
because it's guaranteed to use an index to do its
work.Follow these steps to build a form like frmSearchFind, which will use
a query or SQL expression as the row source for the list box:
- In your own database, create a new form that contains at least a text
box and a list box. For the sake of this example, name the text box
txtCompany and the list box lstCompany. - Set properties, as shown
in Table 2-6.
Control type | Property | Setting |
---|---|---|
Text box | Name | txtCompany |
OnExit | [Event Procedure] | |
OnChange | [Event Procedure] | |
List box | Name | lstCompany |
AfterUpdate | [Event Procedure] | |
RowSource | qryCustomers | |
ColumnCount | 2 | |
ColumnWidths | 0 | |
BoundColumn | 2 |
- Import the table Customers and the query qryCustomers from
02-08.MDB . - Put the following code in the lstCompany_AfterUpdate event procedure:
Private Sub lstCompany_AfterUpdate( )
acbUpdateSearch Me.txtCompany, Me.lstCompany
End Sub - Put the following code in the txtCompany_Change event
procedure:Private Sub txtCompany_Change( )
Dim varRetval As Variant
varRetval = acbDoSearchDynaset(Me.txtCompany, _
Me.lstCompany, "Company Name")
End Sub - Put the following code in the txtCompany_Exit event
procedure:Private Sub txtCompany_Exit(Cancel As Integer)
acbUpdateSearch Me.txtCompany, Me.lstCompany
End Sub - Import the module basSearch from 02-08.MDB . This
module contains the code that does all the work.
Every time you change the value in
txtCompany, Access triggers txtCompany's Change
event. The code attached to that event calls down into the common
function, acbDoSearchDynaset . In general, the
syntax for calling acbDoSearchDynaset is:
varRetval = acbDoSearchDynaset(textbox, listbox, "Field to search")
where textbox is a reference to the text
box in which you're typing,
listbox is the list box in which
you're searching, and
"Field
to
search" is the field in
the list box's underlying record source through
which you're going to search.The function acbDoSearchDynaset creates a
dynaset-type Recordset object, searches through it for the current
value of the text box, then sets the value of the list box to match
the value the code found in the underlying record source. Its source
code is:
Public Function acbDoSearchDynaset(ctlText As Control, _
ctlList As Control, strBoundField As String) As Variant
' Search through a bound list box, given text to find from
' a text box. Move the list box to the appropriate row.
' The list box can have either a table or a dynaset (a query
' or a SQL statement) as its row source.
' In:
' ctlText: A reference to the text box you're typing into
' ctlList: A reference to the list box you're looking up in
' strBoundField: The name of the field in the underlying
' table in which you're looking for values
' Out:
' Return value: Either 0 (no error) or an error variant
' containing the error number
Dim rst As DAO.Recordset
Dim varRetval As Variant
Dim db As DAO.Database
On Error GoTo HandleErr
Set db = CurrentDb( )
Set rst = db.OpenRecordset(ctlList.RowSource, dbOpenDynaset)
' Use the .Text property, because you haven't left the control
' yet. Its value (or its .Value property) isn't set until you
' leave the control.
rst.FindFirst "[" & strBoundField & "] >= " & acbcQuote & _
ctlText.Text & acbcQuote
If Not rst.NoMatch Then
ctlList = rst(strBoundField)
End If
varRetval = acbcErrNoError
ExitHere:
acbDoSearchDynaset = varRetval
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
HandleErr:
varRetval = CVErr(Err)
Resume ExitHere
End Function
The example in this solution is also set
up so that if you leave the text box, it pulls in the currently
selected item from the list box. That means that you can use Tab to
leave the text box, and the code will place the value that matches as
much as you've typed so far in the text box.
2.8.3 Discussion
Notice that the list
box's ColumnCount property is 2 and the ColumnWidths
property is 0 in this example. This occurs because the query used,
qryCustomers, contains two columns, with the first column hidden in
the list box. Because you're searching for the
second column, that must be the bound column.This example, as shown so far, uses a
query as the data source for the list box. This method can really
slow things down for large data sets, since it's not
guaranteed that it will be able to use an index. If possible you
should base your list box directly on a table instead, especially if
your data set is much larger than a few hundred rows. In that case,
you can use the Seek method, which is generally much faster than the
FindFirst method used in this example. On the other hand, because it
works with only a single table as its data source,
it's a lot more limiting.To use the Seek method,
you'll need to change a few properties. To test it
out, make a copy of frmSearchFind and call the new form
frmSearchSeek. Change the RowSource property of your list box to be
Customers, rather than qryCustomers. In addition, change the function
that txtCompany calls from its Change event procedure to the
following:
Private Sub txtCompany_Change ( )
Dim varRetval As Variant
varRetval = acbDoSearchTable(Me.txtCompany, _
Me.lstCompany, "Company Name", "Company Name")
End Sub
In this case, you'll be calling the
acbDoSearchTable function, which searches
through an indexed table instead of through an unindexed dynaset. In
general, you'll call
acbDoSearchTable with the following syntax:
intRetval = acbDoSearchTable(textBox, listBox, "BoundField", "IndexName")
where textbox is a reference to the text
box in which you're typing,
listbox is the list box in which
you're searching,
"BoundField"
is the field in the list box's underlying record
source through which you're going to search, and
"IndexName"
is the name of the index you're going to use.
(Usually it'll just be
"PrimaryKey," but in this example
use "Company Name". This table is
indexed both on the Customer ID field (the primary key) and the
Company Name field; you're using the Company Name
index.)The code for
acbDoSearchTable is almost identical to that for
acbDoSearchDynaset , except that the table search
uses the Seek method to search through an indexed recordset instead
of the FindFirst method. Because it can use the index, it should be
able to find matches much more rapidly than
acbDoSearchDynaset .
|
acbDoSearchDynaset ,
acbDoSearchTable , and
acbUpdateSearch is in the module basSearch. If
you want to use this functionality in other applications, import that
module into your application and follow the steps outlined earlier to
set the properties for your text and list boxes. In addition, if you
import the sample code into a database created in Access 2000 or
later, make sure you use the Tools References menu item
from within VBA to add a reference to the Microsoft DAO type library.
By default, Access applications created in those versions
don't include a reference to DAO, and the sample
code in this demonstration requires this reference in order to do its
work.