Audit Trail using Data Macros

There are different ways to do audit trails in Access. Up until Access 2010, these had to be implemented on the Form level. But Access 2010 has Data macros which operate on the Table level, so they will capture if someone bypasses your security (see my blog on Login Security using VBA) and makes changes in the table itself.
Setting up such an AuditTrail is a little more cumbersome but does provide a greater level of protection.
The first step is creating a table to hold the Audit Trail information. The following shows the design of this table:

The fields are pretty obvious. This table records the table and field that was changed, the RecordID (PK Value) of the changed record, the old and new value and who changed the record and when.
The next step is to open the table you want to audit in Design Mode. From the Table Design ribbon pull down the list of Data Macro events and select After Update event as shown below:

This will open the Macro Design environment where you can build the macro.
The screen shot below shows the macro commands to cover one field:

DataMacro

The first command is an IF command that is triggered if the field value changes. It uses the Updated function with the field name as the argument. The next step is the Create a Record command which uses the argument of tblAuditTrail. This will be the name of your Audit Trail table.

From there you add a SetField command for each field in your Audit Trail table (except for the autonumber PK, which is filled in automatically). The first field is the Tablename which is hardcoded. The next is the RecordID which is filled in by a reference to the fieldname. Then the ChangeBy field is populated with the ID of the user. In this example I am using the fOSUsername() function found at http://www.mvps.org/access. Next, I populate the FieldName, which is again hardcoded. Following are the Old and New values. I use the Old command with the fieldname as the property to capture the previous value. Then reference the tablename.fieldname. Finally, I set the ChangeDate to Now().
To place an Audit Trail on other fields, you can collapse the If and then copy and paste to add another set. Change the fieldname information as needed.
Save the macro and close the editor.
You can test by making changes to the fields you have place an audit trail on and then checking tblAuditTrail to make sure the changes were recorded.
You can also capture Deletes similarly using the After Delete event and writing all the fields to a deleted records table. Again using Create A Record and SetField for each field.
I have a sample file (see link below) that contains a table with Audit Trail and the Audit trail table as well as a form to edit the audit trail table and modules with the needed functions. Note: Both Front end and back end need to be in the same folder.

Limitation: This does not work with certain data types. An error 20342 is thrown with description The Updated function is not supported for memo, rich text, hyperlink, OLE Object, multi-value, or attachment fields. If you have one of those data types, then I would recommend using a VBA alternative (see Comments). In the case of multi-value or attachment data types, you can replace then with a child table.

Audit Trail Sample

32 Comments

Filed under Computers and Internet

Access 2010 – Calculated Data Type

One of the long held principles of data base design has been to not store calculated values. In the past it has always made sense not to do this for a couple of reasons. Foremost is that, you have to ensure that you recalculate the value when the components of the calculation change. Second, when storage space was at a premium, it didn’t make sense to store a value that could be displayed with an expression in a control or a column in a query.

In today’s technology, storage space is not much of an issue. With storage space costing around $100 per Terabyte, developers do not have to scrimp to save space. So Microsoft provides for a new data type in Access 2010 that allows the user to store an expression that displays a value. This satisfies the primary reason against storing calculations.

With the Calculated data type, you store a formula or expression that is updated automatically when the components of the expression change. So there is no need for the developer to ensure that the value is updated. It is handled automatically by Access. Let us look at how the Calculated field data type works.

To illustrate Calculated fields I’m using the Order Details table in the Northwind Traders sample data base. I’m going to add a field to calculate the extended price and another to display a discounted price.  First open the table in Table Design mode. At the first blank line enter ExtendedPrice as the fieldname. And select Calculated as the data type (1).

Once you select Calculated as the data type, the expression builder opens  for you to build the expression to store in the field.

The expression builder opens with the table you are working in highlighted (1).  In the middle pane is a list of fields in that table (note: calculated fields can only use fields in the same table within its expression). So the first step is to select the first field in your expression. In this instance we will select the Quantity field.

The next step is to enter an operator to perform the calculation. First select Operators, from the Expression Elements (1). Then select Arithmetic from the Expression Categories (2). Finally, double click the asterisk (multiplication operator) from the Expression Values (3). The result is to add an asterisk to the expression (4).

You then go back to the Expression Elements and select the table again. Then double click the Unit Price field to add it to your expression.

Finally press OK and the expression will be added to the properties of the field (1).

Switch to Datasheet view of the table, saving the table if prompted. You will now see the Extended Price field added to the end of the table (1).

Notice that it is correctly calculating the extended price at 1400 (100*14). To test the field change the Quantity to 10 and you will see the field recalculated to 140 (1).

Similarly, you can add a discounted price by using the expression: [ExtendedPrice]*(1-[Discount]).

I must admit I am a bit ambivalent about this new Calculated data type. On the one hand, it certainly short cuts doing calculations. By putting the calculation in the table, you do not have to repeat the calculation wherever it is needed. Just add the field to your form, report or query and it is done for you.  Another advantage is compatibility with Sharepoint lists. But it will present problems, like some of the new data types introduced in Access 2007 (multi-value fields and Attachment fields) if you need to migrate your database to SQL Server, Oracle or the like. And it is an unnecessary shortcut. But, for the non developer, for the user making a database on their own for their own use, it does provide an advantage.

1 Comment

Filed under Computers and Internet

Creating Login Security using Access VBA

First, let me state that Access is not the most secure platform. If you really need strong security on a database, you are better off using a more secure back end like SQL Server. But if you don’t need high level security, you can secure your database using VBA. I’ll be using a fairly simple example for this article, but it should give you enough to expand on for more complex needs.

The first step is to setup the tables needed to drive the security. Two tables are needed:

tblUser

UserID (PK) Autonumber
FName Text
Lname Text
Password Text
PWReset Yes/No
AccessLevelID Long Integer

tluAccessLevel

AccessLevelID (PK Autonumber
AccessLevel Text

The second table is a lookup for the AccessLevelID in tblUser. For this example we will have four Access levels:

AccessLevelID AccessLevel
1 Developer
2 Administrator
3 Editor
4 Reader

You can use whatever and how many levels you want or need depending on your application. The above is provided as a basic example. You can also add more fields to tblUser if needed.

Once you have the tables setup, you can create the Login form. This will be an unbound form with two controls; a combobox to select the user and a textbox to enter the password. You will want to restrict this form by turning off the Min, Max and Close buttons. Also set the Navigation Bar and Record Selector properties to No. I suggest the name frmLogin for the form, cboUser for the combobox and txtPassword for the textbox.

For the combobox, you will set the ColumnCount to 4, the ColumnWidths to 0;1;0;0 and the RowSource property to the following SQL statement or create a named query:

SELECT tblUser.UserID, [Lname] & “, ” & [FName] AS Fullname, tblUser.Password, tblUser.PWReset, tblUser.AccessLevelID
FROM tblUser
ORDER BY tblUser.LName, tblUser.FName;

The user will select their name from the combobox and then the password in the textbox. Now we get into the VBA that drives the security. This code will be entered in the AfterUpdate event of the password textbox.

The first task the code performs is to make sure a user has been selected. If, not focus is returned to the cboUser combo. The next step is to check if the password entered matches the stored password. If it doesn’t, focus is returned to the txtPassword control. If there is a match, then the Reset column in the RowSource is checked to see if the user is required to set a new password If it is, a form is opened to enter a new password. If Reset is false, then the main menu is opened. The login form is then hidden. This is so the cboUser control can be referenced in other parts of the application. Alternatively you can save the value in a global variable and close the form.

Private Sub txtPassword_AfterUpdate()
‘Check that User is selected
If IsNull(Me.cboUser) Then
    MsgBox “You need to select a user!”, vbCritical
    Me.cboUser.SetFocus
Else
‘Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then
‘Check if password needs to be reset
        If Me.cboUser.Column(4) Then
            DoCmd.OpenForm “frmPasswordChange”, , , “[UserID] = ” & Me.cboUser
        End If
        DoCmd.OpenForm “frmMainMenu”
        Me.Visible = False
    Else
        MsgBox “Password does not match, please re-enter!”, vboOkOnly + vbExclamation
        Me.txtPassword = Null
        Me.txtPassword.SetFocus
    End If
End If
End Sub
The Password Reset form (frmPasswordChange) is bound to tblUser. So when it’s opened, it’s filtered for the selected user.  The user then enters the new password twice to confirm it. Then open the main menu.

The final piece to this is using the AccessLevel to restrict access to various parts of the application. This is done by checking the Access level in one of two ways. You can reference the column in the combobox by using the expression:
Forms!frmLogin!cboUser.Column(4)
or using the expression:
Dlookup(“[AccessLevel]”,”tblUser:,”[UserID] = “ & Forms!frmLogin!cboUser)

Once you have retrieved the Access level, you can use it to restrict access to forms and controls based on the access level assigned to the user. Following are some examples of this:

Case 1: Restricting access to a form only to administrators
In the On Open event of the form, you place code like:
If Forms!frmLogin!cboUser.Column(4) <> 2 Then
    MsgBox “You are not authorized to open this form!”, vbOkOnly + vbExclamation
    Cancel = True
End If

Case 2: Making a form read-only for Readers
In the On Open event of the form you place code like:
If Forms!frmLogin!cboUser.Column(4) = 4 Then
    Me.AllowEdits = False
    Me.AllowAdditions = False
    Me.AllowDeletes = False
Else
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletes = True
End If

Case 3: Restricting records on a form to ones assigned to the current user
In the On Open event of the form you place code like:
Me.Filter = “[UserID] = “ & Forms!frmLogin!cboUser
RunCommand acCmdApplyFilterSort

These are just three possibilities. There are many other ways to use these techniques to restrict what a user can access in your application.

I hope you find these tips useful.

You can download a sample of this at:

http://www.diamondassoc.com/sampleapps/loginexample.zip

The zip file contains versions for both Access 2007 and 2002-2003. The password for each user is welcome.

© 2010 Scott B. Diamond — Diamond Computing Associates

111 Comments

Filed under Computers and Internet

Sequential Numbering

A common question I’ve received has to do with sequential
numbering. People often want to use some identifier for their records that
involves information about the record and a sequential number. Sometimes these
numbers are sequential throughout the table, other times the sequence is
specific to other criteria.

To illustrate how to do this, I’m going to use the following
scenarios;

1)      Acme
Co. needs to sequentially number their Purchase Orders to conform to standard
accounting rules.

2)      Apex
Inc. wants to sequentially number inquiries they receive but within each year.

3)      Hollywood
& Vine, a law firm, wants to tag all documents they receive with a client
ID, case number and then number them sequentially by case.

There are three issues common to all three scenarios. First,
the sequential number is stored separately in the table as an Integer datatype
(might need Long Integer). Second, the DMax function is used to determine the
last number used. Third, the number needs to be generated immediately prior to
saving the record.

One other caveat here; the number generated in these
scenarios is NOT a primary key. These tables should use an Autonumber as the
PK. The possible exception would be scenario 1, but I would still tend to use
an Autonumber as the PK.

We’ll start with the simplest, scenario 1. Acme will use a
straight sequential number for its Purchase Order numbers. Add a (Long?)
Integer field to the table named PONum. 
Add a control to your Purchase Order form bound to that field. Then use
the following expression to populate that control:

Me.txtPONum
= Nz(DMax(“[PONum]”,”tblPO”),0)+1

The Nz function will return 0 if there are no records so
this expression will return a 1 the first time and then increment the highest
number each additional time. The key is where to put this line of code. As I
said it needs to be generated immediately prior to saving the record so that
another user does not get the same number. So generally I would put this behind
a Save button or a button to specifically generate the number. I would then add
a second line to commit the records. Either:

Me.Dirty
= False

or

DoCmd.RunCommand
acCmdSaveRecord

Next we will look at scenario 2. The variation here is that
Apex wants to start each year with anew sequence of numbers. Also, they want to
include the year when displaying this number. We still use a DMax, but we need
to add criteria to determine the year. So Apex needs to have a field in their
table that indicates the date. Generally, such inquiries will have a date/time
stamp to indicate when received or the user will input the date and time. So we
will assume there is a field in record called InquiryDate. We will also add an
Integer field named Sequence. On the form we will add a control bound to
Sequence, but set its Visible property to No. The line of code will look like
this:

Me.txtSequence
= Nz(DMax(“[Sequence]”,”tblInquiry”,”Year([InquiryDate]) = “ &
Year(Me.[txtInquiryDate])),0)+1

With this expression we check only for records that are in
the same year as the Inquiry date to increment the sequence. Again, the NZ
function will automatically restart the sequence with each year. Cleary,
though, we cannot generate this number until the InquiryDate has been entered.
Is this value is a user entry, then you may want to check that it’s filled in
before generating the number. Something like:

If
IsNull(Me.txtInquiryDate) Then

            MsgBox “Inquiry Date must be entered
first!”, vbOKOnly

            Me.txtInquiryDate.SetFocus

Else

Me.txtSequence
= Nz(DMax(“[Sequence]”,”tblInquiry”,”Year([InquiryDate]) = “ &
Year(Me.[txtInquiryDate])),0)+1

End
If

Also again, you want to generate this number immediately
before saving the record.

The final piece is to display the identifier. For that you
use the following expression:

=Format([InquiryDate],”yyyy”)
& “-“ &Format([Sequence],”0000”)

This expression can be used anywhere you want to display
this identifier. It will appear like this: 2009-0010,
indicating the tenth inquiry of 2009. You can use that as the Controlsource of
a control on a form or report. In a query you would preface with an Alias
rather than the equals sign (i.e.; Inquiry Code:).

The final scenario is very similar to Scenario 2. The two
main differences are in the criteria of the DMax and the expression to display
the identifier. The document table already has two fields to identify a
document; ClientCode, which is a 5 character code that identifies the client
and CaseNumber, which is an alpha-numeric code assigned to the case. Since the
Case Number is unique to the Client, we only need to set the criteria in the
DMax for the case number. We will also need to add an Integer field named
Sequence to the table. So the Dmax expression will look like this:

Me.txtSequence
= Nz(DMax(“[Sequence]”,”tblDocument”,” [CaseNumber] = ‘“ & Me.[txtCaseNumber]
& “’”),0)+1

Notice here that we need to surround CaseNumber with Single
quotes since it’s a Text datatype. As in the other scenarios, The NZ function
automatically restarts the sequence when a new CaseNumber is used. In this
scenario we would want to check whether the CaseNumber has been entered before
generating the sequence.

For displaying this identifier, we would use an expression
like:

=[ClientCode]
 & “/“ & [CaseNumber] & “-“
& Format([Sequence],”000”)

In this scenario we are assuming that there will be no more
than 999 documents attached to a case. In Scenario 2 we assumed no more than
9999 inquires during a year. So you need to adjust the number of zeros when
formatting Sequence for the anticipated number of records. Of course this can
always be changed later. You also don’t need to format the sequence with
leading zeros as the Format function does. As shown the expression returns
something like: DCASD/CI123-025 for
the 25th document in case CI123 for client DCASD. Without leading
zeros it would be: DCASD/CI123-25. The
advantage to the latter is that you don’t have to anticipate the number of
records you might have in the sequence, but I’ve found many users prefer a more
uniform number with the leading zeros.

As you’ve seen, the identifier is built using other data
besides the sequence number. Since this other data is a part of the record, you
don’t want to store this identifier since that means storing the same data
twice. That’s why we use an expression to display the identifier rather than
store it.

To recap, you use a DMax function to return the highest
number in the Sequence and increment it by 1. You assign the incremented number
to a control on your form and then immediately save the record. If your
identifier includes additional information from the record, you use an expression
that concatenates that information with the sequence number to display the full
identifier.

With these techniques, you can generate a sequential number
for just about any situation. I hope you find this information helpful.

© 2009 Scott B. Diamond — Diamond Computing Associates

68 Comments

Filed under Computers and Internet

Displaying data from related tables on one form

This is a question I often get asked on Q&A sites. But before I get into the methods, you need to understand one of the principles of relational databases. That principle is that data should exist in one place only. Having the same data in multiple tables is a violation of normalization. Related records are indicated by a Foreign Key within the record that holds the Primary Key value of the parent record. So when you want to have data from multiple tables on a data entry form, you set it up to display the data not store it in the form’s Recordsource
 
There are basically four ways to display related data on a form; Subforms, The Column property, DLookups and Listboxes. Ill discuss each in turn and suggest where to use each.
 
1) Subforms. You can use a subform to display several fields from the related table. Create the form using the Subform wizard or create a separate form and place it on the main form as a subform (I generally create a separate form). Using the wizard, you go through the following steps.
• Select whether to use an existing form or create a new one
• If you are creating select the table and fields to use
• Select the linking fields, usually accept the defaults Access proposes
You can customize the subform, so it looks like part of the main form, by removing record selectors, navigation buttons, borders etc. I use subforms when I want to display 4 or more fields from the related record. Another advantage of using subforms is where you have a One to Many relation. Using a Continuous Form or Datasheet view, you can display multiple related records at once.
 
2) Column Property. Generally Foreign Keys are entered by selecting the related value from a combobox. The combobox uses a query as it Rowsource. This query displays the records from that parent table. At the least, the query includes the primary key field as its bound column and a description field. However, you can add as many other fields from the table as you want. These fields can then be reference using the Column property. Click the Build button […] next to the Rowsource property to enter Query Design Mode. In Query Design Mode you can add tables and fields to the query. You can control what fields actually display in the pull down list by setting their Column Width. Setting the width of an individual column to 0" will hide that column (Note: Column widths are entered separated by a ; for each column listed in the column Count). The combobox will only display the first non zero length column after selection. The following properties of a combo are key to using combos in this way: RowSource (the list), Bound column (what’s actual stored), Column Count (how many columns in the list, Column Widths (the size of each column in the list).
 
You can then set the ControlSource for an unbound control to:
[comboxname].Column(x)
Where comboxname is the name of the control and x is the number of the column in the query for that field. Note: the column count starts with 0 so the 3rd column is 2.
Since the combobox selects a single record, the Column property will also reflect a single record. I use this method if I need to display 3 or less values from the related record.
 
3) DLookups. DLookups allow you pull a value from a field in a specific record. It uses the syntax: DLookup([fieldname],table/queryname,Criteria). The Criteria is used to specify the record you want to return. Since the Comboxname will store the FK value you would use a criteria like: 
“[keyfield] = “ & [Comboboxname].
This would also be used as the controlsource of an unbound control. Each DLookup should only be returning data from a single record. If its possible that the DLookup might not find a matching record you should use it within a NZ (NullZero) function to prevent errors. I use DLookups when I need to pull data from different tables based on a key value.
 
4) Listboxes. A Listbox can have multiple columns with column headers. It also can be set to display multiple matching records. I will, sometimes, use a Listbox in place of a continuous form or datasheet subform. Listboxes will also display multiple matching records.
There are two exceptions to the rule of not repeating data in multiple tables. The first is the PK value. Obviously, that value has to be repeated as the FK to relate the records to each other. The other exception is time sensitive data. Sometimes you need to freeze data that will change over time. The best example of this is price data. For example: In an order entry application, you want to freeze the price at the time of the order. In such a case, you would have the Price field repeated in the OrderDetails table. Generally you would use the Column property for this and populate the control in the After Update event of the Products combo use code like:
Me.txtPrice = Me.cboProduct.Column(2)
These guidelines should help you build forms that preserve normalization and are well organized and easy for the user to use.
Scott<>

© 2006 Scott B. Diamond — Diamond Computing Associates

9 Comments

Filed under Computers and Internet

Win 7 impressions

I have an old IBM Thinkpad T42. It has a Pentium M (single core) processor runing at 1.7 Ghz with 1 Gigabyte of RAM and a 32 Megabyte ATI Radeon 7500 graphics adapter. Its hard drive was frequently blue screening on me so I decided to replace the frive and load Windows 7 on it.
 
I replaced the drive and popped in one of the Windows 7 Ultimate RTM copies I have. The nstallation went very smoothly. Windows 7 picked up all the hardware. It connected to my nework (wirelessly) with no problem.
 
I am very impressed at how smoothly and sweetly the machine is running. I am able to multitask without any major lag. I really don’t see any performance difference with the original (Windows XP) operating system. In my opinion it speaks volumes about Windows 7 that it can run so nicely on such old hardware.
 
I had previously installed the RC version on my test machine, a desktop with an AMD Athlon X2 5000, with 4 Gig of RAM. Agan the install went smoothly. It also picked up my 10+ year old Jet Direct 3 port (Parallel) Print Server, right down to the model number. I’ve had no problems with any hardware or software running on it.
 
I think Microsoft has a real winner here!

4 Comments

Filed under Computers and Internet