Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Print Word document substituting fields from query (Access 2003 SP2)

    I have a need to print a document from Access that takes 3 fields from a query and puts them into the document and prints the document. Each record from the query will print the document.

    How do I go about this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print Word document substituting fields from query (Access 2003 SP2)

    Have you tried a mail merge?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Print Word document substituting fields from query (Access 2003 SP2)

    Actually I remembered an old application that had this and will try this and see if it's applicable.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Print Word document substituting fields from q

    <P ID="edit" class=small>(Edited by hasse on 22-Feb-06 16:22. Clarification)</P>Patt...
    if you don't find it, let it know.
    There have been some threads about this earlier at the Lounge.
    Further, I adapted a (97) solution from another forum too which you might be able to recycle. It uses automation to create a new document for each record (not so in a regular mail merge). But I need some work to 'clean it out' first <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>. Moreover, it uses bookmarks instead of fields but I guess that's not too easy to modify. It has a query with the data to be used, a table with template info (location,...), bookmark info (which query field equals which bookmarks, for each template) and a form including the merge procedure.
    Hasse

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Print Word document substituting fields from q

    I am interested in what you have.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Print Word document substituting fields from query (Access 2003 SP2)

    Hi Hans, I wonder if you would take a look at this and let me know if it is up to date enough when using Office 2003.

    It has references to 2000, so I was just wondering.

    I also notice when I run it it displays "DDE.." in the status box. I thought DDE was an obsolete technology.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print Word document substituting fields from query (Access 2003 SP2)

    DDE can be used, but it has some disadvantages: it opens a visible instance of Access, and if the database title doesn't begin with "Microsoft Access", a new instance will be opened each time the data source is accessed. Ont he other hand, DDE can handle parameter queries and queries using user-defined functions, which ODBC can't.

    Your code uses ODBC if the sSQL string begins with SELECT and it uses DDE if the string begins with TABLE or QUERY. If you want to use ODBC in the latter case too, you could change sSQL to "SELECT * FROM <whatever>" where <whatever>" is the name of the table or query (the second part of sSQL)

    The reference to wdMergeSubTypeWord2000 is necessary to avoid getting a prompt for the data source.

    There is a third method, OLE DB. This recognizes fewer queries than DDE or ODBC, however.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Using Bookmarks - Re:Print Word... fields from qry

    I've included in attachment an enhanced XP version with template sample and some documentation (objects used, remarks & constraints, todo's).

    It shows how you can merge a query with a Word template using bookmarks:
    - create the data source (query);
    - create a 'template' (word document) including a bookmark at each place where a variable (= data source field's value) should appear;
    - enter in tblMergeTemplates the template's file name, path name and the data source (query) containing the data to be merged;
    - enter in tblMergeBookmarks the names of the template's bookmarks & corresponding data source fields;
    - using the form's controls, select the template you want to use and which field must be used as a variable for the resulting files' names... and click the 'run' button.

    As you'll notice in for instance the procedure & notes, this isn't really a finished masterpiece. So I'ld rather present it as a draft in the sense that it works enough for me <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>... but some issues remain unsolved which might be important to others <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.

    Please let me know if it was of any use.
    And all feedback is appreciated... (so I might use it in a next version - you never know).

    Hasse

    ps fwiw: Helen Feddema just has made a contribution to Woody's Access Watch #7.24 & #8.01 (Access Archon #141) about "Matching Word Document Properties with Access Fields". I had no time to grasp it really, but at first sight it looks quite much like an alternative solution for the same problem which is far more professional... but with most variables hard coded, so less flexible than what I've come to. Please correct me if I'm wrong... And any thoughts about the (dis)advantages of both approaches are welcome.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Using Bookmarks - Re:Print Word... fields from qry

    I have been using bookmarks for some merges in a few projects for a while. I use them when the job can't be done with a standard merge. For example I might need to merge data from several different data sources. e.g One query selects all the people who do some sessional teaching. Another query selects selects all the actual teaching allocations for each of these people. In this case I want to write a letter to each person, and include in it a table listing all the teaching they are doing.

    One thing that has gone wrong in a couple of places is that when the users modify the templates they use ( and I need to allow them to do that) they delete some of the bookmarks needed for the merge. To try to stop this I make sure the bookmarks are visible:

    wrd.ActiveWindow.View.ShowBookmarks = true

    Also before trying to use any particular bookmark check that it exists

    If objDocument.Bookmarks.Exists("Returndate") then
    etc

    I am aware of using document properties instead, but I have not got around to looking in detail at how to do it. It could have the advantage of making it harder for the users to delete them.

    I usually keep all the templates in a templates subfolder of the folder holding the backend data file. So my list of templates is just a list of all templates in that folder. this way I don't need a templates table.
    Regards
    John



  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Using Bookmarks - Re:Print Word... fields from qry

    Thanks John and Hans (Hasse) and Hans
    I will muck around with all this and see how I go.

    Good point John re different sources of data.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Using Bookmarks - Re:Print Word... fields from qry

    FYI... 'in between' I'm trying to improve the file with (i) option to merge one record (inspired by Helen Feddema's contribution to WAW) and (ii) validation (in two directions) of bookmarks & fields: are all present (but also no more than those with a related bookmark/field). Will post an update when it's ready, later.

    John, just curious: do your users also have access to the database and 'compose' their queries themselves too?
    Hasse

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Using Bookmarks - Re:Print Word... fields from qry

    <P ID="edit" class=small>(Edited by johnhutchison on 02-Mar-06 22:21. (oops I put in the wrong picture))</P>Hasse

    I don't give users access to the database to compose their own queries, but I might provide a form with a number of options, then use the selected options to build an sql statement in code . An example shown below would allow users o write to all current GPs at Deakin University.


    Another thing I will add while here. Another time I use bookmarks is when I want to write to just a single person. A standard merge takes quite a while to get started, but then runs pretty quickly. It is pretty efficient if there are lots of records. But for only one record, the bookmark method is much faster.
    Regards
    John



Posting Permissions

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