Add Combo Box to form

Question

I am new to Access and am trying to create my first form.  I have two tables:  Customers & Consultants.  On the form, I show all the Customer Information and the consultants First and Last Name.  The tables obviously are linked by the ID field between the two tables.

Question is, How can I change the Consultant First & Last Name fields to be a Combo Box and when choosing another consultant, store the ID into the Customer table?

Answer 1

Set up the combo box like this:

ControlSource:   ID (or whatever the foreign key in Customers is named)

RowSource:     SELECT ID, FirstName & " " & LastName FROM Consultants 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 ID, LastName & ", " & FirstName FROM Consultants ORDER BY LastName, FirstName;

If any object names include spaces or other special characters you must enclose the name in square brackets, e.g. [First Name].  It is best to avoid spaces or other special characters in object names, as I've done above.

---

Ken Sheridan, Stafford, England

Answer 2

Thanks for the reply.  I just realized that I had the wrong ID as the Control Source and now that I changed it to the correct one, it works great.  Thank you for your reply.