I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!
By Steve Endow
I've had a few Dynamics GP customers that purchase software from me every few years, and a few of them have mailed checks to my old mailing address from 4 years ago. How can this happen?
Well, the Dynamics GP Payables Transaction Entry window does not display the vendor Remit To address, so verifying the vendor address is not an obvious natural step in the invoice entry process. Yes, there is a link to open the Vendor Address Maintenance window, but what if internal controls prevents the user who enters vendor invoices from editing vendor addresses? The user would need to go through a separate process to verify the vendor address...for every invoice. Not ideal.
How can VBA help?
In just a few minutes, VBA can be added to the Payables Transaction Entry window to check if the vendor has not had a transaction in over 60 days, prompt the user to verify the vendor address, and even open the Vendor Inquiry window to review the current Remit To address in Dynamics GP.
It's really easy!
Here's a video discussing the background and walking through the entire process of adding the VBA to Dynamics GP.
First, within Dynamics GP, you add the desired windows to Visual Basic by clicking on Tools -> Customize -> Add Current Window to Visual Basic.
After the window is added, I click on Add Fields to Visual Basic and then click on the Vendor ID field.
Since I am assuming that the user entering invoices will not have access to edit vendors or vendor addresses, I'm going to add the Vendor Inquiry window to Visual Basic and add 3 fields on the Inquiry window to VB: Vendor ID, Address ID, and the Address ID Next button.
Once I have those windows and fields added to Visual Basic, I press CTRL+F11 to open the Dynamics GP Visual Basic Editor. If you don't have access to the VB Editor, you may not be licensed to use it, or you may not have permissions--in which case, talk with your GP administrator or GP partner.
In the VB Editor window, I'll select the PayablesTransactionEntry window on the left, then select the VendorID field and the AfterUserChanged event.
I wrote the simple VBA code below to demonstrate how you can quickly and easily add some VBA to add some valuable functionality to Dynamics GP to save users time and improve data entry.
The code finds the most document date for any vendor transaction in Dynamics GP, and if that date is over 60 days ago, it opens the Vendor Inquiry window and displays the vendor Remit To address for the user to review and verify.
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), '1900-01-01') 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("1900-01-01") 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
In just a few minutes, you can have this customization running in Dynamics GP without any additional development tools.
If you have more complex requirements, you can easily add more advanced functionality using VBA. If you prefer using a separate development tool, you could also develop this customization using .NET or Dexterity, but the appeal of VBA is its simplicity and ease of use.
So if you have some small problem or additional business requirement that you'd like to handle in Dynamics GP, VBA might come in handy.
Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.
No comments:
Post a Comment