Combo box to find records does not work if AllowEdits=No

[Harold A. Mackey]

Trying to create a simple combo box that looks up record in a table, but I
don't want records to be editable. This breaks the combo lookup feature.

Private Sub Combo19_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDdemo] = " & Str(Nz(Me![Combo19], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

This is straight out of an Access 2000 book, but I am using 2003. Any help
would be appreciated.
Harry

 

[Guest]

Hi, Harold.

There are several ways to handle this. One is to use a second form for the
combo box, and then opening your main form. A "Search" command button on the
main form could bring up this form again for subsequent searches.

Another is to Lock all of the controls except your presumably unbound combo
box in the Form OnOpen event:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.Name <> "MyComboBox" Then
ctl.Locked = True
End If
End For

Hope that helps.
Sprinks

 

[Guest]

Hi Harold,

You are correct about this. About three years ago, this drove me nuts trying
to figure it out! Please see the following document, which includes
instructions at the end for how to deal with this situation:

Combo box to find a record
http://www.access.qbuilt.com/html/find_a_record.html


Tom
_________________________________________

:

Trying to create a simple combo box that looks up record in a table, but I
don't want records to be editable. This breaks the combo lookup feature.

Private Sub Combo19_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDdemo] = " & Str(Nz(Me![Combo19], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

This is straight out of an Access 2000 book, but I am using 2003. Any help
would be appreciated.
Harry