Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Master-Detail merge (2000)

    I am working on a program within my company. I am having a major problem and it probably comes from my inability to think like VBA/Access wants me to think.

    One of the reports is a "Confirmation letter" that is a merged file from Word. All I need to do is display the detail items of my master report at the bottom of the letter (i.e. in a PS statement). Like this

    <Name and Address from Master Table>

    [Body of letter in Word .doc file]

    PS You have registered the following people:
    <List of people from Detail Table. Quarenteed to have at least one detail per master. Most likely will have at least two (this is where the problem comes in)>

    How can I do this? In a report, it would be a simple subreport. Word is limited in the number of sources (1), so somehow I have to create a new table/query that has all the information in one record per Master. Can this be done?

    One of my thoughts was to make a new field in the master and populate it on the fly by stepping through the detail records. I can't figure out how to do this in VBA. Any thoughts on any solution?

    Thank you,
    Frank Luke

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master-Detail merge (2000)

    Frank,

    Have a look at the two Word Automation Articles (article 2 in particular) here. THis may point you in the right direction.

    It's not A Word merge, but more flexiable IMHO. (Yes I did write the articles)
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

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

    Re: Master-Detail merge (2000)

    Are you doing this from Access or from Word? In other words, which application do you need help in? If it's Word, you're in the right place. If it's Access, let me know and I'll move this thread to the Access forum.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master-Detail merge (2000)

    Charlotte,

    I need to write a query in Access and then connect the results to a Word merge document. I can connect them. The problem is writing the Access query or VBA code to accomplish what in IMHO should be a simple task. So, it is the Access side I need help on. I thought this might be the right place because I sould do it in the VBA portion of Access if I knew the right commands.

    Carbonnb,

    I'll definitely read those two articles by this afternoon. Thank you.

    Thank you,
    Frank Luke

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master-Detail merge (2000)

    Frank,

    If you have any questions, about them, please feel free to ask.

    Hope they help,
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Master-Detail merge (2000)

    Hi Frank,
    A key question here is whether you are running mulitple documents in addition to wanting to have multiple recipients on the bottom of the page. If you are mergeing one document at a time, you can use a catalog merge to accomplish that. If however you are trying to multiple documents at the same time, life gets a fair bit more complicated. Either you need to run a query that collects multiple attendees into a single record - which isn't simple, or you need to use the technique Bryan suggests, which builds a word document more or less from scratch using Automation. In addition to the references he suggests, there is a pretty good description of this in the Access Developers Handbook by Getz, et al. The technique is not for the faint of heart (as far as VBA is concerned) however, as you need to understand the Word object model pretty well. Hope this helps.
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master-Detail merge (2000)

    <hr>or you need to use the technique Bryan suggests, which builds a word document more or less from scratch using Automation<hr>
    Actually the second article doesn't create the document from scratch, although that is entirely possible as well.

    In a nutshell what you do is create a boilerplate document, with bookmarks, instead of merge fields, and use automation to stuff the data into the document at the bookmarked locations, instead of into merge fields.

    It uses a doc as a base, but you could also use a dot as the base as well.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

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

    Re: Master-Detail merge (2000)

    Just so you know for future reference, we speak VBA in the Access board, but with a funny accent. VBA for Access is a little different from VBA for the rest of Office because Access is a database and doesn't use the same forms and controls that the rest of Office and VB use. For that reason, it's usually advisable to post your Access VBA questions in the Access board. More people will see it there and you may get faster answers.
    Charlotte

Posting Permissions

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