Access Cookbook - Ken Getz [49]
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.
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