Saturday, March 7, 2009

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/

8 comments:

Jaime Orlando said...

Hi Mariano the WorkshopDay3References.package isn't on the page.

Mariano Gomez said...

Jaime,

Thanks for your inquiry! I will be posting the package files later today. Stay tuned, but in the mean time work on the code. :-D

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

manikut said...

Hi

I have just seen your code and wanted to try out the same. I have used my SOP Entry screen to have a field for Salesperson ID and i populated successfully by adding reference to Smartlist and showed up the window for Salesperson Id lookup. Now, when i want to pass this to SOP screen, while compiling the smartlist, since i have referenced as Microsoft_Dynamics_GP.SalesTransactionEntry.SRID, it gives me a compie error that the variable Microsoft_dynamics_GP not defined. Can you help me with this please.

Mariano Gomez said...

Manikut,

Thanks for your inquiry. You may want to revise the project name. In some systems it may appear as Microsoft_Dynamics_GP in some others it may appear as Microsoft Dynamics GP (note the spaces), and in some others it may appear as MicrosoftDynamicsGP.

Best regards,

MG.-
Mariano Gomez, MVP

Anonymous said...

Hi Mariano,

In the next topic you do, do you think you can go over how to set the window properties via vba?

Example, I use a table to set if my code is to execute or not to avoid having to use modified forms sometimes and I would love to be able to disable the close button for example so the user has to use the ok button?

Cheers Arthur

Pragadeeswaran P said...

Hi Mariano,

How can i write a VBA code in cash receipt "post" button?.
For Example:
After the posting process completed just i want to display a message "Thank You".

Thanks,
Pragadees

Mariano Gomez said...

Pragadeeswaran,

There's no decent way to set a procedure trigger with VBA. You can use the Continuum Library to add a focus trigger, database trigger, or even a form trigger, but not a procedure trigger.

What you are requesting seems to be more suitable for Visual Studio Tools or Dexterity, but not VBA.

MG.-
Mariano Gomez, MVP

Pragadeeswaran P said...

Thanks a lot mariano