Add a Check List to your MS Access Database

Add a Checklist to MS Access

Checklists are very useful. However, they can be a pain both for developer and for the user. The last thing you want is a subform comprising of a set of of a combo boxes that the user has to select in order to provide the question and the answer! It’s quite a difficult problem to solve. I have completed several blogs on Check Lists. One covering the setting up a single checklist, another blog leverages the skills learnt and the code developed in the single checklist to create multiple checklists. There’s a Blog I term “CheckList Extras” where I demonstrate the extra functionality that evolved from my development of these nice checklist Solutions. I’m particularly proud of changing the checkbox into a text box, allowing for the collection of barcodes. It was such a minor and obvious change, but it opened up many more possibilities for this coding system.

Checklist Required

Checklist Required

Video 1 (1.27)

Emulating paper forms in MS Access isn’t always an intuitive operation. I emulate one of the problems in this video, Video 1 (1.27). The rest of the Videos on this page demonstrate how to solve this problem… If you’re not already the go to person in your organisation for MS Access problems then solving your problem with this technique should get you noticed!

Video 1 (1.27)

Play Video

… …

How to add a Checklist to MS Access

How to - Checklist

You might be tempted to do this by creating a set of fields across in your table, however this is not considered good design. A better way would be to have a table with the check items individually listed in the table.

In this Blog I would like to demonstrate one method of achieving this.

Stock Image

The minimum number of tables required to make this work is 3 tables, I have given these generic terms: master, data and list.

To explain the use of these tables I will refer to a specific case. Consider a business which manages weddings for couples.

In this case the master table “tblMaster” will contain details about the “wedding planners” customers.

The data table “tblData” will store the data for each individual customer.

Finally a table “tblList” that lists the “check off” items that need to be transferred to the data table “tblData”.

The transfer of the list into the data table is performed by just two functions “Copy List” fCopyListToData, and “Append List” fAppendListToData.

There are several other supporting functions in this example.

fCopyListToData puts the data from the list (tblList) into a recordset and then loops through the record set extracting the Row information from each field, then transfers that information via the function fAppendListToData into the data table “tblData”.

“tblData” is what this code is about, it is this set of data that is created. Looking at the data table (tblData) we have five fields: dataID, dataLinkID, dataSets, dataItems and dataTickedOff.

dataID is not necessary and can be ignored.

dataLinkID is used to relate the values in this table (tblData) to the master table (tblMaster).

“dataSets” would not be necessary if you only had one set of data in your list. It is quite common to have a list divided into separate sets of information so in that instance it would be required and really for a single list with only one set, then you just add a “1” (or some other identifier) and treat it as if it was a set from a number of sets.

“dataItems” links back to the table list “tblList” and can be used to find the string value related to each item.

“dataTickedOff” is a boolean field which allows you to to flag whether a list item has been done, or not done.

The minimum number of tables required to make this work is 3 tables, I have given these generic terms: master, data and list.

To explain the use of these tables I will refer to a specific case. Consider a business which manages weddings for couples.

In this case the master table “tblMaster” will contain details about the “wedding planners” customers.

The data table “tblData” will store the data for each individual customer.

Finally a table “tblList” that lists the “check off” items that need to be transferred to the data table “tblData”.

The transfer of the list into the data table is performed by just two functions “Copy List” fCopyListToData, and “Append List” fAppendListToData.

There are several other supporting functions in this example.

fCopyListToData puts the data from the list (tblList) into a recordset and then loops through the record set extracting the Row information from each field, then transfers that information via the function fAppendListToData into the data table “tblData”.

“tblData” is what this code is about, it is this set of data that is created. Looking at the data table (tblData) we have five fields: dataID, dataLinkID, dataSets, dataItems and dataTickedOff.

dataID is not necessary and can be ignored.

dataLinkID is used to relate the values in this table (tblData) to the master table (tblMaster).

“dataSets” would not be necessary if you only had one set of data in your list. It is quite common to have a list divided into separate sets of information so in that instance it would be required and really for a single list with only one set, then you just add a “1” (or some other identifier) and treat it as if it was a set from a number of sets.

“dataItems” links back to the table list “tblList” and can be used to find the string value related to each item.

“dataTickedOff” is a boolean field which allows you to to flag whether a list item has been done, or not done.

… …

Overview of Where this is Heading

Overview of the finished Form

 Video 0? - 1:41 min

This video was actually compiled last! Hence the unusual numbering employed to get it to the front of the PlayList! No text comment on it is necessary as it is self explanatory.

 Video 0? - 1:41 min

… …

PDF Presentation

Run through of the Presentation

Video 2  (8:02)

In this video; I run through this a Google Presentation explaining how it works.

Video 2  (8:02)

Presentation Links

… …

Code Walkthrough

Walk through of the Code

Video 3  (9:26)

Video 3  (9:26)

… …

Create a Main Form & Subform

Master & Sub-Form

Video 4  (6:26)

Video 4  (6:26)

… …

Setting up the Combo Box

Setting up the Combo Box

Video 5  (7:15)

Video 5  (7:15)

… …

Advanced Combo Box Settings

Tidy up the Combo Box and Form Layout

Video 6  (3:37)

Video 6  (3:37)

… …