I'd like to be able to open a report in VBA and have it go IN ONE STEP to a unique file name, as fed to it somewhere in the code.
I run a routine that prints a report over and over based on customer codes provided by a table. I don't want to print the reports to paper, but to .pdf. Eventually, these reports will be emailed in batches to field salespeople to use in their sales calls.
However, I have a problem. Right now I use a .pdf (not Adobe) driver that prints to my desktop (I don't want to mess with my default, in case I forget to reset it at the end of the session). In a separate step, I then rename and move the file to its proper location. However, this all takes time, and there are times when I get a message asking if I want to overwrite the file. The dialog box always points to the desktop file. Obviously, the two-step of the previous report(s) took longer than the program did to generate the next report, and I can't be sure that I'm not missing some customers' reports when I click 'yes' to the overwrite question.
I think that if I did it in one step, I wouldn't have the potential problem. In order to do that, I need to provide Access with two data: 1-the target folder, and 2-the customer code, which is the actual file name (drive\folder\customercode.pdf). How can I do this all from within VBA?
My current code is:
Name "C:\Documents and Settings\SL\Desktop\rptUnboughtItems.pdf" As "Q:\Reports\UnboughtItems\" & !custno & ".pdf"