Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘macros

Sage 300 ERP Macros

with 5 comments

Introduction

We’ve had macros in Sage 300 ERP since version 1.0A. In the early days we used CABLE (the CA Basic Language Engine) as our macro language. This was a macro language version of CA-Realizer which we used for UI development back then. It was fun creating the development environment with debugging capabilities and such. Amazingly CABLE macros are still supported in Sage 300 ERP and if you run a CABLE macro (*.mac) you will get this environment:

cable1

With version 4.0A we introduced Visual Basic for Applications (VBA) as our macro language. We did this hand in hand with introducing our first COM interface a4wcom. This interface is still around, but generally we use the newer Sage 300 ERP COM interface a4wcomex.

vba

Why Macros?

We provide macros as a method of customizing the product which doesn’t require the SDK. While it does require programming, the Basic language used is simpler than say using C, Java, C# or C++. So hopefully more people can provide meaningful coded customizations that are largely upgrade safe. Generally VBA is a very powerful development environment and we’ve seen some amazing pieces of work implemented as macros. Plus VBA is the macro language used by Microsoft Office, so there are many technical resources, books, courses and such to help you with your development. Further you can use macro recording to help you with some starting code.

For many of our customers, the ERP package handles their financial accounting needs, due to regulations on using Generally Accepted Accounting Practices (GAAP) these are pretty standard. However especially in the operations modules a lot of businesses want custom calculations and procedures to more exactly match their particular business. Whether this is enforcing additional government regulations, implementing custom pricing models or whatever. These are very varied and we need to provide a powerful framework so that these can be accommodated whatever they may be.

At the same time we need these customizations to easily migrate from version to version so that customizations don’t then lock a customer into a particular version and prevent them from ever upgrading.

A powerful macro language with deep hooks into the product is an ideal way to accomplish these goals.

Business Logic

Both CABLE and VBA macros are fundamentally based on our Sage 300 ERP Business Logic Objects or Views. The API for all our Business Logic Objects is the same, so once you learn one, to some degree you learn them all. For a bit more info on our Business Logic, have a look at this blog posting. For an example of creating Orders have a look at this posting.

This can be a great mechanism for say importing data from an external system. VBA can access the API of the external system, extract the data and then feed it into our Business Logic to do things like import G/L Journal Entries or O/E Orders.

Using the VBA Forms capability you can create your own screens that interact with our business logic and perform your custom tasks. The VBA forms library/system is a very powerful but easy to use system for creating potentially quite sophisticated UIs.

The API to our business logic that the macros use is the same as the API used by our UIs, so you know that anything you can do in a UI, you can also do in a macro. It also guarantees that this layer is heavily tested and supported.

Generally the main interfaces to our business logic stays the same. As we add features we add fields, but as long as these aren’t required fields and you don’t need to use these features then your macro can remain the same from version to version.

UIs

With version 5.0A, we gave VBA the ability to customize our product’s User Interface Forms. We accomplished this by re-writing all our UIs from CA-Realizer to VB. The new VB UIs were created as ActiveX controls themselves and hence could be hosted on standard VBA forms. Then each UI contained a uniform set of methods, properties and events to all VBA macros to interact and customize them.

There is a little work to upgrade when you go from version to version. With each version the screen control gets a new class id, so you need to remove the old versions control reference and add the new one. Otherwise the code should remain compatible and continue to work. I documented how to do this in this blog posting.

Automating Processes

Another great use of macros is to automate recurring tasks. Besides business logic we give you full access to printing reports, including setting all the report parameters. These can be either Crystal Reports of Financial Reports. I blogged on printing through macros here and a bit more information on customizing reports here. Plus from the Business Logic you have access to all processing functions like Posting Batches or running Day End.

So you can write a macro to print out all your month end reports. You can write a macro to go through and process un-posted batches. Or whatever other recurring process you want automated.

Summary

Customization through macros is a powerful technology to personalize your ERP and to allow you to achieve greater efficiency. VBA is an industry standard macros language and gives you great power to customize Sage 300 ERP.

Reporting Via Macros

with 71 comments

Introduction

With our Sage 300 ERP 2012 release we updated our Crystal Reports runtime to the newest Crystal 2011 runtime (SP3 actually). The intent is to move to a fully supported version of Crystal Reports, so as they adapt to things like Windows 8 and Windows 2012 Server, we know we are fully supported and can get updates for any problems that show up. Plus it means that people customizing reports can take advantage of any of the new features there.

For reports you can print to preview, print to file or print directly to a printer. Then we have various options for printing from various web contexts like Quotes to Orders. You can drive reports from our regular forms, or you can write VBA macros that automate the reporting process.

This blog post is really for people that are controlling printing reports programmatically and are more affected by the changes in the Crystal runtime and more specifically changes in the Crystal Reports API.

Headache for Customizers

Our intent was that people performing customizations will use our API to drive Crystal Reports. Then your programs are upgrade safe since we maintain compatibility of our COM API. However it turns out that quite a few people have automated the report process by writing to the Crystal COM API directly.

This then leads to a problem because Crystal dropped support for their COM API. Not only did they drop support for it, but they removed it completely from the product. Hence anyone that is writing directly to the Crystal COM API will be broken by the Sage 300 ERP 2012 release. At least for new installs. If you had an older version and don’t un-install it, then you can still use the older version of the Crystal runtime (since it will be still there), but that isn’t a good long term solution as people upgrade computers and go to newer operating systems like Windows 8.

Crystal Reports now only supports a .Net Interface and a Java interface. For this version we had to change our internal interface to Crystal from COM to .Net. (The newer Web portal parts use the Java interface and so were ok).

Printing without User Intervention

It appears that one of the common reasons to go to the Crystal API directly is to print to file without any manual intervention. Often if you choose File as a print destination then we prompt you for the format and then prompt you for the file name to save to. People want to set these programmatically. Our API does have the ability to do this in a couple of situations.

Below is a macro I recorded to print O/E Quotes. I deleted any extra code, like the error handler to make it a bit more compact. Then I edited the destination and format to change the print destination to PD_FILE and the format to PF_PDF.

Sub MainSub()
Dim temp As Boolean
 Dim rpt As AccpacCOMAPI.AccpacReport
 Set rpt = ReportSelect("OEQUOT01[OEQUOT01.RPT]", "      ", "      ")
 Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
 Set rptPrintSetup = GetPrintSetup("      ", "      ")
 rptPrintSetup.DeviceName = "HP LaserJet P3010 Series UPD PS"
 rptPrintSetup.OutputName = "WSD-ad0e8bc6-396c-4e50-84c7-fab17beaf18a.006a"
 rptPrintSetup.Orientation = 1
 rptPrintSetup.PaperSize = 1
 rptPrintSetup.PaperSource = 15
 rpt.PrinterSetup rptPrintSetup
 rpt.SetParam "PRINTED", "0"              ' Report parameter: 4
 rpt.SetParam "QTYDEC", "0"               ' Report parameter: 5
 rpt.SetParam "SORTFROM", " "             ' Report parameter: 2
 rpt.SetParam "SORTTO", "ZZZZZZZZZZZZZZZZZZZZZZ"   ' Report parameter: 3
 rpt.SetParam "SWDELMETHOD", "3"          ' Report parameter: 6
 rpt.SetParam "PRINTKIT", "0"             ' Report parameter: 7
 rpt.SetParam "PRINTBOM", "0"             ' Report parameter: 8
 rpt.SetParam "@SELECTION_CRITERIA", "(({OEORDH.ORDNUMBER} >= """") AND 
     ({OEORDH.ORDNUMBER} <= ""ZZZZZZZZZZZZZZZZZZZZZZ"")) AND (({OEORDH.COMPLETE} = 1) OR 
     ({OEORDH.COMPLETE} = 2)) AND ({OEORDH.TYPE} = 4) AND (({OEORDH.PRINTSTAT} = 1) OR 
     ({OEORDH.PRINTSTAT} = 0) OR ({OEORDH.PRINTSTAT} = -1))"   ' Report parameter: 0
 rpt.NumOfCopies = 1
 rpt.Destination = PD_FILE
 rpt.Format = PF_PDF
 rpt.PrintDir = "c:\temp\quote.pdf"
 rpt.PrintReport
End Sub

 

Basically this technique will silently export to PDF files. The Format member also accepts PF_RTF which will export silently in RTF format. The file they export to is specified in the PrintDir property. If this is a folder, the filename will be the same as the report name, if it’s a filename, it will be that (make sure you get the extension right).

You can also export silently to HTML format by setting the Destination to PD_HTML. For HTML if there is one file then the PrintDir specifies the filename, but sometimes HTML required multiple files, in which case it will use the PrintDir as a folder name.

These are a few ways you can print reports to a file silently without user intervention.

Secret Parameters

In the recorded macro above, you might notice the strange parameter “@SELECTION_CRITERIA”. If you look in the report, there is no such report parameter. Basically our API lets you set report parameters and then print the report. However there are a few other things you might want to do with the Crystal Reports API. Below is a list of these special parameters that might help you get a grip on some more aspects of the Crystal Reports API:

“@SELECTION_CRITERIA”: PESetSelectionFormula (job, asParamValue). This parameter is changed into the API to set the Selection Criteria in the report.

“@SELECTION_CRITERIA_xxxx”:  where  xxxx is the name assigned to the subreport when it was first created. This call will be translated into

job = PEOpenSubreport( parentJob, xxxx)
PESetSelectionFormula (job, asParamValue)

To set the selection criteria in the designated subreport.

“@SELECTION_ADDCRITERIA”: will add the parameter specified to the selection criteria that exists inside the report.

“@TABLENAME”: The parameter value must be in the form:

“table” “name”

Each instance of “table” will be switched to “name” before each PESetNthTableLocation call. This is done for both the main report and all subreports. You must put the table and name in quotes or the parameter will be rejected. Table names are not treated as case sensitive.

“EMAILSENDTO”:   The call will be set the email address when you are using PD_EMAIL.  This will suppress the popping up of the address book dialog.

“EMAILSUBJECT”: The call will be set the email subject when you are using PD_EMAIL.

“EMAILTEXT”: This parameter will be set the email body when you are using PD_EMAIL.

“EMAILPROFILE”:  This parameter will be set the email profile when you are using PD_EMAIL.

“EMAILPROFILEPWD”: This parameter will be set the email password when you are using PD_EMAIL. This password will be used to sign-in to MAPI.

The following are some parameters that if you create them in your report, then the system will automatically set them without you having to do any programming.

“CMPNAME”: The company name.

“ACCPACUSERID”: The Sage 300 User ID.

“ACCPACSESSIONDATE”: The session date from signon.

“ALIGNMENT”: The current alignment option.

“REGIONALFMT” : The current regional format.

Summary

We went through this Crystal API pain once before when Crystal dropped their DLL interface (crpe32.dll). At that point we had to change all API access over to COM. At that point we had similar issues due to various people using the DLL interface and now having to re-code for the COM interface. At that point we did add some functionality to our API, namely we added the TABLENAME special parameter, so that a number of people could start using our API.

Hopefully most people can switch from using the Crystal COM API directly to using the Sage 300 ERP API, however if something is missing, please comment on this blog, so we can consider expanding our API in the future. I’ve already gotten a couple of requests to add silently exporting to Excel format in addition to RTF and PDF. Keep in mind that like we have gone from DLL to COM to .Net, chances are in a few versions, the Crystal API will change again (perhaps to a REST web services API) and we will have to go through this again.

Written by smist08

September 15, 2012 at 7:50 pm

Entering Orders and Header/Detail Views

with 31 comments

Sage ERP Accpac Business Logic objects are called Views. With these Views you can use various interfaces to input data, retrieve data, perform calculations or perform business processes. The interface to every View is the same and this allows us to offer many different interfaces to the Accpac Business Logic (see:  https://smist08.wordpress.com/2010/09/18/a-short-history-of-the-accpac-view-interface/).

Overview of the O/E Views

The Views in this posting are to do with entering and reading O/E Orders. Each View sits on a corresponding database table. Below are some of the Views and tables that make up an order.

Table                     View                      Description
OEORDH              OE0520                 Order Headers
OEORDD              OE0500                 Order Details
OECOINO            OE0180                 Order Comments
OETERMO           OE0740                 Order Pmt Schedules
OEORDQ              OE0526                 Order from Quotes
OEORDHO           OE0522                 Order Optional Fields
OEORDDO           OE0501                 Order Detail Optional Fields
OEORDDD           OE0502                 Order Kitting Details
OEORDDDS         OE0504                 Order Kitting Serial Numbers
OEORDDDL         OE0506                 Order Kitting Lot Numbers
OEORDDB            OE0503                 Order BOM Details
OEORDDS            OE0508                 Order Detail Serial Numbers
OEORDDL            OE0507                 Order Detail Lot Numbers

These Views then work together to manage Orders. These Views form header/detail relationships with each other and Accpac has standard protocols for manipulating header/detail Views. These are documented in the macros section of the System Manager User Guide. You don’t need to use all the Views all the time. If you aren’t using serial numbers or lots then you don’t need to worry about those Views. Mostly you use the Order Header and Order Details Views. To make the Views work together you need to compose them. You can get the list of Views that compose together from SDK tools, the Application Object Model on our Web site or you can get the code generated for you by recording a macro.

These Views manage the data in the database. They ensure data integrity is maintained. They control security. But additionally they will do calculations. For instance you can ask the order header calculate taxes or do a ship all.

Header/Detail

As an example of header/detail we have the Order Header and then its detail lines. The Order header has information that there is one of for the order. The details then contain data that there are many of. The Order Header contains information like the customer id and the ship-to address. Then each detail line contains an I/C Item number and quantity. So for an order you can order as many items as you like.

The Order Header also stores information like various totals. So as each detail line is added, updated or deleted these header totals have to be updated to keep them correct. Details are always associated with their header by having their primary key start with the header’s primary key, then just adding a uniquifier or counter. This is show in the diagram below:

To use header detail views you need to follow what we call View protocols. Generally they go along the following lines:

  1. Open and compose all the Views
  2. Either create or read the correct header record
  3. Insert/update or delete which ever detail lines you are dealing with.
  4. Update or Insert the header record.

Nothing is actually done to the database until you insert or update the header. Until this is done all changes are maintained in memory. Then when you update/insert the header, all changes are written to the database in a single database transaction. This ensures the database’s integrity is maintained where the details always match the header. This protocol also handles any multi-user situations that could arise.

Although Order Entry could potentially involve 12 Views, the basic protocols and interactions all follow this simple model. The Views themselves will also report errors if you do things incorrectly.

Order Entry Macro

Below is the VBA source code of an Accpac macro that enters Orders into Order Entry. The code to open and compose the Views was obtained by macro recording. The code to enter 1 order was also from macro recording but then modified into functions to be made re-usable. This code uses the Accpac COM API, but if you use the .Net interface or Java JNI, then the code will be very similar with just syntax differences for the calling language. Entering orders via SData would be quite different since you just provide the data in an SData XML payload, and the SData server handles all the View opening and composing automatically. The SData server then knows the View header/detail protocol and takes the data from the XML and feeds it into the Views via the algorithm indicated.

The macro below enters some optional fields. There are two optional field views. One is a detail of the header, meaning that you can have any number of optional fields for each order. The second one is a detail of the order detail View, meaning you can have any number of optional fields for each order detail line. Each of these is handled using the header detail protocol, but where the detail optional fields has the order detail View has its header.

This macro shows entering orders, but exactly the same mechanisms, logic and protocols would be used to enter any header/detail document in Accpac whether in G/L, A/R or P/O. So learning to do this once has great benefit towards your second program.

‘ Macro to enter orders into O/E
‘ Global variables for database links and views that are needed in most routines.
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Dim OEORD1header As AccpacCOMAPI.AccpacView
Dim OEORD1headerFields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail1 As AccpacCOMAPI.AccpacView
Dim OEORD1detail1Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail2 As AccpacCOMAPI.AccpacView
Dim OEORD1detail2Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail3 As AccpacCOMAPI.AccpacView
Dim OEORD1detail3Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail4 As AccpacCOMAPI.AccpacView
Dim OEORD1detail4Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail5 As AccpacCOMAPI.AccpacView
Dim OEORD1detail5Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail6 As AccpacCOMAPI.AccpacView
Dim OEORD1detail6Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail7 As AccpacCOMAPI.AccpacView
Dim OEORD1detail7Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail8 As AccpacCOMAPI.AccpacView
Dim OEORD1detail8Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail9 As AccpacCOMAPI.AccpacView
Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail10 As AccpacCOMAPI.AccpacView
Dim OEORD1detail10Fields As AccpacCOMAPI.AccpacViewFields
 
Sub MainSub()
 
On Error GoTo ACCPACErrorHandler
 
    Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
    Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
   
    OpenAndComposeOrderViews
 
    ‘ In sample data we have set a price list of USA for customer 1200 location WAREHS so that prices are
    ‘ filled in automatically (A/R Shipto Locations).
 
    EnterOrder “1200”, “WAREHS”, Array(“A1-310/0”, “A1-400/0”, “A1-105/0”), Array(“5”, “4”, “6”), Array(“Red”, “White”, “Blue”)
 
    Cleanup
 
Exit Sub
ACCPACErrorHandler:
    MyErrorHandler
End Sub
 
‘ Open and componse the various views used in this macro. Code is straight from macro recording.
Sub OpenAndComposeOrderViews()
On Error GoTo OpenAndComposeOrderViewsErrorHandler
 
mDBLinkCmpRW.OpenView “OE0520”, OEORD1header
Set OEORD1headerFields = OEORD1header.Fields
mDBLinkCmpRW.OpenView “OE0500”, OEORD1detail1
Set OEORD1detail1Fields = OEORD1detail1.Fields
mDBLinkCmpRW.OpenView “OE0740”, OEORD1detail2
Set OEORD1detail2Fields = OEORD1detail2.Fields
mDBLinkCmpRW.OpenView “OE0180”, OEORD1detail3
Set OEORD1detail3Fields = OEORD1detail3.Fields
mDBLinkCmpRW.OpenView “OE0680”, OEORD1detail4
Set OEORD1detail4Fields = OEORD1detail4.Fields
mDBLinkCmpRW.OpenView “OE0526”, OEORD1detail5
Set OEORD1detail5Fields = OEORD1detail5.Fields
mDBLinkCmpRW.OpenView “OE0522”, OEORD1detail6
Set OEORD1detail6Fields = OEORD1detail6.Fields
mDBLinkCmpRW.OpenView “OE0501”, OEORD1detail7
Set OEORD1detail7Fields = OEORD1detail7.Fields
mDBLinkCmpRW.OpenView “OE0502”, OEORD1detail8
Set OEORD1detail8Fields = OEORD1detail8.Fields
mDBLinkCmpRW.OpenView “OE0504”, OEORD1detail9
Set OEORD1detail9Fields = OEORD1detail9.Fields
mDBLinkCmpRW.OpenView “OE0503”, OEORD1detail10
Set OEORD1detail10Fields = OEORD1detail10.Fields
 
OEORD1header.Compose Array(OEORD1detail1, OEORD1detail4, OEORD1detail3, OEORD1detail2, OEORD1detail5, OEORD1detail6)
OEORD1detail1.Compose Array(OEORD1header, OEORD1detail7, OEORD1detail10, OEORD1detail8)
OEORD1detail2.Compose Array(OEORD1header)
OEORD1detail3.Compose Array(OEORD1header, OEORD1detail1)
OEORD1detail4.Compose Array(OEORD1header, OEORD1detail1)
OEORD1detail5.Compose Array(OEORD1header)
OEORD1detail6.Compose Array(OEORD1header)
OEORD1detail7.Compose Array(OEORD1detail1)
OEORD1detail8.Compose Array(OEORD1detail1, OEORD1detail9)
OEORD1detail9.Compose Array(OEORD1detail8)
OEORD1detail10.Compose Array(OEORD1detail1)
 
Exit Sub
OpenAndComposeOrderViewsErrorHandler:  MyErrorHandler
   
End Sub
 
‘ Enter an order based on a number of parameters.
Sub EnterOrder(ByVal custno As String, ByVal shipto As String, ByVal items As Variant, ByVal quantities As Variant, ByVal colors As Variant)
    Dim lastLine As Long
On Error GoTo EnterOrderErrorHandler
   
    OEORD1header.Cancel
    OEORD1header.Init
 
    OEORD1headerFields(“CUSTOMER”).Value = custno                         ‘ Customer Number
    OEORD1headerFields(“SHIPTO”).Value = shipto                           ‘ Ship-To Location Code
   
    ‘ Insert any auto insert optional fields.
    OEORD1headerFields(“PROCESSCMD”).PutWithoutVerification (“1”)         ‘ Process Command
    OEORD1header.Process
           
        ‘ Set the CLERK optional field in the Order Header
        OEORD1detail6.RecordClear
        OEORD1detail6.RecordGenerate False
        OEORD1detail6Fields(“OPTFIELD”).Value = “CLERK”
        OEORD1detail6Fields(“VALIFTEXT”).Value = “Sammy ” + CStr(Date) + ” ” + CStr(Time)  ‘ Text Value
        OEORD1detail6.Insert
       
   
    For i = LBound(items) To UBound(items)
   
        lastLine = OEORD1detail1Fields(“LINENUM”).Value
        OEORD1detail1.RecordClear
        OEORD1detail1Fields(“LINENUM”).PutWithoutVerification (CStr(lastLine))          ‘ Line Number
        OEORD1detail1.RecordGenerate False
       
        ‘ Set the item code and quantity.
        ‘   Note that all the other fields are readonly until the ITEM is set.
        OEORD1detail1Fields(“ITEM”).Value = items(i)                                    ‘ Item
        OEORD1detail1Fields(“QTYORDERED”).Value = quantities(i)                         ‘ Quantity Ordered
       
        ‘ Insert any auto insert optional fields
        OEORD1detail1Fields(“PROCESSCMD”).PutWithoutVerification (“1”)                  ‘ Process Command
        OEORD1detail1.Process
       
            ‘ Add the COLOR optional field to the detail.
           ‘ OEORD1detail7.RecordClear
           ‘ OEORD1detail7.RecordGenerate False
            OEORD1detail7Fields(“OPTFIELD”).Value = “COLOR”
            OEORD1detail7.Read ‘ Optional Field
            OEORD1detail7Fields(“VALIFTEXT”).Value = colors(i)                        ‘ Text Value
            OEORD1detail7.Update
       
        OEORD1detail1.Insert
 
    Next i
 
    OEORD1headerFields(“GOCHKCRDT”).Value = “1”                           ‘ Perform Credit Limit Check
    OEORD1header.Process
    OEORD1header.Insert
Exit Sub
 
EnterOrderErrorHandler:
 MyErrorHandler
   
End Sub
 
‘ Cleanup routine destroys the COM objects in the reverse order that they were created.
Sub Cleanup()
On Error Resume Next
 
Set OEORD1detail10Fields = Nothing
Set OEORD1detail10 = Nothing
Set OEORD1detail9Fields = Nothing
Set OEORD1detail9 = Nothing
Set OEORD1detail8Fields = Nothing
Set OEORD1detail8 = Nothing
Set OEORD1detail7Fields = Nothing
Set OEORD1detail7 = Nothing
Set OEORD1detail6Fields = Nothing
Set OEORD1detail6 = Nothing
Set OEORD1detail5Fields = Nothing
Set OEORD1detail5 = Nothing
Set OEORD1detail4Fields = Nothing
Set OEORD1detail4 = Nothing
Set OEORD1detail3Fields = Nothing
Set OEORD1detail3 = Nothing
Set OEORD1detail2Fields = Nothing
Set OEORD1detail2 = Nothing
Set OEORD1detail1Fields = Nothing
Set OEORD1detail1 = Nothing
Set OEORD1headerFields = Nothing
Set OEORD1header = Nothing
Set mDBLinkSysRW = Nothing
Set mDBLinkCmpRW = Nothing
 
End Sub
 
‘ Simple generic error handler.
Sub MyErrorHandler()
  Dim lCount As Long
  Dim lIndex As Long
 
  If Errors Is Nothing Then
       MsgBox Err.Description
     Else
      lCount = Errors.Count
 
      If lCount = 0 Then
          MsgBox Err.Description
      Else
          For lIndex = 0 To lCount – 1
              MsgBox Errors.Item(lIndex)
          Next
          Errors.Clear
      End If
  End If
 
End Sub
 

Written by smist08

November 7, 2010 at 12:53 am

Posted in sage 300

Tagged with , , , , ,

Sage ERP Accpac Macro Tips and Tricks

with 19 comments

Sage ERP Accpac uses Visual Basic for Applications (VBA) (http://en.wikipedia.org/wiki/Visual_Basic_for_Applications) as its macro language. This is the same macro language used by Microsoft Office and many other Windows desktop applications. With VBA macros you can access the Accpac Business Logic layer and enter documents, perform processing like posting batches or print reports. Since every screen in Accpac is an ActiveX control, you can embed any screen in a VBA macro and then manipulate/customize it in a standard uniform manner. Writing VBA macros is a huge topic; there are many books available on the topic along with courses at Sage University on VBA specifically for Accpac. This blog posting just lists a few useful tips and tricks that people find helpful when programming Accpac macros. These assume a general knowledge of writing basic Accpac macros. These aren’t in any particular order and tend to jump all over the place.

Upgrading to a New Version of Accpac

If your macro doesn’t customize an Accpac User Interface Screen then often your macro will just run without any changes. The exception tends to be if you are using a set of Views where their required compositions change. When you run you might get an error saying a required View composition is missing. In this case you should re-macro record the Accpac screen that uses these Views and add the missing View compositions. This tends to especially effect macros that are entering Order Entry documents. The main view compositions added for 5.6A are all to do with Serialized Inventory and Lot Tracking which were fully integrated into the core accounting modules.

The rest of this section applies to macros that customize Accpac User Interface Screens. It would also apply to you receiving a new version of some third party ActiveX control. When you insert an ActiveX control into a VBA macro, VBA reads all the information from the control and caches it in an EXD file. Unfortunately VBA doesn’t check when a new version of the control is installed and keeps using the out of date information. Full VB does something similar with OCA files, but is smart enough to update these when a control is updated. Worse the EXD file is strictly based on the base file name of the control, not its class id in the registry and not on the directory where it’s installed.

When we install a new version of Accpac we install our ActiveX controls with the same control names, but with new class ids. This way macros and other users of these controls can still use either version. When we install Product Updates we overwrite the current control, but have compiled the control with “binary compatibility” so all its interfaces are still compatible. For product updates, it is usually worth deleting the EXD files as we have seen problems with these in spite of binary compatibility.

When we upgrade to a new version, we need to delete the existing control from the macro and then insert the new control, along with deleting all the EXD files. The procedure for this is as follows:

  1. Delete screen control from form
  2. Delete icon from toolbar
  3. Remove reference from Tools – References
  4. Save macro and exit VBA
  5. Go to c:\documents and settings\username\Application Data\Microsoft\Forms
  6. Delete the *.exd files
  7. Load the macro
  8. Add the new control to the palette
  9. Make sure the version is correct (from the path)
  10. Insert the control back onto the UserForm
  11. Make sure the name matches what you used before
  12. Save the macro

References

References that you add to your macro project must match exactly the references on your client’s computers. For instance if you add a reference for an Office 2007 object like an Excel worksheet and your client has Office 2003, then the macro won’t run and will have to be corrected by removing the Office 2007 reference and adding one for Office 2003. Most COM/ActiveX library references are version specific. Also note the previous section on deleting EXD files. Similarly your version of Accpac must match the client’s to the Service Pack level.

Also beware of components you might have that a client doesn’t. For instance you might have full Visual Basic installed, which provides a large variety of useful COM libraries to use. However your client probably won’t have VB installed, so you must ensure the library use are using is freely distributable and install it on your client’s computer.

Windows has a number of optional components like Windows Scripting Host. If you require this, you will need to provide instructions on how to add this to Windows.

Debugging

Use Debug.Print to print things out to the intermediates window to trace things (the intermediates window is closed by defaults, so you need to open it to see the output).

The built-in VBA debugger is very powerful and a great way to step through your program to see what is going on. Set a breakpoint just before the problem area and step through your code. Takes a bit of practice to know when to “step in” versus “step over”. Run to cursor is a very useful function.

Always include an error handler in your macro. Any View error will trigger an exception and there will be a text error message that will tell you why the exception happened. Even if you don’t get errors, you still want this error handler in case your clients get errors. For instance if your client makes an optional field required and your macro doesn’t provide it, is very hard to debug if you don’t have an error handler, but an error handler will instantly tell you what is wrong.

If you are running a long process like importing hundreds of order and you just want it to go through and sort out the errors later, then write all the errors to a file with code such as:

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long

If Errors Is Nothing Then
WriteToErrorLog Err.Description
Else
lCount = Errors.Count

If lCount = 0 Then
WriteToErrorLog Err.Description
Else
For lIndex = 0 To lCount – 1
WriteToErrorLog Errors.Item(lIndex)
Next

Errors.Clear
End If
Resume Next

End If

Public lErrorFileHandle As Long

Public Sub WriteToErrorLog(sMessage As String)
If lErrorFileHandle = 0 Then
Dim sFileName As String
sFileName = “c:\popjctest.txt”

lErrorFileHandle = FreeFile(1)
Open sFileName For Output Access Write As #lErrorFileHandle
End If

If Trim(sMessage) = “” Then
Close #lErrorFileHandle
lErrorFileHandle = 0
Else
Print #lErrorFileHandle, sMessage
End If

End Sub

Getting Information From Accpac

Use macro recording to get a starting point. This is especially useful to get the view.open and view.compose calls correct. Often a lot of noise and hard coded values are recorded that need to be cleaned up. The main source of errors we see in macros are due to bad view compositions, so this is really quite important.

Use RVSpy (from the Accpac – Tools start menu). This tool spies on every View call made. Use it to see what Accpac does and use it to see what your macro is doing. Often easier to use if you turn off recording of view functions you aren’t interested in like attribs or presents.

Use the Application Object Model that is posted on our web site. This contains very useful information on the Views and underlying database tables (useful for reporting).

Use the Accpac U.I. Info tools (from the Accpac – Tools start menu). This gives info on UI programs as well as views.

If your macro isn’t working, try doing the same thing in the Accpac UI to ensure you aren’t being blocked because of some sort of settings or something.

Read the macro chapters in the System Manager User’s Guide, these have some good information on how the Accpac Views and macros work.

Keep a Backup of Your Macros

Always keep a backup of your macros, or use a source control system like SubVersion (http://subversion.apache.org/) or Git (http://git-scm.com/). Sometime a macro file might become corrupted, or you might accidentally delete some important code. It’s always nice to go back to a previous version that isn’t too old.

Global Variables

Set global variables to Nothing when the macro completes. This is useful when debugging. If you are running the VBA Editor, then global variables are not cleared when the macro end automatically (they are cleared when VBA exits). So if you re-run the macro it will run with the global variables still having their values from the last run which can cause confusion. Worse even if you assign each variable as you start, this means the old value will be cleared at this point sometimes causing errors if they are objects.

Objects like datasources must be set to Nothing before the macro terminates or VBA may GPF as it shutsdown.

Only One UI

You can only embed one UI in a VBA project at a time. All the Accpac screens use the same variable names and due to bugs in VBA this causes problems. You should be able to refer to them by object name like AccpacCS2000.datasource versus AccpacAP1000.datasource, but VBA gets confused and won’t work properly.

Don’t Use Init

We have replaced Init with RecordGenerate, RecordCreate and RecordClear to make it clear what we are trying to do. Init did all of these and sometimes did the wrong thing. Newer Accpac Views will only accept the Record* functions and will reject Init.

VBA Versus VB

VBA has a number of limitations imposed by Microsoft, so you are motivated to buy full VB. These limitation include:

  • Having a menu
  • Having an icon (for when minimized and in the task bar)
  • Receive events from popup forms
  • Resizable windows

VBControlExtender

In VB you have to define the controls of type VBControlExtender because unlike VBA, VB can’t do the conversion for you. Here’s an example getting the Click event of a notebook in full VB:

Dim WithEvents tabctl As VBControlExtender

Private Sub AccpacBK1600UICtrl1_OnUIAppOpened()
Set tabctl = AccpacBK1600UICtrl1.UIAppControls(“tbOptions”).GetControl

End Sub

Private Sub tabctl_ObjectEvent(Info As EventInfo)
If Info.Name = “Click” Then
MsgBox (“tab clicked”)
End If

End Sub

(In VBA we could just access the tabctl directly without using a VBControlExtender).

Well that wraps up my blog posting for this week. Next week I’m attending Sage Insights 2010 in Denver Colorado. If any readers are also attending, make sure you say “Hi”.

Written by smist08

May 14, 2010 at 2:35 am

Posted in sage 300

Tagged with , ,