Software

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

You can greatly enhance a form by having Access supply information based on the user's selection. Here's an example that will fill in an employee's first name and phone number when the user chooses that person's last name from a list.

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.

4 comments
qkd000
qkd000

Issue I have in Access 2007. Two get desired multiple fields I have to set column count to 8. All 8 display in combo box pull down. I don't want this. I want first field to display in combo pull down but the data in the others to be available for text boxes. Am I missing another setting.

Marshwiggle
Marshwiggle

This process comes to a screeching halt at Step 7 in Access 2000, where I am looking at a Combo Box Wizard with two list boxes (Available Fields and Selected Fields), and I can not select more than one field at a time in the Available Fields box, but can only add all fields at once, or individual fields one at a time, to the Selected Fields text box. I can probably find a work-around, but it would be so helpful if an "Applicable Versions:" or "Applies To:" line were included at the top of the article.

smith
smith

That's one of my favorite tricks on forms that only need a few fields updated from a combo. It takes a lot less code than looping throug a recordset for each text box or using a dlookup for each. I think it's also faster than the above mentioned methods, although I may be fancying that since I think this trick is clever Thanks for the post. Lisa

alan
alan

Something that I read when I started with access which has stood me in good stead. Never unless it REALLY does not matter leave labels and controls with their default names such as Combo 17 or text 6. What is Combo 17 or text 12? rename your controls with a name that identifies the contol such as cmbNameLookUp. and Text 12 as txtFirstName When you have dozens of controls it makes all the difference and if someone has to come after you and tweak your VBA they will bless you rather than curse you. Cheers - Alan