Duplicate Data Entry For Access

Introduction - I Love A Good Type !!

About 12 years ago when the mining company that Garry worked for ran all of its word processing on a Digital Vax mini computer, there was legal secretary who lost a 50 page legal document (somehow). After 15 minutes of looking, the system administrator had to explain to her that retrieval of the document was going to be difficult and would not retrieve all the work that she had done that day. She funnily enough said this was OK and said that she loved "a good type". She went back down the corridor and hammered all 50 pages in again in a few hours. But what does this have to do with Access programming. Well the lesson here is that unlike programmers, there are a lot of data entry and computer trained persons who quite enjoy the keyboard interface. Likewise you would find that a large number of these people also would not do a really good job of checking data entry against written reports.

This brings me to a challenge that my co-author Taha Kass-Hout handed to me one day. He asked for a form that would easily allow checking of data that was already entered into an Access table. This article demonstrates a user interface suited to data entry and verification and provides you with a couple of alternative approaches to managing the verification process using visual basic.

The Download Database

Included in The Toolshed downloads are examples of the software in Access 97, 2000 and XP. When you open the software database, there are 3 forms. FirstEntry is a simple form for data entry and has very little in noteworthy coding. DoubleEntryBasic is the simpler of the 2 samples of the double entry code. This is specific to the current form and is probably the template that you are most likely to use. The final form is called DoubleEntryAdvanced. This form has code that has been generalised to try and minimize the additional code behind the form. If you have a lot of fields in the table or think you might be adding new fields at a later stage, this format may be for you. There is also a basic report to show what has been entered and checked.

The Data Entry User Interface

When writing a user interface for a data entry specialist, there are some design criteria that you should factor into your interface. First you can safely assume that the person doing the data entry will not use a mouse (so pop it in the drawer during the testing phase). Your interface will consist of plain old text boxes, Alt keys and probably a maximum of 5 buttons. An example of this (really dull) form can be seen in figure one. This form has a number of features that make it simple for the data entry person. The top menu offers no unnecessary choices that might excite a computer programmer (such as database compression, exporting) but would confuse a user.

Figure 1 The very simple data entry interface used to input the initial data.

The bottom menu consists four buttons to enter, save, navigate and close the form. In this form, the bottom menu has been stored in the Form footer. This actually makes keyboard access to the buttons a problem as you cannot readily Tab from the last field to the bottom menus. To get around this we have assigned an Access key to each of those buttons. What does this mean ?? It is the activation of a button using the Alt Key (like Alt P for printing). It is signified to the end user by an underline under the character that activates the button. To program this, open the form in design view and show the caption property for your button as in Figure 2. Now add an Apesand "&" before the letter that you want to assign to your button. Now your button can be activated using the Alt Keys and your form has become keyboard friendly.

Figure 2 Making your command buttons accessible by the keyboard requires an & in Caption

Data Verification

When Taha and I were developing the software for testing the duplication, we came up with a solution that utilized the specific names of fields in the testing and also the use of control collections to process all of the controls using the one piece of generalised code. For this article, we have split the solution into both a basic form that most people should start with and an advanced form that may suit programmers with forms with many fields or systems where duplication is to be added to many forms. So to the actual process that we came up with.

When the form is opened, we open a recordset for the table that we are interested in as follows

Set rst = CurrentDb.OpenRecordset("tblData", _ dbOpenDynaset)

Now the form can be seen in design view in figure 3. Importantly the design of this form consists of all the fields in the table with the same names as the fields in the table. Also there are a second set of the fields that have the same names with a suffix of 2 e.g. Height and Height2. The "2" fields are differences and will only be used if a difference is found between the first entry and the second entry.

Figure 3 - The Data Verification Form Is Unbound and has fields for data an errors.

The verification works by comparing the data that you have just entered with the data that is already in the recordset (and the table). If there is no difference, then an extra field in the table called "checked" is set to True from False. If there is a difference then all the fields where there are differences are displayed on the screen. The user will then make the changes and save the record which is now assumed to be correct. This part of the process may not pass muster in your company and you may want the user to re-enter the data again or modify the existing data before saving the record.

The Visual Basic Used For Verification

After entering the record and firing the verification button (with Alt V), the software will check to see if the unique ID field in the table exists in the recordset as follows. Note the use of the Chr function to insert the double quotes. If there is no entry that matches, the data entry person may save the current record.

rst.FindFirst "ID = " & Chr(34) & Me!ID & Chr(34) If rst.NoMatch Then

The most interesting part of the code comes when we actually have a match and wish to verify that the data is correct. Listing 4 shows the code that is used for verification of a text field, a date field and a numeric field. As you can see, the wonderful world of null values turns what should have been 3 simple tests into a complicated few lines of code. The verification is between the original data which is now the current record in the recordset and the unbound field on the form. Differences are displayed to the second field.

flgDiff = False

If Nz(UCase(Me!Sex), "") <> Nz(UCase(rst!Sex), "") Then
  If IsNull(rst!Sex) Then
    Me!Sex2 = "Null"
  Else
    Me!Sex2 = UCase(rst!Sex)
  End If
  flgDiff = True
End If

If CDate(Nz(Me!DOB, dateOne)) <>  CDate(Nz(rst!DOB, dateOne)) Then
  If IsNull(rst!DOB) Then
    Me!DOB2 = "Null"
  Else
    Me!DOB2 = rst!DOB
  End If
  flgDiff = True
End If

If Val(Nz(Me!Height)) <> Nz(rst!Height) Then
  If IsNull(rst!Height) Then
    Me!Height2 = "Null" |
  Else
    Me!Height2 = rst!Height
  End If
  flgDiff = True
End If

Listing 4 - Testing of text, date and numeric data with the original data in the recordset.

The Advanced Verification Form

In the advanced form, listing 4 (shown above) is handled through both the control collection of the form and the recordset field collection as shown in listing 5. Here the naming convention requires that the text box names are the same as the recordset field names. Because the table will have fields that we do not want to verify, these are ignored in the recordset using some function wide constants such as AutoKeyField, IdField and CheckedField. Of particular interest in this code is the use of the recordset field type property to work out the data type of the field.

flgDiff = False
For Each fld In rst.Fields
  Select Case fld.Name
     Case AutoKeyField, IdField, CheckedField
      'Don't check these fields

   Case Else
      Select Case rst.Fields(fld.Name).Type
         Case dbText, dbMemo
           If Nz(UCase(Me.Controls(fld.Name)), "") <> Nz(UCase(rst.Fields(fld.Name)), "") Then
             If IsNull(rst.Fields(fld.Name)) Then
               Me.Controls(fld.Name & "2") = "Null"
            Else
               Me.Controls(fld.Name & "2") = UCase(rst.Fields(fld.Name))
           End If
           flgDiff = True
        End If

Etc etc…

      End Select
   End Select
Next fld

Listing 6 - The Advanced Form Verifies The Data Using Collections

After the fields are all verified, the advanced form will open a function that will update the data from the unbound form fields back to the recordset. The code for this function is quite concise as follows

rst.Edit For Each fld In rst.Fields

   Select Case fld.Name
      Case AutoKeyField, CheckedField, IdField

      Case Else rst(fld.Name) = Me.Controls(fld.Name)
   End Select
Next fld

rst(CheckedField) = True rst.Update

Summing Up

Double data entry is a method to minimize data entry errors. Data is entered twice; the database program then compares the two values for each field in real-time and then identifies values that do not match. Discrepant entries are then checked on the original data forms and corrected. Double data entry identifies data entry errors at the cost of doubling the time or the personnel required for data entry. Future program development includes 1) rechecking or reentering a random portion of the data. If the error rate is acceptably low, additional data editing is unlikely to be worth the effort and cost, 2) logging the discrepant entries, and 3) providing a one-to-many double data entry advanced form.

Garry Robinson and Taha Kass-Hout

Author Bio.
Taha A. Kass-Hout M.D., M.S established capastatistic.com, a company based in Houston, Texas U.S.A. The company is specialized in eSurveys and statistical data analysis.

Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies web site at http://www.gr-fx.com/ or sign up for his Access email newsletter here. If you like the approach Garry took on this article, why not try out the Automation Class libraries that Garry offers at his website. When Garry is not sitting at a keyboard, he can be found playing golf or flying hot air balloons in the deserts of Australia.

Other Pages On This Site You Might Like To Read

  Click here for the download file if you own "The Toolshed"  Else Click To Purchase