Using a Combo Box to Select a Field's Value

Question

I want to use a combo box to select data, which will be name.

How should I go about this?

I'm assuming I should populate the CB with data a separate table. There will be about 20 or so names.

I've worked with this before but at my age these things slip away.

Thanks,

James

Answer 1

There is a wizard that walks you through this when selecting the combo box control.

Answer 2

A little more detail would help. Are you trying to use the combo to populate a field in a table? For a name, how is the table with the names structured?

Answer 3

I'm using the combo box to populate a field.

Answer 4

OK, then the combobox wizard should walk you through the process. However, if you are trying to select a person's name It depends on how the name table is strucutred.

Answer 5

As an example a combo box to select an employee by name would be set up as below.  Note that the combo box is bound to an EmployeeID foreign key column, not to the employee's name.  Personal names can legitimately be duplicated, so must never be used as keys.  Nor is a combination of names and other columns as a composite key always reliable.  I was once present at a clinic when two patients arrived within minutes of each other, both female, both with the same first and last names and both with the same date of birth.  People should always be identified by a 'surrogate' numeric primary key, e.g. PatientID, EmployeeID etc., usually an autonumber for convenience.

ControlSource:   EmployeeID (for a bound control; leave blank if the combo box is an unbound 'navigational' control)

RowSource:     SELECT EmployeeID, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource:     SELECT EmployeeID, LastName & ", " & FirstName FROM Employees ORDER BY LastName, FirstName;

However, selecting a person purely by name is not really satisfactory, as personal names can be duplicated (I worked with two Maggie Taylors).  To differentiate between them, further details can be shown concatenated to the name, e.g. job title, department etc.  The following is an example for selecting an employee's line manager in a LineManagerID control in a bound form named frmEmployees:

SELECT EmployeeID,

LastName & ", " & FirstName & (": " + JobTitle) AS LineManager

FROM Employees

WHERE EmployeeID <> NZ([Forms]![frmEmployees]![EmployeeID],0)

ORDER BY LastName, FirstName;

A combo box set up like this would of course list all employees apart from the current one in the form.  If you want to define in advance which employees can be selected as a manager add a Boolean (Yes/No) column, IsManager say, to the table and set its value to True (Yes) for each employee who can be a manager.  Then change the above query's WHERE clause to:

WHERE EmployeeID <> NZ([Forms]![frmEmployees]![EmployeeID],0) AND IsManager