Create an Auto fill-in form in Access
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.
- 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.
- 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.
- Save your query.
- 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.
- 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.
Don't miss a single post. Sign up for my RSS feed.
(What the heck is an RSS Feed?)
If you found this post helpful, please click "Share This" to share it with others through e-mail or social media sites like Digg and StumbleUpon.
Leave a Comment