This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
The following VBA example adds the Sales Order Processing User Defined 3 field onto the Receivables Transaction Inquiry window.
When the window is opened the code will obtain the current user’s credentials and open an ADO (ActiveX Data Objects) connection to SQL Server. This connection is closed when the window is closed.
The connection object variable has been declared as public so that it can be used by the main window module to read the name of the prompt for the User Defined 3 field and also from the scrolling window (grid) module to populate the added local field with the data from the transactions using the Grid_BeforeLinePopulate() event.
Because the Document Type for Receivables and for Sales Order Processing have different values, you will see that the script in the scrolling window maps the abbreviations used in the window to the correct document numbers used in the SOP tables.
NOTE: The method of opening an ADO connection to SQL Server differs for each version. v8.00 uses the external RetrieveGlobals.dll, v9.00 use the external RetrieveGlobals9.dll and v10.00 uses the built-in UserInfoGet object. v10.00 will need at least Service Pack 1 to use this sample.
The Knowledge Base (KB) article below demonstrates the different methods:
Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.
Please see the “Installation Instructions.txt” file in each version’s archive for more information.
Adding User Defined Field to Enquiry Window.zip
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
PingBack from http://blogs.msdn.com/developingfordynamicsgp/pages/modifier-vba-samples.aspx
Hi ,
I’m using the above ADO example to achieve a similar solution. On the select Bank transaction window -> we have a deposit number and when i drill down on a given deposit number i get the check number for that deposit. Through this ADO sample as an example I am hoping to get the check number on the select bank transactions window.
As a follow on to the Using VBA with Report Writer post, I would like to discuss using ActiveX Data Objects
I’m having trouble with the knowledge base article. I want to add the item description to the vendor items lookup window. I assume you add a field to the grid and shoot a sql statement with the item number using ado most likely from IV00101 in Grid_BeforeLinePopulate. Does that sound right?
Here’s how I did it. Replace StringM2 with your value.
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String
sqlstring = “SELECT ITEMDESC FROM IV00101 WHERE ITEMNMBR = ” & “‘” & VendorItemsDetail.ItemNumber.Value & “‘”
‘ ADO Command
cmd.ActiveConnection = VendorItems.cn
‘ adCmdText
cmd.CommandType = 1
‘ Command
cmd.CommandText = sqlstring
‘ Pass through SQL
Set rst = cmd.Execute
If Not (rst.EOF And rst.BOF) Then
StringM2.Value = RTrim(rst!ITEMDESC)
End If
rst.Close
Set rst = Nothing
Set cmd = Nothing
End Sub
This is the code that goes with it. This is in VendorItems(Window)
Option Explicit
Public cn As New ADODB.Connection
Private Sub Window_BeforeClose(AbortClose As Boolean)
‘ Close ADO Connection
cn.Close
Set cn = Nothing
End Sub
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
‘ ADO Connection
Set cn = UserInfoGet.CreateADOConnection
‘Use a client-side cursor so that a recordset count can be obtained later.
cn.CursorLocation = 3
‘set the database to the currently logged in db
cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub
Hi Martin
Glad you got it working.
Did you use the downloadable example on this page?
The working example is often more helpful than the description KB article.
David
"Did you use the downloadable example on this page?"
Yes I did. It was very helpful. Thanks so much.
Post from Jivtesh Singh at About Dynamics, Development and Life
http://www.jivtesh.com/…/dynamics-gp-customizations-best.html
We added two text fields in the Transaction Entry window.
One field is displaying the separate JV No for GJ source and other field is the narration storing in the separate table through ADO.
Everything is working fine only we had issues while navigating the data, it always popup the message
"Do you want to save or delete the transaction?" whenever we navigate the records.
We also found the problems. We are assigning the 2 additional field data i.e JV No and the Narration through recordset to the event JournalEntry_Afteruser changed , but if we don't assigned the 2 additional field and we navigate the record we don't
get the message "Do you want to save or delete the transaction?". Is there any other possible way to avoid the system message.