In this article, Keith Bombard shows you how to implement dual control in Access to make critical operations more secure by ensuring that more than one person must approve a task.

TEN years ago, I was a vice president in a big bank and wore a nice suit every day. Those days in the suits are long gone (and so is the bank, because itfailed!), and today I wear jeans, but I took a number of good lessons away from my experience in the financial world. One was the need for dual control. Dual control means that two people are responsible. Someone else, along with yourself, has to sign the check; two bankers have to open the vault; two managers must approve any raises, and so on. It’s a common practice in the financial world. What isn’t common practice is finding an Access database system that incorporates a dual-control approval mechanism.

In this article, I’ll refer to the user (the person who wants to perform a critical function and needs the approval of the manager) and the manager (who supplies the Approval Code and will authorize the process for the user). You’ll need to establish a user table in your application (if you haven’t already) and another table to store a list of the approved requests (auditors like to track everything). These two tables should reside on the network as attached tables. In the sample application in this month’s Source Code file (available at www.smartaccessnewsletter.com), you’ll find a sample database with two tables in it that you can review.

The process

Start by determining which Access process requires a dual-control approval. You’ll wrap the code for this process in an If…Then statement that uses a VBA function called Approve to return a True or False value. The Approve function returns a True value when the user obtains an Approval Code from his or her manager, allowing the critical process to be executed. Here’s a typical example:

If Approve(Form, Description) = true then

  'Critical processing goes here.

  Else

  'Not OK to process

 End if

What kind of process might require an approval? Deleting records from critical data forms is one example. To delete records, I usually use a delete button code behind the click event to delete records. I then nest my code inside the Approve block, thereby placing the entire delete operation under dual control.

When executed, the Approve function opens the dialog form frmObtainApproval (see Figure 1), passing a generated Request Code into the form as an OpenArgs value. The Request Code is a randomly generated number between 10000 and 10000000. The user is then prompted to enter the correct Approval Code, which is obtained from his or her manager. (The Get App Code button that appears in Figure 1 is included only as a sample to illustrate the call to the frmFindApprovalCode form. You must delete this button and associated click event code from the form if you want to use the form.)

Figure 1. Requesting an Approval Code.

Figure 2. Producing an Approval Code

Figure 3. The Got Approval message box.

The user must call (usually by phone) the manager to discuss a dual control request. Once agreement has been reached, the manager loads another form, frmFindApprovalCode (see Figure 2), and looks up the user’s most recent Request Code using the Lookup Request combo box on the form. Once selected, the appropriate Approval Code is computed, and the manager gives it to the user.

The user then enters the supplied Approval Code into the Approval Code text box. Behind the scenes, a second Approval Code is computed and compared against the value supplied by the manager. If a match occurs, the Approve function returns True, and all is well (see Figure 3). This process is secure as long as the user who’s requesting the approval doesn’t have access to the frmFindApprovalCode form.

The code

It’s virtually impossible to guess an Approval Code from a Request Code. The basApprove.CodedString function computes the Approval Code as the result of a computation process that uses the Request Code and a hard-coded 36-byte CharStr string variable.

The basApprove CodedString function is outlined in the following code. The function is seeded with a random long integer ReqCode to produce the resulting value:

 Dim ReqCode as Long

  Randomize

  ReqCode = Int((10000000 - 0 + 10000) * Rnd() + 1)

  ApprovalCode = CodedString(ReqCode)

The CodedString function begins by setting the CharStr constant that’s used to make sure that the resulting values are unique (more on this later):

Function CodedString(Request As Variant) As Long 
Const CharStr As String = "1AB3CD5EFG7HIJ9KLMNOP0QRS8TU6VW4XYZ2"

 Dim i As Integer, j As Integer

  Dim C As String * 1

  Dim KeyVal As Variant

  CodedString = 0

 If IsNull(Request) Then

 GoTo CodedString_EXIT

The routine takes the Request variable that contains the random number passed to the routine and turns it into a string:

 Request = CStr(Request)  KeyVal = 0

The routine’s main loop steps through all characters in the Request variable and computes the resulting KeyVal sum. This result is returned to the calling routine:

For i = 1 To Len(Request)

   C = Mid$(Request, i, 1)

   For j = 1 To Len(CharStr)

    If C = Mid$(CharStr, j, 1) Then

     KeyVal = KeyVal + ((i * j) * CLng(C))

     Exit For

    End If

   Next j

  Next i

 CodedString = (KeyVal * Len(Request)) + 1



CodedString_EXIT:

 Exit Function

In this routine, each character in Request is matched against the characters in the string constant CharStr. When a match is found, a KeyVal value is incremented by multiplying the positions of the character in both strings times the value of the character being examined. KeyVal is increased until all of the characters from the original string are resolved, and then CodedString is finally computed as the product of KeyVal and the length of the original Request string variable, plus 1. To make your Approval Code different from any

other program’s Approval Code for a given Request Code (and hence unique), edit this function to scramble the order of the characters in the CharStr (but don’t remove any characters). This works because the algorithm uses the positioning of CharStr’s characters to compute the Approval Code value. I suggest you do this; otherwise, the auditors might get after you for using a coding routine to which lots of folks have the key.

I invite you to try out the sample database included with the article. Import the objects into your database, establishing the production tables (900 and 901 numbered tables) as network attached tables. Nest your critical functions under the Approve function and give the managers access to the frmFindApproval form. You’ll quickly establish a dual-control mechanism that your auditors will admire.