Using combo box in a form -- how to make drop-down work?

[Rachel Garrett]

I created a form from a query, and I would like users to be able to
select the key field from a combo box in order to find the record they
want to edit. I went into the "Properties" of the column in Query
Design view.

Field Properties >> Lookup >> Display Control >> Combo box
Field Properties >> Lookup >> Row Source Type >> Table/query
Field Properties >> Lookup >> Row Source >> [name of the query]

I also tried having the Row Source be the name of the table that the
query is originally pulling from.

When I get to the form, I do now have a drop-down list of the keys for
all the records in a query. The form is editable. But when I try to
select a different key from the combo box, I get the message "Field
cannot be updated."

What properties do I need to set so that users can have a combo box to
navigate to the record they want?

Thank you,
Rachel

 

[Duane Hookom]

The combo box would be unbound (nothing in the control source).

There is a combo box wizard that creates this functionality for you.

 

[Klatuu]

First of all, I recommend you not use a lookup field. They cause more
problems than any value they have.

A combo used to search for a record should not be a bound control, becuase
it will change the value of the current record when it is. The usual
practice is to use an unbound combo.

Normally, a primary key field doesn't give a user much info. In most cases,
a record will have a primary key and some descriptive text field
understandable to we human types. In this case, the combo should have two
columns. One for the primary key field and one for the descriptive field.
You make the primary key column hidden so the user sees only the descriptive
column. That is done with the combo's Column Widths property. Lets use an
employee table with an autonumber primary key field as an example.

The combo's row source would be:
SELECT EmployeeID, EmployeeLastName & ", " & EmployeeFirstName As EmpName
FROM tblEmployee ORDER BY EmployeeLastName, Employee FirstName;

Now, set these properties:

Column Count = 2
Bound Column = 1
Column Widths = 0"; 2" (0 make the ID field hidden. The 2 can be however
wide necessary to show the employee name
Limit To List = Yes

Now to do the search, you use the After Update event of the combo:

PrivateSub cboEmpLookup_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpLookup
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

When the user selects an employee from the combo list, the code above will
find the employee's record and make it the current record.

 

[Rachel Garrett]

Thanks Duane! The wizard took care of what I needed to do.

Dave, thank you for the thorough response. I will bookmark it, because
I think it would be great if I could let users choose between several
different items to select from, without them needing to search on
their own.

Thanks,
Rachel