Make a text box look up records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get a text box to behave like a combo box, anyone know how to
easily programatically do this? I have a combo box that I am trying to
replace. The problem is that the end user does not want something that
auto-expanse. For some reason the auto expand is causing a bug I can not
fix, the user does not need it, I can not completely surprese the bug so I am
hoping to just scrap the combo box for a text box.

Basically the text box needs to query the records on the form for a record
that contains the value entered.
 
Hi, Rick.

Combo boxes don't have to auto-expand. In Form Design view, change its
AutoExpand property to No.

HTH
Sprinks
 
Here's a way to make that work:

If we assume you have an unique identifying field in your table called RecID
that you want to use to find your record on your form. The value entered in
the lookup text box would have to match the value of RecID in one of the
table's records. In this scenario, you could code the following on the text
box's (let's call it Lookup) AfterUpdate event:

Private Sub Lookup_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RecID] = " & Str(Me![Lookup])
Me.Bookmark = rs.Bookmark

End Sub

The user types a correct RecID in the Lookup text box and presses enter, and
the record is displayed on the form. I will end by saying that it is
preferable to use the combo box instead of this method, simply because it
gives your user a list to pick from instead of having to remember the value
to type in the box.

Hope that helps!

Bonnie
 
Back
Top