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

Filter OutputTo report for Word

100+
P: 114
Anyone have an idea how I can filter a report that I'm outputting to Word.

Using the following 2 Command to print and output to word.

The strWhere is a list of selected items/rows to print, and that works great, but the next command I can't seem to get it to filter. It outputs the complete table, and that table is quite large.

DoCmd.OpenReport "Memo141Report", , , strWhere

DoCmd.OutputTo acOutputReport, "Memo141Report", acFormatRTF, "Selected141Report.rtf", True
Dec 29 '06 #1
Share this Question
Share on Google+
30 Replies


nico5038
Expert 2.5K+
P: 3,072
You'll need to modify the report's query "on the fly"

Store the original query's SQL text in your code and add the strWHERE to it like:

strSQLreport = " select ... from ... where " & strWhere

' This requires a tools/reference to Microsoft DAO version #.##
dim qd as DAO.querydef
' the following query needs to be used for the report, but the contents will be filled from code
set qd = currentdb.querydefs("qryReportOnTheFly")
' fill SQL
qd.SQL = strSQL

' now activate the OutputTo command

Getting the idea ?

Nic;o)
Dec 29 '06 #2

100+
P: 114
You'll need to modify the report's query "on the fly"

Store the original query's SQL text in your code and add the strWHERE to it like:

strSQLreport = " select ... from ... where " & strWhere

' This requires a tools/reference to Microsoft DAO version #.##
dim qd as DAO.querydef
' the following query needs to be used for the report, but the contents will be filled from code
set qd = currentdb.querydefs("qryReportOnTheFly")
' fill SQL
qd.SQL = strSQL

' now activate the OutputTo command

Getting the idea ?

Nic;o)
I think I understand, will try in a day or two. Where dose the strSQL below the comment ' fill SQL?
Dec 29 '06 #3

nico5038
Expert 2.5K+
P: 3,072
The statement:
qd.SQL = "select ..."
will directly write the SQL into the saved query section of Access.

Just give it a try with a qryDummy to see what happens :-)

Nic;o)
Dec 29 '06 #4

100+
P: 114
Got a chance to test code today, and in the process of doing so I realised that the desired report that I want to send to Word is from selected rows in a list box and not from a query.

The PK that the report uses is hidden in that list box. Not sure where or how to use strWhere, a string list of selected rec_no that is used in the report.

here is the code I'm using but not getting the desired results.

Dim valSelect As Variant
Dim strWhere As String

strWhere = "[rec_no] In ("
For Each valSelect In Me.SelectPrintItems.ItemsSelected
strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
Next valSelect

strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space

strWhere = strWhere & ")"

DoCmd.OpenReport "Trans_memo_141_Report", , , strWhere

DoCmd.OutputTo acOutputReport, "Trans_memo_141_Report", acFormatRTF, "Selected_141_report.rtf", True

Any ideas on what I need to change.
Dec 30 '06 #5

nico5038
Expert 2.5K+
P: 3,072
After your statements:
strWhere = strWhere & ")"

Add:

set qd = currentdb.querydefs("qryReportOnTheFly")
' fill SQL
qd.SQL = "<present SQL of your reports query> WHERE " & strWhere

DoCmd.OpenReport "Trans_memo_141_Report"
DoCmd.OutputTo acOutputReport, "Trans_memo_141_Report", acFormatRTF, "Selected_141_report.rtf", True

Just make sure that qryReportOnTheFly is the query used by your report and place the select statement needed in place of <present SQL of your reports query>.
As you see, even the strWhere passing to the OpenOutput can be dropped as the query is changed...

Nic;o)
Dec 30 '06 #6

NeoPa
Expert Mod 15k+
P: 31,656
NB. Make sure there is no trailing semi-colon (;) at the end of your <present SQL of your reports query>.
A semi-colon is not required for SQL to work but is usually found near the end of Access saved SQL in QueryDefs etc.
Dec 30 '06 #7

100+
P: 114
After your statements:
strWhere = strWhere & ")"

Add:

set qd = currentdb.querydefs("qryReportOnTheFly")
' fill SQL
qd.SQL = "<present SQL of your reports query> WHERE " & strWhere

DoCmd.OpenReport "Trans_memo_141_Report"
DoCmd.OutputTo acOutputReport, "Trans_memo_141_Report", acFormatRTF, "Selected_141_report.rtf", True

Just make sure that qryReportOnTheFly is the query used by your report and place the select statement needed in place of <present SQL of your reports query>.
As you see, even the strWhere passing to the OpenOutput can be dropped as the query is changed...

Nic;o)
I kind of understand what you are saying but the qryReportOnTheFly I'm not sure what you meant. Would that be replaced by the name of a query? The report to my knowledge there is no query tied to report, just a table that contains the info used by the report. The OpenReport printed just the seleced rec_no and not the complete table.
Dec 31 '06 #8

100+
P: 114
I think I got it, no compile errors. When I click on the "Send to Word" button I get the following MsgBox message "Item not found in this collection". I'm including my code, but not sure if I make all of the required changes for my need.

Where is the best place to perform a test to check that atleast one row was selected, and if not use a MsgBox telling that a selection is needed. I put in a test but it's not working when I don't select a row. I get the "Item not found---" msg with or without a selected row!

Private Sub Send2WordButton_Click()
On Error GoTo Err_Send2WordButton_Click
' send the selected rows to and open Word
Dim valSelect As Variant
Dim strWhere As String

strWhere = "[rec_no] In ("
For Each valSelect In Me.SelectPrintItems.ItemsSelected
strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
Next valSelect

strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space

strWhere = strWhere & ")"

' check if atleast 1 row was selected
If strWhere = "" Or IsNull(strWhere) Then
MsgBox ("You must make a selection(s) from the list. Selection Required !")
GoTo Exit_Send2WordButton_Click
Else
Set qd = CurrentDb.querydefs("qryReportOnTheFly")
' fill SQL
qd.SQL = "SELECT * FROM Translated_memo WHERE " & strWhere

DoCmd.OpenReport "Trans_memo_141_Report"
DoCmd.OutputTo acOutputReport, "Trans_memo_141_Report", acFormatRTF, "Selected_141_report.rtf", True

End If

Exit_Send2WordButton_Click:
Exit Sub
Dec 31 '06 #9

100+
P: 114
After reading through the prior post I added the "Dim qd As DAO.querydef" to my code but now I'm getting "Compile error: user-defined type not defined" message.

Could this be the reason I'm getting "Item not found in this collection" message?

Should there be a 'rec_no IN' placed in the Select statement like "SELECT * FROM Translated_memo WHERE rec_no IN " & strWhere
Dec 31 '06 #10

NeoPa
Expert Mod 15k+
P: 31,656
After reading through the prior post I added the "Dim qd As DAO.querydef" to my code but now I'm getting "Compile error: user-defined type not defined" message.

Could this be the reason I'm getting "Item not found in this collection" message?

Should there be a 'rec_no IN' placed in the Select statement like "SELECT * FROM Translated_memo WHERE rec_no IN " & strWhere
This syntax - 'In(item,item,...)' - would only be appropriate if you have a comma separated list of values, surrounded by parentheses (), in the strWhere variable.
The In() structure basically returns TRUE if the record item (Rec_No in this case) matches one of the items in the list.
Dec 31 '06 #11

100+
P: 114
This syntax - 'In(item,item,...)' - would only be appropriate if you have a comma separated list of values, surrounded by parentheses (), in the strWhere variable.
The In() structure basically returns TRUE if the record item (Rec_No in this case) matches one of the items in the list.
Thanks NeoPa aand I did a test display of that variable and yes it is comma seperated and has the both parenteses. I'll place the rec_no and IN in the WHERE statement in my code.

Do you have any idea of what I need to do to get that "Dim qd As DAO.querydef" working.
Dec 31 '06 #12

NeoPa
Expert Mod 15k+
P: 31,656
Not really Larry.
If you've copied it in though...
Expand|Select|Wrap|Line Numbers
  1. Dim qd As DAO.querydef
That would indicate that DAO doesn't have a .QueryDef object.
No capitalisation at all usually indicates that the item could not be found (or resolved). Not an area of great experience for me I'm afraid.
Dec 31 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks NeoPa aand I did a test display of that variable and yes it is comma seperated and has the both parenteses. I'll place the rec_no and IN in the WHERE statement in my code.

Do you have any idea of what I need to do to get that "Dim qd As DAO.querydef" working.
Larry

In the VBA Editior window go to Tools - References. This is the list of libraries. Make sure that there is a Microsoft DAO library included on the ticked list. If not go down the list until you find one and tick it.

Mary
Jan 1 '07 #14

100+
P: 114
I'd like to say Thank you and Happy New Year, NeoPa and mmccarthy for all your help and support. I did check and tick the Microsoft DAO library and that took care of the compile error.

I'm still getting "Item not found in this collection" msgBox showing up when I click the command button named "send2WordButton". I have checked the spelling of the column names and the table name, and I can not see anything that would cause this, except for missing code or order of. Sounds like something can not be found, name or table wise.

I did a display to a text box the string value of the SQL Select statement that is built by code. The only option I get is 'OK' .

Value of strWhere = SELECT rec_no, med_rec_no, first_name, last_name, trans_memo FROM Translated_memo WHERE [rec_no] In (26709, 43268)

Here is the code. Maybe you might see something I'm over looking or missing.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Send2WordButton_Click()
  2. On Error GoTo Err_Send2WordButton_Click
  3. ' send the Report to MS Word using selected rows
  4.     Dim valSelect As Variant
  5.     Dim strWhere As String
  6.     Dim strSQLSelect As String
  7.     Dim AnyItemsSelected As Boolean
  8.     AnyItemsSelected = False
  9.     Dim qd As DAO.querydef
  10.  
  11.     strWhere = "[rec_no] In ("
  12.     For Each valSelect In Me.SelectPrintItems.ItemsSelected
  13.         strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
  14.         AnyItemsSelected = True
  15.     Next valSelect
  16.     strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
  17.     strWhere = strWhere & ")"
  18.  
  19.     If AnyItemsSelected = False Then
  20.         MsgBox ("You must make a selection(s) from the list before sending to Word !")
  21.         GoTo Exit_Send2WordButton_Click
  22.     Else
  23.         strSQLSelect = "SELECT rec_no, med_rec_no, first_name, last_name, trans_memo " & _
  24.         "FROM Translated_memo WHERE " & strWhere
  25. '       dspSQLvalues = strSQLSelect ' display the SQL SELECT string for debug
  26.         Set qd = CurrentDb.querydefs("qryReportOnTheFly")
  27.         qd.SQL = strSQLSelect 'fill SQL
  28.         DoCmd.OutputTo acOutputReport, "Trans_memo_141_Report", acFormatRTF, "Selected_141_report.rtf", True
  29.     End If
  30. Exit_Send2WordButton_Click:
  31.     Exit Sub
Thanks
Jan 1 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
I'd like to say Thank you and Happy New Year, NeoPa and mmccarthy for all your help and support. I did check and tick the Microsoft DAO library and that took care of the compile error.

I'm still getting "Item not found in this collection" msgBox showing up when I click the command button named "send2WordButton". I have checked the spelling of the column names and the table name, and I can not see anything that would cause this, except for missing code or order of. Sounds like something can not be found, name or table wise.
What line in the code is the error stopping at?
Jan 1 '07 #16

100+
P: 114
What line in the code is the error stopping at?
After some checking, it is stopping at "Set qd = CurrentDb.QueryDefs("qryReportOnTheFly")" and then displays a message box "Item not found in this collection"

Hope that helps
Jan 1 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
After some checking, it is stopping at "Set qd = CurrentDb.QueryDefs("qryReportOnTheFly")" and then displays a message box "Item not found in this collection"

Hope that helps
It can't find this query

qryReportOnTheFly

Check your query list and make sure the spelling and capitalisation are correct

Mary
Jan 1 '07 #18

nico5038
Expert 2.5K+
P: 3,072
You did build your report on a table, just open the report in design mode and open the table by pressing the report's object source trailing button [...] Access will ask or you want to create a query, accept and save the query by pressing the save button. Name it qryReportOnTheFly

Nic;o)
Jan 1 '07 #19

100+
P: 114
You did build your report on a table, just open the report in design mode and open the table by pressing the report's object source trailing button [...] Access will ask or you want to create a query, accept and save the query by pressing the save button. Name it qryReportOnTheFly

Nic;o)
The report was created on a table. Know what you mean as to opening in design mode but not sure what is meant by "and open the table by pressing the report's object source trailing button [...] "

So what I have to do is create a query based on that table the report uses and name it "qryReportOnTheFly" or change the name used in the VBA code!
Jan 1 '07 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
The report was created on a table. Know what you mean as to opening in design mode but not sure what is meant by "and open the table by pressing the report's object source trailing button [...] "

So what I have to do is create a query based on that table the report uses and name it "qryReportOnTheFly" or change the name used in the VBA code!
Simple answer is Yes
Jan 1 '07 #21

100+
P: 114
Simple answer is Yes
I created a new query named "send2WordQuery" that uses the table that the report uses. Then I changed the name in the VBA code and no error msg this time but I get the dialog box showing that it is sending all records to Word.

Did I create the query the wrong way?
Jan 1 '07 #22

100+
P: 114
I'm confussed, only thing I'm not sure of is the following lines of VBA code.

Expand|Select|Wrap|Line Numbers
  1. strSQLSelect = "SELECT rec_no, med_rec_no, first_name, last_name, trans_memo " & _
  2.         "FROM Translated_memo WHERE " & strWhere
  3.         Set qd = CurrentDb.QueryDefs("send2WordQuery")
  4.         qd.SQL = strSQLSelect 'fill SQL
  5.         DoCmd.OutputTo acOutputReport, "Trans_memo_141_Report", acFormatRTF, "Selected_141_report.rtf", True
Is not the SQL select statement built in the strSQLSelect string. What does the Set qd = do? What is the purpose of the qd.SQL line. It seems like the query is not being performed and inturn the entire table that the report is built on runs using the entire table and not the items that are to be selected!
Jan 1 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Did you change the Record Source of the report to 'send2WordQuery'?
Jan 2 '07 #24

100+
P: 114
Did you change the Record Source of the report to 'send2WordQuery'?
I'm not sure how to set the record source on the report. I do not see that property. Could you tell me how to set it? Is that how a Query is tied to a report?
Jan 2 '07 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm not sure how to set the record source on the report. I do not see that property. Could you tell me how to set it? Is that how a Query is tied to a report?
This is how a query is tied to the report

Open the report in design view, open the properties window. For report properties go to data tab and the first property on the list is the Record Source
Jan 2 '07 #26

NeoPa
Expert Mod 15k+
P: 31,656
1. Open report in Design view.
2. Open Properties (Alt-Enter).
The Record Source property is the top one in the All tab.
Jan 2 '07 #27

100+
P: 114
Thanks to all of you NeoPa, mmccarthy, and anyone else that have helped me even on a holiday. It works the way I wanted it to function.
Jan 2 '07 #28

NeoPa
Expert Mod 15k+
P: 31,656
We're all very pleased to hear it Larry.
Have a Happy New Year.
Jan 2 '07 #29

P: 10
I Am Having The Same Problem Too..but The Thing Is Is It Possible To Output Report To Txt Format With A Filter Or Criteria..or Should I Use Query Table To Output It To Text With Criteria..if So..how?? I Tried Searching For A Couple Days But Havent Found Any Solution..
Feb 28 '07 #30

NeoPa
Expert Mod 15k+
P: 31,656
If the problem is the same then the answer should be able to be found in this thread.
If, as I suspect, the question is only related, then perhaps you should create a new thread (question) for your problem. If you like, you can paste in a link to this one. You may want to consider reading (POSTING GUIDELINES: Please read carefully before posting to a forum) before posting, to avoid repeating your earlier post.
This thread is considered to be answered.
Feb 28 '07 #31

Post your reply

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