Change value after export

[buratti]

I have a button on my home form that when clicked, it exports all records in a certain query to excel, and also opens a report based on the same query so I can view the exported records in a more organized format. The query is based on a status field where the value is "new". I need to change the status field of all exported records from "new" to "exported" whenever the button is clicked. I worked a little with changing my original query to an update query, but then my report based on the same query will not open, and it will not export correctly either. Any suggestions on how to accomplish this?

 

[dcb]

After your current code run the same query as a update query as part of the same sub

 

[buratti]

Thanks, that seemed to work so far. I haven't tested thoroughly though. This brings up another problem though. Like stated before, I have code that exports the query to excel. The code for that is:
DoCmd.OutputTo acOutputQuery, "Exported charges", "Excel97-Excel2003Workbook(*.xls)", "", False, "", 0, acExportQualityPrint


Upon running that code it asks me where I want to save the file. Fine, no problem, but if I click cancel (if I do not want to export at this time), it throws up a visual basic runtime error 2501 "the output to action was canceled". It’s obvious why it’s doing that, but is there a way to remove that error message if I choose not to export (click cancel in the save to window)?

 

[dcb]

Thanks, that seemed to work so far. I haven't tested thoroughly though. This brings up another problem though. Like stated before, I have code that exports the query to excel. The code for that is:
DoCmd.OutputTo acOutputQuery, "Exported charges", "Excel97-Excel2003Workbook(*.xls)", "", False, "", 0, acExportQualityPrint


Upon running that code it asks me where I want to save the file. Fine, no problem, but if I click cancel (if I do not want to export at this time), it throws up a visual basic runtime error 2501 "the output to action was canceled". It’s obvious why it’s doing that, but is there a way to remove that error message if I choose not to export (click cancel in the save to window)?

Hell Yes

Its called error trapping or error handling - search the help file for the latter
Good stuff to get your mind around as you can do alot with it

Basically start your code with
On error goto Error_Handler:

In your Error Handler you can then redirect, display, exit whatever you want based on a error

Such as in your case you may want to stop the update if the user cancels or possibly let it run - your choice.

A possible flow without typing all the code would be

Error_Handler:

If err.number = 2501 Then ''User cancelled
stop update would be Exit Sub
continue update - use the goto command
ask user - use the msgbox return
etc etc etc
ELSE
Dont forget to do something with other possible errors
Help file will show you the exact way but its something like
MsgBox err.Number & " - " & err.Description
resume next
End IF