Freigeben über

Modifier - Adding a field to a scrolling window using ADO Example

  • Artikel

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:

How to use ActiveX Data Object (ADO) with VBA on a window with Microsoft Dynamics GP and with Microsoft Business Solutions - Great Plains 8.0 (KB 942327) Secure Link

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

Comments

  • Anonymous
    September 09, 2008
    PingBack from http://blogs.msdn.com/developingfordynamicsgp/pages/modifier-vba-samples.aspx

  • Anonymous
    September 18, 2008
    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.

  • Anonymous
    October 29, 2008
    As a follow on to the Using VBA with Report Writer post, I would like to discuss using ActiveX Data Objects

  • Anonymous
    December 17, 2008
    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?

  • Anonymous
    January 08, 2009
    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

  • Anonymous
    January 08, 2009
    The comment has been removed

  • Anonymous
    January 08, 2009
    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

  • Anonymous
    January 12, 2009
    "Did you use the downloadable example on this page?" Yes I did. It was very helpful. Thanks so much.

  • Anonymous
    August 19, 2010
    Post from Jivtesh Singh at About Dynamics, Development and Life www.jivtesh.com/.../dynamics-gp-customizations-best.html

  • Anonymous
    October 11, 2010
    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.