Update rows Printed column to current date/time upon report print

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If anyone can help, it is appreciated. I have a report that runs off a
query; this query shows rows of a table that has STATUS:2 and PRINTED:null.
When my report prints this data, I want to update the PRINTED column in the
table (only those rows present in the query result) to NOW(). I've created a
form in datasheet view and I'm using a macro built in the detail of the
report that opens the form hidden, setvalue of PRINTED:Now(), and closes and
saves the form. Only the first row is updating with the date/time. Am I
doing this wrong; can someone point me in the right direction. A Developer I
am not. Thanks ahead of time.
 
I'd place VBA code in the report's DETAIL section ON FORMAT event that
updates the record(s) in question.
 
Jim said:
If anyone can help, it is appreciated. I have a report that runs off a
query; this query shows rows of a table that has STATUS:2 and PRINTED:null.
When my report prints this data, I want to update the PRINTED column in the
table (only those rows present in the query result) to NOW(). I've created a
form in datasheet view and I'm using a macro built in the detail of the
report that opens the form hidden, setvalue of PRINTED:Now(), and closes and
saves the form. Only the first row is updating with the date/time. Am I
doing this wrong; can someone point me in the right direction. A Developer I
am not.
Click to expand...

In the procedure that you use to open the report, add code
to run an UPDATE query that sets all the records;

DIm db As Database
Dim strSQL As String
strSQL = "UPDATE [the table] " _
& "SET PRINTED = Now() " _
& "WHERE STATUS = 2 " _
& " And PRINTED Is Null"
db.Execute strSQL, dbFailOnError
Set db = Nothing
 
Would this not update every record where status equals = 2 and Printed is
null. How would you do it for records that had actually printed and leave the
rest as is?

Marshall said:
If anyone can help, it is appreciated. I have a report that runs off a
query; this query shows rows of a table that has STATUS:2 and PRINTED:null.
Click to expand...
[quoted text clipped - 5 lines]
doing this wrong; can someone point me in the right direction. A Developer I
am not.
Click to expand...

In the procedure that you use to open the report, add code
to run an UPDATE query that sets all the records;

DIm db As Database
Dim strSQL As String
strSQL = "UPDATE [the table] " _
& "SET PRINTED = Now() " _
& "WHERE STATUS = 2 " _
& " And PRINTED Is Null"
db.Execute strSQL, dbFailOnError
Set db = Nothing
Click to expand...
 
jrmask said:
Would this not update every record where status
equals = 2 and Printed is null.
Click to expand...

That's true, but that's also what you said the report would
print. If the report is also filtered by other criteria,
then include that same criteria in the UPDATE query.

How would you do it for records that had actually
printed and leave the rest as is?
Click to expand...

You never know what has actually printed until you have the
printed pages in your hands (and put them in a safe place),
because all kinds of things can go wrong after Access has
processed a record in the report.

If you insist on doing it in the report, then use Dennis'
suggestion to execute an Update query:

DbEngine(0)(0).Execute "UPDATE table " _
"SET PRINTED = NOW() " _
"WHERE [keyfield]=" & Me.[keyfield]

but you really need a plan of what to do when the printer
jams or runs out of ink/toner in the middle of one of your
jobs.
 
I'm not sure I understand the help given, sorry. I used the code included in
the messages and I'm still getting errors in my report when I run it. My
local table is called STAINS1 and the field in the query and table is PRINTED
as a date/time field. This field is NULL. When I print the report, I want
to set the items included in the query results to PRINTED = Now(). when I
run the report next time and the query executes, I only want the PRINTED
items that are NULL to be listed. again, when that report is printed, set
those items values for PRINTED to Now(). This will allow the users to look
at the data in the STAINS1 table to get a list of printed items sorted by
date/time if they must reprint a sheet. I'm placing this code into the
Detail, code builder, printing, of the report. I must be doing something
wrong as I can't get this to work. Additional Info, the STAINS1 table is a
copy of a linked table to a SQL or Oracle database. I'm using a macro to
append items in the linked table to the local table so that I can add the
PRINTED column to the table. Hope that explains in detail what I'm trying to
accomplish...thanks lots for the help.
Marshall Barton said:
jrmask said:
Would this not update every record where status
equals = 2 and Printed is null.
Click to expand...

That's true, but that's also what you said the report would
print. If the report is also filtered by other criteria,
then include that same criteria in the UPDATE query.

How would you do it for records that had actually
printed and leave the rest as is?
Click to expand...

You never know what has actually printed until you have the
printed pages in your hands (and put them in a safe place),
because all kinds of things can go wrong after Access has
processed a record in the report.

If you insist on doing it in the report, then use Dennis'
suggestion to execute an Update query:

DbEngine(0)(0).Execute "UPDATE table " _
"SET PRINTED = NOW() " _
"WHERE [keyfield]=" & Me.[keyfield]

but you really need a plan of what to do when the printer
jams or runs out of ink/toner in the middle of one of your
jobs.
Click to expand...
 
Jim said:
I'm not sure I understand the help given, sorry. I used the code included in
the messages and I'm still getting errors in my report when I run it. My
local table is called STAINS1 and the field in the query and table is PRINTED
as a date/time field. This field is NULL. When I print the report, I want
to set the items included in the query results to PRINTED = Now(). when I
run the report next time and the query executes, I only want the PRINTED
items that are NULL to be listed. again, when that report is printed, set
those items values for PRINTED to Now(). This will allow the users to look
at the data in the STAINS1 table to get a list of printed items sorted by
date/time if they must reprint a sheet. I'm placing this code into the
Detail, code builder, printing, of the report. I must be doing something
wrong as I can't get this to work. Additional Info, the STAINS1 table is a
copy of a linked table to a SQL or Oracle database. I'm using a macro to
append items in the linked table to the local table so that I can add the
PRINTED column to the table.
Click to expand...


Please be specific about what you tried, the results you got
and any error messages that occurred. Use Copy/Paste
wherever possible to avoid typos.

What posted code did you use?

Did you replace the general names used as placeholders in
the posted code?
 
Adding the following in the detail section of the report under code builder:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim strSQL As String
strSQL = "UPDATE dbo_STAINS1 " _
& "SET PRINTED = Now() " _
& "WHERE STATUS = 2 " _
& " And PRINTED Is Null"
db.Execute strSQL, dbFailOnError
Set db = Nothing

End Sub

....I'm getting a "Run-Time error '91': Object variable or With block
variable not set.

When I type in the:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
DbEngine(0)(0).Execute "UPDATE dbo_STAINS1 " _
"SET PRINTED = NOW() " _
"WHERE PRINTED=" & Me.PRINTED


End Sub

....I'm getting a Visual Basic error right away before I'm able to save this
data...
...."Compile error: Expected: end of statement" and the second line of code
"SET PRINTED..." is backlit.

I'm currently trying to do with with a macro in the same report section...
Thanks for the help.
 
Jim said:
Adding the following in the detail section of the report under code builder:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim strSQL As String
strSQL = "UPDATE dbo_STAINS1 " _
& "SET PRINTED = Now() " _
& "WHERE STATUS = 2 " _
& " And PRINTED Is Null"
db.Execute strSQL, dbFailOnError
Set db = Nothing
End Sub

...I'm getting a "Run-Time error '91': Object variable or With block
variable not set.
Click to expand...

As you don't want to set all records at once, that's the
wrong code to use in the report.

When I type in the:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
DbEngine(0)(0).Execute "UPDATE dbo_STAINS1 " _
"SET PRINTED = NOW() " _
"WHERE PRINTED=" & Me.PRINTED
End Sub

...I'm getting a Visual Basic error right away before I'm able to save this
data...
..."Compile error: Expected: end of statement" and the second line of code
"SET PRINTED..." is backlit.
Click to expand...

This is the right idea, BUT the WHERE clause is totally off
base. It needs to select the same record that is being
processed in the detail section:

"WHERE keyfield = " & < Me.keyfield

Replace keyfield with the name of the primary key field in
the table.
I'm currently trying to do with with a macro in the same report section...
Click to expand...

Don't waste your time on a macro. The issue is to get the
query working, it's not about the code. Besides, you get
better error checking in VBA.
 
Back
Top