Barcode Check Digit Validation

Boxes we send out to the Annex need to be barcoded before we can send them. I like to have that barcode linked to the box data in our Collections Management Database, aka KIDB. However, we have only one barcode scanner and sometimes must key in barcodes manually. I needed to validate the barcodes.

On our Box Inventory form there is an Add Barcode button which brings up an input box for the barcode. Here is the code behind that button:

Private Sub cmdBarcode_Click()

     Dim strBar As String           'Variable for the barcode

     Dim strCheck As String       'Variable to hold the output of CheckDigit()

     'Pick up barcode from input box.

     strBar = InputBox("Enter Barcode", "Barcode")

     If strBar = "" Then

          Exit Sub

     Else:

          strCheck = CheckDigit(strBar)

          'Debug.Print dblBar

          'Test the calculated check digit against the check digit enter with the barcode.

          If strCheck = "Barcode Valid" Then

               MsgBox (strCheck)

               'Code here to do whatever it is you want with the valid barcode.

           Else:

               MsgBox (strCheck)

               'Reopen barcode input box.

               Call cmdBarcode_Click

          End If

     End If

End Sub

You will note that this doesn’t appear to do much. The real action is in the function CheckDigit(). You could put the code from CheckDigit() into the button’s OnClick event, but I prefer to put it in a module so that I can easily import it into other databases. In fact I tend to park any functions I might use elsewhere in a module I call BasicFunctionsRDM. You can place this code in any module you like.

Public Function CheckDigit(strBar As String)

'Copyright 2009 Cornell University Library.

'This code was originally written by Randall Miles, Technical Services Archivist.

'Kheel Center for Labor-Management Documentation and Archives.

'227 Ives Hall

'Cornell University

'Ithaca, NY 14853-3901

'Phone: 607-255-3183

'Fax: 607-255-9641

'E-mail: kheel_center@cornell.edu

'http://www.ilr.cornell.edu/library/kheel/

'You are free to use this code for any non-commercial purpose 

'provided this copyright notice is left unchanged.

'To use this code for a commercial purposes contact 

'Cornell University Library to obtain permission.

'This function verifies that the Codabar type barcode, 

'the type commonly used in libraries, is correct.

     Dim int01 As Integer       'Variable for first digit in barcode.

     Dim int02 As Integer       'Second digit.

     Dim int03 As Integer       'And so on.

     Dim int04 As Integer

     Dim int05 As Integer

     Dim int06 As Integer

     Dim int07 As Integer

     Dim int08 As Integer

     Dim int09 As Integer

     Dim int10 As Integer

     Dim int11 As Integer

     Dim int12 As Integer

     Dim int13 As Integer

     Dim int14 As Integer        'Variable for the check digit in the barcode.

     Dim dblSum As Double    'Variable used to calculate the check digit.

     'Parse out the individual digits of the barcode.

     'Digits in the odd places (1, 2, 5, 7, 11, 13) are multiplied by 2.

     'Digits in the even places are not multiplied.

     'If any multiplied digit is greater than 9 subtract 9 from it.

     int01 = 2 * Mid(strBar, 1, 1)

     If int01 > 9 Then

          int01 = int01 - 9

     Else:

          int01 = int01

     End If

     int02 = Mid(strBar, 2, 1)

     int03 = 2 * Mid(strBar, 3, 1)

     If int03 > 9 Then

          int03 = int03 - 9

     Else:

          int03 = int03

     End If

     int04 = Mid(strBar, 4, 1)

     int05 = 2 * Mid(strBar, 5, 1)

     If int05 > 9 Then

          int05 = int05 - 9

     Else:

          int05 = int05

     End If

     int06 = Mid(strBar, 6, 1)

     int07 = 2 * Mid(strBar, 7, 1)

     If int07 > 9 Then

          int07 = int07 - 9

     Else:

          int07 = int07

     End If

     int08 = Mid(strBar, 8, 1)

     int09 = 2 * Mid(strBar, 9, 1)

     If int09 > 9 Then

          int09 = int09 - 9

     Else:

         int09 = int09

     End If

     int10 = Mid(strBar, 10, 1)

     int11 = 2 * Mid(strBar, 11, 1)

     If int11 > 9 Then

          int11 = int11 - 9

     Else:

          int11 = int11

     End If

     int12 = Mid(strBar, 12, 1)

     int13 = 2 * Mid(strBar, 13, 1)

     If int13 > 9 Then

         int13 = int13 - 9

    Else:

         int13 = int13

    End If

    int14 = Mid(strBar, 14, 1)

     'Add digits 1 through 13 together.

     dblSum = int01 + int02 + int03 + int04 + int05 + int06 + int07 + int08 

     dblSum = dblSum + int09 + int10 + int11 + int12 + int13

     'Debug.Print dblSum

     'If the sum is evenly divisible by 10 then the check digit equals 0.

     'Otherwise take the remainder from the division add 

     'subtract it from 10 to get the check digit.

     If Right(dblSum, 1) = 0 Then

         dblSum = 0

     Else:

          dblSum = Right(dblSum / 10, 1)

          dblSum = 10 - dblSum

     End If

     'Debug.Print dblSum

     If dblSum = int14 Then

          CheckDigit = "Barcode Valid"

     Else:

          CheckDigit = "Invalid Barcode"

     End If

End Function

Microsoft Access Back-End Security.

Now that your front-end is secure you’ll want to make sure that users can’t just go to the directory and open the back-end and alter data. Here I learned something new: I had thought it was impossible to mask an input box, but some code written by Daniel Klann allows you to mask the password in an input box. I found the code for this at: http://www.tek-tips.com/faqs.cfm?fid=4617

The beauty of using an input box is that the user can interact only with the input box until log in is successful. Nothing else in the application will work while the input box is open and the database will close if the input box is closed or canceled. Using the input box also makes the password case sensitive.

To secure your back-end you can either put Mr. Klann’s code in an existing module, or in a new module. I made a new module and named it basLogIn so that I can just import it into other databases as needed. Then add the following function:

Public Function LogIn()

Dim strPassword As String

'Pick up the password from the first line in tblUsers.

strPassword = DLookup("[Password]", "tblUsers", "[ID] = 1")

'You can just replace the DLookUp() function with the password like this:

'strPassword = "Password"

'But by putting in a table you will be able to change it from the front end

'using a "change password" accessible only to administrators.

'Debug.Print "strPassword = " & strPassword

If InputBoxDK("enter password", "Log In") = strPassword Then

      'A successful log in closes the input box and allows the user to open tables.

Else:

      'An unsuccessful log in gives the user a second chance to log in.

      MsgBox ("Sorry, Incorrect Password" & vbNewLine & "Please enter Password")

      If InputBoxDK("enter password", "Log In") = strPassword Then

      'A successful log in closes the input box and allows the user to open tables.

      Else:

            'An unsuccessful log in gives the user a third chance.

            MsgBox ("Sorry, Incorrect Password" & vbNewLine & "Please enter Password")

            If InputBoxDK("enter password", "Log In") = strPassword Then

            'A successful log in closes the input box and allows the user to open tables.

            Else:

            'No more chances.

            'An unsuccessful log in closes the application.

            MsgBox ("Sorry you are not authorized to access this database.")

            DoCmd.Quit

            End If

      End If

End If

End Function

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter RunCode and under “Arguments” enter LogIn(). Save the macro. Now whenever the database is opened the function LogIn() will run before anything else happens.

Now I may have to go back and rework some of my front-end security to use input boxes instead of forms, all thanks to the excellent work of Daniel Klann.

Micosoft Access Front-End Security. Part 3: Authentication at the individual control level.

On some of the forms in our collection management database I have buttons that I want everyone to be able to use, some I only want a few to be able to use, and a few I only want one or two people to be able to use. I control these buttons by using the computer’s user name.

First I added a field called “Level,” to the users table, tblUsers. I used four levels, with level 4 being the administrator level, with no restrictions on what that person can do. Level 1 is the most restricted, users at this level can do little more than search and read data.

In the On Click event of each button that is not a level 1 control I wrap the event procedure in the following If-Then-Else logic:

    Dim intUser As Integer                  ‘This variable holds the number for the user level.

    ‘Pick up the user level by matching the computer’s username with the user ID in tblUsers

    intUser = DLookup("[Level]", "tluUsers", "[UserID] = '" & Environ("username") & "'")

    ‘For a level 2 control you want to allow all level 2, 3, and 4 users, so the code allows

    ‘for users greater than level 1.

    ‘For a level 3 control you want to allow only level 3 and 4 users, so the code allows only

    ‘users greater than level 2.

    ‘A level 4 control has code to allow only users greater than level 3.         

    If intUser > 3 Then

        ‘This is where you would put the code for your event procedure.

    Else:

        MsgBox ("Sorry, you are not authorized to access this procedure.")

    End If

Bound text boxes can be protected by putting this If-Then-Else logic in the Got Focus procedure. If the user level is too low to allow updating the data then reset the focus to another control. In this example level 1 users are not allowed to edit the collection title, but are allowed to edit the donor name.

    If intUser < 2 Then

        MsgBox ("Sorry, you are not authorized to edit the Collection Title")

        Me.DonorsName.SetFocus

    End If

Next Week: Back-End Security.

Microsoft Access Front-End Security. Part 2: Requiring Authentication to Open the Database

If you need, or want, to limit who can open your database there are at least two ways of doing so. One is easy and passive, it looks at the user ID for the computer and either opens the database, or not depending on whether or not you have allowed that user access. The other takes a little more work and requires input from the user.

Passive Authentication:
You can use this method if you are working in an environment that requires users to log onto their computers. Each computer stores the user ID and that can be picked up in the VBA code. An AutoExec macro triggering a user defined function and a table containing the allowed user IDs will do the trick. First you must define your function. To do this you can either put it in an existing module, or create a new module for it, either way we’ll call this function “Authentication()”.

Public Function Authentication()

'This function runs from the AutoExec macro when the database is opened.

Dim strUser As String          'This is the user ID picked up from the computer.

Dim intUser As Integer         'This is derived from the table of allowed users tblUsers.

'Pick up the user ID with the Environ() function.

strUser = Environ("username")

'Assign either a 1 or 0 to intUser by comparing strUser with the IDs in tblUsers.

'the DCount() function will count the number of times strUser appears in tblUsers.

'Be sure to user the null function Nz() to account for the null value returned when

'strUser does not match any value in tblUsers and convert it to a 0.
'This way any user in tblUsers will have a value of 1 and anyone not in the table
'will have a value of 0.

intUser = Nz(DCount(“[User]”, “tblUsers”, “[User] =” & strUser & “”), 0)

'If intUser = 0 the user ID is not in tblUsers and not allowed to open the database.

'In that case tell the user they are not authorized and close the application.

'Otherwise allow the database to open.

      If intUser = 0 then

          If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

              DoCmd.Quit acQuitSaveNone

          Else:

              'Correct log in opens whatever form you want your users to start with.
              DoCmd.OpenForm "frmStartPage"

          End If

     End If

End Function

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter RunCode and under “Arguments” enter Authentication(). Save the macro. Now whenever the database is opened the function Authentication() will run before anything else happens.


Interactive Authentication:
This requires the user to enter a password, which can be either unique to each user or the same one for everyone. In either case you will want to mask the password as it is entered, so you will need to use a form instead of a function. For this type of authentication your users table, tblUsers, will have two columns. One column for the user name or ID and one for the password, can be the same for everyone. The form will need three text boxes. One text box for the username, one for the password, and a third one, set not to display, for the counter. There also needs to be a “Log In” button. The OnClick event for this button will run the following code:

Private Sub cmdLogIn_Click()

Dim strUser As String                      'This is the user name entered by the user.

Dim strPassword As String              'This is the password entered by the user.

Dim strAuthenticate As String           'This is the password from tblUsers.

Dim intCounter As Integer                'This keeps track of number of log in attempts.

intCounter = Nz(Me.txtCounter, 0)

'Me.txtCounter refers to a control on the Log In with its “visible” property set to “No.”

strUser = Me.txtUser

strPassword = Me.txtPassword

strAuthenticate = Nz(DLookup("[Password]", "tblUsers", "[User] = '" & strUser & "'"), “None”)

If strAuthenticate = “None” Then

       'Is someone not included in the users table attempts to log in close the database.

       If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

       DoCmd.Quit acQuitSaveNone

       End If

Else:

       If strPassword = strAuthenticate Then

             'Correct log in opens whatever form you want your users to start with.

             DoCmd.OpenForm "frmStartPage"

             'Then close the Log In form.

             DoCmd.Close acForm, "frmLogIn"

       Else:

             Me.txtUser = strUser

             Me.txtPassword = ""

             Me.txtPassword.SetFocus

             intCounter = intCounter + 1

            Me.txtCounter = intCounter

            'After three incorrect log in attempts close the database.

            If intCounter = 3 Then

                 If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

                      DoCmd.Quit acQuitSaveNone

                End If

            Else:

                MsgBox "Incorrect Log In. Please try Again"

           End If

      End If

End If

DoCmd.Close acForm, "frmLogIn"

End Sub

To mask the password as it is typed you will need an On Load event for the form:

Private Sub Form_Load()

       Me.txtUser.InputMask = "Password"

End Sub

If you would like the password to be case sensitive replace
If strPassword = strAuthenticate Then
With
If CBool(InStrB(strPassword, strAuthenticate)) Then

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter OpenForm and under “Arguments” enter frmLogIn. Save the macro. Now whenever the database is opened the log in form will open before anything else happens.

Of course, a user could just close or minimize the log in form, but if you had locked down the front end as described in the last post they would not be able to open any forms or interact with any of the tables.

Next week: Part 3: Authentication at the individual control level.