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.
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.
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.
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.
|
The code for 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.