Generating an output filename for a report 1

[jmacvean]

I have a report based on a query of an open form. The query selects the data for the report based on field information gleaned from the open form. I then output the report in html format to a designated directory on the network.

The user has to type in a file name to save the report to.

I would like to automatically generate the file name by concatenating two fields from the open form. The fields are a date field and a numeric field that designates the shift the user is working (we operate 3 shifts, 1st, 2nd, and 3rd.)

I am currently generating the report using a macro run from a command button. The macro uses the output to function to send the report. In the macro design window I can enter the path and the file name. It is in this macro that I would like to automatically generate the file name from information on the open form.

Thanks in advance for your help.

 

[CosmoKramer]

In a new module, add a function similar to this example of mine, of course adapting it to your situation:

Function Xferdoc_MonthStat()

Dim strFileName As String
strFileName = "c:\SMH Monthly Statistics Report - " & Forms!frmMain!txtStats
DoCmd.OutputTo acOutputReport, "rptMonthlyStats", acFormatRTF, strFileName
End Function

Change your macro to perform a RunCode action against your function name.

Hope this helps.....


Hoc nomen meum verum non est.

 

[jmacvean]

Thanks to CosmoKramer. His reply to my question was right on. After trying the code I had to make a modification to one of the fields I was trying to use in the filename however. One of the fields was a date field. I had to chance the backslashes in between the day, month and year to dashes. Access 97 kept giving me cryptic error messages about my disk being full, saying it could not save the file at this time. The real problem was a basic Microsoft Windows file naming convention - you can't use a backslash in a filename.

Once I changed the backslashes to dashes I was able to use the automated filename I generated in the output to macro. I also was able to use it in the small piece of code that CosmoKramer suggested in his reply to my original post.

Thanks again Cosmo.

 

[CosmoKramer]

You're welcome...Glad I could help

Hoc nomen meum verum non est.