Welcome to the final installment of the
Modifier with VBA workshop. Today we will work with lots of code, some of it very straight forward, some of it will show advanced techniques.
Topics to be covered:
- UserInfoGet.CreateADOConnection() Method
- Dynamics Continuum Integration Library and Pass-through Dexterity
- Dynamic User Object Store (DUOS)
But first, some more fields for our project. Since we will need to have a key for the additional field (Regulatory Agency) being stored, we will use the
Equipment Number as the key -- the assumtion is the Regulatory Agency is the same at the equipment level, regardless of the item assigned to the equipment.
1) Open the Equipment Maintenance window. Go to Cards > Field Service > Equipment
2) Press
Shift + F11 on your keyboard to activate the Add Fields... option.
3) Click on the
Equipment Number field.
4) Click on the
Save button.
5) Click on the
Delete button.
In addition, we will be introducing some professional grade code, so we will want to remove the exiting reference to the
SmartList project and remove the
LookupButton_BeforeUserChanged script created in our previous session.
1) To remove the reference to the SmartList project, open the
VBA Editor - Press
ALT + F11 on your keyboard.
2) Locate the
FieldService project in the
Project Explorer.
3) From the
Tools menu, choose
References...4) Unmark the reference to SmartList and save
5) Highlight the
LookupButton_BeforeUserChanged code in the VBA Editor and delete.
We now want to create our reference to the
FieldService project in our
SmartList project. We will expose some methods in our
FieldService project to facilitate returning the
Customer Number to the
Regulatory Agency field.
1) In the Project Explorer, locate the SmartList project and highlight.
2) From the Microsoft Visual Basic editor's Tools menu, select References...
3) In the References window, locate FieldService, then add a checkmark to indicate that objects in this project will be referenced. Use the Priority buttons (Up) to give the FieldService objects a higher priority. This will help improve your customization's performance.
Good to go!
NOW THE CODE!1) Open the VBA Editor - Press
ALT + F11 on your keyboard.
2) Locate the
FieldService project in the
Project Explorer.
3) In the
Code Editor, we will first start with some general definitions to support our project. Select
(General) from the drop-down list, next to the Project Explorer.
(General)
'Enforce explicit variable declaration
Option Explicit
' Used to stored and retrieve data from DUOS
Dim EquipmentCollection As DUOSObjects
Dim EquipmentObject As DUOSObject
Dim WantToDelete As Boolean
Dim EquimentIDToDelete As String
Public OkToDelete As Boolean
Public Deleting As Boolean
Public LinkedCustomerLookup As Boolean
4) We will now add some code to the
Equipment Number field. Each time this field changes, we need to check our
DUOS repository to see if there is a value in store. If so, we need to display it. In addition, we will need to make sure that we setup our
delete flags. These will let us know if it's ok to delete a record from the DUOS and if the user wants to actually delete the record.
EquipmentNumber_Changed
' Created by Mariano Gomez, MVP
Private Sub EquipmentNumber_Changed()
Set EquipmentCollection = DUOSObjectsGet("Equipment Information")
If Not EquipmentNumber.Empty Then
Set EquipmentObject = EquipmentCollection.Item(EquipmentNumber)
RegulatoryAgency = EquipmentObject.Properties("RegulatoryAgency")
' Lock the field if not empty
If Not RegulatoryAgency.Empty Then
RegulatoryAgency.Locked = True
Else
RegulatoryAgency.Locked = False
End If
Me.Changed = False
End If
' Setup up delete flags if we were able to retrieve something from DUOS
If Not EquipmentNumber.Empty Then
EquipmentIDToDelete = EquipmentNumber
OkToDelete = True
WantToDelete = False
End If
End Sub
5) Now, lets make sure everytime we save our equipment record, the value stored in our
Regulatory Agency field gets flushed to the
DUOS. We need to validate if all required fields have been entered.
Save_BeforeUserChanged
' Created by Mariano Gomez, MVP
Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' Save DUOS Data from Save Button
If Me.Required = True Then
EquipmentObject.Properties("RegulatoryAgency") = RegulatoryAgency
End If
End Sub
6) Deleting an object from DUOS is rather a 3-step process that will require capturing the user's intentions based on the reponse to the modal dialog question of whether they want to delete a record or not.
Delete_BeforeUserChanged
' Created by Mariano Gomez, MVP
Private Sub Delete_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' Flag Deleting
Deleting = True
End Sub
' Created by Mariano Gomez, MVP
Private Sub Delete_AfterUserChanged()
' Delete DUOS if Delete was confirmed on dialog
Deleting = False
If WantToDelete And OkToDelete Then
EquipmentCollection.Remove (EquipmentIDToDelete)
End If
End Sub
' Created by Mariano Gomez, MVP
Private Sub Window_AfterModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)
Select Case PromptString
' Save from Dialog (browse or close window)
Case "Do you want to save changes?"
If Answer = dcButton1 Then
If Me.Required = True Then
EquipmentObject.Properties("RegulatoryAgency") = RegulatoryAgency
End If
End If
' Confirm Delete
Case "Do you want to delete this record?"
If Answer = dcButton1 Then
WantToDelete = True
End If
Case Else
End Select
End Sub
: If you were following the
Translating Dexterity Applications series by
David Musgrave over at
Developing for Dynamics GP, you may want to look at some considerations for your region when using VBA in the article "
Handling Translation when Customizing with VBA". Especially, you can make your
Window_AfterModalDialog code language independent by using the advanced technique outlined in the article.
OUR CUSTOM FIELDS ALSO DESERVE SOME CODE!We can now begin to add code to our custom fields on the window, the Regulatory Agency field, the Zoom button and the Lookup button.
1) Since the Regulatory Agency field can be typed in by the user, we need to validate whether the customer entered exists or not. For this we will use the
UserInfoGet.CreateADOConnection() method to open an ADO connection to the company database. You can also use the Dynamics Continuum Integration Library and pass-through Dexterity to do some more interesting things like positioning the first record in the scrolling window to the closest match of what the user typed in the field.
RegulatoryAgency_BeforeUserChanged
' Created by Mariano Gomez, MVP
Private Sub RegulatoryAgency_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' If the user manually types in the customer number, we need to make
' sure it is a valid record.
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset
Dim Exists As Boolean
On Error Resume Next
'Retrieve an ADO connection for the current user
Set objConn = UserInfoGet.CreateADOConnection()
'Set the connection properties
With objConn
.CursorLocation = adUseClient
'Set the current database, using the IntercompanyID property
.DefaultDatabase = UserInfoGet.IntercompanyID
End With
'Create a command to check for the customer record
With objCmd
.ActiveConnection = objConn
.CommandType = adCmdText
.CommandText = "SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR = '" & Trim(RegulatoryAgency) & "'"
End With
Set objRs = objCmd.Execute
objRs.MoveFirst
If objRs(0) = "" Then
Exists = False
MsgBox "This customer does not exist."
RegulatoryAgency.Focus
Else
RegulatoryAgency.Locked = True
Exists = True
End If
End Sub
In order for this script to execute successfully, you will need to add a reference to
Microsoft ActiveX Data Objects (ADO).
2) Now, lets mix it up a bit. We will use
Pass-through Dexterity with the
Dynamics Continuum Integration Library to zoom into the Customer Maintenance window whenever a user clicks on the field's hyperlink.
ZoomButton_BeforeUserChanged
' Created by Mariano Gomez, MVP
Private Sub ZoomButton_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
'Dim CompilerApp As New Dynamics.Application
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String
' Create link without having reference marked
Set CompilerApp = CreateObject("Dynamics.Application")
Commands = ""
Commands = Commands & "call OpenWindow of form RM_Customer_Maintenance, """ & _
Trim(RegulatoryAgency) & """;"
' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If
End Sub
: The above technique is not supported by Microsoft. However, it shows the endless power and flexibility of the VBA environment when combined with other tools like Dexterity and Continuum.
3) Our Lookup Button deserves some really strong code, so we will use pass-through Dexterity to call the SmartList lookup. This allow us to do some positioning of the record being displayed in the lookup window, replicating Dynamics GP's exact behaviour.
LookupButton_BeforeUserChanged
' Created by Mariano Gomez, MVP
Private Sub LookupButton_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
'Dim CompilerApp As New Dynamics.Application
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String
LinkedCustomerLookup = True
' Create link without having reference marked
Set CompilerApp = CreateObject("Dynamics.Application")
Commands = ""
Commands = Commands & "open form Customer_Lookup {return to CUSTOMER_FIELD}; {Can't return to Modifier field}"
Commands = Commands & "call INITIALIZE of form Customer_Lookup, 1, 0, """ & Trim(RegulatoryAgency) & ""","""","""","""","""","""";"
' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If
LinkedCustomerLookup = False
End Sub
So, we are almost there.
1) Now our
Customer Lookup window needs to be able to return data to our
Regulatory Agency field. In order to do that, we will create a Public method in our FieldService project that will set the field value when returned from the by the lookup.
ReturnRegulatoryAgency()
Public Sub ReturnRegulatoryAgency(sCustomer As String)
' Set the field value with the returned value from the lookup
' Since we cannot reference our field directly with pass-through Dex
' this method is called from the SmartList dictionary to return the
' value when the Select button is clicked.
RegulatoryAgency = sCustomer
End Sub
2) Locate the SmartList project in the Project Explorer, expand and double-click on the DebtorandProspects(Window) object to open the code editor. Add the following code.
Customer Lookup code(General)
' Created by Mariano Gomez, MVP
Option Explicit
Dim LinkedCustomerLookup As Boolean
' Created by Mariano Gomez, MVP
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
LinkedCustomerLookup = FieldService.EquipmentMaintenance.LinkedCustomerLookup
End Sub
' Created by Mariano Gomez, MVP
Private Sub Select_AfterUserChanged()
If LinkedCustomerLookup Then
If FieldService.EquipmentMaintenance.IsLoaded Then
Call FieldService.EquipmentMaintenance.ReturnRegulatoryAgency(DebtorsandProspectsDetail.CustomerNumber)
End If
End If
LinkedCustomerLookup = False
End Sub
Note that the
Select_AfterUserChanged method calls our
ReturnRegulatoryAgency method in our
FieldService project. This is a more natural way of setting our field value.
DownloadsWorkshopDay3References.package - Contains references to Microsoft ActiveX Data Objects 2.6 - Click
here to download.
WorkshopDay3.package - Contains the actual code for the entire workshop - Click
here to download.
I hope you enjoyed the workshop. Please provide your feedback to the topics covered throughout. Let me know if you would like me to address other subjects in this format as well and what difficulties or inconveniences you experienced.
Until next post!
MG.-
Mariano Gomez, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
