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

I have the same question (0)

All responses (15)

Answers (7)

Verified answer

Craig Fuller 411 on at

Like (1)

Report

It's been a long time since I used VBA but I recall you need to use an ADODB.Connection object, not Recordset.

Search the web for something along the lines of:

Dim recordsAffected As Long

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Execute "Your stored procedure here", recordsAffected

MsgBox recordsAffected

Was this reply helpful? Yes No
IT Analyst 10 on at

Like (0)

Report

Thank you so much for your reply. Let me send you my SQL stored Procedure and VBA code because I am using ADO In VBA And the problem is still there

Was this reply helpful? Yes No
IT Analyst 10 on at

Like (0)

Report

Thank you so much for your reply. I am using ADODB Connection in VBA (Please see the VBA code below)

Thank you so much for your reply. Let me show you my code in VBA b/c I am using ADO(Active Data X Objects) connection:

The problem is That I want to add Multiple comment ids on every line item so that these New line items are printed on PO (Purchase order in Great Plains). Right now users Enter "TEXT" as a line item with 0 qty and then add multiple Comment Ids, which are specific to Location /site ids. Now users want to enter only Inventory/Non Inventory base items as a line item on purchase order lines but they don't want to enter "TEXT" WITH 0 QTY as additional line items on that PO WITH COMMENT IDS.so They have requested me , if these additions of TEXT can be automate when the user enters a new Purchase order in GP and after that when user clicks on SAVE Button . then this automation should add TEXT with 0 qty and multiple comment ids depends on the Location /site ids in GP.

Please Note that these purchase orders are the new Purchase orders. They don't exist in any Purchase order table. These purchase orders are added in a Purchase Order Table when the user clicks on Save Button.

My Stored procedure is working fine, if I execute it in SQL Server . and this VBA code also calls my stored procedure but it updates the record not the first time when user clicks on SAVE button but My procedure add The Multiple TEXT lines with 0 qty and multiple Comment ids on 2nd time when user opens the same Purchase order and clicks on Save button.

What I am trying to do is that when user clicks on Save Button on Purchase order screen in GP Then let The GP save this record first and after that this VBA program run my My SQL stored procedure.

The issue is that I am not getting how to do it , I tried Triggers in SQL too but nothing is happening. Can you suggest me what to do?I am copying my SQL stored Procedure here. If you think that this can be done by using triggers in SQL, then please let me know as I have created my own trigger in SQL . I am not using the default trigger, Microsoft has added on POP10110 table in SQL.

I appreciate your help!

Was this reply helpful? Yes No
Verified answer

Craig Fuller 411 on at

Like (1)

Report
I am a little confused so forgive me if I go over some things again.

First, I would not use triggers unless there was no other way...less to think about when upgrading GP.

You said you have a working stored procedure that does exactly what you want when run in SSMS...great...no need to dig into that part.

In another response you asked if VBA works with the PO Save button...yes it does. If you haven't added the Purchase Order Entry window and/or the Save button into your VBA project yet then first go to User Preferences and verify (or change) that the "Window Command Display" is "Menu Bar", otherwise you will not be able to add the Save Button. Then:

Open Purchase Order Entry and go to Customize and "Add Current Window to Visual Basic" if not already added.

"Add Fields to Visual Basic" and click on the PO Number field and Save button.

"Add Fields to Visual Basic" again to turn that toggle off.

Open up the Visual Basic Editor. You will need to use two Subs on the Save Button:

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

You will not need any of your PO Number Subs for this process (if you added them for this).

Before executing the procedure I would first use SQL to check the POP10100.POSTATUS for the current PO. If it is zero, then it is new and you can run the procedure, otherwise skip so you don't keep adding line comments every time the save button is clicked. You can use your recordset for that, not for executing your stored procedure.

After you get this working you may want to consider looking into the Window's BeforeModalDialog and the ActionsButton subs. Users can save the PO but bypass clicking the Save Button by selecting Print, choosing an action from the Actions button or using the VCR buttons on the bottom of the window. With the BeforeModalDialog you will want to capture the dialog "Do you want to save or delete the document?".

Please be sure and mark this as the correct answer if it has indeed answered your question.

Was this reply helpful? Yes No
IT Analyst 10 on at

Like (0)

Report

Thanks for your prompt reply. I appreciate your advise(s). But I had already changed the user preferences Display option as "MENUBAR" in GP. After that I opened the Purchase Order screen in GP and added the Save Button and Purchase order Number Field . After this I created the Module and in the Declaration Event. I added the following Program code

----------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------

The issue is when I hard code the Purchase order # as Parameter then SQL adds the Multiple Text Lines. But when I pass the PO# as a variable for this parameter . The VBA code runs but nothing happens on 1st time when user clicks on SAVE button in GP. but when the user opens the same PURCHASE ORDER in Purchase order Entry screen in GP. AND without making any changes , if the user clicks on SAVE button, then TEXT lines are added .

Thats the issue which i am not getting. I think the reason 1st time the TEXT lines were not added with the comment ids because PO was not exist in PO table in SQL but I got confused when I passed the Purchase order number as a Constant value then the TEXT lines were added automatically.(This is the main issue)

Since I have never worked on VBA so that is another issue. Let me try your code and Lets see what happens. One more Request, since I am copying your code to try in VBA so Is this a complete code?...........Plz let me know......... So much Thankful for all your efforts!!

Was this reply helpful? Yes No
Verified answer

Craig Fuller 411 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
IT Analyst 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Craig Fuller 411 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
IT Analyst 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Craig Fuller 411 on at

Like (1)

Report

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:

Was this reply helpful? Yes No