On Report Print, Run Query

[tuoggy]

I'm not sure if this is possible in Access, but I was wondering if there is a way to automatically run an Update query when a report is printed.

More specifically, I have confirmation reports, and I want the date printed and confirmation sent fields to be updated upon printing. I have an update query ready, but because I'm not going to be the person actually doing the work, I'd like the update query to be run automatically.

 

[Rabbie]

You could try firing the query from the On Print event of the report footer.

 

[gemma-the-husky]

if you need to include the results IN the report, then run it in the report open event

 

[AC5FF]

I do this for several reports; not sure it's the best way but it works...
My button to control the print links to an event written as:

Private Sub FlightAllReport_Click()
On Error GoTo Err_FlightAllReport_Click

Dim stDocName As String

stDocName = "MD5FlightDelete" 'EmptyFlightTable
DoCmd.OpenQuery stDocName

stDocName = "MD5FlightBuild" 'LoadFlightTable
DoCmd.OpenQuery stDocName

stDocName = "MD5FlightAllReport"
DoCmd.OpenReport stDocName, acPreview

Exit_FlightAllReport_Click:
Exit Sub

Err_FlightAllReport_Click:
MsgBox Err.Description
Resume Exit_FlightAllReport_Click

End Sub

You could also do the same w/a macro. Point the button to a macro written to delete/append/update queries and then open report.

 

[tuoggy]

You could try firing the query from the On Print event of the report footer.

Thank you very much, I guess I never saw that option. That makes it very simple.

 

[AC5FF]

Rabbie;

Can you explain this option? Not sure how to access that or to set it up. However, it might help me w/another option I was looking at needing to do...

Gemma;
Same question... Run the report as an open event?

What I have is a history report that runs by a user selectable date field. I need to run this same report, but for a 12 month history... Basically re-run the same report 12 times, but will also need additional calculations

 

[Rabbie]

Rabbie;

Can you explain this option? Not sure how to access that or to set it up. However, it might help me w/another option I was looking at needing to do...

Open the Properties sheet of the report footer section in design Mode. and then write some code to run your update query from the OnPrint Event.

 

[AC5FF]

I see what you are talking about now. Thanks
However, I don't think this will exactly work how I need it to.
I need the data from the table that is created for 1 months worth of data to be saved so I can add every month's data. This all needs to be exported to excel (only because that's the easiest way for me to manipulate the data into the graphs i want to use).

Again, I have a few other ideas, just been too busy with other projects to give it all a try... This however will/can come into handy in the future.. Thanks!