Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Mail merge: Word/Access ok but Access/Word is problem

Discussion in 'Information Technology' started by Kevryl, Jul 28, 2006.

  1. Kevryl

    Kevryl
    Expand Collapse
    Guest

    Strange situation happening.

    I'm running Access 2000 and Word 2000. Have modified previously clumsy
    mail-merge system in customer database to operate more tidily from a menu
    form within Access. It worked before - from a menu form - but I have extended
    the Access Query that the Word document is based on to include user input
    parameters for year and "prior to" date. Here's the problem:

    Mail-merge document works fine and is linked to data-source ok if I call it
    up in Word. But calling from within Access (dynamic link within a command
    button on menu form) the document opens but all merge buttons in Word are
    greyed out and the document has not found the data-source.

    Directory structure: the document is one directory down from the database in
    the same tree.

    Anyone any clues as to why this is happening?

    Regards, Keith
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Armed Police Enter Sikh Temple In Birmingham During Protest - Daily Mail Breaking News Sep 12, 2016
    Sikh News Veteran Sikh Soldiers To Inspire Young - Malay Mail Online Breaking News Aug 1, 2016
    Sikh Gmail Calendar Questions and Answers Jul 5, 2016
    UK Daily Mail ISIS entrapment Breaking News Jul 7, 2014
    India India sets up elaborate system to tap phone calls, e-mail Breaking News Jun 20, 2013

  3. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

  4. Kevryl

    Kevryl
    Expand Collapse
    Guest

    Thanks Albert. The merge single record could be useful in future, but I don't
    see how I can do anything with your demo file for my current situation, and
    I'm about as green as grass when it comes to using VB.
    Keith

    "Albert D.Kallal" wrote:

    > You could give my sample word merge library a try. It is designed to be used
    > in EXISTING applications.
    >
    > It solves many problems, including that security, and also allows different
    > versions of word to be used..
    >
    > Give the word merge a try, you can find it here:
    >
    > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
     
  5. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    Hum, what I would do is remove the paramters from the query (did that ever
    work anyway?)

    Then, simply build a form that is UN-BOUND with two text boxes

    one text box is for year (call it txtYear)

    one text box is for "before date" (call it txtBeforeDate)

    The code to use my merge system would thus be:

    dim strWhere as string
    dim strSql as string

    strWhere = "(Year([InvoiceDate]) = " & me!txtYear & ")" & _
    " and (InvoiceDate <= #" &
    format(me!txtBeforeDate,"mm/dd/yyyy") & "# )"

    strSql = "select * from myTable where " & strWhere

    MergeAllWord strSql

    The above would do what you want. As for why you existing setup don't work,
    it not clear what code, or how you are launching word, nor is it clear how
    you are having word establishing a connection to the access data.

    The above is only 5 lines of code.....

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  6. Kevryl

    Kevryl
    Expand Collapse
    Guest

    Thank you Albert, very much. I expect thats the way I'll go, but right now
    I've run into a time problem and will have to defer a little.

    To answer your questions, I accessed the Word merge document from a command
    button on a menu form (not the "switchboard" but a form created to do what a
    "switchboard" does). The command button was installed from the toolbox with
    Wizard help, and I used "browse" within that wizard to select the merge file
    and set as a hyperlink.

    As to the other way around - accessing the database from the open Word
    document, that works fine. It was set up through the mail-merge wizard, first
    the databse being selected and when the dialogue box opened for the object to
    be selected, the parameter quesry was selected from the list provided within
    that dialogue box.

    Oh, and yes, if I remove the parameters from the query it works fine both
    ways. The first parameter was [Please enter year: (yyyy)] and the second was
    <[For dates prior to: (dd/mm/yyyy)]. These reference two different underlying
    fields representing the year of the program and the actual date of customer
    application, and function exactly as designed to when the document is called
    up in Word and Word does the data accessing. I'd really like to understand
    why what seems to be a standard enough process within Access's built in
    functions, uncomplicated by user-written code doesn't seem to work as it
    should. :-(

    Thanks for your help. Keith :)

    "Albert D.Kallal" wrote:

    > Hum, what I would do is remove the paramters from the query (did that ever
    > work anyway?)
    >
    > Then, simply build a form that is UN-BOUND with two text boxes
    >
    > one text box is for year (call it txtYear)
    >
    > one text box is for "before date" (call it txtBeforeDate)
    >
    > The code to use my merge system would thus be:
    >
    > dim strWhere as string
    > dim strSql as string
    >
    > strWhere = "(Year([InvoiceDate]) = " & me!txtYear & ")" & _
    > " and (InvoiceDate <= #" &
    > format(me!txtBeforeDate,"mm/dd/yyyy") & "# )"
    >
    > strSql = "select * from myTable where " & strWhere
    >
    > MergeAllWord strSql
    >
    > The above would do what you want. As for why you existing setup don't work,
    > it not clear what code, or how you are launching word, nor is it clear how
    > you are having word establishing a connection to the access data.
    >
    > The above is only 5 lines of code.....
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
     

Share This Page