Buy Office Enterprise 2007 Power Tip Making forms

Reader Štefan Masič provides today's Power Tip.

In today’s post I would like to share with you some tips about form design that I’m using consistently in all applications I develop. Maybe you can get some benefit from my approach to form design.

As we all know, analysis and preparation of user needs are essential in building all elements of an application. This approach results in forms that  are easy to use, are uniform throughout the whole application,Key Office 2010, and are easy to maintain.

Here are some design needs which must be satisfied on every Access form:
The name of a form must be easily visible. The name of a form must be an expression of its functionality. User must easily identify all mandatory (required) fields. All mandatory fields should be visible on the first page of a form. All fields with a default value, auto number functionality, or read only property should be designed with a different background color.
It is common to put the name of a form into the window’s title with the Forms!FormName.Caption property. I prefer to use the Form Header Section with the label called lblFormName. This label can be larger and is therefore much more visible.


Figure 1: Title of the presented form is larger and more visible than the window’s title.

In my applications all mandatory fields have bold labels with the additional foreground color, named mandatoryLabelColor. All mandatory fields are surrounded with the border color, called mandatoryBorderColor.


Picture 2: Mandatory fields are visible on the first view.

All mandatory fields are positioned on the first page. All other non-mandatory fields are positioned after first page break. With this design, the user can immediately determine which fields are required. All other fields are below them and on a second page, which is immediately visible with moving of the scrollbar. I prefer designing a form with page breaks and using the scrollbar for easy moving between pages. From my experience, this works better than a Tab control on forms with many fields.

All fields with default values, fields with the auto number functionality or read-only fields should have a different background color. For example: I use a yellow background color, called specialBackgroundColor. Additionally, all those fields have the AutoTab property set to No.

As mentioned above I use three colors, mandatoryLabelColor, mandatoryBorderColor, specialBackgroundColor. For all those colors I always use company colors or ask the customer to pick  specific colors.

In my examples the following colors were used:
color of mandatory labels: Pantone 341 C (CMYK 100, 0,London Of Links, 70, 30 or RGB 0, 117, 84) borders of mandatory fields: Pantone 166 C (CMYK 0, 60, 100, 0 or RGB 239, 124, 0) background color of special fields: Pantone 109 C (CMYK 0, 10, 95, 0 or RGB 255, 222, 0)
If all the colors are determined during the analysis phase, and they’re not likely to change later,  I always use a solution that does not require any programming. Here are Access numbers for all three colors, which I put into the appropriate properties of the labels and fields:
mandatoryLabelColor = LabelName.ForeColor = 5534976 (for Pantone 341 C) mandatoryBorderColor = FieldName.BorderColor = 31983 (for Pantone 166 C) specialBackgroundColor = FieldName.BackColor = 57087 (for Pantone 109 C) Using global variables for more flexibility
If I think the customer might prefer different colors at a later date, I use variables instead of constants. This solution requires creation of a new module, with declarations of three public constants and three public variables. See the example modGlobalConstantsAndVariables below.

Public constants can be used when colors are defined in an early phase and are not likely to be changed. Public variables can be used when it’s possible that the colors might be changed in the future.

Module modGlobalConstantsAndVariables looks like:

'------------------------------------------------------------
' Element: Module,Buy Office Enterprise 2007, 2010
' Description: Definition of global constants and variables.
' - mandatoryLabeleColor
' - mandatoryBorderColor
' - specialBacgroundColor
'' Purpose : Global constants,Windows 7 Enterprise Sale, global variables.
'' Author: Stefan Masic, january 2010
' Information: con = Constant; R=Red; G=Green; B=Blue
' See also: -
' Changes:
' (dd.mm.yyyyy, Author) Text
'------------------------------------------------------------
'-------------------------------------------------------------------- ' Public constants.
Public Const conMandatoryLabelColor = 5334976 ' R=0 G=117 B=84
Public Const conMandatoryBorderColor = 31983 ' R=239 G=124 B=0
Public Const conSpecialBackgroundColor = 57087 ' R=255 G=222 B=0
'--------------------------------------------------------------------
'-------------------------------------------------------------------- ' Public variables.
Public lngMandatoryLabelColor As Long
Public lngMandatoryBorderColor As Long
Public lngSpecialBacgroundColor As Long '--------------------------------------------------------------------
' End of module
'--------------------------------------------------------------------
The simplest programming solution requires creation of a new OnOpen event on each form, with the following code for every mandatory field and every special field:

Private Sub Form_Open(Cancel As Integer) On Error GoTo Err_Form_Open Me.MandatoryLabelName.ForeColor = conMandatoryLabelColor
Me.MandatoryFieldName.BorderColor = conMandatoryBorderColor
Me.SpecialFiledName.BackColor = conSpecialBackgroundColor Exit_ Form_Open Exit Sub Err_Form_Open MsgBox Err.Description
Resume Exit_Form_Open End Sub ‘ Form_Open Storing color values in a table
When I’m creating a general purpose application where colors are likely to change, I use a special table, called tabDefaultApplicationValues. In its simplest definition, this table includes only one row and many fields. This table includes three fields MandatoryLabelColor, MandatoryBorderColor,Office Professional Plus 2010, and SpecialBackgroundColor. I also need function getDefaultApplicationValue(ValueName).

With this method, the simple procedure Form_OnOpen needs a little rearranging:

Me.MandatoryLabelName.ForeColor = lngMandatoryLabelColor
Me.MandatoryFieldName.BorderColor = lngMandatoryBorderColor
Me.SpecialFiledName.BackColor = lngSpecialBackgroundColor
Public variables can be filled during the loading process of Main form or Switchboard with the  following code:

lngMandatoryLabelColor = getDefaultApplicationValue(“MandatoryLabelColor”)
lngMandatoryBorderColor = getDefaultApplicationValue(“MandatoryBorderColor”)
lngSpecialBackgroundColor= getDefaultApplicationValue(“SpecialBackgroundColor ”)
Here is a simple function you can use to retrieve the color values from the table:

'------------------------------------------------------------
' Element: Function, 2010
' Purpose: Get value from table tabDefaultApplicatonValues.
'
' Input: Default value field name (String)
' Output: Value (String)
'
' See also: tabDefaultApplicationValues
' Avtor: Stefan Masic, january 2010
' Remark:
' Changes:
'------------------------------------------------------------ Public Function getDefaultApplicationValue(strDefaultValueName As String) As String On Error GoTo Err_getDefaultApplicationValue Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strDefaultValue As String
Dim strSqlStmt As String ' Check default value name.
If IsNull(strDefaultValueName) Then strDefaultValue = ""
Else ' Define current DB. Set dbs = DBEngine.Workspaces(0).Databases(0) strSqlStmt = "SELECT " & strDefaultValueName & " FROM tabDefaultApplicationValues" Set rst = dbs.OpenRecordset(strSqlStmt) ' Get value if record exists (rst.RecordCount>0). If rst.RecordCount > 0 Then ' Check NULL. If Not IsNull(rst.Fields(0).Value) Then strDefaultValue = rst.Fields(0).Value Else strDefaultValue = "" End If Else strDefaultValue = "" End If ' Free rst in dbs. rst.Close dbs.Close
End If ' IsNull getDefaultApplicationValue = strDefaultValue Exit_getDefaultApplicationValue:
Exit Function
Err_getDefaultApplicationValue:
MsgBox Err.Description & " (Error no.: " & Err.Number & ")"
Resume Exit_getDefaultApplicationValue End Function ' getDefaultApplicationValue
If the solution requires personalization of default application values, or if the solution will be used by many companies, then this table will have two or more rows of default values for every configuration, and the function will have two parameters. Here are two examples:

getDefaultApplicationValue(UserId, ValueName).
getDefaultApplicationValue(CompanyId, ValueName).
The first described approach does not require any programming, and as such it is easier to manage the application over time and with new versions of Access. I recommend this approach.


Send your Power Tips to Chris & Mike at accpower@microsoft.com. <div