Advanced Forms Design in Microsoft Access 2010 and 2013

Introduction

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: , , , , , , , ,

4 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.

    Sam

    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

Leave a Reply

You must be logged in to post a comment.