Change Name Of PDF file

[Michael57]

I have a series of quotes that I print in PDF from Access however everytime I print the quote it comes up with the same file name which I have to modify so it does not over write the previous quote as I print a series of quotes at one time. The code is show below can anyone help me with a way to automatically have the name of the file = the quote number

Private Sub Command15_Click()
PrintQuoteNo = Me.StartQuoteNo
While (PrintQuoteNo <= Me.EndQuoteNo)

DoCmd.OpenReport "QuotePrintSignedPDF", acViewNormal, , "QuoteNo =" & PrintQuoteNo
DoCmd.Close acReport, "QuotePrint"
PrintQuoteNo = (PrintQuoteNo + 1)
Wend
End Sub

 

[Remou]

How about the Name Statement? If the report takes much time to run, you may need a delay, otherwise it may suit:

[tt]OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file. [/tt]

 

[Michael57]

Thanks Remou but not sure how or where I would put this in the code.

 

[Remou]

This code:
[tt]DoCmd.OpenReport "QuotePrintSignedPDF", acViewNormal, , "QuoteNo =" & PrintQuoteNo
DoCmd.Close acReport, "QuotePrint"[/tt]
generates the file, is that right?

So just after these lines. You can also use Dir to check if the file exists.

 

[Michael57]

Yes you are correct that generates the file but If I put your code in after those lines the file will have already been generated with the name QuotePrintSignedPDF.pdf

 

[Remou]

The idea is to rename the file after it has been created, as it will not be possible to create the report with another name, so:

DoCmd.OpenReport "QuotePrintSignedPDF", acViewNormal, , "QuoteNo =" & PrintQuoteNo
    DoCmd.Close acReport, "QuotePrint" 'Is the name here a typo?

Name "QuotePrintSignedPDF.pdf" As "Quote" & PrintQuoteNo & ".pdf"

 

[Michael57]

I have tried the code in several ways and I get Runtime error 53 file not found

Thanks for sticking with me!

 

[Remou]

The thing is, I do not have all the deatils, so I can only give you an outline. Have you included the full path for each file? If so, thy stepping through the code, as this will cause a delay. Do you get the same error stepping?

 

[Michael57]

This is how I tried it and still received an error:
Name "C:\Documents and Settings\mventrella\Desktop\QuotePrintSignedPDF.pdf" As "Quote" & PrintQuoteNo & ".pdf"

Not sure how to step program

 

[Remou]

To step through code, click on a line and press F9, this will create a break point, marking the line with a large brown dot. When you run the code, it will stop at this line, allowing you to press F8 to step to the next line.

You need to put the path on both sides, otherwise Name is likely to rename and move the file.

 

[Michael57]

I have done that and get the same error.

 

[Remou]

It seems that there may be a problem with the file name. It may be worth running your code once, without the rename, to ensure that the file exists where you expect, and then trying the rename in a separate procedure. That way it should be possible to sort out exactly where the error is occurring.

 

[Michael57]

The code works without the rename

 

[Remou]

I think that was already clear. Does the rename work, once the code has run?

 

[Michael57]

It will rename the file but how do I now link that to the actual quote number.

Thanks for hanging in.

 

[Remou]

OK. Try this.

Sub WaitABit()
PauseTime = 5 '5 seconds
varStart = Timer
Do While Timer < varStart + PauseTime
    DoEvents  
Loop
End Sub

Sub OutputReport()
'Your code here
DoCmd.OpenReport "QuotePrintSignedPDF", acViewNormal, , "QuoteNo =" & PrintQuoteNo

FileFound = Dir("C:\Documents and Settings\mventrella\Desktop\QuotePrintSignedPDF.pdf")

Do While FileFound = ""
    WaitABit
    FileFound = Dir("C:\Documents and Settings\mventrella\Desktop\QuotePrintSignedPDF.pdf")
Loop

Name  "C:\Documents and Settings\mventrella\Desktop\QuotePrintSignedPDF.pdf"  As  "C:\Documents and Settings\mventrella\Desktop\Quote" & PrintQuoteNo & ".pdf"

'Your code here
End Sub

If you try to name a file with an existing name, you will get an error that you need to check for.

 

[Michael57]

Im sorry but you lost me here is my code could you put everything together for me as it should appear. Thanks.

Private Sub Command15_Click()
PrintQuoteNo = Me.StartQuoteNo
While (PrintQuoteNo <= Me.EndQuoteNo)
DoCmd.OpenReport "QuotePrintSignedPDF", acViewNormal, , "QuoteNo =" & PrintQuoteNo
DoCmd.Close acReport, "QuotePrint"
Name "C:\Documents and Settings\mventrella\Desktop\QuotePrintSignedPDF.pdf" As "C:\Documents and Settings\mventrella\Desktop\& PrintQuoteNo & .pdf"
PrintQuoteNo = (PrintQuoteNo + 1)
Wend

End Sub

 

[Michael57]

I got it working now with a lot of your help. Thank you so much you have been a great help. I hope you have a super day!

 

[Remou]

I more or less have! Test as it as it is setting PrintQuoteNo to an actual number, then test adding in the loop.