Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / Report Writer VBA
Microsoft Dynamics GP forum
Answered

Report Writer VBA

(0) ShareShare
ReportReport
Posted on by Sunil Chhabra 1,930

I have a VBA created for a report that extracts info from a SQL and table and works fine. I wanted to test it to bring in another field and I cannot understand why the VBA is failing

The message translates into that the column HOURS_I is invalid. But the column exists. If I run the query in Studio Management I correctly get a single value.

Not sure why the error.  Another puzzling thing is it works with the original SQL Field called compbal.  I just replace the query/table and want to insert the value to HRAttendVacUsed calculated field. There is no issue with calculated field - not sure why it is erroring out on HOURS_I column.

pastedimage1618283423634v1.png

Thanks

Categories:
Development / Customization / SDK
  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,831 Moderator on at
    RE: Report Writer VBA

    You can use the same connection to run multiple queries.

    Or you can return multiple columns in the one query.

    The reason you are failing with the code is that there is no HOURS_I column in your query.  It would be just an untitled column.

    You need to use sum(HOURS_I) as HOURS_I to give the aggregated sum column a name.

    This article might help

    https://winthropdc.wordpress.com/2008/10/29/using-ado-with-vba-with-report-writer/

    Also if you want to move away from VBA which is now end of life, you can use GP Power Tools instead:

    https://winthropdc.wordpress.com/2019/08/12/gppt-replacing-vba-with-gp-power-tools-developer-tools/

    Regards

    David

  • Sunil Chhabra Profile Picture
    Sunil Chhabra 1,930 on at
    RE: Report Writer VBA

    Thanks Sandip - How would I add 1 more field.

    I have 2 calculated fields. The first one is populated using the above vba script. But how do I populate a second one. Do I need to create another Subroutine?

  • Verified answer
    sandipdjadhav Profile Picture
    sandipdjadhav 18,252 on at
    RE: Report Writer VBA

    Hello,

    May be you need to put "Select sum(HOURS_I) as HOURS_I"  , "As HOURS_I" required?

    Thanks much

    Sandip

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans