Combo-box data input in Datasheet View in MS Access

5
votes

I need help in creating my first application in MS Access 2007.

Consider a table in MS Access with a column "AgeGroup" and the possible values for this column are "Child" and "Adult". Is there an option in MS Access to enter data into this column using a combo-box with these two values in Datasheet view?

2
ms-access

2 Answers

5
votes

The Simple (but questionable) solution:

(in strict accordance with OP's question)

  1. Create a new table
  2. Add the column which you want to control by a combobox and give it a column name and the Text type.
  3. Make sure that the appropriate column is selected and then click the Lookup tab page in the MS Access table designer
  4. Change the Display Control from TextBox to Combo Box
  5. Select Row Source Type = Value List
  6. Enter the following for Row Source: Child,Adult

Since you probably want to leverage the fact that MS Access is a relational database, you could also have the have the Child and Adult alternatives as rows in a separate AgeCategory table. In order to connect the rows in this table with your combo box you would have to use other Lookup settings. This I leave as an excercise to you.

EDIT: As HansUp is pointing out, it is really not a very good solution define this combo box lookup along with the table definition.

This is probably a better approach for a real world solution:

  1. Create your table
  2. Create a new form which uses your table as data source
  3. Add a combo-box to the form and specify the row sourcing for it
  4. Switch the Form to Datasheet View. A form in Datasheet View looks exactly like the type of row editor which you get when you double-click on a table.
2
votes

Read The Evils of Lookup Fields in Tables

Instead of creating an evil lookup field in your table, build a form which uses the table (or a query based on the table) as the form's record source.

Add your combo box to the form. Use a value list or another query as the combo's row source. Switch the form to datasheet view. You can also select datasheet for "Default View" on the form's property sheet.