Print a report from the last time the report was printed

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)

 

Answer 1

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

Answer 2

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  

 

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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