Name and export a report using VBA (1 Viewer)

[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

 

[Pat Hartman]

The most efficient way to do this is to use a query as the RecordSource for the report and the query has a WHERE clause that refers to a form control. If the report is run from multiple forms, then rather than using a form control, put the PK value in a tempvar and use that.

Select ... From ... Where YourPK = Forms!yourform!txtYourPK

Not sure why you are using firstname and last name to create this report but that is likely to cause a problem at some point in time because you will end up with producing one report that mushes the data for multiple people.

Since as has been mentioned the OutputTo method does not take a where argument, the alternate solution requires that you open the report first, using the where argument and then run the outputTo method and than close the report is less efficient. If you are only doing one report at a time it makes no difference but if you are doing hundreds as I frequently do, having to open the report first seriously slows down the process.