470,831 Members | 1,689 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,831 developers. It's quick & easy.

Need to change DoCmd.OutputTo using Word

114 100+
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
3 5178
MMcCarthy
14,534 Expert Mod 8TB
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
ljungers
114 100+
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
14,534 Expert Mod 8TB
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.

Similar topics

reply views Thread by Victor Kaiser | last post: by
reply views Thread by Robert Langen | last post: by
4 posts views Thread by tmountjr | last post: by
1 post views Thread by inika301 | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.