AEU26: Working with CommandBars, the Access Shortcut Tool and a simple Audit Log (Dale Fye)
This month's session was led by Dale Fye, MVP 2013-2016. Dale gave a detailed explaination of how to work with built-in and custom CommandBars which are used to create right click context menus. He then demonstrated his free Access Shortcut Tool add-in which was designed to simplify working with CommandBars. Finally Dale demonstrated a Simple Audit Log he created for a client.
Related links: https://www.isladogs.co.uk/aeu-26 and https://accessusergroups.org/europe/e...
Each of these pages has links to 4 articles on CommandBars that Dale wrote for Experts Exchange together with a PDF of the presentation used for this session
You can also download Dale's Access Shortcut Tool from his website: http://dev-soln.com/access-shortcut-r...
There is also a link to the Simple Audit tool utility on Experts Exchange
Times:
0:00 Welcome by Colin
0:48 Intro by Dale Fye
1:26 What is a shortcut menu?
1:40 CommandBar history 2003 to 2007 to 2010
3:55 Rutime Access cannot use default shortcut menus but can use your own custom menus
4:04 CommandBars - there are 200+. What are they / what do they contain?
4:47 Displaying elements of a command bar
5:27 Well over 200 built-in menus in the CommandBar collection depending on Access version
Determiniing properties of a CommandBar can be difficult due to a lack of consistency.
7:51 Explanation by Ade as to why command bar controls have caption property instead of name.
This allows the same caprtion e.g. Copy to be used in mutliple command bars
8:38 Using built-in CommandBars - be careful about manipulating these - recommened you make any changes temporary
11:34 Create your own shortcut menu - early binding ; declare variables ; define CommandBar name / position / menu bar = false / temporary property
13:37 Comments / questions about how permanent changes to built-in / custom command bars are
16:21 Ade B. - questioned whether added command bar items are part of Access itself (in the commandbar collection) rather the the specific application. Long discussion
19:31 Chris A - believes that command bars are stored as a property
20:08 Control Properties - Caption / OnAction / FaceID / Visible / Enabled / BeginGroup / ToolTipText / State
21:29 Runtime Shortcut Menus- built-in menus are disabled. Can copy & use these as custom menus in runtime environment
22:37 Access Shortcut Tool - What's This Menu option / add new CBar / copy CBar / Import toolbar & code
23:48 Links to 4 articles on Experts Echange & the Access Shortcut Tool
24:01 Access Shortcut Tool Add-In - DEMO of its features
27:41 List of all command bars and their controls - can filter by name or control caption
29:29 Example code for working with CommandBars
36:37 Simple Audit Log - used to track changes to data - commonly used in financial systems & public records
37:27 Standard Audit Log technique is to have a separate audit table for each tracked table. Issues - database bloat / lack of clarity
39:14 Dale's approach - single audit table with fields for table name / action (who/what/when) ; record ID /field changed & new value
39:41 Processes involved - module AuditLog which uses form BeforeUpdate/AfterUpdate/Delete/AfterDelConfirm events with one line of code in each
Code writes data first to a temp table then moves to final Audit_Log table. Both needed as BeforeUpdate/Delete events can be cancelled.
40:43 DEMO - Audit Log
42:13 Q1:: Ben S - is that one audit table for each tracked table? No - one audit log table for all tables tracked. Use separate crosstab queries to track change in each table. Further discussion / clarification of approach
45:33 Q2: - Tim J - asked for clarification re temp table. A: this is cleared after each transfer to final audit log table
46:04 Q3 - Ben S - do you have provision to handle combos which might have ID value rather than text. A: Audit done on bound field whether text or ID
Ben suggests also extracting the related data and will share his code
46:59 Thanks to Dale for an excellent presentation. Links to Dale's articles on Experts-Exchange and downloads of shortcut tool / audit log & a PDF of this session available from https://isladogs.co.uk/aeu-26
48:42 Adoph Dupre - intro to next month's presentation on Using VBA to create a class based on a table.
50:40 Final discussion - Olaf offered code to simplify aspects of Dale's audit tool
51:24 End discussiion about commandbars without names - Colin / Dale / Chris A. Also that commandbar menu captions are language specific
53:44 End of session