Setting Colour Mode for Report 1

[TrekBiker]

When trying to print a report in black & white on a colour printer the colour mode setter doesn't work, so the prints always come out coloured. The report's query source includes a criterion referencing a field on another form.

This is some code I've assembled from web searches for a simplified version of what I need

    Dim stDocName As String
    Dim rpt As Report

    stDocName = "Test report"
    DoCmd.OpenReport stDocName, acViewDesign, , "[Investment]=Forms![PickInvestment].Investment", acHidden
    
    Set rpt = Reports("Test report")
    rpt.Printer.ColorMode = acPRCMMonochrome
    
    DoCmd.OpenReport stDocName, acViewPreview, , "[Investment]=Forms![PickInvestment].Investment"

It looked as though it worked first time but now the report shows all data rather than just the records meeting the query criterion.

As I say, the objective is to force the colour mode change when the printer setup doesn't do it. The same code above works fine if the report's source is a simple query, not one with a criterion.

 

[dhookom]

I would either change the code to the following or modify the SQL property of the reports recordsource using a little DAO code.

    Dim stDocName As String
    Dim rpt As Report

    stDocName = "Test report"
    [COLOR=#4E9A06]' this code assumes Investment is numeric, if text you will need to add quote delimiters.[/color]
    DoCmd.OpenReport stDocName, acViewDesign, , "[Investment]=" & Forms![PickInvestment].Investment, acHidden
    
    Set rpt = Reports("Test report")
    rpt.Printer.ColorMode = acPRCMMonochrome
    
    DoCmd.OpenReport stDocName, acViewPreview, , "[Investment]=" & Forms![PickInvestment].Investment

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for your help again Duane.

I'm still having problems with this and maybe need to explain the full setup. Instead of running the report directly from a first form's command button there's an intermediate form launched by the report's On Load event. So the Print button is on the first form and its code includes the OpenReport procedure. Then the On Load procedure opens the second form, which displays some other information and contains an OK button to initiate displaying the report in preview.

I'm finding that when reports appear they contain all data from the source query, so not filtered by the equivalent of the Investment]= criterion.

Is this to do with the way this criterion is applied to the two OpenReport stages in the code I sent, or because of the On Load procedure, or what?

 

[dhookom]

If you use some DAO code to change the Sql property of a saved query that is the record source of the report, you won't need any other filtering.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

I think you're saying to build the filtering into the SQL for the report source, not add it in the OpenReport definition?

I tend to use the query design window to generate the SQL rather than writing it directly, and assume it comes to the same thing.

 

[dhookom]

Yes, change the SQL faq701-7433 and the SQL is the same as you would get from the query design.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for your help, as ever.