Filter OutputTo report for Word

[ljungers]

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

[nico5038]

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)

[ljungers]

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?

[nico5038]

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)

[ljungers]

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.

[nico5038]

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)

[NeoPa]

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.

[ljungers]

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.

[ljungers]

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

[ljungers]

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

[NeoPa]

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.

[ljungers]

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.

[NeoPa]

Not really Larry.
If you've copied it in though...

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.

[MMcCarthy]

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

[ljungers]

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.

Private Sub Send2WordButton_Click()
On Error GoTo Err_Send2WordButton_Click
' send the Report to MS Word using selected rows
    Dim valSelect As Variant
    Dim strWhere As String
    Dim strSQLSelect As String
    Dim AnyItemsSelected As Boolean
    AnyItemsSelected = False
    Dim qd As DAO.querydef
 
    strWhere = "[rec_no] In ("
    For Each valSelect In Me.SelectPrintItems.ItemsSelected
        strWhere = strWhere & Me.SelectPrintItems.ItemData(valSelect) & ", "
        AnyItemsSelected = True
    Next valSelect
    strWhere = Left(strWhere, Len(strWhere) - 2) 'removes last comma and space
    strWhere = strWhere & ")"
 
    If AnyItemsSelected = False Then
        MsgBox ("You must make a selection(s) from the list before sending to Word !")
        GoTo Exit_Send2WordButton_Click
    Else
        strSQLSelect = "SELECT rec_no, med_rec_no, first_name, last_name, trans_memo " & _
        "FROM Translated_memo WHERE " & strWhere
'       dspSQLvalues = strSQLSelect ' display the SQL SELECT string for debug
        Set qd = CurrentDb.querydefs("qryReportOnTheFly")
        qd.SQL = strSQLSelect 'fill SQL
        DoCmd.OutputTo acOutputReport, "Trans_memo_141_Report", acFormatRTF, "Selected_141_report.rtf", True
    End If
Exit_Send2WordButton_Click:
    Exit Sub

Thanks

[MMcCarthy]

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?

[ljungers]

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

[MMcCarthy]

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

[nico5038]

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)

[ljungers]

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!

[MMcCarthy]

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

[ljungers]

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?

[ljungers]

I'm confussed, only thing I'm not sure of is the following lines of VBA code.

strSQLSelect = "SELECT rec_no, med_rec_no, first_name, last_name, trans_memo " & _
        "FROM Translated_memo WHERE " & strWhere
        Set qd = CurrentDb.QueryDefs("send2WordQuery")
        qd.SQL = strSQLSelect 'fill SQL
        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!

[MMcCarthy]

Did you change the Record Source of the report to 'send2WordQuery'?

[ljungers]

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?

[MMcCarthy]

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

[NeoPa]

1. Open report in Design view.
2. Open Properties (Alt-Enter).
The Record Source property is the top one in the All tab.

[ljungers]

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.

[NeoPa]

We're all very pleased to hear it Larry.
Have a Happy New Year.

[ionphoenix]

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..

[NeoPa]

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.