Save Access Report to File

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to be able to save a Access generated report to a permanent file and
not quite sure how to do it. Is there any one out there that has been there
and done this? Thanks,
 
Jim/Chris,

First of all Thank You for the guidance on the OutPTo action. It allows me
to save my Access File report to a separate file on the drive, BUT,,,, what
I really wanted to do was to build this file on an automated basis. I
wanted to take the record key number (in this case the CASE_NUM value ex.
04-123456) and use this value as part of the file name and save the output to
a default directory file named 04-123456.doc.

Problem is that the OutPTo action will not allow you to use a variable for
the file name, unless of course this can be hard coded in code format
(instead of executing a macro of OutPTo ....) action command. I'm able to
create the following macro

Macro Name = FR_CR_FO

Macro contains:

Output To (action)
Object Type = Report
Object Name = FR_CR_EP
Output Format = Rich Text Format
Output File name = null
AutoStart = No
Template file = Null
Encoding = no

By leaving the Output file name null or empty it prompts the user to enter a
file name, where they could enter the case number 04-123456, but that's not
what I would like to do, I would like it to read the value in CASE_NUM field
from the form at "AFTER CLIKC) on command button, accept this data value and
plug it into the string.

This allows me to save the file to hard disk file in .rtf format, which
would allow the file to be opened later by Word and reprinted, (minus of
course a few of the minor page line and textbox border formatting). Which
would be usable if needed.

Any suggestions would be greatly appreciated.

Thanks Again,

Robert

Jim/Chris said:
In the macro use the OutpTo action

Jim
Click to expand...
 
I would also like the answer to this problem, ie outputo c:\Temp\"File
Name".rtf where "File name" is a field or control on a report that has been
opened

Robert Nusz @ DPS said:
Jim/Chris,

First of all Thank You for the guidance on the OutPTo action. It allows me
to save my Access File report to a separate file on the drive, BUT,,,, what
I really wanted to do was to build this file on an automated basis. I
wanted to take the record key number (in this case the CASE_NUM value ex.
04-123456) and use this value as part of the file name and save the output to
a default directory file named 04-123456.doc.

Problem is that the OutPTo action will not allow you to use a variable for
the file name, unless of course this can be hard coded in code format
(instead of executing a macro of OutPTo ....) action command. I'm able to
create the following macro

Macro Name = FR_CR_FO

Macro contains:

Output To (action)
Object Type = Report
Object Name = FR_CR_EP
Output Format = Rich Text Format
Output File name = null
AutoStart = No
Template file = Null
Encoding = no

By leaving the Output file name null or empty it prompts the user to enter a
file name, where they could enter the case number 04-123456, but that's not
what I would like to do, I would like it to read the value in CASE_NUM field
from the form at "AFTER CLIKC) on command button, accept this data value and
plug it into the string.

This allows me to save the file to hard disk file in .rtf format, which
would allow the file to be opened later by Word and reprinted, (minus of
course a few of the minor page line and textbox border formatting). Which
would be usable if needed.

Any suggestions would be greatly appreciated.

Thanks Again,

Robert
Click to expand...
 
I would also like the answer to this problem, ie outputo c:\Temp\"File
Name".rtf where "File name" is a field or control on a report that has been
opened

Robert Nusz @ DPS said:
Jim/Chris,

First of all Thank You for the guidance on the OutPTo action. It allows me
to save my Access File report to a separate file on the drive, BUT,,,, what
I really wanted to do was to build this file on an automated basis. I
wanted to take the record key number (in this case the CASE_NUM value ex.
04-123456) and use this value as part of the file name and save the output to
a default directory file named 04-123456.doc.

Problem is that the OutPTo action will not allow you to use a variable for
the file name, unless of course this can be hard coded in code format
(instead of executing a macro of OutPTo ....) action command. I'm able to
create the following macro

Macro Name = FR_CR_FO

Macro contains:

Output To (action)
Object Type = Report
Object Name = FR_CR_EP
Output Format = Rich Text Format
Output File name = null
AutoStart = No
Template file = Null
Encoding = no

By leaving the Output file name null or empty it prompts the user to enter a
file name, where they could enter the case number 04-123456, but that's not
what I would like to do, I would like it to read the value in CASE_NUM field
from the form at "AFTER CLIKC) on command button, accept this data value and
plug it into the string.

This allows me to save the file to hard disk file in .rtf format, which
would allow the file to be opened later by Word and reprinted, (minus of
course a few of the minor page line and textbox border formatting). Which
would be usable if needed.

Any suggestions would be greatly appreciated.

Thanks Again,

Robert
Click to expand...
 
As I'm a non expert and self taught over many years I guess there are some
who will say this is not correct but this is how I overcame the same problem.

1 convert the macro to visual basic by selecting the macro and selecting
tools convert macros to visual basic.

You will then see the outputto commands

You need to select the control name on the form you are interested in, in my
case Form_Invoice.txtInvNum.Value This gives the variable "the invoice
number" of each document to the myinvNum (As the invoice number is common to
all four sheets, this is only performed once)

Then assign that value to a string

The following example opens four reports, C of C, Invoice, Invoice List and
Packing list. it then exports these to snapshot files (at the moment to
C:\Temp\ but this will be changed to a network directory but it worked for
the trial.) It then exports the same four documents as RTF files (eventually
to a different netwok directory to the Snapshot files)

Once complete, the programme closes the reports. You will have to assign the
visual basic to a command button.

I have missed a few dims for the strings but the sample works in both access
2000 and access 2003. I did have a problem originally because it was written
in 2003 and wouldn't work in 2000 but I overcame that problem. the reason for
exporting in both formats is, I wish to retain the RTF Files which can be
searched for specific text fields like "Moulding Insert", where the snapshot
views will be e mailed but cannot be serched for text values.

I hope this helps but as I have said, I am not an "expert"

Function Export_Tooling()
Dim myInvNum, myfile As String

On Error GoTo Export_Tooling_Err

myInvNum = Form_Invoice.txtInvNum.Value
SNPCofC = "C:\Temp\" & myInvNum & " C of C Tooling" & ".snp"
SNPInvoice = "C:\Temp\" & myInvNum & " Invoice Tooling" & ".snp"
SNPInvoiceList = "C:\Temp\" & myInvNum & " Invoice List Tooling" & ".snp"
SNPPackingList = "C:\Temp\" & myInvNum & " Packing List Tooling" & ".snp"
RTFCofC = "C:\Temp\" & myInvNum & " C of C Tooling" & ".rtf"
RTFInvoice = "C:\Temp\" & myInvNum & " Invoice Tooling" & ".rtf"
RTFInvoiceList = "C:\Temp\" & myInvNum & " Invoice List Tooling" & ".rtf"
RTFPackingList = "C:\Temp\" & myInvNum & " Packing List Tooling" & ".rtf"

DoCmd.OpenReport "C_of_C", acViewPreview, "", ""
DoCmd.OpenReport "Invoice", acViewPreview, "", ""
DoCmd.OpenReport "Rprt_Invoice_List_Tooling", acViewPreview, "", ""
DoCmd.OpenReport "Rprt_Packing_List_Tooling", acViewPreview, "", ""

DoCmd.OutputTo acReport, "C_of_C", "SnapshotFormat(*.snp)", SNPCofC,
True, ""
DoCmd.OutputTo acReport, "Invoice", "SnapshotFormat(*.snp)", SNPInvoice,
True, ""
DoCmd.OutputTo acReport, "Rprt_Invoice_List_Tooling",
"SnapshotFormat(*.snp)", SNPInvoiceList, True, ""
DoCmd.OutputTo acReport, "Rprt_Packing_List_Tooling",
"SnapshotFormat(*.snp)", SNPPackingList, True, ""
DoCmd.OutputTo acReport, "C_of_C", "RichTextFormat(*.rtf)", RTFCofC,
True, ""
DoCmd.OutputTo acReport, "Invoice", "RichTextFormat(*.rtf)", RTFInvoice,
True, ""
DoCmd.OutputTo acReport, "Rprt_Invoice_List_Tooling",
"RichTextFormat(*.rtf)", RTFInvoiceList, True, ""
DoCmd.OutputTo acReport, "Rprt_Packing_List_Tooling",
"RichTextFormat(*.rtf)", RTFPackingList, True, ""
DoCmd.Close acReport, "C_of_C"
DoCmd.Close acReport, "Invoice"
DoCmd.Close acReport, "Rprt_Invoice_List_Tooling"
DoCmd.Close acReport, "Rprt_Packing_List_Tooling"
I have found a solution, as I'm a self taught non expert, I guess there will
be some who say this is not correct but this is the way that I overcame the
problem.

1, Convert the macro to VBA

Export_Tooling_Exit:
Exit Function

Export_Tooling_Err:
MsgBox Error$
Resume Export_Tooling_Exit

End Function
 
Back
Top