Using Barcodes from the Dynamics GP Report Writer


David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

A few months ago, I worked on a support case where we needed to add a barcode to a Sales Order Processing transaction print out.  We were able to achieve what we needed using Visual Basic for Applications (VBA) to handle some of the scripting for us.  I have been meaning to write a blog post on how to make a customization like this for a really long time and have finally got around to it.

This post is a good follow up to the Dynamics Report Writer is the Best Report Writer in the World post by demonstrating another cool customization using Report Writer with VBA. I will even use the same style to format the post.

Note: There are a number of different types of barcodes used, this example will use a Code 128 barcode subtype C (aka 128C), however, it does include the code needed for 128A and 128B as well.

 

The Situation

The customer wanted to use Code 128 barcodes on their SOP Packing Slips for the Item Number so they could be scanned at the warehouse before sending the order out.  The customer had already purchased barcode True Type fonts from a vendor (in our case BarCodeWiz.com).

 

The Problem

Using a barcode font is not just a matter of printing the Item Number using a different font.  Barcodes have additional characters added for Quiet Zones and Check Digits (see Wiki post). There is an algorithm required to convert your string into the barcode characters which can then be displayed using the barcode font.

Luckily, the barcode font vendor had provided some example BASIC code designed to be used with Microsoft Office Excel’s Visual Basic for Applications (VBA).  As the VBA in Office is almost identical to the VBA in Microsoft Dynamics GP, we were able use their code with almost no changes.

 

The Solution

So, we added a couple of blank string calculated fields to return our barcode characters to.  One we left as the standard font (for testing purposes) and the other was changed to use one of the barcode fonts installed previously.  Next we used the Tools menu to add the report to VBA, then selected the Item Number and the two newly added calculated fields and added them to VBA.

The screenshot below shows the calculated fields added onto the report layout.


Report Layout showing added calculated fields.

 

The Code

The code is quite simple.  We just need to call one of the provided conversion functions and return our results to the added calculated fields.

Code from SOPBlankPackingSlipForm Module

Option Explicit
 
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)
  Dim BarCode As String
  Select Case Level
    Case 5:
      BarCode = BCW_Code128A(sItemNumber)
      VBABarcode = BarCode
      VBABarcodeText = BarCode
    Case Else
  End Select
End Sub
 
' Copyright © Microsoft Corporation. All Rights Reserved.
' This code released under the terms of the
' Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
 
' Code below provided by BarCodeWiz.com and used with permission.
 
Private Function C128ToBars(ascVal As Integer) As String
  ' This function creates the check character bar-by-bar
  ' in order to hide the human readable text
  Const c As String = "212222222122222221121223121322" & _
  "131222122213122312132212221213221312" & _
  "231212112232122132122231113222123122" & _
  "123221223211221132221231213212223112" & _
  "312131311222321122321221312212322112" & _
  "322211212123212321232121111323131123" & _
  "131321112313132113132311211313231113" & _
  "231311112133112331132131113123113321" & _
  "133121313121211331231131213113213311" & _
  "213131311123311321331121312113312311" & _
  "332111314111221411431111111224111422" & _
  "121124121421141122141221112214112412" & _
  "122114122411142112142211241211221114" & _
  "413111241112134111111242121142121241" & _
  "114212124112124211411212421112421211" & _
  "212141214121412121111143111341131141" & _
  "114113114311411113411311113141114131" & _
  "3111414111312114122112142112322331112"
 
  Dim posString, ckDigitString As String
  posString = Mid(c, 6 * ascVal + 1, 6)
 
  Dim i As Integer
  For i = 1 To 6
    If i Mod 2 = 0 Then 'space
      Select Case Mid(posString, i, 1)
        Case "1": ckDigitString = ckDigitString & Chr(184)
        Case "2": ckDigitString = ckDigitString & Chr(185)
        Case "3": ckDigitString = ckDigitString & Chr(186)
        Case "4": ckDigitString = ckDigitString & Chr(187)
      End Select
    Else 'bar
      Select Case Mid(posString, i, 1)
        Case "1": ckDigitString = ckDigitString & Chr(180)
        Case "2": ckDigitString = ckDigitString & Chr(181)
        Case "3": ckDigitString = ckDigitString & Chr(182)
        Case "4": ckDigitString = ckDigitString & Chr(183)
      End Select
    End If
  Next
 
  C128ToBars = ckDigitString
 
End Function
 
Public Function BCW_Code128A(ByVal inputString As String) As String
 
  If Len(inputString) = 0 Then
    Exit Function
  End If
 
  Dim currentPos, chkDigitPos, chkDigitTotal As Integer
  currentPos = 1
  chkDigitTotal = 103 'Start Code 128 A
 
  While currentPos <= Len(inputString) Dim asciiVal As Integer asciiVal = Asc(Mid(inputString, currentPos, 1)) Dim bcVal As Integer If asciiVal >= 32 And asciiVal <= 95 Then bcVal = asciiVal - 32 ElseIf asciiVal >= 0 And asciiVal <= 31 Then bcVal = asciiVal + 64 Else BCW_Code128A = "" Exit Function End If chkDigitTotal = chkDigitTotal + bcVal * currentPos currentPos = currentPos + 1 Wend chkDigitTotal = chkDigitTotal Mod 103 Dim bcChkDigitAscii As Integer If chkDigitTotal = 0 Then bcChkDigitAscii = 232 ElseIf chkDigitTotal >= 1 And chkDigitTotal <= 94 Then bcChkDigitAscii = chkDigitTotal + 32 ElseIf chkDigitTotal >= 95 And chkDigitTotal <= 99 Then bcChkDigitAscii = chkDigitTotal + 132 ElseIf chkDigitTotal >= 100 And chkDigitTotal <= 103 Then
    bcChkDigitAscii = chkDigitTotal + 100
  End If
 
  inputString = Replace(inputString, " ", Chr(232))
  BCW_Code128A = Chr(203) & inputString & _
  C128ToBars(chkDigitTotal) & Chr(206)
  'Chr (bcChkDigitAscii) & Chr(206)
 
End Function
 
Public Function BCW_Code128B(ByVal inputString As String) As String
 
  If Len(inputString) = 0 Then
    Exit Function
  End If
 
  Dim currentPos, chkDigitPos, chkDigitTotal As Integer
  currentPos = 1
  chkDigitTotal = 104 'Start Code 128 B
 
  While currentPos <= Len(inputString) Dim asciiVal As Integer asciiVal = Asc(Mid(inputString, currentPos, 1)) Dim bcVal As Integer If asciiVal >= 32 And asciiVal <= 127 Then bcVal = asciiVal - 32 Else BCW_Code128B = "" Exit Function End If chkDigitTotal = chkDigitTotal + bcVal * currentPos currentPos = currentPos + 1 Wend chkDigitTotal = chkDigitTotal Mod 103 Dim bcChkDigitAscii As Integer If chkDigitTotal = 0 Then bcChkDigitAscii = 232 ElseIf chkDigitTotal >= 1 And chkDigitTotal <= 94 Then bcChkDigitAscii = chkDigitTotal + 32 ElseIf chkDigitTotal >= 95 And chkDigitTotal <= 99 Then bcChkDigitAscii = chkDigitTotal + 132 ElseIf chkDigitTotal >= 100 And chkDigitTotal <= 103 Then
    bcChkDigitAscii = chkDigitTotal + 100
  End If
 
  inputString = Replace(inputString, " ", Chr(232)) 'replace spaces
 
  BCW_Code128B = Chr(204) & inputString & _
  C128ToBars(chkDigitTotal) & Chr(206)
  'Chr (bcChkDigitAscii) & Chr(206)
End Function
 
Public Function BCW_Code128C(ByVal inputString As String) As String
  On Error GoTo Err
 
  If Len(inputString) = 0 Then
    Exit Function
  End If
 
  ' If the number of digits is not even, then
  ' add a zero to the beginning of the string
  If Len(inputString) Mod 2 <> 0 Then
    inputString = "0" & inputString
  End If
 
  Dim currentPos, chkDigitPos, chkDigitTotal As Integer
  currentPos = 1
  chkDigitPos = 1
  chkDigitTotal = 105 'Start Code 128 C
 
  Dim encodeToAscii As Integer
 
  While currentPos < Len(inputString) Dim twoChars As String twoChars = Mid(inputString, currentPos, 2) Dim curVal As Integer curVal = CInt(twoChars) chkDigitTotal = chkDigitTotal + curVal * chkDigitPos If curVal = 0 Then encodeToAscii = 232 ElseIf curVal >= 1 And curVal <= 94 Then encodeToAscii = curVal + 32 ElseIf curVal >= 95 And curVal <= 99 Then encodeToAscii = curVal + 132 ElseIf curVal >= 100 And curVal <= 103 Then encodeToAscii = curVal + 100 End If Dim outputString As String outputString = outputString & Chr(encodeToAscii) chkDigitPos = chkDigitPos + 1 currentPos = currentPos + 2 Wend chkDigitTotal = chkDigitTotal Mod 103 If chkDigitTotal = 0 Then encodeToAscii = 232 ElseIf chkDigitTotal >= 1 And chkDigitTotal <= 94 Then encodeToAscii = chkDigitTotal + 32 ElseIf chkDigitTotal >= 95 And chkDigitTotal <= 99 Then encodeToAscii = chkDigitTotal + 132 ElseIf chkDigitTotal >= 100 And chkDigitTotal <= 103 Then
    encodeToAscii = chkDigitTotal + 100
  End If
 
  outputString = Chr(205) & outputString & _
  C128ToBars(chkDigitTotal) & Chr(206)
  'Chr (encodeToAscii) & Chr(206)
 
  BCW_Code128C = outputString
  Exit Function
 
  Err:
    BCW_Code128C = ""
End Function

 

The Result

Below is a screenshot of the resulting report. Note the additional characters added to the barcode text:


Example screen output showing populated barcode fields

 

More Information

The links below provide mode information on the Barcode code 128 and its subtypes as well as a link to the barcode font supplier used by this customer.  The barcode functions included in the VBA code for this example were provided by BarCodeWiz.com and are used in this example with their permission.

 

I hope you find this example of how barcodes can be used on Dynamics GP reports useful.

David

SOPBlankPackingSlipForm_Barcode.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

13 thoughts on “Using Barcodes from the Dynamics GP Report Writer

  1. David,
    Are you able to scan these barcode ? I have used barcode fonts on Report Writer to show Sales Order No but not able to scan until I added * on starting and Ending of barcode.
    Thanks
    Sandip Jadhav

    Like

  2. Hi Sandip
    I don't have a barcode scanner to test with, but adding any additional characters is simple from the VBA code.  
    Please note that we are already calling functions to convert the Item Number into the text needed for Code 128 barcodes which is adding additional characters before and after the Item Number.
    David

    Like

  3. Hey Sandip,
    Having done a lot of work with 1 dimensional and 2 dimensional barcodes, I can say from experience that a lot of users encounter the problem with the asterisk (*) at the beginning and end of the barcode.
    One of the most common problems is that applications like Microsoft Word will change the text from simply a bunch of characters with an asterisk on both sides, into a bold face set of characters. The program thinks you are trying to place emphasis on the text. With the code David is showing, this is not an issue as he is programmatically specifying the format. It’s a good example and should shed some light on just how easy it can be for a Dynamics GP system to start taking advantage of barcodes.
    -Mike

    Like

  4. Hi Kim
    If your barcode reader works as a keyboard wedge (ie, it inserts characters as though they have been typed), then a QR code reader should work.
    David

    Like

  5. Great Article David,
    Have you run across VBA code for 2D barcode that I could use just like your 1D barcode? Looking to put on several Dynamics GP 2016 SOP reports that have already been modified. I have not been able to find any code that will allow for the 2D within GP. Many thanks for taking time to assist and provide articles like the above.

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.