Creating Last Run Date for Reports

[housmand]

Good afternoon everyone,

I currently have a form set up to create and save reports in Excel format. I would like to include the date that the reports were last run. I currently have three buttons for running the report, Report A, Report B and one for both A&B. My form currently prompts for both the Start Date and End Date for the report. So I would like to have documented the Date the Report was Run and What Start and End Dates were specified.





And the code for my buttons is:

'------------------------------------------------------------
' Removal List
'
'------------------------------------------------------------
Private Sub Command0_Click()
On Error GoTo Command0_Click_Err


DoCmd.OutputTo acOutputQuery, "removeFAST", "ExcelWorkbook(*.xlsx)", CurrentProject.Path & "\" & Format(Date, "mm-dd-yyyy") & " remove FAST.xlsx", True, "", , acExportQualityPrint


Command0_Click_Exit:
   Exit Sub


Command0_Click_Err:
   MsgBox Error$
   Resume Command0_Click_Exit


End Sub




'------------------------------------------------------------
' Add List
'
'------------------------------------------------------------
Private Sub Command2_Click()
On Error GoTo Command2_Click_Err


   DoCmd.OutputTo acOutputQuery, "addFAST", "ExcelWorkbook(*.xlsx)", CurrentProject.Path & "\" & Format(Date, "mm-dd-yyyy") & " add FAST.xlsx", True, "", , acExportQualityPrint


Command2_Click_Exit:
   Exit Sub


Command2_Click_Err:
   MsgBox Error$
   Resume Command2_Click_Exit


End Sub




'------------------------------------------------------------
' Both
'
'------------------------------------------------------------
Private Sub Command1_Click()
On Error GoTo Command1_Click_Err


    DoCmd.OutputTo acOutputQuery, "addFAST", "ExcelWorkbook(*.xlsx)", CurrentProject.Path & "\" & Format(Date, "mm-dd-yyyy") & " add FAST.xlsx", True, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "removeFAST", "ExcelWorkbook(*.xlsx)", CurrentProject.Path & "\" & Format(Date, "mm-dd-yyyy") & " remove FAST.xlsx", True, "", , acExportQualityPrint


RunCommand acCmdSaveRecord


Command1_Click_Exit:
   Exit Sub


Command1_Click_Err:
   MsgBox Error$
   Resume Command1_Click_Exit


End Sub

On a side note, while I am asking about this, the report is currently set for CurrentProject.Path . How do I set it for a different path?


Thank you for all of your help, I appreciate it.

[ranman256]

In your output query, make a virtual field with the NOW() function.
It will go into the report.

set the path anywhere you want in the FILE parameter, instead of "CurrentProject.Path"

[June7]

As ranman suggests, construct fields in query with expressions. Expression can also reference control on form.

Path is just a string value. Use whatever you want.

[housmand]

Thank you for your quick responses. I wasn't looking for the date to be included in the report, I was looking for it to be displayed on my Form. I was thinking of creating a table and having it write to that, then to have my form indicate the date it pulls from the table.

[June7]

Can bind the FAST Access Reports form to that table. Can set the form to open only to new record. Code can populate the date Run fields.

[housmand]

Can bind the FAST Access Reports form to that table. Can set the form to open only to new record. Code can populate the date Run fields.

I'm sorry, I don't follow. Just to make sure I explained what my goal is. I want the form to display the date that it was last run. Or even, to display the date that the button was last pushed.

[ranman256]

My folks always want to know when the report ran, thats why it's in the report qry.

[June7]

Do you want to maintain history of these run dates? Or do you just want the one record and continually change the values?

[housmand]

I would prefer to maintain history, but if it's too difficult just the last run date.

[June7]

To maintain history, options:

1. open form to the latest record, code executes export, creates new record, closes form
RecordSource for the form can be: SELECT * FROM [Fast Dates] ORDER BY ID DESC;
To create the new record, execute an INSERT sql action, like: CurrentDb.Execute "INSERT INTO [Fast Dates](addFAST_run) VALUES(#" & Me.AddRun & "#)"

2. open form to blank new record and DMax() expression in textbox displays most recent date, code executes export, populates date fields, closes form


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention, including objects.

[housmand]

My folks always want to know when the report ran, thats why it's in the report qry.

I see. The purpose of the date in my case is so you can see when the last report was run when specifying the dates for your new report.

[housmand]

To maintain history, options:

1. open form to the latest record, code executes export, creates new record, closes form
RecordSource for the form can be: SELECT * FROM [Fast Dates] ORDER BY ID DESC;
To create the new record, execute an INSERT sql action, like: CurrentDb.Execute "INSERT INTO [Fast Dates](addFAST_run) VALUES(#" & Me.AddRun & "#)"

2. open form to blank new record and DMax() expression in textbox displays most recent date, code executes export, populates date fields, closes form


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention, including objects.

I am still having trouble understanding exactly what I should be doing. Is there any sample you have? I think the best approach for me, if possible, would be to insert code into the command buttons, to add the date to the table, then to have the text box display the most recent date. Do you mind helping me with that code, and how to set up the text box to display the most recent date?

Thank you for all of your help

[June7]

I don't have any sample. Option 1 appears simplest. Did you make attempt? I suggested code for saving record. Put it in button code if that's where you want it.

Option 1 opens form displaying the most recent record in table. Bind textboxes to the fields and the most recent dates will show. Then code will save new record for a new Run. What exactly is not clear?

[housmand]

I don't have any sample. Option 1 appears simplest. Did you make attempt? I suggested code for saving record. Put it in button code if that's where you want it.

Option 1 opens form displaying the most recent record in table. Bind textboxes to the fields and the most recent dates will show. Then code will save new record for a new Run. What exactly is not clear?

I inserted the code into the command button and I get an error indicating "Syntax error in date in query express '#'. This is the code I am using:

CurrentDb.Execute "INSERT INTO [tblFAST_dates](addFAST_run) VALUES(#" & Me.addFAST_run & "#)"

When I used your initial wording of Me.AddRun, it gave me an error saying the method or data member was not found.

[June7]

Certainly must use your actual control names, mine was just an example since I don't know your db.

There's nothing wrong with the code syntax so possibly issue is with the value in addFAST_run.

At this point will have to review your db. If you want to provide, follow instructions at bottom of my post.