VBA To Insert Next Microsoft Dynamics GP DD Transaction Code

I am tending to encourage clients to use SmartConnct from eOne Solutions for integrating data into Microsoft Dynamics GP, but I do still have quote a few clients using Integration Manager.

SmartConnect supports the use of custom eConnect nodes which I have created for a few clients, either manually or through using Node Builder (also from eOne).

You can accomplish the same result through Integration Manager by extending the integration using VBA. I had a client a while ago who were using the Direct Debits & Refunds module. This means that each transaction needs to have a DD Transaction Code code stamped on it, which Integration Manager doesn’t do. However, with a little VBA, this can be accomplished.

In the Before Integration script we instantiate the ODBC connection:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
' BEFORE INTEGRATION
Dim oCon

Set oCon = CreateObject("ADODB.Connection")

oCon.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
GPConnection.Open(oCon)

SetVariable "gblCon", oCon

in the Before Document script we needed to get the next SOP number instead of allowing it to default in. I did this using a stored procedure I wrote for a similar purpose earlier this year.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
' BEFORE DOCUMENT
Dim oCmd

Set oCmd = CreateObject("ADODB.Command")

With oCmd
	.ActiveConnection = GetVariable("gblCon")

	.CommandText = "EXEC usp_AZRCRV_GetNextSOPDocumentNumber"
	Set rsQuery = .Execute

	If Not (rsQuery.EOF and rsQuery.BOF) Then
		SetVariable "SOPNUMBE", Cstr(rsQuery("SOPNUMBE"))
	End If
	rsQuery.Close
	
End With
Set oCmd = Nothing

in the After Document script we then insert the DD Transaction Code into the DDEU22 table:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
' AFTER DOCUMENT
Dim oCmd

Set oCmd = CreateObject("ADODB.Command")
With oCmd
	.ActiveConnection = GetVariable("gblCon")
		
	.CommandText = "INSERT INTO DDEU022 (DOCTYPE,DOCNUMBR,TRXSOURC,CUSTNMBR,DDTRANS) VALUES (3," & GetVariable("SOPNUMBE") & ",'Sales Entry','" & SourceFields("Sales Ledger Template.Customer Number") & "', '01')"
	Set rsQuery = .Execute
	
End With
Set oCmd = Nothing