Solved

Generate a Check Digit

Posted on 2002-04-20
23
1,880 Views
Last Modified: 2008-03-17
I have a large number of computers arriving for our computer refurbishing project (http://helpingtulsa.org) and I need to generate a sequential series of numbers, each with a check digit, which I can print out on Avery Labels (#5160 30 labels to a sheet), so I can stick a label on each computer, and then when it is processed manually type in the number on the label and have the check digit make sure I did not transpose digits when typing in the number.

(1) How can I generate let us say 20 sheets at a time (600 lables), such that the 600 numbers are all unique and include a check digit, and the next time I generate another 20 sheets they use different numbers

and

(2) When I am manually updating the database how can I put validation in an Access Form to perform the checkdigit calculation on the entered number.

I realize many checkdigit routines are based on bar coding, and I don't object if the label includes a bar code version of the number as well as a human readable version, but right now I don't have a bar code wand available everytime I might want to read a number, so the label must have a human readable number, whether it has a bar code readable one or not.
0
Comment
Question by:singleton
  • 8
  • 8
  • 4
  • +1
23 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6956781
I assume you're registering the computers in a table, thus using an autonumber to keep them unique.
With this autonumber you can use a "modulo 11" routine to generate a checkdigit for printing on your labels.

The modulo is calculated by multiplying the digits with different values and calculating the remainder.
This text from the web shows how:
==========================================================
In this example, we want to encode the data "21435" in a Code 11 barcode with a single check digit. The check digit is a  modulo 11 checksum.

First, we determine the weights of the numbers in the Code 11 barcode starting from the right side of the number. We then multiply the number of the barcode by the weighting and add these together as in the example:
Barcode to encode: 2  1  4  3  5
Weighting:         5  4  3  2  1
Totals:            10 4  12 6  5  Then add them 10+4+12+6+5=37
Next we divide the total by 11 and get the remainder which is 37/11=3 remainder 4. The remainder is the check character.
To type or print a Code 11 barcode encoding the data 21435 that can be scanned, we need to print (214354) .
==========================================================

This is used on barcodes, but also for your banking number.

Need more info ?

Nic;o)
0
 
LVL 11

Author Comment

by:singleton
ID: 6956790
Auto number is a numeric. Would I convert it to text to isolate the individual digits, and then convert each back to numeric for the multiplication?

Can you provide me with sample code to do the generation of the check digit, how to use it to generate n pages of 30 labels per page, and how to do the validation of a number entered in a form?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6956839
Hi,

>Auto number is a numeric. Would I convert it to text to isolate the individual digits, and then convert
each back to numeric for the multiplication?

Yes ;-)

Writing a loop using a string function will do this like:

intLen = len(AutoNumKey)  
intCount = 0
for intI=intLen to 1 step -1
    intCount = intCount + val(mid(AutoNum,intI,1) * intI)
next intI

Now the intCount has the value to be "modulo'd".

Can you try some coding of a function yourself ?

The label generation would be best to use the report label wizard. But using MS Word's mailmerge is another option.
Personally I would enter a "LabelGenerated" field to the tblComputer. Now you can create a report query where you check for "LabelGenerated" to be false. When printing fails you can print again, when success full you run a query to set all "LabelGenerated" fields to true.
Newly added computers will have the field set to false, thus a new print will only print for the newly added computers.

Getting the picture ?

Nic;o)

   
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6957513
Hi singleton,

There are many ways to calculate check digit. One of them is as Nico suggested, another is to use EAN/UCC method.
This is how EAN/UCC (BarCode organization) calculate check digit for variouse BarCode Types (EAN/UCC-8, UCC-12, EAN/UCC-13 and EAN/UCC-14). Just follow this link for more instructions:
http://www.ean-int.org/cdcalcul.html

If you are interested, I have a hole bunch of VBA routines (written for MS Access 97 application) that deals with this format of BarCode check digit calculation. The routines can:
Validate user input.
Calculate the correct check digit for a given number.

All we'll have to add is a routine that generates new numbers. This can be achieved by using the Autonumber capability, calculate the correct check digit for that number and assign it to a CheckDigit field that holds the check digit for the newly generated autonumber.

HTH,

Nosterdamus
0
 
LVL 11

Author Comment

by:singleton
ID: 6957616
Nosterdamus that would be very useful. The bar code is not required at this time, but it would be useful, since I may get a wand in the future, and the rest of it sounds very good. I have been futzing around trying to do what nico said, but I have been more focused on the other problem I am trying to resolve with him, related importing data from an HTML Form Email message. If you could add the code you referred to and email me the mdb at don@donsingleton.com I can post it intact on the website which I am working on, so that when this Q is closed others purchasing the Answer will be able to see what solved the problem.
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6957617
Hi singleton,

Sending you the MDB is not possible unfortunatly, but if it's OK with you, then I'll post the relavant code here.

Nosterdamus
0
 
LVL 11

Author Comment

by:singleton
ID: 6957624
It would certainly be easier if you could send it, and I thought that posting a link to it in my response would make it available for those needing it as a PAQ, but I have no problem with you posting the relevant code here.

I am not a terribly strong Access Programmer, so I need more that just a hint as to how to use it.
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6957626
Hi singleton,

Sending you the MDB is not possible unfortunatly, but if it's OK with you, then I'll post the relavant code here.

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6957689
Hi singleton,

(Sorry about the Dup posting...)

Here goes...

Copy the following code and paste it in a (new?) module.

'<Code>

Option Compare Database
Option Explicit

Public Const CREATE_CHECK_DIGIT = True
Public Const VALIDATE_CHECK_DIGIT = False

Public Const MAX_BAR_CODE_LEN = 14
Public Const EAN_13 = 13
Public Const UCC_12 = 12
Public Const EAN_8 = 8

'*******************************************************************************
'Function GetCheckDigit(strBarCode As String) As String
'
' The function gets a number (up to 14 digits long) as a string variable
' and returns the value of the check digit of that number,
' according to EAN/UCC methodology
'*******************************************************************************
Private Function GetCheckDigit(ByVal strBarCode As String) As String
       
    Dim i As Long
    Dim OddNumbers As Long
    Dim EvenNumbers As Long
    Dim OddAndEvenSum As Long
       
        For i = (MAX_BAR_CODE_LEN - Len(strBarCode)) To 1 Step -1
            strBarCode = "0" & strBarCode
        Next i
   
        EvenNumbers = 0
        For i = 1 To MAX_BAR_CODE_LEN Step 2
            EvenNumbers = EvenNumbers + Val(Mid(strBarCode, i, 1))
        Next i
   
        OddNumbers = 0
        For i = 2 To MAX_BAR_CODE_LEN Step 2
            OddNumbers = OddNumbers + Val(Mid(strBarCode, i, 1))
        Next i
        OddNumbers = OddNumbers * 3
   
        OddAndEvenSum = OddNumbers + EvenNumbers
   
        GetCheckDigit = IIf(10 - (OddAndEvenSum Mod 10) = 10, "0", _
                            CStr(10 - (OddAndEvenSum Mod 10)))
End Function    'GetCheckDigit

'*******************************************************************************
'Private Function CreateCheckDigit(strBarCode As String) As String
'
' The function gets a number (up to 14 digits long) as a string variable
' and returns the number (up to 15 digits) including the check digit
'*******************************************************************************

Private Function CreateCheckDigit(ByVal strBarCode As String) As String
   
    CreateCheckDigit = strBarCode & GetCheckDigit(strBarCode)

End Function    'CreateCheckDigit

'*******************************************************************************
'Public Function IsCheckDigitOK(ByVal strBarCode As String) As Boolean
'
' The function validates if the check digit is OK.
' It will return True if the check digit is OK, otherwize False is returned
'
'*******************************************************************************

Public Function IsCheckDigitOK(ByVal strBarCode As String) As Boolean

    Dim strTemp As String
    Dim i As Long
    Dim CheckSumNumber As String
   

        CheckSumNumber = Right(strBarCode, 1)
        strTemp = Left(strBarCode, Len(strBarCode) - 1)
       
        IsCheckDigitOK = (CheckSumNumber = GetCheckDigit(strTemp))

End Function    'IsCheckDigitOK

'*******************************************************************************
'Public Function CalcCheckDigit(ByVal strBarCode As String, _
'                               Optional Mode As Boolean = CREATE_CHECK_DIGIT) _
'                               As Variant
'
' According to the value of Mode (optional), the function will do:
' 1. If Mode=CREATE_CHECK_DIGIT (default)
'    get a number in a string format and calculates the check digit for that
'    number. The function will return the number + check digit as one number.
' 2. If Mode = VALIDATE_CHECK_DIGIT
'    Return True if Check digit is OK, otherwize, return false
'*******************************************************************************

Public Function CalcCheckDigit(ByVal strBarCode As String, _
                               Optional Mode As Boolean = CREATE_CHECK_DIGIT) _
                               As Variant
    Select Case Mode
        Case CREATE_CHECK_DIGIT
            CalcCheckDigit = CreateCheckDigit(strBarCode)
        Case VALIDATE_CHECK_DIGIT
            CalcCheckDigit = IsCheckDigitOK(strBarCode)
    End Select

End Function    'CalcCheckDigit



Public Sub test()
    Const MyNumber = 12376780#  'Correct check digit
'    Const MyNumber = 12376789#  'Wrong check digit
   
    MsgBox "The number entered is " & MyNumber & vbCrLf & vbCrLf & _
           "The number with the relevant check digit is: " & CalcCheckDigit(MyNumber)

    MsgBox "The Number entered is " & MyNumber & vbCrLf & vbCrLf & _
           "The check digit is " & IIf(IsCheckDigitOK(MyNumber), _
           "OK", "WRONG" & vbCrLf & vbCrLf & _
           "The check digit should be " & GetCheckDigit(Left(MyNumber, Len(CStr(MyNumber)) - 1)))
End Sub

'</Code>


Run Sub Test above to check the routines. You can play around with the numbers passed in MyNumber and see how:
1. New check digit is generated for each number.
2. A validation on the last digit is performed, as it is assumed to be a check digit. If it's not OK, a calculation for the correct Check digit is performed.

To use the code in your application, you should use it as follows:
To validate user input, you should place the following code in the BeforeUpdate event of a TextBox (assuming it's name is InputNumber):
Private Sub InputNumber_BeforeUpdate(Cancel As Integer)
    If Not CalcCheckDigit(Me.InputNumber.Value, VALIDATE_CHECK_DIGIT) Then
       strMsg = "Wrong check digit!" & vbCrLf & vbCrLf & "Allow input anyway?"
       If MsgBox(strMsg, vbCritical + vbYesNo, "Error") <> vbYes Then
           Cancel = True
       End If
    End If
End Sub


To calc the check digit for any given MyNumber, use:
Dim strCheckDigit As String
strCheckDigit = GetCheckDigit(MyNumber)


Hope this helps,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6957701
Correction...

The following should be ignored:

>>>To calc the check digit for any given MyNumber, use:
>>>Dim strCheckDigit As String
>>>strCheckDigit = GetCheckDigit(MyNumber)

This is because function GetCheckDigit is declared as private. This means that you can not call it from your forms.

To calc the check digit of a given number, you should use:
CalcCheckDigit(MyNumber, CREATE_CHECK_DIGIT)
or
CalcCheckDigit(MyNumber)
which will return the full number with it's calculated check digit.

To isolate the check digit after it is calculated, use:
Dim MyCheckDigit As String
MyCheckDigit = Right(CalcCheckDigit(MyNumber), 1)

HTH,

Nosterdamus
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6957777
Nic0;),
I was playing with your code and I believe something is missing.  Shouldn't this read:

intLen = len(AutoNumKey)  
intCount = 0
I = 1
for intI=intLen to 1 step -1
   intCount = intCount + val(mid(AutoNum,intI,1) * I)
   I = I + 1
next intI
vCheckDigit = intCount Mod 11

intI is decreasing with each digit starting from the right most while the multiplier should be increasing by one with each digit. Please correct me if I have missed the intent.

Bob Scriver


0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6957804
Also, I take it that in the event that the remainder is 10 that a zero should be considered the checkdigit?  

Example:  18  8*1 = 8   1*2= 2  8+2=10  10/11=0 remainder 10.  

Should the following be used in that instance?

vCheckDigit = IIf(IntCount Mod 11 = 10, 0, IntCount Mod 11)

I don't know the rule for this instance.

Bob Scriver
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6957809
Correct Bob, the multiplier should read:
(intLen + 1 - intI)
Thus giving:
intLen = len(AutoNumKey)  
intCount = 0
for intI=intLen to 1 step -1
   intCount = intCount + val(mid(AutoNum,intI,1) * (intLen + 1 - intI))
next intI

Thus you don't need an extra I.....

The general disadvantage of air-code has been proven by you once again ;-)

Nic;o)
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6957819
I knew it was just an overwite.  What about the remainder of 10 for numbers.  Should they be converted to 0.  They would have the same check digit as a number that has no remainder. Any problem with that scheme?

Bob Scriver
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6958104
You have multiple Modulo tests, in general Modulo 9 or 11 is used. 11 is more secure as 9, but just surf the web and checkout what the math professors have written about that ;-)

Nic;o)
0
 
LVL 11

Author Comment

by:singleton
ID: 6958309
It would certainly be easier if you could send it, and I thought that posting a link to it in my response would make it available for those needing it as a PAQ, but I have no problem with you posting the relevant code here.

I am not a terribly strong Access Programmer, so I need more that just a hint as to how to use it.
0
 
LVL 11

Author Comment

by:singleton
ID: 6958311
Please excuse double posting of my last response. I just did a refresh to see if there were any more posts, since the emails seem to be arriving late, and it apparently resent my last posting.
0
 
LVL 11

Author Comment

by:singleton
ID: 6958599
When you say
To validate user input, you should place the following code in the BeforeUpdate event of a TextBox (assuming
it's name is InputNumber):
Private Sub InputNumber_BeforeUpdate(Cancel As Integer)
   If Not CalcCheckDigit(Me.InputNumber.Value, VALIDATE_CHECK_DIGIT) Then
      strMsg = "Wrong check digit!" & vbCrLf & vbCrLf & "Allow input anyway?"
      If MsgBox(strMsg, vbCritical + vbYesNo, "Error") <> vbYes Then
          Cancel = True
      End If
   End If
End Sub

Do you mean a text box associated with a field in the table, or just a text box for transfer to the next record. I presumed the latter, but when I put that code in the box and entered an incorrect number nothing was displayed. Is that because since it was not a part of the database, there was nothing to update?

What I want is to make changes to one record then fill in the box to go to the next record.

0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6959112
Hi singleton,

In order to trigger the BeforeUpdate event, you have to leave the field by pressing the Enter or Tab keys, or by pointing to another control in the form with your mouse and click.

I suggest that for the purpose of testing, create a form wih two controls: InputNumber & Dummy. Place the BeforeUpdate Event code in the BeforeUpdate Evenet procedure of the InputNumber textbox, enter a number and hit Tab or Enter.

Does this work for you?

For more information regarding the Events and when they occur, search MS Access help for "Find out when events occur".

HTH,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6959120
Sorry,

Regarding your question, the procedure should work both ways (either you associate the textbox with a field in a table, or not).

Nosterdamus
0
 
LVL 11

Author Comment

by:singleton
ID: 6962794
Nosterdamus, what you gave me almost works, but I am still having problems. I have increased the points to 200 since I am extending the question, but rather than closing this and posting a new Q I thought it would be good to have everything together so this will be more useable when it becomes a PAQ

You gave me:

Private Sub InputNumber_BeforeUpdate(Cancel As Integer)
   If Not CalcCheckDigit(Me.InputNumber.Value, VALIDATE_CHECK_DIGIT) Then
      strMsg = "Wrong check digit!" & vbCrLf & vbCrLf & "Allow input anyway?"
      If MsgBox(strMsg, vbCritical + vbYesNo, "Error") <> vbYes Then
          Cancel = True
      End If
   End If
End Sub

since my field is "work" I substituted that for InputNumber but got a message that strMsg was not defined. How do you define a temporary string variable.

I went with:

Private Sub work_BeforeUpdate(Cancel As Integer)
   If Not CalcCheckDigit(Me.work.Value, VALIDATE_CHECK_DIGIT) Then
      If MsgBox("Not a valid number; reenter") <> vbYes Then
          Cancel = True
      End If
   End If
End Sub

and that worked, but if it is a good number I need to go to that record, so I tried:

Private Sub work_BeforeUpdate(Cancel As Integer)
   If Not CalcCheckDigit(Me.work.Value, VALIDATE_CHECK_DIGIT) Then
      If MsgBox("Not a valid number; reenter") <> vbYes Then
          Cancel = True
      End If
   Else: DoCmd.GoToRecord acDataTable, "frmDonation", acGoTo, Me.work.Value
   End If
End Sub

This code is in the BeforeUpdate field "work" of the form frmDonation, yet I get the message that frmDonation is not open.

0
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 200 total points
ID: 6964869
Hi singlton,

1. Define strMsg

Private Sub Work_BeforeUpdate(Cancel As Integer)

   Dim strMsg As String   'Define strMsg

  If Not CalcCheckDigit(Me.work.Value, VALIDATE_CHECK_DIGIT) Then
     strMsg = "Wrong check digit!" & vbCrLf & vbCrLf & "Allow input anyway?"
     If MsgBox(strMsg, vbCritical + vbYesNo, "Error") <> vbYes Then
         Cancel = True
     End If
  End If
End Sub

2. Goto Record

If you need to go to a specific record, then I suggest that you do it at the AfterUpdate Event, rather in the BeforeUpdate. The reason is that you realy DO NOT WANT to stop the update procedure from executing, if the number is OK (which is what will happen if you go your way).

What I suggest is as follows:

In the form's module, define a module's parameter:
Dim NumberOK As Boolean

Note that you should place the above Dim command right bellow these two lines:
Option Compare Database
Option Explicit

Change the work_BeforeUpdate event procedure to:
Private Sub work_BeforeUpdate(Cancel As Integer)
    NumberOK = False
    If Not CalcCheckDigit(Me.work.Value, VALIDATE_CHECK_DIGIT) Then
       If MsgBox("Not a valid number; reenter") <> vbYes Then
          Cancel = True
       End If
    Else
       NumberOK = True
    End If
End Sub

and in the work_AfterUpdate procedure, put:
Private Sub work_AfterUpdate()
    If NumberOK Then
        DoCmd.FindRecord Me.work.Value
    End If
End Sub


HTH,

Nosterdamus
0
 
LVL 11

Author Comment

by:singleton
ID: 6992650
I still have not been able to figure out how to actually implement your code in a form, i.e. prompt for a number, call the module code to validate, and if it passes do a search for the record, but I figured I owed it to you to go ahead and close this question out, and let it become a PAQ.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now