How to automatically name reports

[Eric the Viking]

I have a command button on a continuous form that opens a report for an individual in my fishing club with the following:

Private Sub Command22_Click()
DoCmd.OpenReport "rptMembersLedgerDebitsAndReceiptsTEMP", , , "Surname='" & Me.Surname & "'AND FirstName='" & Me.FirstName & "'"
End Sub

Rather than having to manually name the report when I save it to a folder I would like to be able to have it automatically add the members name and the date so all I have to do is click save. I cannot find a way of doing this so I am hoping someone out there has some code that works.

Many thanks for reading and hopefully guiding me in the right direction.

Cheers

Eric

 

[Gasman]

Save it as pdf. Plenty of examples out there, plenty here as well.

 

[The_Doc_Man]

Look at the "Output To" option, which allows you to specify a file name.

DoCmd.OutputTo method (Access)

Office VBA reference topic

learn.microsoft.com

 

[Eric the Viking]

Look at the "Output To" option, which allows you to specify a file name.

DoCmd.OutputTo method (Access)

Office VBA reference topic

learn.microsoft.com

Thanks but I am struggling with the syntax for this and keep getting a Compile error.

 

[Gasman]

Thanks but I am struggling with the syntax for this and keep getting a Compile error.

Well we are not mind readers.

Show us what you have been using.

 

[Eric the Viking]

This most lately which doesn't return an error but does not add a name:

DoCmd.OutputTo acOutputReport, , , "Surname='" & Me.Surname & "'AND FirstName='" & Me.FirstName & "'"".pdf", False

 

[Gasman]

Have you looked that link?
Perhaps this is simpler?

Export dynamically filtered Access report to PDF

How to filter and export an Access Report in three lines of VBA code.

codekabinett.com

 

[Eric the Viking]

Have you looked that link?
Perhaps this is simpler?

Export dynamically filtered Access report to PDF

How to filter and export an Access Report in three lines of VBA code.

codekabinett.com

I understand but this still doesn't add a unique name to the report which is what I am trying to achieve.

 

[Gasman]

Really?,
It shows you how to specify a filename, and where it is used.
All you have to do is construct the filename using whatever you want using concatenation, which you appear to know, going from your initial post?
If not google for examples or search here.

 

[Eric the Viking]

Really?,
It shows you how to specify a filename, and where it is used.
All you have to do is construct the filename using whatever you want using concatenation, which you appear to know, going from your initial post?
If not google for examples or search here.

I am no expert hence my confusion. This is what I tried:

Dim reportName As String
Dim fileName As String
Dim criteria As String

reportName = "rptMembersLedgerDebitsAndReceiptsTEMP"
fileName = "C:\Users\John\OneDrive\Desktop\Wyresdale Ledger accounts November 2022"\"Surname='" & Me.Surname & "'AND FirstName='" & Me.FirstName & "'"
criteria = "Surname='" & Me.Surname & "'AND FirstName='" & Me.FirstName & "'"

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo

Any thoughts?

 

[Gasman]

No, you just concatenate the variables?
Simple example

Filename = me.surname & "_" & me.firstname

Now YOU can prefix that with a path of your choosing.
Debug.print filename to ensure you get it right.

 

[Eric the Viking]

No, you just concatenate the variables?
Simple example

Filename = me.surname & "_" & me.firstname

Now YOU can prefix that with a path of your choosing.
Debug.print filename to ensure you get it right.

Tried this:

Dim reportName As String
Dim fileName As String
Dim criteria As String

reportName = "rptMembersLedgerDebitsAndReceiptsTEMP"
PathName = "C:\Users\John\OneDrive\Desktop\Wyresdale Ledger accounts November 2022\"
fileName = PathName & Me.Surname & "_" & Me.FirstName
criteria = "Surname='" & Me.Surname & "'AND FirstName='" & Me.FirstName & "'"

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo

But although it saved a file with the correct name it was not formatted as a pdf. Sorry if I am being dim but I was only a surgeon in my pre retirement life

 

[Eric the Viking]

Ok I added:

&.".pdf"

to the file name and everything works.

But I am still uncertain why acFormatPDF wasn't sufficient to achieve the desired result.

Any idea what I am missing here?

Cheers

Eric

 

[Eric the Viking]

For some reason I also can't add Date() to the file name....any thoughts on how to do this?

 

[Gasman]

Yes, but you need to show how you are trying to do it?

Yes, and I was only a lowly engineer.
I once had a doctor on the phone when I worked tech support, who could not get on the net. After half an hour of various changes, I had to disable the firewall which she did not want to do. ALl of a sudden she was on the net.
Her response 'Oh I had a Nortan update at 09:30 and I have not been able to get on since then' ???

If only she had told me first , plus my TL was listening in on the call and I got told off, as disabling the firewall is the first thing we should do, but she did not want that. Bit like me coming into theatre and saying you need to cut out my painful organ, but I am not telling you which one it is?
So it takes all sorts.

I will give a hint as dates are really just numbers. You need to format that date to the way you would like.
In the UK here I would use ddmmyyyy or perhaps yyyymmdd. Do not use / in the format, as they are not allowed in window's filenames.

 

[Gasman]

Ok I added:

&.".pdf"

to the file name and everything works.

But I am still uncertain why acFormatPDF wasn't sufficient to achieve the desired result.

Any idea what I am missing here?

Cheers

Eric

The clue is in the name
acFormatPDF

The file will be output in that format? that is all. Nothing to do with filenames.

Like me asking you to take out my kidney. You know which organ, but not which location?
WIth computers, you need to be specific. Same applies to surgery I hope, so the correct kidney gets removed?

 

[Pat Hartman]

The first two samples that experts linked to did NOT address the question of how to format the file name, hence the continued confusion.

@Eric the Viking test your code with the surname O'Brien. You will get an error or incorrect results. This is a two-part problem.
1. How to handle the ' in a search
2. How to avoid the ' in the file name

To solve #1:
I always define a constant in a standard module (not in a form's class module)

Public Const QUOTE = """"

This gives me the ability to make strings with embedded quotes more easily.

Also, whenever you build a string in VBA, it is best to build it into a variable rather than where you are using it as you are doing.

Private Sub Command22_Click()
Dim strWhere
strWhere = "Surname=" & QUOTE & Me.Surname & QUOTE & " AND FirstName=" & QUOTE & Me.FirstName & QUOTE
DoCmd.OpenReport "rptMembersLedgerDebitsAndReceiptsTEMP", , , strWhere
End Sub

Also, it is always best to give each control a MEANINGFUL name IMMEDIATELY after you create it and BEFORE you use it for anything. A better name for Command22 would be cmdExportToPDF

To solve #2,
You have to substitute space or a ZLS (ZeroLengthString) for the single quote in any name

Dim strSurName
strSurName = Replace(Me.SurName, "'", "")
fileName = PathName & strSurName & "_" & Me.FirstName
End Sub