Can a Report's OnPrint event update the Report's Recordsource?

[Ed B]

Hi,

My report's recordsource is a single-table query. I would like to record
the date/time that each record was printed in a field in that same table. It
seems to me that the report detail's OnPrint is the right event within which
to try to update the Date/Time field in the recordsource table.

The only problem is I can't seem to get Access to let me do it.

The recordsource query is a Dynaset, and I have verified that I can update
the field manually from there.

Is what I want to do possible from within the report? I have successfully
done this in the past by calling a subroutine from within the OnPrint event,
but I am hoping that there is a simpler approach whereby I can get the report
to do it by itself, without having to call such a routine.

Thanks in advance,

Ed

 

[Douglas J. Steele]

What code are you trying to use, and what happens when you try to use that
code? Do you get an error? If so, what's the error (and what line of code is
causing it)?

 

[Ed B]

Hi Douglas,

The code I'm trying to make work is (more or less):

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
'only update the Date field once, in case the user traverses the
Print Preview
Me.Date_ComplianceNotification = Now()
End If
End Sub

where Date_ComplianceNotification is the field in the report's recordsource
that I want to update.

The error (for that attempt) is: Run-time error '2448': You can't assign a
value to that object.

When I tried instead to update the field via a (hidden) text box bound to
that field in the report detail like this:

...
Me.tbhDate_ComplianceNotification = Now()
...

The error message is: Run-time error '-2147352567 (80020009)': You can't
assign a value to this object.

[Interesting: first 'that' then 'this' ;-) ]

I am trying to avoid having to open the same recordset in parallel and then
search it for the current record to do this update, since the report is
always at the record I need to update when I need to do so. Nonetheless,
Access does seem to want my code to have updatable access to the data set
from within the report.

TIA,

Ed

 

[Ed B]

It appears that the primary problem here is that the RecordSource of a report
is always/only a Snapshot dataset, even if defined otherwise in the Query
Builder.

Anyway, here's one possible solution that works:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
CurrentDb.Execute "UPDATE tblReportData SET Date_ReportPrinted =
Now() WHERE ReporteeID=" & Me.tbhReporteeID
End If
End Sub

It appears that this only works if the 'ReporteeID' is bound to a (can be
hidden, as in my case) text box in the report detail. In other words, even
though it is part of the Record Souce, {...WHERE ReporteeID=" &
Me.ReporteeID} does not work

Thanks, Kevin Bell