Progress Tracker Form

Mike Wolfe -

Sometimes an Access application includes a recurring task with many individual subtasks that all must be completed before the main task can be considered done.

Occasionally these tasks are very structured, where certain subtasks are absolute prequisites to other subtasks.  In those cases, I've built those dependencies into the application itself.  If there are no dependencies among the tasks, though, this approach is too limiting.

The approach I'm showing below is one that makes the most sense when you have a recurring process with lots of required subtasks with no dependencies among the subtasks.

Progress Tracker Screenshot

Here's what the form looks like:

Items of Note

Text Box Formatting

The green/yellow/red lights are nothing more than text boxes.

The ControlSource for each text box is GreenLight, YellowLight, and RedLight, as defined in the form's RecordSource query:

GreenLight: IIf([StatusCode]='C','=',Null)
YellowLight: IIf([StatusCode]='I','=',Null)
RedLight: IIf([StatusCode]='U','=',Null)

In other words, if the text box is equal to the equal sign (=), then the corresponding circle appears on the form.

Here are the other important text box properties:

  • Font: Webdings
  • Font size: 10
  • Width: 0.125"
  • Height: 0.1979"
  • Fore color: Green (#70AD47), Yellow (#FFC20E), Red (#BA1419)

Not Applicable Items

Items that do not apply to the current defendant are marked out with a strikethrough.

As with the red/yellow/green circles, the strikethrough is nothing more than a standard text box.

The ControlSource of the text box is "Strikethrough" as defined in the form's RecordSource query:

Strikethrough: IIf([StatusCode]='H',String(100,'-'),Null)

The String() function simply repeats the character you pass to it a certain number of times.  I sized the tbStrikethrough text box so that it is the exact same size and position as the description text box.  

Color Coding

In this example, I'm using a standard red-yellow-green scheme.  

  • Red: Not started yet
  • Yellow: In progress
  • Green: Complete

While these are universally understood colors, they are not universally distinguishable colors.  For this reason, you may want to use a different color scheme.

To help red-green color blind users, I did arrange the statuses into separate columns: green, then yellow, then red.

Hard-Coded Items

The Edit button next to each item does something different for each row.

For example, clicking "Edit" on the "Driving Record" row will open up a form to enter moving violations, while clicking "Edit" on the "Military History" form will open up a different form to enter the defendant's military record (if they have one).

By hard-coding the tasks, I retain complete developer control over what appears in the list.  This allows me to safely add task-specific logic to each one.  

Possible Variations

User-Editable List of Items

This approach could be easily adapted to make the list user-editable.

For example, instead of having each row open a different form, each row could open the same form but with different filtering.  I can't actually think of a good example scenario for that approach right now, but that's probably due to my own lack of imagination.

User Templates

Though it may seem counterintuitive, letting users add arbitrary items to the list is an inflexible approach.

It greatly limits what you can do when the user could enter literally anything as a list item.  Instead, I can imagine a scenario where you have dozens of possible recurring tasks, but a different subset of those tasks applies to different accounts/clients/customers/etc.  

You could allow your users to build and save these various task subsets as templates.  They could then assign the appropriate task templates to each account.  When it comes time to create a new set of tasks, the application would only create tasks based on the account's assigned task template.