1 report 4 altered copies

[psuplat]

Hi all,

Not sure if this is possible to do but..

I have an access db from which I print a report, but I am using a dot matrix printer that print on special paper giving me 4 copies on pink, green, yellow and white paper.

I need to move this report from dot matrix printer to normal printer.

What I'm trying to achieve is this: on the left side report there is a horizontal bar going from top to bottom of the page. When I press the print button I want the Access to print 4 copies of the document but on each printed copy change the color of this bar to 1-pink,2-green,3-yellow,4-light grey.

I hope this is clear enough to understand.

Thanks

 

[vbaInet]

I'm guessing this bar is a rectangle control?

Do you know how to print 4 copies of the report?

 

[psuplat]

Yes it is, but I could just as well change it to a text box with color background.

I have a piece of VB code somewhere that will spit out 4 copies of report.

 

[vbaInet]

Let me see the code and the event it's run from.

 

[psuplat]

The code is as below

DoCmd.SelectObject acReport, "NewReport", True
DoCmd.PrintOut acPrintAll, , , , 4

is is set to a button onClick event.

but all this does is prints 4 copies without any alterations

 

[vbaInet]

You will need to print in a different way.

    Dim x As Integer
    Dim rpt As Report
 
    ' Colour constants. Amend as required
    Const PINK_COLOUR = [COLOR=red]16751103[/COLOR]
    Const GREEN_COLOUR = 65280
    Const YELLOW_COLOUR = 65535
    Const GREY_COLOUR = [COLOR=red]12632256[/COLOR]
    
    ' Open
    DoCmd.OpenReport "[COLOR=red]Report Name[/COLOR]", acViewPreview, , , acHidden
    Set rpt = Reports("[COLOR=red]Report Name[/COLOR]")
 
    With rpt
        ' Print sequentially
        For x = 1 To 4
            Select Case 1
                Case 1
                    .[COLOR=red]BoxControl[/COLOR].BackColor = PINK_COLOUR
                Case 2
                    .[COLOR=red]BoxControl[/COLOR].BackColor = GREEN_COLOUR
                Case 3
                    .[COLOR=red]BoxControl[/COLOR].BackColor = YELLOW_COLOUR
                Case 4
                    .[COLOR=red]BoxControl[/COLOR].BackColor = GREY_COLOUR
            End Select
 
            .Print
            DoEvents
        Next
 
        ' Close
        DoCmd.Close acReport, .Name, acSaveNo
    End With

Amend the bits in red.

 

[psuplat]

I'm getting compile error "Argument not optional" and .Print gets highlighted

 

[vbaInet]

Change that to rpt.Print or you can use your PrintOut code (without the 4)

 

[psuplat]

rpt.Print didn't work.

When I use DoCmd.PrintOut the printer spits out 4 copies alright but all of them are of pink color version. It seems like the switching doesn't work

 

[vbaInet]

Let me see your entire code.

 

[psuplat]

Here is the code launch by the onClick event of a button:

Private Sub Command196_Click()
Dim x As Integer
    Dim rpt As Report
 
    ' Colour constants. Amend as required
    Const PINK_COLOUR = 16751103
    Const GREEN_COLOUR = 65280
    Const YELLOW_COLOUR = 65535
    Const GREY_COLOUR = 12632256
    
    ' Open
    DoCmd.OpenReport "Test K Part Report", acViewPreview, , , acHidden
    Set rpt = Reports("Test K Part Report")
 
    With rpt
        ' Print sequentially
        For x = 1 To 4
            Select Case 1
                Case 1
                    .Box73.BackColor = PINK_COLOUR
                Case 2
                    .Box73.BackColor = GREEN_COLOUR
                Case 3
                    .Box73.BackColor = YELLOW_COLOUR
                Case 4
                    .Box73.BackColor = GREY_COLOUR
            End Select
 
            DoCmd.PrintOut acPrintAll
            DoEvents
        Next
 
        ' Close
        DoCmd.Close acReport, .Name, acSaveNo
    End With
On Error GoTo Err_Command196_Click

    Dim stDocName As String

    stDocName = "Test K Part Report"
    DoCmd.OpenReport stDocName, acNormal

Exit_Command196_Click:
    Exit Sub

Err_Command196_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Command196_Click
    
End Sub

 

[vbaInet]

Alright, I would use a variable. Upload your db and I will make the changes.

 

[psuplat]

That will be problematic due to the size of the file. It is a massive one with over 80MB. Any other way of doing it?

 

[vbaInet]

* Create one table from the record source of your report.
* Remove sensitive data but keep testing data
* Create a report from this new table
* Upload the db.