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


David Meego - Click for blog homepageThis 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:

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)

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.

11 thoughts on “Modifier – Adding a field to a scrolling window using ADO Example

  1. 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.

    Like

  2. 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?

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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.

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.