I've run into this several times, but I never bothered to figure out the cause or solution.

In GP 9, sometimes when you attempt to add new fields to…

This has come up time and time again on message boards I frequent on the web. People are always asking how they can access SQL Tables and other objects through VBA to extend GP. It's pretty simple to add a field to a window with Modifier or create a user form in VBA but many seem to wonder how to access the database to really make things happen.

You can find all you need to know on Customer/PartnerSource. I'm only disclosing the same information here and a little of what I have learned to complement that information.

When Microsoft Dynamics GP 9.0 was released, a change was made in the password policy. This change required changes in how Microsoft Dynamics GP Dexterity works with passwords and in how the RetrieveGlobals.dll file works with passwords. The RetrieveGlobals.dll file was available for all earlier versions but does not work with Microsoft Dynamics GP 9.0. Therefore, you must replace the RetrieveGlobals.dll file with the new RetrieveGlobals9.dll file. The new RetrieveGlobals9.dll file is an ActiveX file. It returns the following information:

•The current user ID
•The current company to which you are logged in
•The current SQL data source
•The current user date in Microsoft Dynamics GP 9.0

The RetrieveGlobals9.dll file also returns an ActiveX Data Objects (ADO) connection object that lets you connect to Microsoft Dynamics GP data. The…

Patrick Roth looks to keep you from chasing your tail by Avoiding VBA Circular References…

Such a weird issue, but since it's said to be known, we can't do much about it. But David…

I've been asked several times to add custom lookups to user defined fields in GP.

Clients want to utilize user defined or custom fields, but they want to have the values validated, and they want to allow the user to select a valid value from a list. Makes sense.

I briefly checked out the drop down and list box controls available in Modifier, but they are pretty limited, and aren't really oriented towards displaying dynamic result sets from a database query.

So I ventured out and created my own custom lookup window in VBA.

As a side note, my biggest gripe with the standard GP lookup windows is their lack of a simple, "real-time" search field. By "real-time", I mean that I want the lookup window to instantly filter the displayed records based on what I type in the lookup search box--as I type it. And I want to have the window simultaneously search multiple fields at the same time--not just one field. For standard GP lookup fields, Rockton SmartFill does an excellent job in this regard, but sadly, in my experience, few clients have been willing to pay for SmartFill.

I'm going to show an example of a lookup that displays Sales Prospect records. In this case, the client called prospects "End Users", so it is called the "End User Lookup" window.

I first created a custom User Form in VBA, and added a Search field at the top. Because VBA is pretty limited in terms of controls, I had to use a ListBox to display my results. Finally, I have a Select and Cancel button.



When the form loads, I utilize UserInfoGet to have it query the GP company database and retrieve all prospects. I store those prospects in a client-side disconnected ADO recordset so that they remain in memory. I then have a simple procedure that displays the recordset results.



When a user types in the Search box, I send the equivalent of a SQL WHERE clause to the ADO recordset Filter property. This dynamically changes the records available in the recordset, allowing me to quickly redisplay only matching records as the user types each character.



As you can see in this example, when I type "wav" in the search box, I am simultaneously searching on the ID, name, and city of the prospect records,…

David Musgrave knocks it over the wall with this fun, yet powerful customization that shows the endless possiblities of Dynamics GP's Visual Basic for Applications. For those of you who are not quite familiar with the first four or five releases of GP, upon launching the application, a WAV…

Fellow MVP Leslie Vail has a look at switches in the DEXVBA.ini file in Dynamics GP.

 

In keeping with my ‘.ini’ switch theme I would like to keep track of entries we can make to the DEXVBA.ini file. I currently have only two. I hope you will comment here if you have any others and I can add them to the list. I have titled this ‘Part 1’ in hopes that I will get additional settings so that I may post a ‘Part 2’.

The DEXVBA.ini file is not created by default. You will need to open Notepad (or similar text editor) and…

Dynamically Get GP database connection information when using VBA:

'Class Module: GP Dex_VBA_Link
'Author: Matt…