Access query to open another application (SQL CODE)

Posted on 2008-06-26
Last Modified: 2012-05-05
I have a query that pulls data for employees that are 65 and older, within that query there is a make table command to populate the table every time I run the query. I have created a mail merge document in MS Word 2003 to source the data from the table the query populates. All I want to do is make the original query button to open up the word mail merge documents I created. Because the mail merge doc automatically updates the source files when you open the doc the data should always be valid.
The MS Word doc name is: Mail Merge Design.doc
The Query Name is: qry_AddressLabels
The Table Name is:Address Labels for 65+

Thank you!
Question by:jaugermeister
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Author Comment

ID: 21882448
Okay it appears I'm on my own for now...
This is what I gathered so far from searching, but it still does not work in the VB editor lines of code.

  Dim LWordDoc As String
    Dim oApp As Object

    'Path to the word document
    LWordDoc = "S:\Consult Referral Tracking - RM\Mail Merge Design.doc"
    oApp.Documents.Open filename:=LWordDoc

How can I make this work? what am I missing here, Access doesn't have an issue with the syntax it debugs just fine but does nothing when I run the query.
LVL 14

Expert Comment

ID: 21883908
My first suggestion is to add the double quote character (") to each side of your string.  The double quote is represented by ASCII character 34.

'Path to the word document
    LWordDoc = chr(34) & "S:\Consult Referral Tracking - RM\Mail Merge Design.doc" & chr(34)
    oApp.Documents.Open filename:=LWordDoc

If that doesn't work, I have an Access 2002 DB that opens Word using the Shell() command.  Here's an example:

  shellpath = Chr(34) & "c:\program files\microsoft office\office" & _
    CInt(Application.Version) & "\winword.exe" & Chr(34) & " " & _
    Chr(34) & "S:\Consult Referral Tracking - RM\Mail Merge Design.doc" & Chr(34)
  Shell (shellpath)

Author Comment

ID: 21885076
don't know what I'm doing wrong here, that doesn't work. the example above debugs just fine like my other string but when I run the query everything works except the word doc doesn't open up...

this is where I'm placing the line of code.

Private Sub cmdAddressLabels_Click()
On Error GoTo Err_cmdAddressLabels_Click

    Dim stDocName As String

    stDocName = "qry_AddressLabels"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdAddressLabels_Click
    Exit Sub
   shellpath = Chr(34) & "c:\program files\microsoft office\office" & _
    CInt(Application.Version) & "\winword.exe" & Chr(34) & " " & _
    Chr(34) & "S:\Consult Referral Tracking - RM\Mail Merge Design.doc" & Chr(34)
  Shell (shellpath)
End Sub

Accepted Solution

jaugermeister earned 0 total points
ID: 21885142
Adding a screen shot so you can see where I'm placing the text...
I get this dialoge box via opening the properties of the button and clicking the (...) to the right of the click action.

LVL 14

Assisted Solution

JohnK813 earned 500 total points
ID: 21886142
Where you have the shellpath and Shell() lines now, Access thinks they're part of Err_cmdAddressLabels_Click.  So technically they'd only be run if you get an error.  But you also have the Resume and Exit Sub before that, meaning they never get run.

Try moving the shellpath and Shell() lines before the Exit_cmdAddressLabels_Click line.

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question