Tuesday, December 28, 2010

GP 9 Year End Update Causes Blank Report Writer Sections when using VBA

Last month a client asked me to make a few modifications to their Dynamics GP 9 Purchase Order report.  Because Report Writer is such a pathetic tool (I don't care what Mr. Musgrave says ;-), I had to use VBA to add some fields to the report.

And because the client is still using GP 9, I had to use RetrieveGlobals9.dll to query the GP company database directly.  Not a problem, I finished the modifications and everything worked fine.

Then last week I get a call from the client saying that their PO report isn't printing properly.  They explain that edges of the report are cut off, and the bottom 2 inches of the report are also being cut off.  Based on that explanation, I suspected there was a printer driver issue.  But the problem occurred on all workstations with multiple printers.

To see first hand what this looked like, I setup a GoToMeeting session with the client and we printed to a PDF file.   Sure enough, the right edge was cut off, and the bottom of the PO was blank.

Here is some additional info:

1) When printed to screen, the report will display properly and look fine
2) When printed to screen, and then sent to the printer, the report will print properly
3) When printed directly to a printer, the PO will have the right and bottom edges cut off
4) This problem seems to have occurred after installing the GP 9 2010 YE Payroll Update (KB2435598, version 9.00.0386)
5) The problem happens consistently on multiple workstations and multiple printers

I performed tests on my development server, but was unable to reproduce the issue.  I then installed the GP 9 2010 YE update.  Sure enough, I was then able to reproduce the problem.

At first glance, it appears to be a printer driver issue, since the report formatting gets messed up.  After moving fields around to fix the margins and prevent fields from being cut off, I then performed dozens of tests to figure out why the bottom of the report was still being cut off.

After a few tests, I realized that the sections weren't being cut off, they were actually being suppressed completely.  And it appeared that the sections being suppressed were the two sections where I had VBA code.

Once I discovered the VBA connection, I added some breakpoints in my code to see if there was an error that was causing the VBA to fail and somehow cause the section from printing.  Even though the report printed to screen fine, perhaps there was a VBA error when printing directly to the printer.  But after stepping through every line of code without an error, yet the sections still did not appear, it didn't seem to be a VBA error.

So then I tried removing all of my VBA code from the report.  After I did that, the missing report sections printed properly, albeit with blank custom fields.  So then I decided to see exactly which line of code was causing the section to fail.  After several tests, I narrowed it down to this line:

Set objUserInfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")

It appears that the 2010 Year End update introduced some type of bug or issue that will prevent a report section from printing if it involves a VBA reference to Retrieve Globals 9.

Here is an example of what the PO lines should look like:

But here is what it looks like after the year end update is installed and the VBA code calls RetrieveGlobals9:

So if you happen to be lucky enough to have a client on GP 9 that has modified reports with VBA and needs the 2010 year end update, be on the lookout.

I have a support case open to see if Microsoft has any thoughts on this issue.

UPDATE:  Microsoft Support responded very quickly, indicating that this is a known bug, discussed in KB Article  884601.  Apparently this issue has been around for a long time (it seems to have existed with GP 7.5 and 8), but based on my experience with GP 9, it doesn't always occur--it just happened to start after the customer installed a year end update.  The support rep indicated that because the issue is relatively easy to work around, there are no plans to fix the problem, at least with GP 9.  I don't know if the issue exists with GP 10 or GP 2010.

KB884601 offers two workarounds.  First, you can switch the report from a graphical format to a text format.  Obviously, this is not a viable option for the vast majority of reports, and text reports are horrifically ugly.  Scratch that one off the list.

The second option is to restructure your VBA to change the scope of RetrieveGlobals and ADO objects.  But, the support rep indicated that this option may not resolve the problems for all reports.  First, you need to move the RetrieveGlobals and ADO variable declarations to the General Declarations section of the report.  You then only instantiate RetrieveGlobals and your ADO objects in the Report Start event.  Finally, your report section event code will make use of the shared objects and variables.

These code changes appear to have resolved the issue on my development server--I am now able to print the report directly to printer without any issues.  I am waiting to schedule the installation of the new report package on the client's server, but I am hopeful that this will resolve the problem for them.

Of course, if that still didn't work, the final workaround would be to print the report to screen first, then print to printer.  This is apparently considered an "easy" workaround, hence the lack of interest in fixing the problem.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.


1 comment:

Unknown said...


I just wanted to say that I really enjoyed your blog and this post. You make some very informative points. Keep up the great work!

Custom Dynamics GP