Page 1 of 4 123 ... LastLast
Results 1 to 15 of 60
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automated Mailmerge from Access (Access 2000)

    I would like a command button to perform this action for me ----- when i click on a specific query, then click on the office links icon and tell it to merge and link to existing document in word...is there a way to write a macro or clip of vba or an sql string to accomplish this task. I would like the command button on my main form, so a user who does not have access to the design view of my database objects can just click this command button, it merges the specified query to existing word document.

    I have been reading the methods written here so far, and none I see answer that in particular.
    Thank you,
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Automated Mailmerge from Access (Access 2000)

    If you want to hide the design of the database from the user, how do you want to let the user specify a query to be used in the mail merge?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Mailmerge from Access (Access 2000)

    With a macro or module attached to a command button from a main form

    Have tried a hyperlink to open doc and then a command button to run query...but that's no good, because I need a merge and I get that by using the Office links icon at this point...But they won't have access to the query icon if they cannot get into design view...so thought there might be a way to make it all go together with a command button and can see several sites I've visited that have done it one way or another, but seems like a lot more then what I need on each site...hmmmm ... It is not imperative I guess. But if anybody knows a way, please hollar! [img]/forums/images/smilies/smile.gif[/img]
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Automated Mailmerge from Access (Access 2000)

    Hi again,
    You might find our Word Merge Tutorial of value - it also contains a link to the WAW article that preceeded it. You must of course set some limits on how flexible the query can be - the Word Merge Document has fields embedded that have to match to the query. It is possible to actually allow a user to build a query without exposing the database container, but it isn't trivial. I would look at parameter queries as a alternative, though they don't work with ODBC or OLE-DB based merges. Also be aware that Word 2002 significantly changed the way that mIl merges work. If you want to pursue this further, it sounds like a fun thread!
    Wendell

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

    Re: Automated Mailmerge from Access (Access 2000)

    On second thought, you might find that Automation would do what you want with less complications - it's an alternative to many mail merges, and often faster. See our website for an introductory Automation Tutorial as well.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Mailmerge from Access (Access 2000)

    Wendell,

    This code came from the last tutorial you gave me...

    ***********************
    Option Compare Database

    Function MergeIt()
    Dim objWord As Word.Document
    Set objWord = GetObject("A:MergeLetter-PymtDue2.doc", "Word.Document")
    ' Make Word visible.
    objWord.Application.Visible = True
    ' Set the mail merge data source as the Example Database
    objWord.MailMerge.OpenDataSource _
    Name:="A:Example.mdb", _
    LinkToSource:=True, _
    Connection:="QUERY qryMonthlyBillingMerge", _
    SQLStatement:="SELECT * FROM [qryMonthlyBillingMerge]"
    ' Execute the mail merge.
    objWord.MailMerge.Execute
    End Function
    ***********************

    The above is the exact code with the exception of me asking for my database, and my own already existing word merged letter called, A:MergeLetter-PymtDue2.doc and using my Example.mdb

    The only problem...it did run in the intermediate window...and it did bring up a merged letter (1)...but this is a parameter query I am using, and it did not bring up the 2 boxes the user is to fill in before executing the mailmerge...I have a parameter query that uses: Between[Enter first date] and [Enter last date]
    So believe the parameter part is throwing this off....is there a way in the code above to tell it to continue to execute the parameter questions the query asks before sending to word...because it gives me this error message as well....am enclosing the picture of error message...
    Thanks for the great tutorial...its just what I needed with the exception of this parameter prompt not appearing and the merge going straight to word without waiting for parameter prompts to fill in, and this error picture I'm sending appearing...
    NMP
    Attached Images Attached Images
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Automated Mailmerge from Access (Access 2000)

    A document on A: ? You should NEVER directly open a document on diskette!

    As Wendell mentions in his tutorial, I use a slightly different method for parameter queries:
    - Put the code from the attached text file into a standard module.
    - Check your ODBC Data Sources control panel to check the exact name of the user data source for Access; this name should be used in the DSN=... part of the code.
    - Open qryMonthBillingMerge in design view.
    - Remove "Between [Enter first date] and [Enter last date]" from the criteria line.
    - Close and save the query.
    - Create a form with text boxes txtFirstDate and txtLastDate, and a command button cmdOK.
    - Set the Format property of the text boxes to a data format, e.g. Medium Date.
    - Create an On Click event procedure for the command button:

    Private Sub cmdOK_Click
    Dim strDoc As String
    Dim strSQL As String

    If IsNull(Me.txtFirstDate) Then
    Me.txtFirstDate.SetFocus
    MsgBox "Please enter a first date.", vbExclamation
    Exit Sub
    ElseIf IsNull(Me.txtLastDate) Then
    Me.txtLastDate.SetFocus
    MsgBox "Please enter a last date.", vbExclamation
    Exit Sub
    ElseIf Me.txtLastDate < Me.txtFirstDate Then
    Me.txtLastDate.SetFocus
    MsgBox "The last date cannot before the first date.", vbExclamation
    Exit Sub
    End If

    ' Please don't use a document on diskette
    strDoc = "A:MergeLetter-PymtDue2.doc"
    ' Replace DateField by the correct field name
    strSQL = "SELECT * FROM qryMonthlyBillingMerge " & _
    " WHERE DateField Between #" & Me.txtFirstDate & _
    "# And #" & Me.txtLastDate & "#"

    StartWord strDoc, strSQL
    End Sub
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Mailmerge from Access (Access 2000)

    Hans,

    Thank you.
    I have printed your directions and will try this today.
    Sounds like quite a few steps, but worth it, so will let you know how it goes.

    As always, always grateful to you folks!!!! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Mailmerge from Access (Access 2000)

    Hans

    I feel bad, but where do I check the correct ODBC Data Source control panel for the user data source for access?
    This is the code I have changed...not so sure I did it properly, and am still missing the SQL line statement I have in red...this is where I would put the ODBC info?

    Attaching changed code......................

    NMP (Sorry to drag this on, but am still a "beginner" in VBA...)
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Automated Mailmerge from Access (Access 2000)

    It is one of the items in Start | Control Panel. Depending on the Windows version, it can be named "ODBC Data Sources" or "Data Sources (ODBC)" or something like that.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Mailmerge from Access (Access 2000)

    Okay,
    It keeps saying invalid use of Me word...I put frmMain there because that is the name of my form I have the txtfields on and the OK button.
    Am attaching...would you take a peek...
    At one point, after removing some code...(I put it back)
    I did get to "Be patient, word is loading" in the status tray, but nothing happened.

    Take a peek at code for me puhleaaassee and see what I may have wrong.

    NMP
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Mailmerge from Access (Access 2000)

    I already looked there, because I know the OCDB used to be there, have seen it many times (not particularly on this computer)...I have Windows XP Home Edition...and it's "not" there...got any ideas why?

    EDITED' It was in admin tools
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Automated Mailmerge from Access (Access 2000)

    Click Start | Help and Support.
    Type ODBC in the search box and press Enter.
    The "Working with data sources" help item found will explain where the control panel can be found, and it will even contain a link to open it directly.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Mailmerge from Access (Access 2000)

    It was in control panel...just never seen it tucked away in the Administrative tools befoer...
    sorry
    Code is still acting up, would you take a peek at last message...
    Thanks...[img]/forums/images/smilies/smile.gif[/img]
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Automated Mailmerge from Access (Access 2000)

    Sorry, that was a leftover from the code I filched it from. You can entirely remove the line

    DoCmd.Close acForm, Me.frmMain

Page 1 of 4 123 ... LastLast

Posting Permissions

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