Avoiding VBA Circular References when using a Dynamics GP Lookup
Honestly, I struggled a bit on the title of this article.
Is this article to show one way to use a Dynamics GP lookup window in your Modifier/VBA customization? Or is it an article that shows a way to work around the circular reference problem that you can get yourself into when using VBA across projects in Dynamics GP?
I guess it is both and to cover the bases for our readers to find this article I chose a title that conveyed both subjects.
As I've created windows in VBA over the years and now using Visual Studio Tools to do the same, I find one of the features that I really like in Dexterity is how the lookup windows work.
In Dexterity, I issue the command:
open form 'lookup form' return to 'my local field';
On on the lookup window - typically the 'Select Button', it is usually coded simply as:
return 'TheLookupField';
Very simple for the programmer to write and Dexterity keeps track of the "return" field for me without any extra work. When the return statement runs on the select button, the data is returned back to the "return to" field as if it was entered by the user. If the calling form was closed before the item in the lookup was selected, the field simply returns back to nowhere without errors.
Making your own lookup windows in VBA or Visual Studio Tools isn't quite the same. While not horribly difficult, it is extra work to keep track of the calling form and return to field in your customization. The same would hold if you are trying to return data back to a Dynamics window field.
A common situation that developers try to do is add an extra field to a Dynamics GP window. This extra field is very often a customer, vendor, item, etc that has a Dynamics advanced lookup (in the Smartlist dictionary) for the data. While a bit of extra work, it would be possible to write your own lookup window using VBA or Visual Studio Tools but that introduces a new lookup window that isn't what your users are already familiar with.
The decision is made to leverage the Dynamics lookup window of interest.
Easy - you just mark the reference to Smartlist in your VBA project and write VBA code to open the lookup window from your lookup button. Then in VBA in Smartlist, mark a reference back to Microsoft Dynamics GP and then you get the error:
Cyclic reference of projects is not allowed
Uh, oh - so much for that plan. Now we have a problem. Since we don't get the automatic return functionality like Dexterity gives us, how exactly are we going to set the field of the GP (or other project) from our Smartlist window code?
I had thought about the way that Dexterity "knows" where to return data back to. Obviously it isn't magic, of course Dexterity keeps track of the return form/field somewhere. So could we do something like that in VBA as well?
After a bit of thought, an "object" seemed like the best solution. Objects can be anything and don't care where they come from or what they reference.
So by setting a public variable on the lookup window, my plan was to use that field as the return variable.
Because the Smartlist VBA code isn't referencing the Microsoft Dynamics GP project directly, we don't have to mark a reference back to it to set the field. The variable does all the work.
So now we have the background, the problem, and the goal. But does it work? Yes, it works great and I think opens up some other possibilities that I haven't explored yet.
For the project, I modified the Dynamics GP Customer Maintenance window. I added a new lookup button for the User Defined 2 field for convenience so that Dynamics saves the data for me into the User Defined 2 field. To avoid data evaluation code on User Defined 2, I disabled the field with VBA to make sure the user has to use the lookup button.
The code is actually pretty simple- I pasted it below and have commented it to explain what each piece does. I've also included a package file for Dynamics 10.0 that I would assume would work on any GP version. As I modified the customer maintenance window to add the lookup button, you'll need to give yourself access to the modified window for this to work.
Smartlist lookup form Code Example
Option Explicit
Public ReturnField As Object 'this is a reference to the calling window field
Private Sub Select_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
'If the reference is set, then set the return field to the selected
'lookup window return field. In this case, the Customer Class ID
If Not (ReturnField Is Nothing) Then
ReturnField.Value = CustomerClassesDetail.ClassID
End If
End Sub
Private Sub Window_AfterClose()
'After the window closes, clear the return field
'This makes sure that it is cleared if the user
'pressed Select or Cancel
Set ReturnField = Nothing
End Sub
Custom lookup button Code Example
Option Explicit
Private Sub UserDef2PB_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
'Set the lookup window reference to the field we want to return back to
Set SmartList.CustomerClasses.ReturnField = UserDefined2
'open the lookup and set the hidden sortby field which will fill the window for us
SmartList.CustomerClasses.Open
SmartList.CustomerClasses.SortBy = 1
End Sub
In this example, the object reference is set to the window field itself. However there is no reason that the reference could not be set to the window itself if desired. This would be useful if you wanted to set multiple fields for instance. Or even call a procedure from the calling form.
While I've used an example of a lookup window for this trick, there isn't any reason why it would not work in any other circumstance where you run into circular references. The only requirement is that at some point you can get the public object variable set from one project to the other project.
Just another trick of the trade that hopefully will benefit the readership.
Best Regards,
Patrick Roth
Microsoft Dynamics GP Developer Support
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)