Automatically export report as a snapshot

[Guest]

I know that you can export a report as a .snp using OutputTo, but is there a
way to have Access save the file to a specific location, but with a different
name for each file? I need to make backups of statements sent to customers in
case they don't receive them or dispute them, so I'm wanting to save a copy
of the statement as a .snp every time one is printed. But I want Access to do
this on it's own, using the account number of that customer as the filename
for the save. (I can do it manually, but I go through anywhere from 5 to 20
statements a day in addition to my other duties, plus I'm trying to make this
process easier in case someone has to do it when I'm not here.) I'm assuming
that I could use a function in the OutputTo statement to get the name, the
only question is how? My VBA skills are severely lacking, so any help is
appreciated.

 

[Guest]

Hi, Nicholas.

I'm assuming
that I could use a function in the OutputTo statement to get the name, the
only question is how?

In the form that displays the customer's account number, create a new button
and paste the following code into the form's code module:

Private Sub SaveAsSNPBtn_Click()

On Error GoTo ErrHandler

DoCmd.OutputTo acOutputReport, Me!txtAcctNum.Value, acFormatSNP, _
"C:\Statements\" & Me!txtAcctNum.Value & ".SNP"

Exit Sub

ErrHandler:

If (Err.Number = 2103) Then
MsgBox "The report name, " & Me!txtAcctNum.Value & ", is not valid."
& vbCrLf & _
"Please ensure that it is spelled correctly.", vbCritical +
vbOKOnly, "Invalid Report Name!"
Else
MsgBox "Error in SaveAsSNPBtn_Click( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
End If
Err.Clear

End Sub

Replace SaveAsSNPBtn with the name of your button, "C:\Statements\" with the
path where you want these snapshot files to be saved, and txtAcctNum with the
name of the text box holding your customer's account number. Save the code
module and compile the code.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

If I wanted to use a value from a table field instead of a value from the
form, I assume I could change this to:

DoCmd.OutputTo acOutputReport, [Mytable]![Myfield], acFormatSNP, _
"C:\Statements\" & [Mytable]![Myfield] &" - " & Today() & ".SNP"

and that would still work, correct?

Also, could this code work if used as a stand-alone module to call from a
macro?

 

[Douglas J Steele]

Well, there's no Today function in VBA, it's Now.

And whether or not that would work depends on what the user's date and time
separator characters have been set to (through Regional Settings). If it's a
slash and colon (so that Now returns, for instance, 09/22/2005 12:12:16),
no, it won't work, as those are invalid characters in file names. If you use
spaces or underscores or periods as separators, though, it should work.

That means that the safe approach is to explicitly format the date:

DoCmd.OutputTo acOutputReport, [Mytable]![Myfield], acFormatSNP, _
"C:\Statements\" & [Mytable]![Myfield] &" - " & _
Format(Now(), "mm\-dd\-yyyy hh\.nn\.ss") & ".SNP"

 

[Guest]

Well, I've tried everything I can think of, but for some reason now I keep
getting an Object Required error. I'm not sure on the syntax of this so
that's probably where I'm going wrong. What I need to do is have the report
named Itemized Statement saved after the user clicks the button on the form
for a Print Preview. This is what my code looks like right now:


Function SaveAsSNP()

On Error GoTo ErrHandler

DoCmd.OutputTo acOutputReport, [Reports]![Itemized Statement Type O],
acFormatSNP, "C:\My Documents\Sent Statements\" & Tables![Account Number
Field Table]![Account Number] & " - " & Format(Now(), "mm\-dd\-yyyy") & ".SNP"

Exit Function


I'm not real sure on the VBA translations of Access field names, so my
syntax is probably horrid here, but basically I want to save Itemized
Statement Type O (there are 4 statement types, this is just the first one I
picked) as a Snapshot file to my C:\My Documents\Sent Statements folder, with
a naming convention of "CUST00000 - 09/22/2005". What am I doing wrong?

Thank you all so much for your help, once I get this licked my job will be
that much more secure.

 

[Guest]

Hi, Nicholas.

If you have a form that the user selects to print the report, then in the
line of code after the report prints, you could try the following:

DoCmd.OutputTo acOutputReport, Me!txtReportType.Value, _
acFormatSNP, "C:\My Documents\Sent Statements\" & "CUST" & _
Format(Me![Account Number].Value, "00000") & _
" - " & Format(Date, "mm\-dd\-yyyy") & ".SNP"

Remember that a form's record source can be a query, so this query can
include the tables you need, including the Account Number Field Table and a
table listing the types of reports. Based upon this query, you could have
text boxes on the form showing the customer's account number (Account Number)
and the type of report you want to use displayed in a text box (in this
example, txtReportType, which you'll have to replace with your own name),
which shows "Itemized Statement Type O" for the current record. (A combo box
might be more desirable but since you're having trouble with VBA, let's just
try to keep it as simple as possible.)

I see a problem with this approach, though. Unless you've saved the report
with the current customer's information or the open report contains this
specific information, every file saved will have the same data (or no data at
all).

It might be easiest for us to help you if you posted your code that prints
out the customer's invoice for you. Or are you manually printing each
invoice? Or are you wanting to just save a batch of invoices, such as an
invoice for every customer's record in the table?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Well, I've tried everything I can think of, but for some reason now I keep
getting an Object Required error. I'm not sure on the syntax of this so
that's probably where I'm going wrong. What I need to do is have the report
named Itemized Statement saved after the user clicks the button on the form
for a Print Preview. This is what my code looks like right now:


Function SaveAsSNP()

On Error GoTo ErrHandler

DoCmd.OutputTo acOutputReport, [Reports]![Itemized Statement Type O],
acFormatSNP, "C:\My Documents\Sent Statements\" & Tables![Account Number
Field Table]![Account Number] & " - " & Format(Now(), "mm\-dd\-yyyy") & ".SNP"

Exit Function


I'm not real sure on the VBA translations of Access field names, so my
syntax is probably horrid here, but basically I want to save Itemized
Statement Type O (there are 4 statement types, this is just the first one I
picked) as a Snapshot file to my C:\My Documents\Sent Statements folder, with
a naming convention of "CUST00000 - 09/22/2005". What am I doing wrong?

Thank you all so much for your help, once I get this licked my job will be
that much more secure.

 

[Guest]

Well, I don't have code, I have about 15 queries, lol. The basic process of
generating the statement is something along these lines:

1. Import text file from the mainframe
2. Run queries to break down the data
3. Save statement header info in Customer Info temp table, and account info
in Statement temp table
4. Verify data
5. Print report

The way I have the print set up, you click the Print menu button from the
main form, which pops up a second window with the different report types,
since the statement formats are slightly different with each. One you click
that button, it automatically runs the report in Print mode. The report is
linked to the two main temp tables. The data isn't backed up in the database
anywhere, so once you run a new statement, you have to go back and re-run the
old one if you want it again... which is where my problem came about. If the
original input file is deleted, or the payoff information is lost (this comes
from the mainframe, but has to be entered manually... another problem I'm
trying to lick, lol), then there's no guarentee that the new statement will
be the same as the old one, which can lead to problems when customers call to
dispute a statement.

So here's what I decided would probably be the easiest way to handle this:
Whenever I review the report in Print Preview mode, automatically save a copy
of the report to a backup file. The Print Preview mode is the same as the
Print mode, except it opens the report in Print Preview mode rather than
printing it automatically. Same menu format and everything though. I would
post the code to the buttons, but they're all macros. (I didn't know the
coding well enough to do them in VBA, plus there's a little more to them than
simply opening the report in Preview mode.)

So, would it be easier to just use the OutputTo command in the Print Preview
macro, and have the coding for the naming convention in the File Name field?
Or will that not work in this case? What I'm doing now is using a RunCode
command in the macro and calling the function that way, using the report name
as the source and the account number field from my Customer Info table, plus
the current date, for the name of the file. (There's only one record in the
Customer Info table and it gets deleted every time a new report is run.)


I appreciate all the help you've been giving, even if some of what I've been
told hasn't worked for this problem, it did help me with another problem,
lol... so thank you all very much.