[AccessD] Framework discussion - continuation? YES PLEASE!!!

John (and all)

A few suggestions:
Additional service classes:
- error logging/tracing to table or file, I can post a messy attempt to
achieve this with a class which pushes and pops all trace calls to a stack
and writes this to a table/file when an error occurs (together with some
other relevant data)
- behaviours of combinations of controls, e.g.
-- eample of class implementation of combination of treeview/listview with
splicer for explorer like navigation
(I can create the behaviour, but I cannot implement it in a class yet)
-- class implementation of combinations of controls, e.g.
combo/labels/button, see below for further info

My half cent:

Triggered by a recent discussion about lookup-tables, hard-coded lookup
lists, tag, etc.
Recently I had to redevelop a performance monitoring system for a public
sector body. At the start they did not know what they wanted, and at the end
they still didn't. So requirements analysis was a bit difficult to say the
least. Just everything inside and outside the scope of the project related
to just about everything else.
Usually I would have send them home to do their homework first, but since
the project had some very interesting elements I decided to give it a go.

Permananetly changing requirements make you necessarily creative in avoiding
hardcoding. 

What did I do to resolve issues
1.
All lookup fields were filled from lookup tables with the following
structure
LookupFieldID (an autonumber)
LookupFieldShortDescription (for the display on forms and reports)
LookupFieldLongDescription (mainly for value dependent tooltips)
LookupFieldIsActive (boolean set True when field should be displayed in
lookups)
LookupFieldSortOrder (a customisable sort order of the lookups)
LookupFieldMemo  (my documentation + recording of audit trail)
LookupFieldMaxLengthInCharacters (of short description)
LookupFieldMaxLengthInTwips (calculated based on the standard font and size,
and used to determine columnwidth in reports)
LookupFieldParent 
LookupFieldActiveDateFrom
LookupFieldActiveDateTo
and a few others..

Filling combos with source:
SELECT LookupFieldID, LookupFieldShortDescription 
FROM tlkLookupField
WHERE LookupFieldIsActive = True
ORDER BY LookupFieldSortOrder;
linking on 2 columns, making the witdh of the first as small as possible, in
effect only displaying the second

2.
The lookup table is linked to the table (just in case the user wants to view
the description of the meaningless code in a certain column of a record) and
a combobox on the form.
Given the number of lookups only few were actually linked in the
relationships table

3.
On the form I had to display the ShortName and the Tooltip
I achieved this by relating an (additional) label displaying the ShortName
and a commandbutton with the value dependant tooltip, the latter transparent
and overlayed on the former, to the combobox
this gave a combination of 4 controls
cbxLookupfield (the Lookupcombobox dropping down the short description, but
actually entering the ID)
lbxLookupfield (the normal label related to the combo)
lkxLookupField (with the Shortname)
cmxLookupField (with the tooltip from the LongDescription)
So the user still had the look and feel of working with meaningfull
descriptions.

4.
The main user/administrator could add (but not delete) records to Lookup
tables and change the descriptions, IsActive and SortOrder.  All changes
were logged and datestamped to the LookupMemo, thus preserving an audit
trail.
This was all managed from one form with a combobox selecting the different
lookuptables. The table feeding this combobox had again exactly the same
structure as above and could be edited in the same form.


Advantages
- Language independent (by design), both in terms of the internal
organisational language and changing terminology as well as internationally
(just add LookupFieldLanguage and you can start filling the table with
descriptions)
- Perfect Audit trail
- Smart looking forms with additional behaviours easily added (derived from
tlkLookupField) to the combo-combination
- Smart looking report snapshots (dynamically formatted using data from the
above). I only needed a few, thanks to some COOL code for dynamically
positioning controls at runtime from Shamil Salakhetdinov and Stephen
Lebans' code for calculating the width of a data element.
- Highly standardised naming conventions allowed most of the coding to be
reduced to a few loops.

Disadvantages:
-some overhead in a few events
--Form_Current event had to refresh all tooltips and lookups
--After changing the combobox selection I had to refresh the tips and lookup
for that combo
Luckily most of this could be achieved by looping through the controls, the
"x" in the third position of the names above (3) allowed for this.

Next steps?
- Researching the possibility of further streamlining the lookup tables
towards a full fledged data dictionary, e.g. by pulling them into a few
master tables.
- Creating a class for the above combo/label/button behaviour (as soon as I
understand what John, Shamil and a few others are actually doing).



Harry