Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Avoiding VBA Circular References when using a Dynamics GP Lookup

Avoiding VBA Circular References when using a Dynamics GP Lookup

Rate This
  • Comments 4

Patrick Roth - Click for blog homepageHonestly, 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

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, http://opensource.org/licenses/ms-pl.html.)

Attachment: CustomerClassLookup.zip
  • Posting from Mark Polino from DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../avoiding-vba-circular-references-when.html

  • Hi

    How can i write a VBA code in "post" button?

    For Example: After posting process completed i just want to display a message "Thank you".

  • Hi Pragadees

    As VBA is unable to "see" or add events to the underlying code (procedures and functions), you will find it difficult to trigger an event after the posting background process is complete.

    If the posting process prints a report when it completes, you could trigger on the Report_Start() event and about the report and then display your dialog.

    Dexterity and Visual Studio Tools can see the procedures and functions and could be used instead.

    David

  • Thanks for the clear explanation David.

Page 1 of 1 (4 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post