Print Only New Orders?

[Guest]

Good Day All,

I have a form that I use to create new orders, within the form are all
the details for the order, including a check box "ordersent" (default value
of false" what I want is a report button that will open all the reports in
which this value is false, then when the printing is complete and the report
is closed, the check box values are updated to "true"

Any tips?

Thanks,

Brook

 

[Allen Browne]

Brook, there are too many things that can go wrong to simply assume that all
records were printed when the report closes.

Examples:
- The user only previewed the report without printing.
- Some or all of the printout failed (e.g. paper jam).
- The report was open in preview for quite a while before it was closed
(e.g. phone call), and other users have added further records since so there
are other new records which have not been printed.

So, even though you could use the Close event procedure of the report to
Exceute an Update query statement to set the OrderSent field to True where
it is False, that approach would not be reliable.

A better solution is to create another table where you store the primary key
values of the records in the report as a print batch. With this approach,
you know exactly what records were printed and when, and you can reprint a
batch if something goes wrong. The table would have fields like this:
BatchID Number a number for the print run
OrderID Number the primary key of the records to be printed.
You would populate the table by executing an Append query statement *before*
you run the report. The report's RecordSource query includes this table, so
it only contains the records in the batch.

 

[Guest]

Hello Allen,

I guess I was a little unclear on what I was wanting,

maybe this will clear things up.

That is what I am wanting to do, have the reports come to the screen (print
preview), then the user will manually print the reports, then on close of the
report (after a successful print), a confirmation box will pop up "Did the
reports Print Properly?" Yes will close and update / No will go back to the
preview.

Brook

 

[Allen Browne]

You can use the Close event of the report to execute an Update query
statement to change the yes/no field from yes to no:

Private Sub Report_Close()
Dim strSql As String
If MsgBox("Printed okay?", vbYesNo) = vbYes Then
strSql = "UPDATE Table1 SET ordersent = True WHERE ordersent =
False;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End If
End Sub

However, I suggest you do NOT take that approach:
1. You cannot cancel the event if the user says it did not print correctly.

2. You have no way to know which records were shown in the report when it is
closing (i.e. there might be new records that were not in the report at the
time it was opened.)

 

[Guest]

So you saying that if the user selects "NO" then that will not cancel the
update event?

What would you suggest that I do instead of this option?

Brook

 

[Guest]

Good Day,

I have tried the code that you provided and I am getting a Compile Error on
the line starting in "strSQL = .."

Is there something I am missing, something else I need to do?

Brook

 

[Allen Browne]

The line beginning:
strSql = "UPDATE ...
just assigns whatever is in quotes to the string, so that should not fail.
Note that the False bit is part of that line, even though it wrapped onto
the next line in the post.

The actual contents of the SQL string will depend on the structure of your
table. Replace Table1 with the name of your table. If you are not sure what
this string should look like:
1. Create a query into your table.
2. Drag the ordersent field into the grid.
3. In the Criteria row under this field, ener: False
4. Change it to an Update query (Update on Query menu).
5. In the Update row, enter: True
6. Switch to SQL View (View menu).
7. Delete the line endings so the entire statement is on one line.
8. Copy what you see there inside the quotes into your code.
It goes inside the quotes on the line starting:
strSql =