none
Print a report from the last time the report was printed RRS feed

  • Question

  • hi:

    i have a reports that every employed go to a end user pc enter his name and password and click print. it well print the jobs he have to do

    my Q' is how do i set the report to print only the new records that created after the last print (i have a field for record insert date+time)

     

    Thursday, September 22, 2016 12:32 PM

Answers

  • thank you all

    i build the code different 

    
    
    Private Sub btn334_Click()
    DoCmd.PrintOut PrintRange:=acPrintAll, PrintQuality:=acHigh, CollateCopies:=True
    Dim rst As RecordsetSet rst = CurrentDb.OpenRecordset(Name:="my query", Type:=RecordsetTypeEnum.dbOpenDynaset)
    Do While Not rst.EOF
     CurrentDb.Execute "myquery set printed=true whereprinted=false"       
     rst.MoveNext
    Loop 
     End Sub





    • Edited by eshay1 Wednesday, September 28, 2016 11:36 PM
    • Marked as answer by eshay1 Wednesday, September 28, 2016 11:38 PM
    Wednesday, September 28, 2016 11:29 PM

All replies

  • Hello,

    I suggest you could add a field (Yes/No type) to identify if it was printed. Every time select and print the Unprinted records and after printing change the identification.

    Regards,

    Celeste

    Friday, September 23, 2016 10:45 AM
    Moderator
  • Hello,

    I suggest you could add a field (Yes/No type) to identify if it was printed. Every time select and print the Unprinted records and after printing change the identification.

    Regards,

    Celeste

    nice idea but to do it manually well take to long

    but i will consider that and build a code for auto update the field  

     
    Saturday, September 24, 2016 4:37 PM
  • ........but i will consider that and build a code for auto update the field  
     
    It's not difficult to do.  Let's say for example that you are printing a report of sales by a specific employee, the code would be along these lines:

    Const MESSAGE_TEXT = "Did the report print successfully?"
    Dim strCriteria AS String
    Dim strSQL As String

    strCriteria = "EmployeeID = " & Me.cboEmployee & " AND HasPrinted = FALSE"
    strSQL = "UPDATE Sales SET HasPrinted = TRUE WHERE " & strCriteria

    DoCmd.OpenReport "rptSales", WhereCondition:=strCriteria

    If MsgBox(MESSAGE_TEXT, vbQuestion+vbYesNo, "Confirm Printing") = vbYes Then
        CurrentDb.Execute strSQL, dbFailOnError
    End If


    Ken Sheridan, Stafford, England

    Sunday, September 25, 2016 5:13 PM
  • ........but i will consider that and build a code for auto update the field  
     

    It's not difficult to do.  Let's say for example that you are printing a report of sales by a specific employee, the code would be along these lines:

    Const MESSAGE_TEXT = "Did the report print successfully?"
    Dim strCriteria AS String
    Dim strSQL As String

    strCriteria = "EmployeeID = " & Me.cboEmployee & " AND HasPrinted = FALSE"
    strSQL = "UPDATE Sales SET HasPrinted = TRUE WHERE " & strCriteria

    DoCmd.OpenReport "rptSales", WhereCondition:=strCriteria

    If MsgBox(MESSAGE_TEXT, vbQuestion+vbYesNo, "Confirm Printing") = vbYes Then
        CurrentDb.Execute strSQL, dbFailOnError
    End If


    Ken Sheridan, Stafford, England

    first thank you

    1.EmployeeID is the name of the field?

    2.don't have cbofield

    i get an error 2498: An expression you entered is the wrong data type for one of the arguments(it go for openreport) 

    me field is "operator"


    • Edited by eshay1 Monday, September 26, 2016 11:24 PM
    Monday, September 26, 2016 11:21 PM
  • Hi,

    You could try like the following. It selects the records which unprinted (Printed field=No) then changes them into Yes.

    Sub update()
    Dim qrySQL As String
    Dim rst As DAO.Recordset
    Dim upDSQL As String
    qrySQL = "SELECT Table1.ID,Table1.EmailAddress, Table1.[First Name], Table1.[Last Name] FROM Table1 WHERE Table1.Printed=No;"
    Set rst = CurrentDb.OpenRecordset(qrySQL)
        Do Until rst.EOF
            upDSQL = "UPDATE Table1 Set Printed = Yes WHERE ID =" & rst.Fields("ID").Value
            CurrentDb.Execute upDSQL, dbFailOnError
            'DoCmd.RunSQL upDSQL
            rst.MoveNext
        Loop
    End Sub
    

    Tuesday, September 27, 2016 12:58 PM
    Moderator
  • My code is only an example of course.  You need to modify it to suit the context in which it is called in your own database.

    In my hypothetical example EmployeeID would be the name of the (usually autonumber) primary key of an Employees table, and a foreign key in the Sales table.  One or the other of these would be included in the report's recordset.

    cboEmployee would be the name of a control in the form in whose module the code is executed, set up like this:

    ControlSource:   EmployeeID (for a bound control; leave blank if the combo box is an unbound 'navigational' control)

    RowSource:     SELECT EmployeeID, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

    Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

    RowSource:     SELECT EmployeeID, LastName & ", " & FirstName FROM Employees ORDER BY LastName, FirstName;

    My guess would be that the type mismatch error which you are experiencing is due to your Operator column being of text data type, so when you assign a value to the strCriteria variably you need to enclose the value in literal quotes characters, e.g.

        strCriteria = "Operator = """ & Me.Operator  & """ AND HasPrinted = FALSE"

    However, if Operator is the name of the operator, this is not bulletproof as personal names can legitimately be duplicated, and this is not uncommon, even in quite small workgroups.  I worked with two Maggie Taylors.  This is why a person should always be identified by a distinct numeric key as in my example.

    Ken Sheridan, Stafford, England

    Tuesday, September 27, 2016 3:44 PM
  • thank you all

    i build the code different 

    
    
    Private Sub btn334_Click()
    DoCmd.PrintOut PrintRange:=acPrintAll, PrintQuality:=acHigh, CollateCopies:=True
    Dim rst As RecordsetSet rst = CurrentDb.OpenRecordset(Name:="my query", Type:=RecordsetTypeEnum.dbOpenDynaset)
    Do While Not rst.EOF
     CurrentDb.Execute "myquery set printed=true whereprinted=false"       
     rst.MoveNext
    Loop 
     End Sub





    • Edited by eshay1 Wednesday, September 28, 2016 11:36 PM
    • Marked as answer by eshay1 Wednesday, September 28, 2016 11:38 PM
    Wednesday, September 28, 2016 11:29 PM