List Box look-up.

[ddmtn546]

I have created an "address" table. I also created a form to display the information from this table. My problem: In the first field on the form, LastName, I want an operator to begin typing the last name and have MSAccess "auto complete" the entry and "look-up" and display all fields for the record. My noviceness shows, as this must be a simple process. Thanks in advance.

 

[papparuff]

Create a separate combobox (call it cboSearch) and place is in the Form Header. Set the following properties for the combobox

Row Source Type: Table/Query
Row Source: SELECT NameID, LName FROM tblAddress
Column Count: 2
Column Widths: 0;1
Bound Column: 1
After Update: [procedure]

You can cut and past the following code: to the cboSearch After Update event

Private Sub cboSearch_AfterUpdate()
' Go to Tools > References and insure that
' there is a reference to
' Microsoft DOA x.x Object Library
' where x.x is the latest version you have

Dim rst As dao.Recordset
Dim varBookmark As Variant

If Not IsNull(cboSearch) Then
Set rst = Me.RecordsetClone

'Find the record that matches the control
With rst
varBookmark = Me.Bookmark
.FindFirst "[NameID] = '" & cboSearch & "'"

If .NoMatch Then
MsgBox "Record not found"
Me.Bookmark = varBookmark
Else
Me.Bookmark = .Bookmark
End If

.Close
End With
Set rst = Nothing
End If

End Sub
John Ruff - The Eternal Optimist