Can a Report's OnPrint event update the Report's Recordsource? Ed B 14.09.2006 00:00:01 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

Re: Can a Report's OnPrint event update the Report's Recordsource? "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 14.09.2006 00:24:26 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)? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Ed B" <EdB[ at ]discussions.microsoft.com> wrote in message news:464C8104-31AD-4BBA-A743-539F125F9B03[ at ]microsoft.com... [Quoted Text] > 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

Re: Can a Report's OnPrint event update the Report's Recordsource? Ed B 14.09.2006 01:20:02 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 "Douglas J. Steele" wrote: [Quoted Text] > 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)? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Ed B" <EdB[ at ]discussions.microsoft.com> wrote in message > news:464C8104-31AD-4BBA-A743-539F125F9B03[ at ]microsoft.com... > > 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 > > >

RE: Can a Report's OnPrint event update the Report's Recordsource? Ed B 14.09.2006 02:31:02 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