Advanced Forms Design in Microsoft Access 2010 and 2013


If you have mastered the basics of Microsoft Access and are looking for more advanced tips and techniques, you have found the right place. If you are just getting starting with Microsoft Access, I suggest you first go through my MS Access 2007/2010 Tutorial as it provides instructions on all of the basic Access components (Tables, Queries, Forms, Reports and Navigation Forms) including a gentle introduction to relational databases. Once you have mastered these basics, please return to this tutorial for the more advanced features.

Note also the work done in this tutorial on Forms assumes you have made the changes and embellishments to the table designs as shown in the Advanced Database Table Design in MS Access 2010 and 2013 tutorial.

The screen pictures and examples for this advanced tutorial were done using Microsoft Access 2013. This is the latest version at this time. For the most part the features and screens shown here are virtually identical to the Access 2010 and Access 2007 versions.

The intent of this tutorial is to cover more advanced features of MS Access Forms Design including the following:

In my MS Access 2007/2010 tutorial the basic steps for creating a “single table” form and a “Master Detail” form were introduced. Some of the concepts to review here including running through the Create Form wizard, selecting tables and fields, form colors and designs and working with (navigating) forms. For example, in the section on Creating and Running a Data Entry Form the Customer Data entry form was created:
Customer Data Entry Form

The general idea we are working here is to make entering and updating data as easy as possible for the users. So as much as possible we want to try and suggest good data to be put into the form fields, provide default values wherever possible, and apply some common-sense checks to make sure bad data is not entered.

Before we get into these details, we will start with a more complete overview of data entry forms.

1. Four basic types of data entry forms in MS Access

A typical database schema can contain dozens of tables, each with several columns of various data types. We develop applications (forms, reports, menus, etc.) to make the job of manipulating data in those tables easier for users. In Microsoft Access, a form (or data entry form) acts like a “window” into the database schema. An individual form focuses the attention of the user to one or a few of the tables at a time. In addition, a form can give prompts so the user knows what kind of input is expected and how data is to be entered and manipulated. By default, every form in Access has the capability to query existing data in a table, modify existing data and add new data (records) to the table. A form is built up using a main form and possibly one or more “subforms” that correspond to tables in the database. Fields within a form correspond to columns in the database tables.

There are four main types of forms that can be designed.

Single Table Form. This form design contains a single form corresponding to a single database table. This is the most basic type of form.

Single Table Form with lookup field. This form design contains a single form corresponding to a single database table with the addition of one or more fields that display data from some other tables or hold the results of summary or calculated values. This extra data is “looked up” when the form runs.

Master/Detail Form. This form design contains a “master” form and a “Subform” that are arranged in a master/detail (one to many) relationship.

Master/Detail Form with lookup fields. This form design is similar to the Master/Detail but has the additional lookup fields in the master and/or detail blocks.

My MS Access 2007/2010 Tutorial covered how to create single-table forms and master/detail forms. In this tutorial we will cover how to add “lookup” fields (called “unbound” fields” to these form designs. First we will dig a bit deeper into the forms designer and the properties of forms.

Tags: , , , , , , , ,

11 Responses to “Advanced Forms Design in Microsoft Access 2010 and 2013”

  1. sam_i_am_i_is #

    Hi. First, thank you for all these tutorials. They are amazing.

    Second, When I choose Tabular for my AccountsDataEntry form, it doesn’t look the same as yours. I have to choose Columnar to get the same look as yours. I’m using Access 2013. I just wanted to give you a heads up on that. I think I had the same issue with the beginner tutorial, but did not have an account at the time.

    Again, thanks.


    April 11, 2014 at 5:08 pm
  2. scott93063 #

    I’m sure you’ve covered this topic somewhere in your tutorials, but I can’t find it. I’m looking for help with the Form Property for Help Files and Help Context. I know the field is used to insert a filename and the context field identifies a bookmark location. But, what type of files are acceptable? If only .hlp files work, how do I create one? Microsoft help, as usual, is no help at all.

    Is it better ro create a command button for ‘Help’ that opens Word and loads a help document?

    Also, if possible, please modify the search bar on your pages so that I can limit the findings to only one of your topic areas: Oracle, Access, Visio, etc. Even though I include ‘Access’ in my searches, the results include lots of things that aren’t for Access.

    June 5, 2014 at 7:16 am
  3. firedog #

    Thanks!!!! …a lot!!!!! I’m am slowly learning about Access (being self-taught, the going is very slow).

    I thought that I knew a little about Access, and I was right, I do know VERY little. This experience was extremely beneficial to me and will raise my level of knowledge a bit off of the floor. At least I have something to build on. Thanks again, I appreciate it.

    August 20, 2014 at 7:15 pm
  4. ryan #

    Can you please provide some tutorial about updating the balance field when new transaction records are added to the transaction table? I badly need to learn how to do it for my current database project I’m working with right now. Any help would be much appreciated. Thanks in advance.

    July 4, 2015 at 2:34 am
    • It should automatically update the totals when you re-open the form (or refresh the data in the form).

      July 4, 2015 at 10:06 am
  5. sberdeaux #

    I have a data entry form, with a name box for the person entering the data. I have put a check box on the form that they are to check, to “certify” they are the person entering the data. Is there a way to make the check box mandatory? If not, I found where I can tell if they checked the box or not and am thinking I could use that to pop up a message that says they have to check it. I’m not sure how to create the message box though.

    Thanks, again, for your website. It holds a tremendous amount of valuable information.

    August 10, 2015 at 4:49 pm
    • Hi
      When you are in Design Mode on the form you can get the properties of the form.
      There should be some “Events” you can associate with the form such as when you
      want to save data. Create a new event subroutine for something like “Before Insert”
      where you can check if the state of the text box is checked.

      August 11, 2015 at 10:08 am
  6. sberdeaux #

    My apologies, I can’t figure this out.
    Here is the layout of the data entry form:
    ID – a number that is keyed in by a user, entered by the form into a user table. The ID corresponds to an auto numbered “Event” table (primary key). This ID is not entered into the Event table.
    Name – keyed in by a user, entered by the form into the same user table.
    Check box, with a label box that says “I certify that I am the person completing this form”, not entered into any table.
    Two additional fields, into which the user keys in data and the form enters into the user table.

    I can’t enter a subroutine until after the check box, right? I created macro (on Enter) for the field immediately after the check box that says if the check box value =0, then open a MessageBox that says — you must certify that you are the person completing the form.

    The macro is ignored. Help, please.

    August 11, 2015 at 4:30 pm
    • Hi
      I suggest to take that same macro code and make it trigger when the “Before Insert” event happens at the Form level.
      If you can send me the form at my gmail account (see “About Me” on my web site) I can code up a quick example for you.

      August 13, 2015 at 10:08 am

Leave a Reply

You must be logged in to post a comment.