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.
Included in The Toolbox 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.
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
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.
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.
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
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
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 re-entering 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
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
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
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.
Sample database is suited to all versions of Access
If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.