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 a Dynamics GP Consultant (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 a Dynamics GP Consultant (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 a Dynamics GP Consultant (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

Read original post VBA To Insert Next Microsoft Dynamics GP DD Transaction Code at azurecurve|Ramblings of an IT Professional