jaugermeister
asked on
Access query to open another application (SQL CODE)
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!
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!
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)
'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)
ASKER
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_cmdAddressLabels_Clic k:
Exit Sub
Err_cmdAddressLabels_Click :
MsgBox Err.Description
Resume Exit_cmdAddressLabels_Clic k
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
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_cmdAddressLabels_Clic
Exit Sub
Err_cmdAddressLabels_Click
MsgBox Err.Description
Resume Exit_cmdAddressLabels_Clic
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.