Dynamically change report name on output (1 Viewer)

[KellySmith]

My application generates several different reports which must be archived for historical purposes. Currently I create the reports manually and save each one with the following format:

012701ReportName.doc

(or .rtf, .xls etc.)

I have a command button on my form which will save my report, but right now it will only save it as "ReportName.rtf" and each time I click the button, it overwrites the previous report without prompting me for a new file name.

How can I put a specific date (i.e. the week-ending date) in front of the report name when I output it? I would like Access to automatically date the report based on the last day of the current week, if possible.

Thank you!
Kelly Smith

[This message has been edited by KellySmith (edited 01-29-2001).]

 

[Louise]

First of all the report name: Before you run your save command, create the name of the report and assign it to a variable. Then the variable is used as the object name in your save command.

To create the name based on last day in week, you could use something like the dhLastDayInWeek function, described in the following article:
http://msdn.microsoft.com/library/partbook/vbahand/findingbeginningorendofweek.htm

Hope this helps!

 

[KellySmith]

Woohoo! It worked!

Thank you for your help, Louise. After some messing around, this is what I came up with. It may not be elegant, but it works.

Private Sub cmd_Save_Defect_Report_Click()
On Error GoTo Err_cmd_Save_Defect_Report_Click

Dim stDocName As String
Dim myDate As String
Dim myPath As String


'Before I save the file, I need to append the week ending date to the report name.

'Call the LastDayInWeek function, passing in today's date.
'Format the returned date as string in my custom format.
myDate = Format(dhLastDayInWeek(Date), "mm_dd_yyyy")


'Put the name of the report into a variable.
stDocName = "Defect Report"

'Put the path into a variable, and append the week-ending date to the doc name.
myPath = ("C:\KellyTest\" + myDate + stDocName)


'Output the file.
DoCmd.OutputTo acReport, stDocName, acFormatRTF, myPath + ".doc", False



Exit_cmd_Save_Defect_Report_Click:
Exit Sub

Err_cmd_Save_Defect_Report_Click:
MsgBox Err.Description
Resume Exit_cmd_Save_Defect_Report_Click

End Sub

I had to put the path into a variable because the output statement did not like any of the ways I tried to append the date to the report name.

Using ".doc" lets me save it as a Word doc file, and it opens just fine. (I saw several messages where people wanted .docs instead of .rtfs, so maybe this will help them too.)

Kelly

[This message has been edited by KellySmith (edited 01-30-2001).]

[This message has been edited by KellySmith (edited 01-30-2001).]