dinsdag 5 augustus 2008

Management of controls' metadata (tooltips, captions etc.) and international support in your MS Access applications.

A complex access application can have hundreds of controls and the
problem you face is how to smartly manage controls metadata such as
tool tips, captions etc? A flexible solution would allow international
support and update of metadata also by the user himself.

As the standard each control has a number of textual properties and a
developer can assign a value to the tool tip text. Handmade management
of these properties will soon become complex task because of the
growing number of controls. it also makes further customization such
as international support.

An effective solution to this problem is to separate metadata from the
controls and manage it in a separate table(s). Here is a simplified
On form load event a function GetTooltip is called. The argument of
this function is the control name.


Private Sub Form_Load()
For Each CTL In Me.Controls
On Error Resume Next
CTL.ControlTipText = GetTooltip(CTL.Name)
End Sub

Public Function GetTooltip(CTRL As String)
GetTooltip = Nz(DLookup("[CTRL_TOOLTIP_NL]", "CONTROLS",
"UCASE([CTRL_NAME])='" & UCase(CTRL) & "'"), "")
End Function

As you can see the function GetTooltip runs a dlookup function that
finds necessary metadata (in this case dutch translation of the
tooltip text) in the metadata table CONTROLS.

The metadata table look like this:


cmdRefresh Gegevens Vernieuwen Refresh data
cmdRepAgents Rooster Rapport Roster Report
imgAgentsForm Medewerker Gegevens Employee Data
cmdAgentRooster Medewerker Bruto Rooster Employee Bruto Schedule
cmdAgentSkill Medewerker Skills Employee Skills

A nice thing about Dlookup is that you can compute the searched
column. For example if the application has a variable ULan that
carries user language settings, the call of dlookup can be adjusted to
the current user language settings like that:

GetTooltip = Nz(DLookup("[CTRL_TOOLTIP_" & ULAN & "]", "CONTROLS",
"UCASE([CTRL_NAME])='" & UCase(CTRL) & "'"), "")

In this case ULAN shoul be assigned a standard language abbreviation
(NL - for dutch, EN for English etc).

In a similar manner all labels, captions and other textual metadata
can be pulled out of the controls metadata table.

This works great for me, why not trying this solution in your applications?