Using Combo Box with Table Rows in Form

Q

I have a field in a MS Access form that is a foreign key pointing to another table. Can I use a combo box for this field?

✍: FYIcenter.com

A

Yes. You can. This tutorial shows you how to add a combo box with rows from a reference table and bind it to a column in the supporting table in MS Access 2000.

Another problem in the standard Account Form created by the Form Wizard is the foreign key field, which allows user to enter any number. If number entered does not match any record in the reference table, it will cause problems.

Here are steps I used to change the form design to make the foreign key field as a combo box to select any existing record from the reference table.

0. Continue from the previous tutorial to create the columnar form.

1. Open the Account Form in the Form Design mode.

2. Right mouse click in the MS Access menu area and select Toolbox. The Toolbox window shows up.

3. Open the properties box for the client ID field. Click the All tab. Change the value of Enabled to "No". Change the value of Locked to "Yes". Change the value of Back Color to "12632256". These changes will stop user using this field.

4. Click the Combo Box icon in the Toolbox window. Then click on the blank space next to the client ID. A new combo box field shows up.

5. Click the new combo box label and delete it.

6. Open the properties box for the new combo box field. Click the All tab. Change the value of Control Source to "client_id". Change the value of Row Source Type to "Table/Query". Change the value of Row Source to "Client" to use records from the Client table as the combo box list. Change the value of Column Count to "2" to use only the first 2 columns from Client records. Change the value of Column Width to '0";1"' to hide the first column and display the second column with 1 inch width for the combo box list. Change the value of Bound Column to "1" to bind the first column of Client records to the Control Source "client_id". The picture below shows some of these changes:
MS Access Combo Box with a Table or Query

7. Then Close the properties box and switch the form to the Form View mode. The Client ID field is must easier to use now.

8. Repeat steps above to add a combo box for the Manager ID field.

2010-04-10, 8666👍, 0💬