Results 1 to 14 of 14

Adding Code 128/39 Barcodes to Access Reports

  1. #1
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875

    Adding Code 128/39 Barcodes to Access Reports


    Hello all! I just wanted to add this amazing bit of code to the repository!

    In my recent searches, I've found that adding barcode capabilities to Access falls into one of two categories:
    Extremely basic (limited to using a "Barcode Font" to simulate barcodes), or
    Costly (paying for a pre-packaged - and proprietary - barcode "solution" that can be imported into Access).

    Well, here's an alternative: Free, "true" barcode support in Access! It's not perfect, but it works. And, it gives you far greater control over the results than using a font to simulate barcodes!

    Code 128 Barcodes
    Usage:
    • Step 1 - Paste the function below into a VBA Module.
    • Step 2 - In your Reports, create a Text Box with the dimensions you want the barcode to fill.
    • Step 3 - Set the Text Box's Visible Property to No.
    • Step 4 - In the Report Detail's On Print Event, add Result = Barcode_128(Me!MyTextBox, Me). You may need to Define the variable Result as a Boolean first.
    • Step 5 - Print out the Report (or view it via "Print Preview") to see your barcode!


    Code:
    ' Written by Rodney Marr (RodMarr@mailcity.com) October 7, 2000
    '
    ' Barcode 128-B Generator
    '
    ' Permission granted for public use and royalty-free distribution.
    ' No mention of source or credits is required.
    '
    ' I got a lot of help from the following people's work
    ' Russ Adams' BarCode 1 Web Page   http://www.adams1.com/pub/russadam/info.html
    ' A Free 128-B code generator in Visual Basic by Stefan Karlsson (mrswede@libertysurf.se)
    ' And the Creator of the code 39 Module
    '
    'For Notes on how to use this code look at the code for the barcode39 Generator
    
    Public Function Barcode_128(Ctrl As Control, rpt As Report)
        'On Error GoTo ErrorTrap_Barcode_128
    
        'Code 128B has 5 main parts to it. The first part is a start character(211214), followed by DataCharcters. The Data
        'Characters are followed by a check(or Checksum) Character, and that is followed by a stop Character(2331112)
        'The last part of Code 128B is the two quiet sections at the front and back of the barcode. These are 10 dimensions
        'Long(I am thinking that is 11 modules long). Each character is 11 modules long, except the stop character which is
        '13 modules long
    
        Dim CharNumber As Variant, CharData As Variant, CharBarData As Variant, Nratio As Variant, Nbar As Variant
        Dim barcodestr As String, Barcode As String, Barchar As String, Barcolor As Long, Parts As Integer, J As Integer
        Dim tsum As Integer, lop As Integer, s As Integer, checksum As Integer, p As Integer, barwidth As Integer
        Dim boxh As Single, boxw As Single, boxx As Single, boxy As Single, Pix As Single, Nextbar As Single
        Const White = 16777215: Const Black = 0
    
        'This is the Raw data that I threw into an arrays
        CharNumber = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16,", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29,", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106")
        CharData = Array("SP", "!", Chr(34), "#", "$", "%", "&", "'", "(", ")", "*", "+", ",", "-", ".", "/", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ":", ";", "<", "=", ">", "?", "@", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "[", "\", "]", "^", "_", "`", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "I", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "{", "|", "}", "~", "DEL", "FNC 3", "FNC 2", "SHIFT", "CODE C", "FNC 4", "CODE A", "FNC 1", "Start A", "Start B", "Start C", "Stop")
        CharBarData = Array("212222", "222122", "222221", "121223", "121322", "131222", "122213", "122312", "132212", "221213", "221312", "231212", "112232", "122132", "122231", "113222", "123122", "123221", "223211", "221132", "221231", "213212", "223112", "312131", "311222", "321122", "321221", "312212", "322112", "322211", "212123", "212321", "232121", "111323", "131123", "131321", "112313", "132113", "132311", "211313", "231113", "231311", "112133", "112331", "132131", "113123", "113321", "133121", "313121", "211331", "231131", "213113", "213311", "213131", "311123", "311321", "331121", "312113", "312311", "332111", "314111", "221411", "431111", "111224", "111422", "121124", "121421", "141122", "141221", "112214", "112412", "122114", "122411", "142112", "142211", "241211", "221114", "413111", "241112", "134111", "111242", "121142", "121241", "114212", "124112", "124211", "411212", "421112", "421211", "212141", _
                            "214121", "412121", "111143", "111341", "131141", "114113", "114311", "411113", "411311", "113141", "114131", "311141", "411131", "211412", "211214", "211232", "2331112")
    
        barcodestr = "211214" 'Add the Startcode for Start B (characterset B) to the barcode string
        tsum = 104                      'And this is the value for that startcode which will be added with the other character values to find the checksum character
        boxx = Ctrl.Left: boxy = Ctrl.Top: boxw = Ctrl.Width: boxh = Ctrl.Height    'Get control size and location properties.
    
        Barcode = Ctrl                                                          'Set handle on control.
    
        Nratio = Array("0", "15", "30", "45", "60")           'Set up the array for the different bar width ratios
        Parts = ((11 * (Len(Barcode))) + 35) * Nratio(1)  'This is the formula for the width of the barcode
        Pix = (boxw / Parts)                                                  'Here I find out exactly how many Pixels a bar will be
        Nbar = Array((Nratio(0) * Pix), (Nratio(1) * Pix), (Nratio(2) * Pix), (Nratio(3) * Pix), (Nratio(4) * Pix)) 'Set up the array to handle the pixels for each type of bar
    
        'Loop through all bardata to count the sum for all characters and add barcode charcter strings the to the barcode string
        For lop = 1 To Len(Barcode)
            Barchar = Mid(Barcode, lop, 1)
            If Barchar = " " Then Barchar = "SP"
            For s = 0 To UBound(CharData)
                If Barchar = CharData(s) Then
                    barcodestr = barcodestr & CharBarData(s) 'This is where I added the character strings to each other to make one long string of 1's, 2's, 3's, & 4's
                    tsum = tsum + (CLng(CharNumber(s)) * lop) 'Here every barcode character's number value is multiplied by its position in the line and added to tsum
                    'The actual formula for find the the  Checksum  is "(104 + (1 * CharcterNumber) + (2 * CharcterNumber) + ...)/103" You would Use the Remainder as
                    'The Checksum Character. In the case of "BarCode 1" the formula would look
                    'like "(104+(1*34)+(2*65)+(3*82)+(4*35)+(5*79)+(6*68)+(7*69)+(8*0)+(9*17))/103=20 with Remainder of 33" Therefore the checksum would equal 33
                    Exit For
                End If
            Next s
        Next lop
    
        checksum = tsum - (Int(tsum / 103) * 103)                                             'Here I use the the totat sum (tsum) to find the checksum
        barcodestr = barcodestr & CharBarData(checksum) & "2331112" 'Here I add the checksum then the stop character into the barcode string
    
        'lets do some initialization
        Barcolor = Black
        Nextbar = boxx + 11     'I added the 20 for the whitespace (or quiet space) at the beginning of the barcode
    
        'Draw the Barcode
        For J = 1 To Len(barcodestr)
            Barchar = Mid(barcodestr, J, 1)   'Reuse variable barchar to store the character to be drawn
            barwidth = CInt(Barchar)              'Change the barcode charcter into an integer so I can use in the array part of the next line
            rpt.Line (Nextbar, boxy)-Step(Nbar(barwidth), boxh), Barcolor, BF  'Draw the line
            Nextbar = Nextbar + Nbar(barwidth)                                                      'Calculate the next starting point
            If Barcolor = White Then Barcolor = Black Else Barcolor = White      'Swap line colors
        Next J
    
    Exit_Barcode_128:
        Exit Function
    
    ErrorTrap_Barcode_128:
        MsgBox Error$
    
        Resume Exit_Barcode_128
    End Function
    Code 39 Barcodes
    Usage:
    • Step 1 - Follow Steps 1-3 for creating Code 128 barcodes.
    • Step 2 - In the Report Detail's On Print Event, add Result = Barcode_39(Me!MyTextBox, Me) (instead of Barcode_128()). You may need to Define the variable Result as a Boolean first.
    • Step 3 - Print out the Report (or view it via "Print Preview") to see your barcode!


    Code:
    '
    ' mod_BarCode_Generator_Code39
    '
    ' Barcode Generator for Code 3 of 9, Code 39, and Mil-spec Logmars.
    '
    ' version 2.0 (updated for MsAccess 97)
    '
    ' (c) 1993-1999 James Isle Mercanti, Cocoa Beach, FL 32931  USA
    ' Permission granted for public use and royalty-free distribution.
    ' No mention of source or credits is required. All rights reserved.
    '
    ' TO USE THIS CODE:
    '
    '   1 - Create Report with a TextBox control. (example named Barcode)
    '       Make sure the Visible property is set to "No".
    '   2 - Set On-Print property of section to [Event Procedure]
    '       by clicking on the [...] and selecting "Code Builder"
    '   3 - Confirm that the following code matches yours...
    '
    '      Sub Detail1_Print (Cancel As Integer, PrintCount As Integer)
    '
    '         Result = Barcode_39(Barcode, Me)
    '
    '      End Sub
    '
    '   4 - NOTE: The name of the section is "Detail1" for example only!
    '       Your section might show a different name. Ditto for "Barcode".
    '
    '   5 - NOTE: To use on sub-forms, the Report name should be hard-coded
    '       into the function. i.e. Rpt = Reports!MainForm!SubForm.Report.
    '       The easy method is to just avoid using sub-forms and sub-reports.
    '
    
    Function Barcode_39(Ctrl As Control, rpt As Report)
        On Error GoTo ErrorTrap_BarCode39
    
        Dim Nbar As Single, Wbar As Single, Qbar As Single, Nextbar As Single
        Dim CountX As Single, CountY As Single, CountR As Single
        Dim Parts As Single, Pix As Single, Color As Long, BarCodePlus As Variant
        Dim Stripes As String, BarType As String, Barcode As String
        Dim Mx As Single, my As Single, Sx As Single, Sy As Single
        Const White = 16777215: Const Black = 0
        Const Nratio = 20, Wratio = 55, Qratio = 35
    
        'Get control size and location properties.
        Sx = Ctrl.Left: Sy = Ctrl.TOP: Mx = Ctrl.Width: my = Ctrl.Height
    
        'Set handle on control.
        Barcode = Ctrl
    
        'Calculate actual and relative pixels values.
        Parts = (Len(Barcode) + 2) * ((6 * Nratio) + (3 * Wratio) + (1 * Qratio))
        Pix = (Mx / Parts):
        Nbar = (20 * Pix): Wbar = (55 * Pix): Qbar = (35 * Pix)
    
        'Initialize bar index and color.
        Nextbar = Sx
        Color = White
    
        'Pad each end of string with start/stop characters.
        BarCodePlus = "*" & UCase(Barcode) & "*"
    
        'Walk through each character of the barcode contents.
        For CountX = 1 To Len(BarCodePlus)
            'Get Barcode 1/0 string for indexed character.
            Stripes = MD_BC39(Mid$(BarCodePlus, CountX, 1))
            For CountY = 1 To 9
                'For each 1/0, draw a wide/narrow bar.
                BarType = Mid$(Stripes, CountY, 1)
    
                'Toggle the color (black/white).
                If Color = White Then Color = Black Else Color = White
                Select Case BarType
                    Case "1"
                        'Draw a wide bar.
                        rpt.Line (Nextbar, Sy)-Step(Wbar, my), Color, BF
                        Nextbar = Nextbar + Wbar
                    Case "0"
                        'Draw a narrow bar.
                        rpt.Line (Nextbar, Sy)-Step(Nbar, my), Color, BF
                        Nextbar = Nextbar + Nbar
                End Select
            Next CountY
    
            'Toggle the color (black/white).
            If Color = White Then Color = Black Else Color = White
    
            'Draw intermediate "quiet" bar.
            rpt.Line (Nextbar, Sy)-Step(Qbar, my), Color, BF
            Nextbar = Nextbar + Qbar
        Next CountX
    
    Exit_BarCode39:
        Exit Function
    
    ErrorTrap_BarCode39:
        Resume Exit_BarCode39
    End Function
    
    Function MD_BC39(CharCode As String) As String
        On Error GoTo ErrorTrap_BC39
    
        ReDim BC39(90)
    
        BC39(32) = "011000100" ' space
        BC39(36) = "010101000" ' $
        BC39(37) = "000101010" ' %
        BC39(42) = "010010100" ' * Start/Stop
        BC39(43) = "010001010" ' +
        BC39(45) = "010000101" ' |
        BC39(46) = "110000100" ' .
        BC39(47) = "010100010" ' /
        BC39(48) = "000110100" ' 0
        BC39(49) = "100100001" ' 1
        BC39(50) = "001100001" ' 2
        BC39(51) = "101100000" ' 3
        BC39(52) = "000110001" ' 4
        BC39(53) = "100110000" ' 5
        BC39(54) = "001110000" ' 6
        BC39(55) = "000100101" ' 7
        BC39(56) = "100100100" ' 8
        BC39(57) = "001100100" ' 9
        BC39(65) = "100001001" ' A
        BC39(66) = "001001001" ' B
        BC39(67) = "101001000" ' C
        BC39(68) = "000011001" ' D
        BC39(69) = "100011000" ' E
        BC39(70) = "001011000" ' F
        BC39(71) = "000001101" ' G
        BC39(72) = "100001100" ' H
        BC39(73) = "001001100" ' I
        BC39(74) = "000011100" ' J
        BC39(75) = "100000011" ' K
        BC39(76) = "001000011" ' L
        BC39(77) = "101000010" ' M
        BC39(78) = "000010011" ' N
        BC39(79) = "100010010" ' O
        BC39(80) = "001010010" ' P
        BC39(81) = "000000111" ' Q
        BC39(82) = "100000110" ' R
        BC39(83) = "001000110" ' S
        BC39(84) = "000010110" ' T
        BC39(85) = "110000001" ' U
        BC39(86) = "011000001" ' V
        BC39(87) = "111000000" ' W
        BC39(88) = "010010001" ' X
        BC39(89) = "110010000" ' Y
        BC39(90) = "011010000" ' Z
    
        MD_BC39 = BC39(Asc(CharCode))
    
    Exit_BC39:
        Exit Function
    
    ErrorTrap_BC39:
        MD_BC39 = ""
    
        Resume Exit_BC39
    End Function
    IMPORTANT NOTE: I did not create this code, it was created by the gentleman Rodney Marr and made available freely to all. I'm merely sharing is free code with you.

  2. #2
    Paul_s is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    1

    a light change.

    For my use case I found that adding this snippet of code helped a lot.

    if pix > 3 then
    pix = 3
    end if

    the barcode reader I was using doesn't like really wide bars. you can also fix this issue by making the report field shorter, but if you don't know the expected data size, then this code change is necessary.

    I added my change to the code below


    ' Written by Rodney Marr (RodMarr@mailcity.com) October 7, 2000
    '
    ' Barcode 128-B Generator
    '
    ' Permission granted for public use and royalty-free distribution.
    ' No mention of source or credits is required.
    '
    ' I got a lot of help from the following people's work
    ' Russ Adams' BarCode 1 Web Page http://www.adams1.com/pub/russadam/info.html
    ' A Free 128-B code generator in Visual Basic by Stefan Karlsson (mrswede@libertysurf.se)
    ' And the Creator of the code 39 Module
    '
    'For Notes on how to use this code look at the code for the barcode39 Generator

    Public Function Barcode_128(Ctrl As Control, rpt As Report)
    'On Error GoTo ErrorTrap_Barcode_128

    'Code 128B has 5 main parts to it. The first part is a start character(211214), followed by DataCharcters. The Data
    'Characters are followed by a check(or Checksum) Character, and that is followed by a stop Character(2331112)
    'The last part of Code 128B is the two quiet sections at the front and back of the barcode. These are 10 dimensions
    'Long(I am thinking that is 11 modules long). Each character is 11 modules long, except the stop character which is
    '13 modules long

    Dim CharNumber As Variant, CharData As Variant, CharBarData As Variant, Nratio As Variant, Nbar As Variant
    Dim barcodestr As String, Barcode As String, Barchar As String, Barcolor As Long, Parts As Integer, J As Integer
    Dim tsum As Integer, lop As Integer, s As Integer, checksum As Integer, p As Integer, barwidth As Integer
    Dim boxh As Single, boxw As Single, boxx As Single, boxy As Single, Pix As Single, Nextbar As Single
    Const White = 16777215: Const Black = 0

    'This is the Raw data that I threw into an arrays
    CharNumber = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16,", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29,", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106")
    CharData = Array("SP", "!", Chr(34), "#", "$", "%", "&", "'", "(", ")", "*", "+", ",", "-", ".", "/", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ":", ";", "<", "=", ">", "?", "@", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "[", "\", "]", "^", "_", "`", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "I", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "{", "|", "}", "~", "DEL", "FNC 3", "FNC 2", "SHIFT", "CODE C", "FNC 4", "CODE A", "FNC 1", "Start A", "Start B", "Start C", "Stop")
    CharBarData = Array("212222", "222122", "222221", "121223", "121322", "131222", "122213", "122312", "132212", "221213", "221312", "231212", "112232", "122132", "122231", "113222", "123122", "123221", "223211", "221132", "221231", "213212", "223112", "312131", "311222", "321122", "321221", "312212", "322112", "322211", "212123", "212321", "232121", "111323", "131123", "131321", "112313", "132113", "132311", "211313", "231113", "231311", "112133", "112331", "132131", "113123", "113321", "133121", "313121", "211331", "231131", "213113", "213311", "213131", "311123", "311321", "331121", "312113", "312311", "332111", "314111", "221411", "431111", "111224", "111422", "121124", "121421", "141122", "141221", "112214", "112412", "122114", "122411", "142112", "142211", "241211", "221114", "413111", "241112", "134111", "111242", "121142", "121241", "114212", "124112", "124211", "411212", "421112", "421211", "212141", _
    "214121", "412121", "111143", "111341", "131141", "114113", "114311", "411113", "411311", "113141", "114131", "311141", "411131", "211412", "211214", "211232", "2331112")

    barcodestr = "211214" 'Add the Startcode for Start B (characterset B) to the barcode string
    tsum = 104 'And this is the value for that startcode which will be added with the other character values to find the checksum character
    boxx = Ctrl.Left: boxy = Ctrl.Top: boxw = Ctrl.Width: boxh = Ctrl.Height 'Get control size and location properties.

    Barcode = Ctrl 'Set handle on control.

    Nratio = Array("0", "15", "30", "45", "60") 'Set up the array for the different bar width ratios
    Parts = ((11 * (Len(Barcode))) + 35) * Nratio(1) 'This is the formula for the width of the barcode
    Pix = (boxw / Parts) 'Here I find out exactly how many Pixels a bar will be
    if pix > 3 then
    pix = 3
    end if
    Nbar = Array((Nratio(0) * Pix), (Nratio(1) * Pix), (Nratio(2) * Pix), (Nratio(3) * Pix), (Nratio(4) * Pix)) 'Set up the array to handle the pixels for each type of bar

    'Loop through all bardata to count the sum for all characters and add barcode charcter strings the to the barcode string
    For lop = 1 To Len(Barcode)
    Barchar = Mid(Barcode, lop, 1)
    If Barchar = " " Then Barchar = "SP"
    For s = 0 To UBound(CharData)
    If Barchar = CharData(s) Then
    barcodestr = barcodestr & CharBarData(s) 'This is where I added the character strings to each other to make one long string of 1's, 2's, 3's, & 4's
    tsum = tsum + (CLng(CharNumber(s)) * lop) 'Here every barcode character's number value is multiplied by its position in the line and added to tsum
    'The actual formula for find the the Checksum is "(104 + (1 * CharcterNumber) + (2 * CharcterNumber) + ...)/103" You would Use the Remainder as
    'The Checksum Character. In the case of "BarCode 1" the formula would look
    'like "(104+(1*34)+(2*65)+(3*82)+(4*35)+(5*79)+(6*68)+(7 *69)+(8*0)+(9*17))/103=20 with Remainder of 33" Therefore the checksum would equal 33
    Exit For
    End If
    Next s
    Next lop

    checksum = tsum - (Int(tsum / 103) * 103) 'Here I use the the totat sum (tsum) to find the checksum
    barcodestr = barcodestr & CharBarData(checksum) & "2331112" 'Here I add the checksum then the stop character into the barcode string

    'lets do some initialization
    Barcolor = Black
    Nextbar = boxx + 11 'I added the 20 for the whitespace (or quiet space) at the beginning of the barcode

    'Draw the Barcode
    For J = 1 To Len(barcodestr)
    Barchar = Mid(barcodestr, J, 1) 'Reuse variable barchar to store the character to be drawn
    barwidth = CInt(Barchar) 'Change the barcode charcter into an integer so I can use in the array part of the next line
    rpt.Line (Nextbar, boxy)-Step(Nbar(barwidth), boxh), Barcolor, BF 'Draw the line
    Nextbar = Nextbar + Nbar(barwidth) 'Calculate the next starting point
    If Barcolor = White Then Barcolor = Black Else Barcolor = White 'Swap line colors
    Next J

    Exit_Barcode_128:
    Exit Function

    ErrorTrap_Barcode_128:
    MsgBox Error$

    Resume Exit_Barcode_128
    End Function

  3. #3
    panoss is offline Advanced Beginner
    Windows 7 32bit Access 2003
    Join Date
    Oct 2014
    Posts
    53
    I tried to use the above mentioned code but failed.
    I tried and this which I believe belongs to Rodney Marr, the developer who created the code.
    But I thinK it need a component, Wang Image Admin Control?
    Is there a way for creating barcode in Access 2003?

    Edit: I replaced 'BarCode as String' with 'BarCode as Control' and 'BarCode = Ctrl' with 'Set BarCode = Ctrl' and works!
    Last edited by panoss; 11-06-2014 at 08:13 AM.

  4. #4
    Join Date
    Nov 2014
    Posts
    1
    Hi All,
    Panos asked if anyone had a 2003 version of these routines.

    Is this possible?

    Regards,

    Alan

  5. #5
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    It looks like panoss was able to modify the code so that it worked on earlier versions. Since I only have access 2010, the best I can do is make his changes on Monday and see if it still works in that version.

    Feel free to try his changes for yourself though.

  6. #6
    panoss is offline Advanced Beginner
    Windows 7 32bit Access 2003
    Join Date
    Oct 2014
    Posts
    53
    Try it, you 'll see it really works.
    Check this out:dbBarCode2.zip

  7. #7
    Jzlantz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Posts
    3
    Sorry to drag this post from the depths, but I am having issues using this barcode method to print from the database. I am using MS Access 2010 and have implemented the code as a module, and tying it to my text box on the details print event. The barcode shows up perfectly fine in Print Preview but when actually printed it does not show up. If I export to PDF, and print the PDF, it works perfectly fine as well and the barcode scans the correct value. Has anyone run into this, or have any ideas on what I can do to be able to print correctly straight from the database itself using this barcode module?

    Thank you

  8. #8
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    I've never run into that before and we use Access 2010 here. I use the code to print barcodes here at work on a daily basis and have never seen that issue.

    Are you trying to Print from either Layout or Report View? (can you even do this? I never use either of these views.) The barcodes will only appear on acViewPreview (Print Preview) and acViewNormal (actual printing) modes and will not display in acViewReport or acViewLayout.

  9. #9
    Jzlantz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Posts
    3
    Quote Originally Posted by Rawb View Post
    I've never run into that before and we use Access 2010 here. I use the code to print barcodes here at work on a daily basis and have never seen that issue.

    Are you trying to Print from either Layout or Report View? (can you even do this? I never use either of these views.) The barcodes will only appear on acViewPreview (Print Preview) and acViewNormal (actual printing) modes and will not display in acViewReport or acViewLayout.

    Well, thank you for making me think to try different views. Printing directly from pretty much any view EXCEPT Print Preview produces the correct results. I assume it may have to do with it only drawing the bar code once for the Print Call? (If you make it go to print preview it draws it, but doesn't draw itself again when printed from print preview or something of the sort). Not quite sure, it is strange. But, many thanks for helping me out here, it seems to be working fine now!

  10. #10
    Jzlantz is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    3
    So, I've found some more strange phenomena that I can't quite wrap my head around. In Access 2010, when trying to print my report with a barcode, I go to File >> Print >> Print >> OK (not changing any settings and allowing it to print to the default printer) it does not print the barcode. However, when I do File >> Print >> Quick Print (which prints to the default printer with default settings if I'm not mistaken) it prints perfectly fine. Does anyone have any ideas on what is going on here? What settings are important for the printer to be able to print barcodes properly? What printer settings should I be looking at? This has been thoroughly frustrating not understanding why some of these things are working for me and some are not, but it seems like some kind of settings issue to me.

    Hope you all had a good 4th of July

    Cheers,
    Jzlantz

  11. #11
    MichaelJ is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2015
    Posts
    2

    Thumbs up Great code. Would it be possible to rotate the code by 90 degrees

    Great code. Would it be possible to rotate the code by 90 degrees I have the on the same report 1 code horizontal and an other one vertical

    Quote Originally Posted by Rawb View Post
    Hello all! I just wanted to add this amazing bit of code to the repository!

    In my recent searches, I've found that adding barcode capabilities to Access falls into one of two categories:
    Extremely basic (limited to using a "Barcode Font" to simulate barcodes), or
    Costly (paying for a pre-packaged - and proprietary - barcode "solution" that can be imported into Access).

    Well, here's an alternative: Free, "true" barcode support in Access! It's not perfect, but it works. And, it gives you far greater control over the results than using a font to simulate barcodes!

    Code 128 Barcodes
    Usage:
    • Step 1 - Paste the function below into a VBA Module.
    • Step 2 - In your Reports, create a Text Box with the dimensions you want the barcode to fill.
    • Step 3 - Set the Text Box's Visible Property to No.
    • Step 4 - In the Report Detail's On Print Event, add Result = Barcode_128(Me!MyTextBox, Me). You may need to Define the variable Result as a Boolean first.
    • Step 5 - Print out the Report (or view it via "Print Preview") to see your barcode!


    Code:
    ' Written by Rodney Marr (RodMarr@mailcity.com) October 7, 2000
    '
    ' Barcode 128-B Generator
    '
    ' Permission granted for public use and royalty-free distribution.
    ' No mention of source or credits is required.
    '
    ' I got a lot of help from the following people's work
    ' Russ Adams' BarCode 1 Web Page   http://www.adams1.com/pub/russadam/info.html
    ' A Free 128-B code generator in Visual Basic by Stefan Karlsson (mrswede@libertysurf.se)
    ' And the Creator of the code 39 Module
    '
    'For Notes on how to use this code look at the code for the barcode39 Generator
    
    Public Function Barcode_128(Ctrl As Control, rpt As Report)
        'On Error GoTo ErrorTrap_Barcode_128
    
        'Code 128B has 5 main parts to it. The first part is a start character(211214), followed by DataCharcters. The Data
        'Characters are followed by a check(or Checksum) Character, and that is followed by a stop Character(2331112)
        'The last part of Code 128B is the two quiet sections at the front and back of the barcode. These are 10 dimensions
        'Long(I am thinking that is 11 modules long). Each character is 11 modules long, except the stop character which is
        '13 modules long
    
        Dim CharNumber As Variant, CharData As Variant, CharBarData As Variant, Nratio As Variant, Nbar As Variant
        Dim barcodestr As String, Barcode As String, Barchar As String, Barcolor As Long, Parts As Integer, J As Integer
        Dim tsum As Integer, lop As Integer, s As Integer, checksum As Integer, p As Integer, barwidth As Integer
        Dim boxh As Single, boxw As Single, boxx As Single, boxy As Single, Pix As Single, Nextbar As Single
        Const White = 16777215: Const Black = 0
    
        'This is the Raw data that I threw into an arrays
        CharNumber = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16,", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29,", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106")
        CharData = Array("SP", "!", Chr(34), "#", "$", "%", "&", "'", "(", ")", "*", "+", ",", "-", ".", "/", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ":", ";", "<", "=", ">", "?", "@", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "[", "\", "]", "^", "_", "`", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "I", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "{", "|", "}", "~", "DEL", "FNC 3", "FNC 2", "SHIFT", "CODE C", "FNC 4", "CODE A", "FNC 1", "Start A", "Start B", "Start C", "Stop")
        CharBarData = Array("212222", "222122", "222221", "121223", "121322", "131222", "122213", "122312", "132212", "221213", "221312", "231212", "112232", "122132", "122231", "113222", "123122", "123221", "223211", "221132", "221231", "213212", "223112", "312131", "311222", "321122", "321221", "312212", "322112", "322211", "212123", "212321", "232121", "111323", "131123", "131321", "112313", "132113", "132311", "211313", "231113", "231311", "112133", "112331", "132131", "113123", "113321", "133121", "313121", "211331", "231131", "213113", "213311", "213131", "311123", "311321", "331121", "312113", "312311", "332111", "314111", "221411", "431111", "111224", "111422", "121124", "121421", "141122", "141221", "112214", "112412", "122114", "122411", "142112", "142211", "241211", "221114", "413111", "241112", "134111", "111242", "121142", "121241", "114212", "124112", "124211", "411212", "421112", "421211", "212141", _
                            "214121", "412121", "111143", "111341", "131141", "114113", "114311", "411113", "411311", "113141", "114131", "311141", "411131", "211412", "211214", "211232", "2331112")
    
        barcodestr = "211214" 'Add the Startcode for Start B (characterset B) to the barcode string
        tsum = 104                      'And this is the value for that startcode which will be added with the other character values to find the checksum character
        boxx = Ctrl.Left: boxy = Ctrl.Top: boxw = Ctrl.Width: boxh = Ctrl.Height    'Get control size and location properties.
    
        Barcode = Ctrl                                                          'Set handle on control.
    
        Nratio = Array("0", "15", "30", "45", "60")           'Set up the array for the different bar width ratios
        Parts = ((11 * (Len(Barcode))) + 35) * Nratio(1)  'This is the formula for the width of the barcode
        Pix = (boxw / Parts)                                                  'Here I find out exactly how many Pixels a bar will be
        Nbar = Array((Nratio(0) * Pix), (Nratio(1) * Pix), (Nratio(2) * Pix), (Nratio(3) * Pix), (Nratio(4) * Pix)) 'Set up the array to handle the pixels for each type of bar
    
        'Loop through all bardata to count the sum for all characters and add barcode charcter strings the to the barcode string
        For lop = 1 To Len(Barcode)
            Barchar = Mid(Barcode, lop, 1)
            If Barchar = " " Then Barchar = "SP"
            For s = 0 To UBound(CharData)
                If Barchar = CharData(s) Then
                    barcodestr = barcodestr & CharBarData(s) 'This is where I added the character strings to each other to make one long string of 1's, 2's, 3's, & 4's
                    tsum = tsum + (CLng(CharNumber(s)) * lop) 'Here every barcode character's number value is multiplied by its position in the line and added to tsum
                    'The actual formula for find the the  Checksum  is "(104 + (1 * CharcterNumber) + (2 * CharcterNumber) + ...)/103" You would Use the Remainder as
                    'The Checksum Character. In the case of "BarCode 1" the formula would look
                    'like "(104+(1*34)+(2*65)+(3*82)+(4*35)+(5*79)+(6*68)+(7*69)+(8*0)+(9*17))/103=20 with Remainder of 33" Therefore the checksum would equal 33
                    Exit For
                End If
            Next s
        Next lop
    
        checksum = tsum - (Int(tsum / 103) * 103)                                             'Here I use the the totat sum (tsum) to find the checksum
        barcodestr = barcodestr & CharBarData(checksum) & "2331112" 'Here I add the checksum then the stop character into the barcode string
    
        'lets do some initialization
        Barcolor = Black
        Nextbar = boxx + 11     'I added the 20 for the whitespace (or quiet space) at the beginning of the barcode
    
        'Draw the Barcode
        For J = 1 To Len(barcodestr)
            Barchar = Mid(barcodestr, J, 1)   'Reuse variable barchar to store the character to be drawn
            barwidth = CInt(Barchar)              'Change the barcode charcter into an integer so I can use in the array part of the next line
            rpt.Line (Nextbar, boxy)-Step(Nbar(barwidth), boxh), Barcolor, BF  'Draw the line
            Nextbar = Nextbar + Nbar(barwidth)                                                      'Calculate the next starting point
            If Barcolor = White Then Barcolor = Black Else Barcolor = White      'Swap line colors
        Next J
    
    Exit_Barcode_128:
        Exit Function
    
    ErrorTrap_Barcode_128:
        MsgBox Error$
    
        Resume Exit_Barcode_128
    End Function
    Code 39 Barcodes
    Usage:
    • Step 1 - Follow Steps 1-3 for creating Code 128 barcodes.
    • Step 2 - In the Report Detail's On Print Event, add Result = Barcode_39(Me!MyTextBox, Me) (instead of Barcode_128()). You may need to Define the variable Result as a Boolean first.
    • Step 3 - Print out the Report (or view it via "Print Preview") to see your barcode!


    Code:
    '
    ' mod_BarCode_Generator_Code39
    '
    ' Barcode Generator for Code 3 of 9, Code 39, and Mil-spec Logmars.
    '
    ' version 2.0 (updated for MsAccess 97)
    '
    ' (c) 1993-1999 James Isle Mercanti, Cocoa Beach, FL 32931  USA
    ' Permission granted for public use and royalty-free distribution.
    ' No mention of source or credits is required. All rights reserved.
    '
    ' TO USE THIS CODE:
    '
    '   1 - Create Report with a TextBox control. (example named Barcode)
    '       Make sure the Visible property is set to "No".
    '   2 - Set On-Print property of section to [Event Procedure]
    '       by clicking on the [...] and selecting "Code Builder"
    '   3 - Confirm that the following code matches yours...
    '
    '      Sub Detail1_Print (Cancel As Integer, PrintCount As Integer)
    '
    '         Result = Barcode_39(Barcode, Me)
    '
    '      End Sub
    '
    '   4 - NOTE: The name of the section is "Detail1" for example only!
    '       Your section might show a different name. Ditto for "Barcode".
    '
    '   5 - NOTE: To use on sub-forms, the Report name should be hard-coded
    '       into the function. i.e. Rpt = Reports!MainForm!SubForm.Report.
    '       The easy method is to just avoid using sub-forms and sub-reports.
    '
    
    Function Barcode_39(Ctrl As Control, rpt As Report)
        On Error GoTo ErrorTrap_BarCode39
    
        Dim Nbar As Single, Wbar As Single, Qbar As Single, Nextbar As Single
        Dim CountX As Single, CountY As Single, CountR As Single
        Dim Parts As Single, Pix As Single, Color As Long, BarCodePlus As Variant
        Dim Stripes As String, BarType As String, Barcode As String
        Dim Mx As Single, my As Single, Sx As Single, Sy As Single
        Const White = 16777215: Const Black = 0
        Const Nratio = 20, Wratio = 55, Qratio = 35
    
        'Get control size and location properties.
        Sx = Ctrl.Left: Sy = Ctrl.TOP: Mx = Ctrl.Width: my = Ctrl.Height
    
        'Set handle on control.
        Barcode = Ctrl
    
        'Calculate actual and relative pixels values.
        Parts = (Len(Barcode) + 2) * ((6 * Nratio) + (3 * Wratio) + (1 * Qratio))
        Pix = (Mx / Parts):
        Nbar = (20 * Pix): Wbar = (55 * Pix): Qbar = (35 * Pix)
    
        'Initialize bar index and color.
        Nextbar = Sx
        Color = White
    
        'Pad each end of string with start/stop characters.
        BarCodePlus = "*" & UCase(Barcode) & "*"
    
        'Walk through each character of the barcode contents.
        For CountX = 1 To Len(BarCodePlus)
            'Get Barcode 1/0 string for indexed character.
            Stripes = MD_BC39(Mid$(BarCodePlus, CountX, 1))
            For CountY = 1 To 9
                'For each 1/0, draw a wide/narrow bar.
                BarType = Mid$(Stripes, CountY, 1)
    
                'Toggle the color (black/white).
                If Color = White Then Color = Black Else Color = White
                Select Case BarType
                    Case "1"
                        'Draw a wide bar.
                        rpt.Line (Nextbar, Sy)-Step(Wbar, my), Color, BF
                        Nextbar = Nextbar + Wbar
                    Case "0"
                        'Draw a narrow bar.
                        rpt.Line (Nextbar, Sy)-Step(Nbar, my), Color, BF
                        Nextbar = Nextbar + Nbar
                End Select
            Next CountY
    
            'Toggle the color (black/white).
            If Color = White Then Color = Black Else Color = White
    
            'Draw intermediate "quiet" bar.
            rpt.Line (Nextbar, Sy)-Step(Qbar, my), Color, BF
            Nextbar = Nextbar + Qbar
        Next CountX
    
    Exit_BarCode39:
        Exit Function
    
    ErrorTrap_BarCode39:
        Resume Exit_BarCode39
    End Function
    
    Function MD_BC39(CharCode As String) As String
        On Error GoTo ErrorTrap_BC39
    
        ReDim BC39(90)
    
        BC39(32) = "011000100" ' space
        BC39(36) = "010101000" ' $
        BC39(37) = "000101010" ' %
        BC39(42) = "010010100" ' * Start/Stop
        BC39(43) = "010001010" ' +
        BC39(45) = "010000101" ' |
        BC39(46) = "110000100" ' .
        BC39(47) = "010100010" ' /
        BC39(48) = "000110100" ' 0
        BC39(49) = "100100001" ' 1
        BC39(50) = "001100001" ' 2
        BC39(51) = "101100000" ' 3
        BC39(52) = "000110001" ' 4
        BC39(53) = "100110000" ' 5
        BC39(54) = "001110000" ' 6
        BC39(55) = "000100101" ' 7
        BC39(56) = "100100100" ' 8
        BC39(57) = "001100100" ' 9
        BC39(65) = "100001001" ' A
        BC39(66) = "001001001" ' B
        BC39(67) = "101001000" ' C
        BC39(68) = "000011001" ' D
        BC39(69) = "100011000" ' E
        BC39(70) = "001011000" ' F
        BC39(71) = "000001101" ' G
        BC39(72) = "100001100" ' H
        BC39(73) = "001001100" ' I
        BC39(74) = "000011100" ' J
        BC39(75) = "100000011" ' K
        BC39(76) = "001000011" ' L
        BC39(77) = "101000010" ' M
        BC39(78) = "000010011" ' N
        BC39(79) = "100010010" ' O
        BC39(80) = "001010010" ' P
        BC39(81) = "000000111" ' Q
        BC39(82) = "100000110" ' R
        BC39(83) = "001000110" ' S
        BC39(84) = "000010110" ' T
        BC39(85) = "110000001" ' U
        BC39(86) = "011000001" ' V
        BC39(87) = "111000000" ' W
        BC39(88) = "010010001" ' X
        BC39(89) = "110010000" ' Y
        BC39(90) = "011010000" ' Z
    
        MD_BC39 = BC39(Asc(CharCode))
    
    Exit_BC39:
        Exit Function
    
    ErrorTrap_BC39:
        MD_BC39 = ""
    
        Resume Exit_BC39
    End Function
    IMPORTANT NOTE: I did not create this code, it was created by the gentleman Rodney Marr and made available freely to all. I'm merely sharing is free code with you.

  12. #12
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Quote Originally Posted by MichaelJ View Post
    Great code. Would it be possible to rotate the code by 90 degrees I have the on the same report 1 code horizontal and an other one vertical
    While I'm certain it's possible, since I'm not the author of the code and have never put much effort into truly understanding how it works, I couldn't tell you specifically how to accomplish this.

    That said, the barcodes are made by using the Report.Line() method to make a series of alternating black and white vertical lines, drawn left-to-right. It should be possible to change how those lines are drawn to make them horizontal lines drawn top-to-bottom.

    Unfortunately since my recent job change I don't have much call to work with Access anymore and won't be able to help you make the necessary code changes. I work almost exclusively on Debian systems now which makes keeping up on Access development/programming somewhat difficult.

  13. #13
    MichaelJ is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2015
    Posts
    2
    Thanks so far. As I'm not a specialist in vba I haven't enough knowledge to change.
    I understand also that the Report.Line will drive the Barcode. But I didn't have an idea how to change. I habe done some trials, but the Code always stay horizontal and only size and position has been changed

  14. #14
    k9aland is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Finland
    Posts
    1
    My first post in this forum, so nice to see you all!

    Does anyone know how to convert this code that are presented here to "code 128-c"? I have this code that are presented here working in my access database!

    As I work in law enforcement, we are using an very protected enviorment where solution where to purchase code 128c font are not available, but an vba code that prints in report is working good!

    My database is to handle and have an followup logg on each evidence bag how that are handled and my interest in code 128c is due that that code are used on each evidence bag so I like to have same barcode on each report that are printed.

    Magnus

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Code to Print MS Access Reports to PDFCreator
    By NeilIrving in forum Programming
    Replies: 3
    Last Post: 11-20-2012, 06:08 AM
  2. Adding SQL Code to VBA
    By REAbernathy in forum Programming
    Replies: 20
    Last Post: 09-06-2012, 01:29 PM
  3. Tracking values in Access reports in Code
    By dahcjohnson in forum Programming
    Replies: 3
    Last Post: 05-11-2012, 03:43 PM
  4. Printing barcodes through Access
    By JackieEVSC in forum Reports
    Replies: 1
    Last Post: 11-16-2011, 10:21 AM
  5. Adding Running Balance from Form to Reports
    By KIDGEO3 in forum Reports
    Replies: 1
    Last Post: 01-18-2006, 07:52 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums