VBA - Preventing backdated transactions using Visual Basic
Another recent request was to provide a method to prevent backdated transactions from being entered into Microsoft Dynamics GP. This means that we needed to validate the document date of a transaction as it as entered and again when it is saved. This is a perfect opportunity to demonstrate how Visual Basic for Applications (VBA) can add custom business rules to the application.
Note: If you want to take it one step further, you can check the transaction dates before attempting to post a batch of transactions. This is possible with VBA but would require the use of ADO (ActiveX Data Objects) to access the SQL data to validate the transactions in a given batch. This post will not be covering this additional level of complexity.
Below is summary of the steps used to add the business rules to the Sales Order Processing Sales Transaction Entry window:
-
Selected Transactions >> Sales >> Sales Transaction Entry to open the window.
-
Selected Tools >> Customise >> Add Current Window to Visual Basic.
-
Selected Tools >> Customise >> Add Fields to Visual Basic.
-
Clicked on the Save Button and the Date field to add them to VBA.
-
Selected Tools >> Customise >> Add Fields to Visual Basic to disable the selection tool (You can also press Escape).
-
Selected Tools >> Customise >> Visual Basic Editor.
-
Optional but recommended, only needs to be performed once on a workstation. Select Tools >> Options and on the editor tab, check the Require Variable Declaration option.
-
Located Microsoft Dynamics GP >> SalesTransactionEntry in the Project window.
-
Double clicked on SalesTransactionEntry module.
-
In the Properties window, selected Date.
-
Changed the name property from Date to DocDate as Date is a keyword in VBA.
-
Wrote the code for the 3 events (see below).
-
Selected Debug >> Compile Microsoft Dynamics GP.
-
Selected File >> Save Microsoft Dynamics GP.
-
Selected File >> Close and Return to Microsoft Dynamics GP.
The code needs to handle 3 events. The first event is when the user changes the Document Date field. The second and third events handle when the document is saved, both when the Save Button is pressed and also when save is selected from the "Do you want to save or delete the document?" dialog.
SalesTransactionEntry VBA Code
Note: The text for the PromptString in the Select Case statement in the Window_BeforeModalDialog() event will need to be adjusted to match the text for the transaction entry window where the code is being used.
An example package of the v10.0 Sales Transaction Entry window is attached at the bottom of the article.
Hope you found this example useful.
David
