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

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

Comments (10)
  1. Paul Iswariah says:

    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.

  2. As a follow on to the Using VBA with Report Writer post, I would like to discuss using ActiveX Data Objects

  3. Martin Miller says:

    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?

  4. Martin Miller says:

    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 =

       ‘ 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


       Set rst = Nothing

       Set cmd = Nothing

    End Sub

  5. Martin Miller says:

    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


       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

  6. David Musgrave says:

    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.


  7. Martin Miller says:

    "Did you use the downloadable example on this page?"

    Yes I did. It was very helpful. Thanks so much.

  8. David Musgrave says:

    Post from Jivtesh Singh at About Dynamics, Development and Life…/dynamics-gp-customizations-best.html

  9. Amish Dalal says:

    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.

Comments are closed.

Skip to main content