Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating One-to-Many Reports in VBA (Win2KPro, Office 2KPro SP-2)

    I was working on creating a one-to-many mailmerge in Word from an Access database and in the course of trying to do this ended up learning a method for doing not only simple one-to-many reports but potential much more complex 'drill-down' style queries for reports. This method can be done generically in any application that supports the provider MSDatShapes. I ended up using ASP in a webserver to output my results to an .rtf file but you should be able to do the same thing internally in Word. One problem I had with using Word for this is that my workgroup enviornment shares Normal.dot and this can be a pain when trying to work with a macros. In addition, I've a lot more experience using recordsets in ASP and enjoy the less 'finicky' development environment of ASP. However, the solution to this sort of thing should work fine in the VBA development enviornment.

    The attached Word document goes thru the general method of invoking MSDataShapes, creating a MSDataShape SQL statement with generic example LOOPing code for paging thru the MSDataShape recordset objects.

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating One-to-Many Reports in VBA (Win2KPro, Office 2KPro SP-2)

    Steve,
    Nice piece of work. Very lucid, concise and helpful.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating One-to-Many Reports in VBA (Win2KPro, Office 2KPro SP-2)

    thanks! as you might imagine, when i started working on the basic problem i had no idea what it would eventually involve, but in this case the discovery of Data Shaping was downright cool & I thought people should know about it.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating One-to-Many Reports in VBA (Win2KPro, Office 2KPro SP-2)

    Data Shaping is extremely powerful but it is also rather esoteric. Regular SQL is bad enough for most people. I doubt that data shaping is going to become *really* popular! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating One-to-Many Reports in VBA (Win2KPro, Office 2KPro SP-2)

    Yes it is a bit more confusing than SQL. My biggest problem with using Data Shaping was not so much figuring out how to make it work (not that that was a piece of cake) but that, in testing, a malformed Data Shape string can easily consume all your memory and cause you to have to reboot. I got around this obnoxiousness by testing in Netscape rather than IE: Netscape would choke and I could kill it and free up resources; if IE chokes rebooting is hard to avoid. Messing with Data Shapes is one of those things that can kill Win 2000, an otherwise remarkeably stable OS. I suspect even *thinking* about Data Shaping on a Win98 box would cause to collapse...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •