This blog has relocated to a new address: https://blog.steveendow.com/.

Future posts will exclusively be on the new site (https://blog.steveendow.com).

Thank you!

By Steve Endow

I’ve encountered a situation where Dynamics GP customers, purchasing software from me every few years, have sent checks to an outdated address from four years ago. This raises the question: why is this occurring?

The root of the issue lies in Dynamics GP’s Payables Transaction Entry window. This window doesn’t directly show the vendor’s “Remit To” address, making address verification an easily overlooked step during invoice entry. While there’s a link to access the Vendor Address Maintenance window, it poses a problem if internal controls restrict invoice entry personnel from modifying vendor details. This necessitates a separate process for verifying vendor addresses for each invoice, which is far from ideal.

Fortunately, VBA offers a solution by enhancing the Payables Transaction Entry window. With a few minutes of VBA coding, we can implement a check for vendor transactions older than 60 days. If such a transaction exists, the system prompts the user to confirm the vendor’s address and even provides access to the Vendor Inquiry window for reviewing the current “Remit To” address within Dynamics GP.

Implementing this is surprisingly straightforward.

A comprehensive video guide details the rationale behind this customization and provides a step-by-step walkthrough of integrating the VBA code into Dynamics GP.

Initially, within Dynamics GP, the required windows are added to Visual Basic. This is achieved by navigating to Tools -> Customize -> Add Current Window to Visual Basic.

Once the window is incorporated, I proceed by clicking on “Add Fields to Visual Basic” and selecting the Vendor ID field.

Assuming that the user handling invoices lacks the authorization to modify vendor information, I’m adding the Vendor Inquiry window to Visual Basic as well. Within this window, three specific fields are included: Vendor ID, Address ID, and the Address ID “Next” button.

After integrating these windows and fields into Visual Basic, pressing CTRL+F11 grants access to the Dynamics GP Visual Basic Editor. If this access is restricted, it could be due to licensing limitations or insufficient permissions, in which case consultation with your GP administrator or partner is recommended.

Inside the VB Editor window, I’ll choose the PayablesTransactionEntry window on the left side, then select the VendorID field and the AfterUserChanged event.

The VBA code provided below is a simple example illustrating how effortlessly you can enhance Dynamics GP’s functionality to streamline user experience and improve data accuracy.

This code identifies the latest document date for any vendor transaction within Dynamics GP. If this date is older than 60 days, it opens the Vendor Inquiry window and presents the vendor’s “Remit To” address for user review and validation.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

Private Sub VendorID_AfterUserChanged()

    Dim strVendorID As String
    Dim strSQL As String
    Dim dtLastDocDate As Date

    strVendorID = VendorID.Value

    'Find the most recent document date for the vendor
    strSQL = "SELECT COALESCE(MAX(DOCDATE), '2022-08-12T20:14:25Z') AS DOCDATE FROM PM00400 WHERE VENDORID = '" & strVendorID & "'"

    Set oConn = UserInfoGet.CreateADOConnection
    oConn.DefaultDatabase = UserInfoGet.IntercompanyID

    Set rsResult = oConn.Execute(strSQL)
    strLastDocDate = Trim(rsResult.Fields("DOCDATE").Value)
    rsResult.Close

    'Get the Remit To Address ID for the vendor
    strSQL = "SELECT VADCDTRO FROM PM00200 WHERE VENDORID = '" & strVendorID & "'"

    Set rsResult = oConn.Execute(strSQL)
    strRemitID = Trim(rsResult.Fields("VADCDTRO").Value)
    rsResult.Close
    oConn.Close

    dtLastDocDate = CDate(strLastDocDate)

    'If Doc Date is 1/1/1900, vendor has no transactions
    If dtLastDocDate = CDate("2023-07-02T16:37:59Z") Then
        Exit Sub
    Else
        intDays = DateDiff("d", dtLastDocDate, DateTime.Date)

        'If the last doc date is > X days ago, display a dialog
        If intDays > 60 Then
            msgResult = MsgBox("This vendor has not had a transaction since " & strLastDocDate & " (" & intDays & " days ago)." & vbNewLine & vbNewLine & "Please review the current vendor Remit To address and compare to the invoice address", vbOKOnly, "Verify Vendor Address")

            VendorInquiry.Open
            VendorInquiry.VendorID.Value = strVendorID
            VendorInquiry.Activate
            VendorInquiry.Show

            While VendorInquiry.AddressID.Value <> strRemitID
                VendorInquiry.NextButtonWindowArea.Value = 1
            Wend

        End If
    End If

End Sub

Within minutes, this customization can be implemented in Dynamics GP without relying on external development tools.

For more intricate requirements, VBA offers the flexibility to incorporate advanced functionalities. Developers comfortable with alternative tools like .NET or Dexterity could achieve the same result, but VBA’s appeal lies in its user-friendliness and ease of implementation.

In conclusion, if you encounter minor issues or need additional features in Dynamics GP, VBA presents a powerful solution.

Steve Endow, a Microsoft MVP based in Los Angeles, is the proprietor of Precipio Services, a firm specializing in Dynamics GP integrations, customizations, and automation solutions.

You can connect with him on Twitter, YouTube, and Google+.

http://www.precipioservices.com