Saving report as a PDF with custom file name, VBA

Question

Hello,

On a form I have two list boxes called LstBox_ChooseDivision and ListBox_Venue and an unbound text field called FileName.

I would like to create a button on the same form called "Save Agenda as PDF".

When I click this button I would like VBA to perform the following...

Save an Access report called "rpt_Meeting Agenda" as a pdf to a folder called "Meeting Agenda Archives" dynamically which is located in the same folder as the Access Database resides with a file name as such

[LstBox_ChooseDivision] [ListBox_Venue.column(1) -this a date and time field] [ListBox_Venue.column(2) - this is a text field of the meeting location] [ListBox_Venue.column(3) this is a text field of attendee names] Now() - today's date and time

Example

Arts, YYYY-MM-DD HH-MM, Small Conference Room, Sarah John, YYYY-MM-DD HH-MM.pdf

Once done a pop appears saying "Agenda has been saved to the Meeting Agenda Archives folder"

Could you help me with this?

Iram/mcp

Answer 1

The code behind a button would look like this:

  Dim strFileName      As String

   Dim strReport        As String

   strReport = "rpt_Meeting Agenda"

   strFileName = Me.LstBox_ChooseDivision & ", " & _

                 Format(Me.ListBox_Venue.Column(1), "YYYY-MM-DD HH-NN") & ", " & _

                 Me.ListBox_Venue.Column(2) & ", " & _

                 Me.ListBox_Venue.Column(3) & ", " & _

                 Format(Now(), "YYYY-MM-DD HH-NN") & ".pdf"

   strFileName = CurrentProject.Path & "\Meeting Agenda Archives" & strFileName

   DoCmd.OpenReport strReport, acViewPreview

   DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False

   DoCmd.Close acReport, strReport

So the above is "air" code, but should be very close to what you require.

Regards

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

>What about the Pdf Save Date part?

Try to start reading how the code works. Look at how we added the text for Meeting.

How we added the text for meeting is the same idea and same approach and same concept for the pdf save Date part.

You could change

 Me.ListBox_Venue.Column(2) & ", " & _

To

 Me.ListBox_Venue.Column(2) & ", Pdf Save Date " & _

For other topics, other questions, you not find anyone here eager to answer a "different" question attached here since no one will ever realize that you asking a different question! And MORE important how will how people benefit and see the answer + question to a different issue?

So for the file folder issue and other questions you REALLY need to start a new question and topic else it will get lost in the shuffle of other questions here.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

******@msn.com

Answer 3

Great.

Thanks for setting me on the right path.

Thank you.

Iram