By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

Need to change DoCmd.OutputTo using Word

100+
P: 114
I need to make some changes to a Query/select/print report using word application. What I need to do is change the way Word is called yet keep the process the same. Word is used so changes can be made before actual printing. Need some way of calling Word as a mail merge with the information from an Access table that the current report uses.

I have seen mail merge templates that use something like <<field name>> in them that uses a table. Reason for the change is the need for some graphics in the report and rtf will not carry the graphics across to word.

Here is the current code that is used when a person selected the desired information from a list box and a command button is clicked and the following is performed.

Expand|Select|Wrap|Line Numbers
  1.     Dim valSelect As Variant
  2.     Dim strWhere As String
  3.     Dim strSQLSelect As String
  4.     Dim AnyItemsSelected As Boolean
  5.     AnyItemsSelected = False
  6.     Dim qd As DAO.querydef
  7.  
  8.     strWhere = "[rec_no] In ("
  9.     For Each valSelect In Me.SelectPrintItems.ItemsSelected
  10.         strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
  11.         AnyItemsSelected = True
  12.     Next valSelect
  13.     strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
  14.     strWhere = strWhere & ")"
  15.  
  16.     If AnyItemsSelected = False Then
  17.         MsgBox ("You must make a selection(s) from the list before sending to Word !")
  18.         GoTo Exit_Send2WordButton_Click
  19.     Else
  20.         strSQLSelect = "SELECT rec_no, med_rec_no, first_name, last_name, trans_memo " & _
  21.         "FROM Translated_memo WHERE " & strWhere
  22.         Set qd = CurrentDb.QueryDefs("send2WordQuery")
  23.         qd.SQL = strSQLSelect 'fill SQL
  24.         MsgBox ("Only one MS Word document may be open at a time. After you Edit and Print " & _
  25.         "you may want to Save the document before exiting Word!")
  26.         DoCmd.OutputTo acOutputReport, "Trans_memo_141_Word", acFormatRTF, "Selected_141_report.rtf", True
  27.     End If
Can the DoCmd.OutputTo be changed to use a mail merge document? I can create a word template that looks like the Access "Trans_memo_141_Word" report if that is needed.

Thanks in advance
Jan 9 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I need to make some changes to a Query/select/print report using word application. What I need to do is change the way Word is called yet keep the process the same. Word is used so changes can be made before actual printing. Need some way of calling Word as a mail merge with the information from an Access table that the current report uses.

I have seen mail merge templates that use something like <<field name>> in them that uses a table. Reason for the change is the need for some graphics in the report and rtf will not carry the graphics across to word.

Here is the current code that is used when a person selected the desired information from a list box and a command button is clicked and the following is performed.

Expand|Select|Wrap|Line Numbers
  1.     Dim valSelect As Variant
  2.     Dim strWhere As String
  3.     Dim strSQLSelect As String
  4.     Dim AnyItemsSelected As Boolean
  5.     AnyItemsSelected = False
  6.     Dim qd As DAO.querydef
  7.  
  8.     strWhere = "[rec_no] In ("
  9.     For Each valSelect In Me.SelectPrintItems.ItemsSelected
  10.         strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
  11.         AnyItemsSelected = True
  12.     Next valSelect
  13.     strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
  14.     strWhere = strWhere & ")"
  15.  
  16.     If AnyItemsSelected = False Then
  17.         MsgBox ("You must make a selection(s) from the list before sending to Word !")
  18.         GoTo Exit_Send2WordButton_Click
  19.     Else
  20.         strSQLSelect = "SELECT rec_no, med_rec_no, first_name, last_name, trans_memo " & _
  21.         "FROM Translated_memo WHERE " & strWhere
  22.         Set qd = CurrentDb.QueryDefs("send2WordQuery")
  23.         qd.SQL = strSQLSelect 'fill SQL
  24.         MsgBox ("Only one MS Word document may be open at a time. After you Edit and Print " & _
  25.         "you may want to Save the document before exiting Word!")
  26.         DoCmd.OutputTo acOutputReport, "Trans_memo_141_Word", acFormatRTF, "Selected_141_report.rtf", True
  27.     End If
Can the DoCmd.OutputTo be changed to use a mail merge document? I can create a word template that looks like the Access "Trans_memo_141_Word" report if that is needed.

Thanks in advance
Create a mail merge document in word with 'send2WordQuery' as the data source. Then instead of using the output to you could just open the word template you had designed.

Mary
Jan 12 '07 #2

100+
P: 114
Thanks Mary for the info. Wondering if there is a way to start Word from within the VBA code instead of having to manually start Word.

The only reason I'm chaing the working code is because they desire some graphics to print and that is not happening when I send the Access Report to Word in a rtf file.
Jan 15 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandXX_Click()
  2. Dim wordDoc As String
  3. Dim accApp As Object
  4.  
  5.    wordDoc = "full path to document"
  6.  
  7.    If Dir(wordDoc) = "" Then
  8.       MsgBox "Document not found."
  9.    Else
  10.       Set accApp = CreateObject(Class:="Word.Application")
  11.       accApp.Visible = True
  12.       accApp.Documents.Open filename:=wordDoc
  13.    End If
  14.  
  15.    Set accApp = Nothing
  16.  
  17. End Sub
  18.  
This is the code to open a word document. I've put the code in the button click event.

Mary
Jan 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.