The class and demonstration are available in Media:clsAutoNum.zip (Access 2000 and 2007/2010 formats.) The class is intended as an alternative approach for the creation and managment of continuous number series. There are several examples for creating custom number series using VBA including this Code Archive example.
R E V I S I O N H I S T O R Y
|| 20 April 2011
|| original version
|| 22 April 2011
|| add committed property and same audit report
|| 24 April 2011
|| modified demo form ...Lev3 to commit only new records
|| added eof handling to commit method
|| added non-null handling to commit method
|| 24 April 2011 09:53AM (EST)
|| modified commit method and form handling of previous commitment
The code in the sample files includes comments about the class and its properties. Some points to keep in mind when implementing the class are:
- referential integrity between the primary table and the control value table. When establishing the relationship between the control number table and the primary table, the control number table should be on the left of the relationship. This will prevent the addition of new records to the primary table until there is a related record in the control number table. Similarly, control number records cannot be deleted if a corresponding record exists in the primary table.
- the generated number is used as the primary key of both tables
- the control number table has a creation time stamp. This value is copied to the corresponding record in the primary table when it is created for audit purposes. If the two timestamps are different that is an indication that the primary record has been tampered with.
- the demonstration form prevents the change of customer information except when creating a new record.
- in production use, the user should only be able to access the control number table through a form.
This class can provide a measure of security and audit control when external documents or internal data uses continuity of a number series as a test for improper deletion of critical data. Attempts to manually modify the data may also be detected if the time stamp in the primary table does not exactly equal the time stamp in the number control table.
If you have suggestions for improvements/modifications to this class, please use the talk page.
Updated: 2010 April 22nd
The class now includes methods for managing distinct creation and commitment dates. The sample file includes a simple audit report. The demonstration form illustrates creating the sequence number when a new record is first created and recording the number as committed in the form's after updat event.
AutoNum by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.