Solved Mark report as Printed in access, Printed = Yes in access table (1 Viewer)

Ihsang

Member
Local time
Today, 11:15
Joined
Apr 7, 2020
Messages
31
Hi,
1) I have report, based on query.
2) This report is filtered with between dates criteria and combo-box selection.
This means reports brings very specific record every time
3) report has print button to print that record.

Now I want to this record as printed in the table. I have a field in table named "Printed" (yes/no). If the document is printed then yes (True) otherwise no (False).
1st Method = Report close event

I tried to make Report close event property, with confirmation message "have you printed the report" vb yes/no. still this does not work.
I have following code on report close event.

Code:
Private Sub Form_Unload(Cancel As Integer)
Dim Msg, Style, Title, Help, ctext, Response, Mystring
Msg = "Have you printed your report??" '' Define message
Style = vbYesNo + vbQuestion + vbDefaultButton1 ''Define Style
Title = "Printing"
Help = "DEMO.Hlp"
ctext = 1000
    ' context.
    ' Display Message
Response = MsgBox(Msg, Style, Title, Help, ctext)
If Response = vbYes Then
    Me.ChkboxPrinted = True
    Else
    Me.ChkboxPrinted = False
End If
End Sub
I get the error as in picture " you can assign value to object"

2nd Method = Form Unload event

I tried to bring this report on Form, on form unload property used the same code, I got the same error.

3rd Method = on form itself marked "Printed=Yes"
I brought the Query Field "Printed" on the form, once the report printing was finished. I marked this field (Printed = Yes) tick marked. Then I looked into table,
Only the 1st record of report was marked as printed int the but not other.
Meaning not all records which were on report, were not marked as printed (Yes).

Problem not solved..........Can someone help me. Thanks
 

Attachments

Last edited:

theDBguy

I’m here to help
Local time
Today, 02:15
Joined
Oct 29, 2018
Messages
10,798
Hi. How exactly are reports opened by the user? Where do the criteria for the filter come from?
 

Micron

AWF VIP
Local time
Today, 05:15
Joined
Oct 20, 2018
Messages
3,172
still this does not work.
This means what? You get an error? Report prints but your table field isn't updated?

Keep in mind one thing - you can record that it was sent to the printer. Likely that is no guarantee that it was printed. Does that matter?
If not, and there is a print button, then why isn't your code behind the command button instead of report events?
A report is a snapshot of data at a time. AFAIK, you cannot alter a table record by altering the field in a report, but then I have never even thought of trying that.
You would have to run an update query or sql in code, and if need be, requery or reopen the report?
 

arnelgp

error reading drive A:
Local time
Today, 17:15
Joined
May 7, 2009
Messages
9,615
maybe on your report's
Code:
Private Sub cmdPrint_Click()
    Dim db As DAO.Database
    Dim p As Parameter
    On Error GoTo print_err:
    DoCmd.RunCommand acCmdPrint
    Set db = CurrentDb
    With db.QueryDefs(Me.RecordSource)
        For Each p In .Parameters
            .Parameters(p.Name) = Eval(p.Name)
        Next
        With .OpenRecordset(dbOpenDynaset)
            If Not (.BOF And .EOF) Then
                .MoveFirst
                While Not .EOF
                    .Edit
                    !Printed = True
                    .Update
                    .MoveNext
                Wend
            End If
        End With
    End With
exit_sub:
    Set db = Nothing
    Exit Sub
print_err:
    Select Case Err.Number
        Case 2501
            'print was cancelled
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
    Resume exit_sub
End Sub
print_button:
 

Ihsang

Member
Local time
Today, 11:15
Joined
Apr 7, 2020
Messages
31
Thank you very much All of you, for taking time, helping me out --- @arnelgp @theDBguy @Micron

Problem Solved by @ arnelgp
great thanks. This code worked a great job. Each record in report is marked as Printed = Yes, and on the top of it, if print is cancelled, Printed yes is not saved. Thank you.

@ theDBguy
Criteria filter comes from query.
Reports open in two ways 1) as popup 2) Emebeded inside form which has navigation panel
@
Micron
good explanation for my understanding, yes if printer gives error this is not a big issue. But It is also solved in above code @ arnelgp
 

Ihsang

Member
Local time
Today, 11:15
Joined
Apr 7, 2020
Messages
31
maybe on your report's
Code:
Private Sub cmdPrint_Click()
    Dim db As DAO.Database
    Dim p As Parameter
    On Error GoTo print_err:
    DoCmd.RunCommand acCmdPrint
    Set db = CurrentDb
    With db.QueryDefs(Me.RecordSource)
        For Each p In .Parameters
            .Parameters(p.Name) = Eval(p.Name)
        Next
        With .OpenRecordset(dbOpenDynaset)
            If Not (.BOF And .EOF) Then
                .MoveFirst
                While Not .EOF
                    .Edit
                    !Printed = True
                    .Update
                    .MoveNext
                Wend
            End If
        End With
    End With
exit_sub:
    Set db = Nothing
    Exit Sub
print_err:
    Select Case Err.Number
        Case 2501
            'print was cancelled
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
    Resume exit_sub
End Sub
print_button:

great thanks. This code worked a great job. Each record in report is marked as Printed = Yes, and on the top of it, if print is cancelled, Printed yes is not saved. Thank you.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom