Automatically fill in Access text boxes based on a combo box selection

Your employees would like a quick way to look up each others’ business phone numbers. They want to be able to choose an employee’s last name from a list and have Access output the employee’s first name and phone number.

To create this form, follow these steps:

1. Open the Employees database.
2. Click Forms under Objects in the Database window, click the New button, and then click OK. In Access 2007, click the Create tab and then click Forms Design in the Forms group.
3. Click the Combo box control in the Controls toolbox. In Access 2007, click the Design tab under Forms Design Tools and click Combo Box (Form Control) in the Controls group.
4. Click and drag in the form where you want to locate the control.
5. Click Next.
6. Select the Employees table and then click Next.
7. Select the Employee ID, Last Name, First Name, and Phone Number fields.
8. Click Next.
9. Click the drop-down arrow in the first text box and click Last Name.
10. Click Next.
11. Adjust fields as necessary and then click Next.
12. Enter Employee Contact Form and click Finish.
13. Click in the Combo Box label control and change the label to Last Name.
14. Click the Text Box control in the Controls toolbox (Access 2003) or the Controls group (Access 2007).
15. Click and drag to create an unbound text box control below the combo box.
16. Change the text box label to First Name.
17. Click the Text Box control in the Controls toolbox (Access 2003) or the Controls group (Access 2007).
18. Click and drag to create an unbound text control below the first text box.
19. Change the text box label to Phone Number (Figure A).

Figure A

1. Right-click the combo box and select Properties.
2. Click in the On Change property box in the Event tab and select Event Procedure.
3. Click the Build button and enter this code (Figure B):

Me.Text19 = Me.Combo17.Column(2)
Me.Text21 = Me.Combo17.Column(3)

Figure B

1. Add an error handler to the On Change event subroutine.
2. Press [Alt] + [Q].

Employees can now use this form to scroll through a list of employees sorted by last name, and Access will immediately fill in the person’s first name and business phone number (Figure C).

Figure C

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.