Microsoft Great Plains Customization Scenarios - Combining Continuum with VBA
by Daniel Sionov
Introduction. Microsoft Great Plains is very popular ERP and it has multiple customization tools and options: Dexterity, Modifier/VBA, Continuum, SQL stored procs, Crystal Reports, ReportWriter, Integration Manager, eConnect to name a few. In this article author shows you advanced programming technique which allows you to switch Great Plains modules in VBA and use Great Plains Dexterity language - Sanscript to do the job. Alternative approach would force you to use SQL stored procedures and call them from VBA code
Note to Reader
This document assumes an intermediate to advanced understanding of Great Plains VBA and Continuum.
Overview
This is a tip for Great Plains Continuum for VB developers.
Continuum is a good product that allows the VB developer to access the OLE layer of Great Plains to pass commands through the application runtime. It gives you a virtually limitless amount of flexibility especially in working with multiple dictionaries.
Despite the power and flexibility that Continuum provides, it does have its nuisances. One nuisance for which a solution will be provided is the difficulty with deployment.
Currently, to launch the VB ActiveX .EXE, the developer must include a statement in the DEX.INI file to point to the path of the .EXE. Also, the Launcher.cnk file must be placed in the eEnterprise or Dynamics folder on each workstation. Further, the developer must include code to trigger when Great Plains exits to end the .EXE correctly. If this does not happen, the application will continue to run even after Great Plains has exited. Finally, the developer must deploy all the DLL's that come with the VB program to each workstation.
If it sounds like a lot of work, that's because it is.
The solution is to use VBA and place declarations in the code to get the same access to the OLE layer as VB with Continuum. By doing this, the need to consider items mentioned above is eliminated.
There are two problems with the VBA approach however. Modifier with VBA must be registered. The other problem is that you cannot use the Continuum wizard. That means all coding. This however eliminates the need for purchasing Continuum. The Customization Site Enabler must be purchased instead.
Code
Although the code seems complicated at first, it is actually quite simple. It involves 4 sections as follows:
1. A .bas module in the eEnterprise/Dynamics product containing the declarations to access the Application object of Great Plains. This also includes the trigger registration.
2. A class module in the eEnterprise/Dynamics product containing the code that will run when specific events are triggered (as registered in the .bas module). This is referred to as the CallBack Class.
3. Some code to be run when triggered by a VBA event to launch the main sub in the .bas module.
4. A class module in the eEnterprise/Dynamics product containing the ParamHandler section. This is optional and allows the developer to pass data back from a PassThrough Sanscript script.
.Bas Module
To keep consistent with the code that the Continuum Wizard produces with VB, the .bas module was named TemplateMain. As a matter of fact, the code listed below was pretty much pasted from the code generated by the Continuum Wizard.
Most of it contains declarations of constants. But it also creates the Application object of Great Plains. It is through this object that all communication takes place.
Once all of these things have been created, the developer can than proceed to register triggers to fire. To better understand Great Plains triggers, please refer to the Programmer's Guide and Sanscript Supplement that comes with Continuum for VB.
Option Explicit
Public eEnterpriseApp As Object
Public eEnterpriseCB As New eEnterpriseCB
Public ParamHandler As New ParamHandler
Public ValueCollection As Collection
Public Const TRIGGER_FOCUS_PRE = 0
Public Const TRIGGER_FOCUS_CHANGE = 1
Public Const TRIGGER_FOCUS_POST = 2
Public Const TRIGGER_FOCUS_PRINT = 3
Public Const TRIGGER_FOCUS_ACTIVATE = 4
Public Const TRIGGER_FOCUS_FILL = 5
Public Const TRIGGER_FOCUS_INSERT = 6
Public Const TRIGGER_FOCUS_DELETE = 7
Public Const TRIGGER_BEFORE_ORIGINAL = 1
Public Const TRIGGER_AFTER_ORIGINAL = 2
Public Const REG_ERR_NOERR = 0
Public Const REG_ERR_UNKNOWN = 1
Public Const REG_ERR_SCRIPT = 2
Public Const REG_ERR_FOCUS = 3
Public Const REG_ERR_OBJECT = 4
Public Const REG_MSG_UNKNOWN = "A problem of unknown origin occured."
Public Const REG_MSG_SCRIPT = "The Callback routine was not found."
Public Const REG_MSG_FOCUS = "The event is not valid for this object."
Public Const REG_MSG_OBJECT = "The object does not exist."
Public Sub WatchRegistration(obj As String, proc As String)
CheckRegistration eEnterpriseApp.RegisterWatchTrigger(obj, eEnterpriseCB, proc),
obj, "change"
End Sub
Public Sub FocusRegistration(obj As String, proc As String, focus As Integer,
attach As Integer)
CheckRegistration eEnterpriseApp.RegisterFocusTrigger(obj, focus, attach,
eEnterpriseCB, proc), obj, "focus"
End Sub
Public Sub FormRegistration(obj As String, proc As String, item As String, accel
As String)
CheckRegistration eEnterpriseApp.RegisterFormTrigger(obj, item, accel,
eEnterpriseCB, proc), obj, "form"
End Sub
Public Sub ShutdownRegistration(proc As String)
CheckRegistration eEnterpriseApp.RegisterShutdownRoutine(eEnterpriseCB, proc),
"the eEnterprise application", "shutdown"
End Sub
Private Sub CheckRegistration(error As Integer, obj As String, reg As String)
If error <> REG_ERR_NOERR Then
MsgBox "Failed to register a " & reg & " notification on " & obj & ". " &
RegistrationErrStr(error)
Set eEnterpriseApp = Nothing
End
End If
End Sub
Private Function RegistrationErrStr(error As Integer) As String
Select Case error
Case REG_ERR_NOERR
RegistrationErrStr = ""
Case REG_ERR_UNKNOWN
RegistrationErrStr = REG_MSG_UNKNOWN
Case REG_ERR_SCRIPT
RegistrationErrStr = REG_MSG_SCRIPT
Case REG_ERR_FOCUS
RegistrationErrStr = REG_MSG_FOCUS
Case REG_ERR_OBJECT
RegistrationErrStr = REG_MSG_OBJECT
End Select
End Function
Public Sub Main()
Dim szSavedProduct As String
Dim ErrVal As Integer, error As String
Dim strDatasource As String
Dim strUser As String
Dim strPassword As String
Dim strSQLServer As String
On Error Resume Next
Set eEnterpriseApp = CreateObject("Dynamics.Application")
ErrVal = eEnterpriseApp.setparamhandler(ParamHandler)
On Error GoTo 0
If eEnterpriseApp Is Nothing Then
MsgBox "Failed to create a Dynamics.Application object"
End
End If
eEnterpriseApp.CurrentProduct = "eEnterprise"
' Sample Triggers Registered
ErrVal = DynamicsApp.RegisterDatabaseTrigger( _
"table SVC_Serial_MSTR", "form SVC_Serial_Maintenance", 4, DynamicsCB, "SVCSerialAdd")
If ErrVal <> 0 Then
MsgBox "Unable to register the database notification."
End If
ErrVal = DynamicsApp.RegisterDatabaseTrigger( _
"table SVC_Serial_MSTR", "form SVC_Serial_Maintenance", 8, DynamicsCB, "SVCSerialAdd")
If ErrVal <> 0 Then
MsgBox "Unable to register the database notification."
End If
End Sub
Calling the Main Sub
The Main Sub is what is called to create the Application object and register appropriate triggers.
The Main Sub should be called when a user has logged into Great Plains. A good place to add an event that does this is in the Toolbar Window on the Company Name field. The developer should therefore add the Toolbar Window to the VBA IDE as well as the Company Name field.
This event would look something like this:
Private Sub CompanyName_Changed()
TemplateMain.Main
End Sub
The CallBack Object
The developer should create a CallBack object if he or she wishes to capture events triggered by the Application object.
To keep consistent with the code that the Continuum Wizard produces, it can be named eEnterpriseCB or DynamicsCB as appropriate.
In this class, public methods will be created that will have the code for the events. The triggers registered in the Main Sub will refer to the methods in this class.
The ParamHandler Object
The ParamHandler class should be created when the developer needs to pass data from Sansript code. The Continuum documentation should be referred to for further information about the ParamHandler class.
Conclusion
There are numerous reasons why you would use the approach described above. Deployment is one reason. Another reason is that it gives a lot of power to VBA. Passthrough Sanscript code will do what VBA cannot.
Happy modifying! If you want us to do the job - give us a call 1-866-528-0577! help@albaspectrum.com
Dan Sionov is Technical Writer in Alba Spectrum Technologies – USA nationwide Microsoft CRM, Microsoft Great Plains customization company, based in Chicago, Boston, San Francisco, Los Angeles, Houston, Atlanta, and Miami and having locations in multiple states and internationally (www.albaspectrum.com), he is Dexterity, Modifier/VBA, SQL, VB/C#.Net, Crystal Reports and Microsoft CRM SDK developer.
Alba Spectrum Technologies
Andrew Karasev's comments - we are happy for the opportunity to say great thanks to Dan Sionov - who was my teacher in Great Plains Customization while we were colleagues in Illinois based Softbite Solutions. This article actually is a classic example of Great Plains Dexterity sanscript advanced programming. Dan's website: www.sionova.com