How to call Stored Procedure from VBA for Great Plains on save button

(0) Share

Report

Posted on by IT Analyst

i have a stored procedure which can add the comment ids on every purchase order lines in GP. But the issue is I am using VBA AND I want THAT WHENEVER USER ENTERS a new Purchase Order in GP with few line items and as user click on save Button. It should call my stored procedure to add comment id for every line item automatically. Note that this PO is a new PO so It was not existed before so I want that VBA First saves the PO and after that VBA should call my SQL Stored procedure along with the paramter PONumber so that My stored procedure will add comment ids for every po line items. I am trying my level best. but I don't know how to do that : The code i have written down in VBA IS BUT IT IS NOT WORKING . Can ANYBODY Help me, will appreciate help

Public Sub Save_AfterUserChanged()
Dim rs As ADODB.RecordSet
Dim sDocNumber As Variant
'dim abc as

Set rs = New ADODB.RecordSet

'Assign Values to Local Variable
'===============================
sDocNumber = sDocNumber1

'Create RecordSet
'=================================
Set rs = CreateObject("ADODB.Recordset")

'Use RecordSet to execute Stored Procedure and pass in the variables to the Parameter
'=====================================================================================
rs.Open "EXEC RMC_Fill_CommentID1 '" & sDocNumber & "'", GetConnection()

'rs.Open "EXEC RMC_Fill_CommentID1 '" & "P00022795" & "'", GetConnection()
End Sub

*This post is locked for comments

All responses (15)

Answers (7)

IT Analyst on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

There is another error when I try to take the print out of this PO on my local computer by using the MS Word base customize template , i get the following error. Please note that i just copied the Chunk File from GP server and paste on my local machine where the GP Dictionary and DYNAMICS.SET files lie . Before that I was getting the error message that Template is missing so I opened the server and copied the WORD DOCUMENT folder and paste on my local computer at the same location where DYNAMICS. SET AND DYNAMICS.DIC file resides on my Desktop . After this I am getting the following error .One more thing i have noticed that I have MS Office 365 pro plus on my local computer where as on GP server it is MS office 2016.

After seeing this i opened the same word template by using MS Office on my local computer so it was showing me the compatibility mode. Is this the reason, that I am not able to take the print out on my local machine . This is the same place where MY VBA program lies. Can you please help me what to do??.............
Verified answer

Craig Fuller 409 on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

You deploy the package by going to each machine running a Dynamics GP Client and choosing Import instead of Export. When you choose Import, navigate to where you saved your exported package and select it. You do not change the dynamics.set file.

So, if you only want to deploy the VBA changes based on your project in this thread and leave everything else as they are (any custom reports or forms) then you may want to create a package only containing the VBA and references. In the previous example above you would only select (Ctrl click) the Microsoft ActiveX Data Objects 6.1 Library reference and the POP_PO_Entry Form with VBA, then Export to a package giving it an appropriate name. You would then import this package on each client machine.

If you want all your custom reports, forms and VBA in one package, make sure the client machine you are working on has all of those on it, select all of them in the window and export to a package, then import to all other Clients.

I'm glad I could help. This forum is a better place to pose questions than just a single individual like myself. I believe you can add me as a friend by clicking my name and making that selection. I will see about enabling the ability to send me a private message as some others have this option in their profile.
IT Analyst on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

Thank you so much Craig ..................Honestly I have no words to show you how much obliged I am . About Your experience, I have only one word.........WOW.

But I am curious on .CNK (cHUNK FILE), How do I deploy on MY test Machine where i have this VBA Code. . Do I change the Dynamics.set file? if yes then how I can do this?

One more request, Can I have your email address If you don't mind. With your permission, if any question comes in my mind then i would be able to sent you the email too. You can delete your response later having only your email address if you don't want others to know
Verified answer

Craig Fuller 409 on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

It's been awhile but create a package. You can create a package that includes all the customizations with forms, reports and VBA or pick and choose...meaning you could also have separate packages for each. If your test environment has all the correct modified forms, reports and VBA then I would create one package for all. Go into Customization Maintenance, Shift-Click to select all the entries, click export and give it a name and save it to a location other GP Clients can access. Then go to all other GP clients and Import.

All Dexterity code is compiled into a dictionary file (.dic) and is not affected by packages. Dexterity is the programming language that Dynamics GP was created with. If you want full customization capability then Dexterity would be wise to learn if you are a developer of Dynamics GP, work for a Dynamics GP developer or are a consultant that wants no limits on what can be developed for GP. But Dexterity doesn't really exist outside of GP and so it's value is limited to the GP space. I've been working with GP for over 20 years and still don't know Dexterity due to GP Development not not being my primary job and the fact that VBA (and .Net now) allows us to do much of our customization needs...but you heard right in that it is not able to support 100% of GP.

.Net is much more capable than VBA in that it has more access to GP functionality. Once you switch from VBA and start using Visual Studio along with Winthrop's Visual Studio Integraton Toolkit https://www.winthropdc.com/products_VSIT.htm (for additional menu options) you will not return to VBA unless helping others who are still using it like in this case.

It doesn't matter whether you choose C#.Net or VB.Net. The only issue I've ever run into is when sample code is in C#...then it can be difficult for me to figure out the syntax, construction etc of what it should be in VB.Net. Since I already knew VBA I just went with VB.Net but I have wanted to learn C# somewhere along the way so I would be flexible using either (really for reading either sample code). No matter the coding language chosen, you compile your .Net project into a single dll file and distribute that single file...so it is irrelevant which language was used. If you are starting out, I would choose C#.Net as it has more broad application usage. Dexterity has a big learning curve and is limited to GP so do that when you find time after C#.Net or VB.Net.
IT Analyst on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

So much thanks to you as you solved this issue so quickly . I will recommend every body to click on Like Button as you are like a diamond in IT technical Industry and very helpful for every body .At this point I need your advises more for the following related matter:

We have some customization for Purchase order in GP , I believe in Dexterity also Print out of Purchase order in GP is modified too. keeping this in mind, My question is Do I add this code in the production environment in the same way as I have done it in Testing Environment.or DO i Create a Customization Package ??

Would there be any consequence if some Modification is done in Dexterity and some in VBA like this VBA program code? Let me elaborate my question, what I am trying to ask is that IF I create Customization package so would there be any problem?? I am sorry, i am asking other questions too but I have worked on BI side not on Microsoft VBA/Dextrity side so lots of confusion . Can you send me screen shot that how to create the Customization package in GP AND WHAT SHOULD BE THE STEPS. In between Thanks a lot from bottom of my heart!!!!!!!!!!!

Down below is the Customization maintenance screen GP has. Looking at this one, It looks like that Customization is done only on Purchase order print out but not by using Dexterity... Am I right?

Can you suggest me that If I start learning some programming Language then what in your eyes would be best programming Language from GP customization point of view like C#.NET OR Dexterity or VBA?? My experience is more on the Functional implementation of any ERP application + BI like SSRS, SSIS, SSAS or SQL server programming or Power BI, OR Sharepoint but no experience on C#.NET/VBA/Dexterity. Some one was telling me that Dotnet applications don't support GP 100%. Is it right?
Verified answer

Craig Fuller 409 on at

Like (1)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

You don't need the GetConnection function with the code I have provided. With my old VBA code I typically used connections within my subs and functions so I didn't leave any open recordsets or connections. When my users are in a GP window all day processing orders then I would open a single connection when the Purchase Order Entry window (for example) opens and then close it when the window closes. I have done it both ways depending upon user interaction and how long they keep GP windows open and/or how long I want a SQL connection open.

To check for a new PO you first would have to look at the POP10100.POSTATUS on the Save_BeforeUserChanged sub. If the POSTATUS > 0 then it has been saved before so skip running the stored procedure if it exists.

I often make a generic function for checking existence of records and such. Below would work:
IT Analyst on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

Thank you so so Much......... I am so glad that your valuable advise solved this issue but Few more questions

1-. How to stop VBA not to add TEXT lines every time when user clicks on Save Button. Can this be done in VBA as you were saying that by using record set, I can restrict VBA not to call SQL stored procedure every time.

2- My 2nd question is, We have some customization for Purchase order in GP , I believe in Dexterity also Print out of Purchase order in GP is modified. keeping this in mind, My question is Do I add this code in the production environment in the same way as I have done it in Testing Environment. Would there be any consequence if some Modification is done in Dexterity and some in VBA like this VBA program code? Let me elaborate my question, what I am trying to ask is that I create Customization package so would there be any problem?? I am sorry, i am asking other questions too but I have worked on BI side not on Microsoft VBA/Dextrity side so lots of confusion . Can you send me screen shot that how to create the Customization package in GP AND WHAT SHOULD BE THE STEPS

A
Verified answer

Craig Fuller 409 on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

I haven't used a Variant data type in my code before but if it works and has no issues I don't see any reason why you couldn't use it.

Update the post with your results and mark as answered if this has done so.
IT Analyst on at

Like (0)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

No words to show you that How much I am thankful for your consecutive replies

For SDocNumber , Can i use Variant Data Type instead of String??

let me change everything as per your advise in your previous feedback like below:

Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

sDocNumber = PONumber.Value

End Sub

Private Sub Save_AfterUserChanged()

Dim SQLStr As String

SQLStr = "EXEC RMC_Fill_CommentID1 '" & sDocNumber & "'"

Dim cn As New ADODB.Connection

Set cn = UserInfoGet.CreateADOConnection

cn.DefaultDatabase = UserInfoGet.IntercompanyID

cn.Execute SQLStr, recordsAffected

MsgBox (CStr(recordsAffected) & " Line comments were added.")

If Not (cn Is Nothing) Then

cn.Close

Set cn = Nothing

End If

End Sub
Verified answer

Craig Fuller 409 on at

Like (1)

Report

RE: How to call Stored Procedure from VBA for Great Plains on save button

Glad to help.

Ok, so if I'm understanding correctly if you hard code the PO number it works the first time but if you use the variable it only works the second time (and afterward).

Three things:

1) Make sure you declare your variable "sDocNumber" as a String in the General Declarations area and not within your Subs. Clear this value at the end of your Save_AfterUserChanged sub.

2) Comment out all of your PONumber subs. These can fire more than once during events (like save), can behave differently with new versus existing documents and may be clearing your sDocNumber.

3) sDocNumber1 is not necessary.

The code I provided is complete and works on the first save...but I used a simple update statement to the POP10100 table instead of running your stored procedure. I have attached a screenshot of my fully working example...which may help clarify what is needed.