Name and export a report using VBA

[Eric the Viking]

Dear All

Happy New Year

I am trying to find a way to use VBA to name and export a report.
I have a continuous form displaying records with a button to generate the report when I need to.
I am using the following VBA for the onclick event of the button to open the report:
DoCmd.OpenReport "rptMembersLedgerDebitsAndReceiptsTEMP", , , "Surname='" & Me.Surname & "'AND FirstName='" & Me.FirstName & "'"
I can then name and save this as a pdf but it's a bit tedious and I would prefer the report to save to a folder with the days date date, surname and first name as the file name.
I would appreciate some guidance please.

All the best Eric

 

[The_Doc_Man]

I would prefer the report to save to a folder with the days date date, surname and first name as the file name.

Look up DoCmd.OutputTo as a way to select format (i.e. PDF) and supply a file name at the same time.

Then it is merely a matter of selecting and concatenating the name parts for the file and concatenating the drive & path. Tedious? Yep, it is always a bit tedious doing concatenation - but you only have to CODE it once. After that, Access gets to do the tedious stuff.

 

[bastanu]

In addition to the advice above from the_Doc_Man please note that Docmd.OutputTo does not have the same Where clause like Docmd.OpenReport has, so you either need to build it straight in the report's record source (meaning you create a version of your report that only returns the current record on the form and export that) or you need to call the OpenReport first like you're doing now (can use the acHidden argument to hide it) then the Docmd.OutputTo the Docmd.Close to close the report.

Cheers,

 

[MarkK]

You can also take a look at the DoCmd.SetParameter() method, described here.

 

[SHANEMAC51]

Function macro21()
    Dim sname, spath
    Dim s1
    On Error Resume Next
    spath = "c:\temp3"
    MkDir spath
    spath = sname & "\" & Format(Date, "yyyy-mm-dd_")
    MkDir spath
    s1 = InputBox("last name(a,b,c,[a-z])", , "a")
        DoCmd.OpenReport "Hollywood Park Results", acViewReport, "", "[horse name] Like '" & s1 & "*'", acHidden
    DoCmd.OutputTo acOutputReport, "Hollywood Park Results", "PDFFormat(*.pdf)", spath & ".pdf", True, "", , acExportQualityPrint
    DoCmd.Close acReport, "Hollywood Park Results"

End Function