A much simpler version of the same thing can be done with the Access wizards. If you turn on the form wizards in form design and click the Combo Box button and point to a location in the header of your form, you'll automatically get a choice to create a combo box that will look up a record that matches something listed in the dropdown list.
However, keep these things in mind:
it works only when you've bound your form to an entire table (which is not recommended for large recordsets)
the code it creates is horrendously BAD.
There are a number of ways to solve this problem. If you're happy binding your form to the entire table, bookmark navigation (as with the code created by the wizard) will be fine. However, I recommend you use this code instead of the wizard code (assumes the combo box has a bound column with the PK ID of the record you're trying to find):
Private Sub MyComboBox_AfterUpdate()
If IsNull(Me!MyComboBox) Then Exit Sub
With Me.RecordsetClone
.FindFirst "[MyID]=" & Me!MyCombBox
If Not .NoMatch Then
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With
End Sub
The combo box would need to use a SQL rowsource, and it would be something like:
SELECT CompanyID, CompanyName FROM Company ORDER BY CompanyName
And you'd define the combo box to have 2 columns, the first one the bound column, and you'd set the width for the first column to 0. The wizard walk you through doing this and set it up for you. The only thing it does wrong is write really bad code to do so.
Now, if you're not searching for a unique value, it gets more complicated, and you might want to use a different approach. Suppose you have a form that displays people and you want to see the ones for a particular company. In that case, you might want to filter by CompanyName. In that case, instead of doing a Find operation as outlined above, you might want to apply a filter. In that case the AfterUpdate event of your combo box would be something like this:
Private Sub MyComboBox_AfterUpdate()
If IsNull(Me!MyComboBox) Then Exit Sub
Me.Filter = "[CompanyName]=" & Chr(34) & Me!MyComboBox & Chr(34)
Me.FilterOn = True
End Sub
Now, in that case, your combo box would have but one column, with the company name and no hidden ID field, so it's somewhat different.
I don't like programming this kind of filtering as it's provided by the Access UI (you can right click on the CompanyName field and in the shortcut menu that pops up, type in the company name you want to filter for, including wildcards, ? and *). Also, it can get confusing when you try to filter when there's already a filter in place. If you're viewing just CitiCorp and then you filter by JP Morgan Chase, you won't get anything.
Instead, I'll tend to change the Recordsource of the form instead of applying a filter. This means each search will give you a new resultset and is the most efficient approach. It does make things a little bit more complicated in terms of returning to your original starting point, but in a properly-designed app, you should probably be presenting only subsets of data to the users in the first place, so the problem this approach "causes" is one that you'd need to resolve to build a proper and efficient user interface that retrieves only the data that the user really needs.