Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.
Option Compare Database Const constErrNoError = 0 Const constQuote = """" Public Function SearchRecordset(ctlText As Control, _ ctlList As Control, strBoundField As String) As Variant ' Search through a bound listbox, given text to ' find from a text box. ' Move the list box to the appropriate row. ' The listbox can have either a table or a dynaset ' (a query or an 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've not yet left the ' control. Its value (or its .Value property) isn't ' set until you leave the control. rst.FindFirst "[" & strBoundField & "] >= " & _ constQuote & ctlText.Text & constQuote ' Find a match? Set the ListBox to ' the correctly value. If Not rst.NoMatch Then ctlList = rst(strBoundField) End If varRetval = constErrNoError ExitHere: SearchRecordset = varRetval On Error Resume Next rst.Close Set rst = Nothing Exit Function HandleErr: varRetval = CVErr(Err) Resume ExitHere End Function Private Sub List11_AfterUpdate() UpdateSearch Me.Text9, Me.List11 End Sub Private Sub Text9_Change() SearchRecordset Me.Text9, _ Me.List11, "LastName" End Sub Private Sub Text9_Exit(Cancel As Integer) UpdateSearch Me.Text9, Me.List11 End Sub