Workshop Day 3 - Adding the VBA code to the project

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

Delete_AfterUserChanged

' 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

Window_AfterModalDialog

' 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

NOTE: 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

NOTE: 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

NOTE: 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

ALMOST DONE!

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

Window_BeforeOpen

' Created by Mariano Gomez, MVP
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
    LinkedCustomerLookup = FieldService.EquipmentMaintenance.LinkedCustomerLookup
End Sub

Select_AfterUserChanged

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

Downloads

WorkshopDay3References.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/