Create an Auto fill-in form in Access

By | Feb 28, 2008

When you’re doing data entry, you want to minimize keystrokes as much as possible to save time (not to mention prevent carpal tunnel). Because we all love to save time, don’t we? One good way to save keystrokes is to have fields fill-in automatically as you enter data. For example, once you enter the customer number, the company name, address and phone number could automatically fill in.
Here is a relatively simple way to create that form. For this example, I’ll be using 3 tables. The Students table has 3 fields: Student_ID, FirstName, and LastName. The Classes table has 2 fields: Class_ID and ClassName. The Registration table has 2 fields: Student_ID and Class_ID. You’ll notice those two fields already exist in the other tables.

The simplest way to create your autofill-in form is to create a query from the fields you want to auto fill. In this case, I want a registration form that when I enter the Student ID and the Class ID, the student’s name and the class name is automatically filled in.

  1. Create a query in design view and choose the tables you want to pick fields from. In this case, the Students, Classes, and Registration tables. If you do not already have relationships between the tables, you should create them now.
  2. Now, choose the fields that you want in the query. In this case, we want the Student_ID and the Class_ID from the Registration table. We want to use these because we want the update from our data entry to go to that table. Then choose the FirstName and LastName from the Students table and the ClassName from the Classes table.
  3. Save your query.
  4. Create a form using the Forms Wizard. Why use the Wizard? Because it’s easy, and we all like easy! I usually use the wizard to create the form and then make changes I want in the design view. It saves time.
  5. Choose the query you just made for the basis of your form. Choose the rest of your form options and click finish.

That’s it. You now have a autofill-in form. Go ahead and try it! When you enter the Student ID and hit tab, the student’s name should fill in. When you enter the Class ID, the class name should fill in. At this point, I would go in and set the tab order to go from the Student ID to the Class ID, so you don’t have to use the couple of extra tabs to go through the fields that automatically fill in. If you find you need to change something in those fields, you can always click on them.

I uploaded the database I used in the above example to give you an idea of how these tables, query and form will look. You can access it here.

Remember, when you are creating a database, plan out what you want to do ahead of time. It’s easier to set up the database if you have the design sketched out ahead of time.

Similar Posts:

8 Comments so far
  1. Celine (1 comments) February 17, 2010 5:22 am

    The class name autofills when I enter the class ID but the last name and first name does not autofull when I enter the student ID. Why is that?

  2. Jodith (189 comments) February 20, 2010 8:38 am

    I would have to look at the database to be sure, but have you checked that you have a one to one relationship between the student ID and the last and first name?

  3. Zyl (1 comments) July 13, 2010 5:52 am

    Would it be possible to keep filling the tables using the form you posted?? What I need is a form which autofills (from a query) but at the same time keeps updating information (to the tables), when the form is filled in with new data. What method could be used? Thanks in advanced..

  4. Jodith (189 comments) July 15, 2010 3:37 pm

    This form will write back to the table as you fill data in. That is essentially what a form does. This one is just set up so that it autofills certain data as you go.

  5. Yavor (1 comments) July 23, 2010 9:58 pm

    Hello. I make everithing that You mentioned but in test database everithing is OK. But when i make true database access autofill didn’t work
    I create a database with query and form with subform connected 2 tables. But i do not know how did it. I try to repeat but in table i see ID but no field and autofilling is not work. Can You help me

  6. Jodith (189 comments) July 24, 2010 10:10 am

    The only way I can help is to actually look at your database. If you e-mail it to me, I’ll take a look at it and see if I can figure out your error.

  7. skip (1 comments) December 4, 2012 2:04 pm

    I did as you explained in the article above using Access 2007. When I tried to save my form I was told “The wizard is unable to open your form in Form view or Datasheet view, possibly because another user has a source table open in exclusive mode. Your form will be opened in Design view.” I am the only person using my database and all tables and queries are closed. Then when I save it and open it in form view I am unable to enter data into it.
    Can you help me?

  8. Diane (1 comments) January 4, 2013 8:44 am

    Thank you for sharing this information. I had been trying for sometime to similar. This approach worked very well. Saved me much time and frustration. Thanks so much for sharing!!!!

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)



CommentLuv badge

Comments links could be nofollow free.

© 2007 Administrative Arts, - WordPress Themes by DBT Privacy Notice