Avatar of kdr2003
kdr2003

asked on 

Adding a field name to an output file name in Access

have a macro in Access that opens 10 different reports.  Each report is sent to an output file with a different name.  Is there a way to add a field from the report to the outpur file name?  Attached is the code.

Option Compare Database

'------------------------------------------------------------
' Accounting_Reports1
'
'------------------------------------------------------------
Function Accounting_Reports1()
On Error GoTo Accounting_Reports1_Err

    DoCmd.OutputTo acReport, "4 Telecomm Giro Paisano (Acct Rpt) May 2004", "SnapshotFormat(*.snp)", "g:\user\eporeports\Giro Paisano 022207.snp", False, ""
    DoCmd.OutputTo acReport, "4 Telecomm Giro (Acct Rpt) May 2004", "SnapshotFormat(*.snp)", "G:\user\eporeports\Giro Telegrafico 022207.snp", False, ""
    DoCmd.OutputTo acReport, "2 Telecomm Trans (Acct Rept) Dia Siguiente May 2004", "SnapshotFormat(*.snp)", "G:\user\eporeports\Dia Siguente 022207.snp", False, ""
    DoCmd.OutputTo acReport, "2 Telecomm Trans (Acct Rept) Din Min May 2004", "SnapshotFormat(*.snp)", "G:\user\eporeports\Dinero en Minutos 022207.snp", False, ""
    DoCmd.OutputTo acReport, "7 Bital Acct Rep 11xx02", "SnapshotFormat(*.snp)", "G:\user\eporeports\Bital 022207.snp", False, ""
    DoCmd.OutputTo acReport, "9 Banamex Foreign Fx adjusted", "SnapshotFormat(*.snp)", "G:\user\eporeports\Banamex Foreign FX 022207.snp", False, ""
    DoCmd.OutputTo acReport, "9 Banamex US FX adjusted", "SnapshotFormat(*.snp)", "G:\user\eporeports\Banamex US FX 022207.snp", False, ""
    DoCmd.OutputTo acReport, "CEMECA Acct Rept 021904", "SnapshotFormat(*.snp)", "G:\user\eporeports\CEMECA 022207.snp", False, ""
    DoCmd.OutputTo acReport, "6 Commercial Mexicana Accounting Report Foreign", "SnapshotFormat(*.snp)", "G:\user\eporeports\Commercial Mexicana Foreign 022207.snp", False, ""
    DoCmd.OutputTo acReport, "6 Commercial Mexicana Accounting Report US", "SnapshotFormat(*.snp)", "G:\user\eporeports\Commercial Mexicana US 022207.snp", False, ""
    DoCmd.OutputTo acReport, "8 HEB Foreign FX Adjusted", "SnapshotFormat(*.snp)", "G:\user\eporeports\HEB Foreign 022207.snp", False, ""
    DoCmd.OutputTo acReport, "8 HEB US FX adjusted", "SnapshotFormat(*.snp)", "G:\user\eporeports\HEB US 022207.snp", False, ""
    DoCmd.OutputTo acReport, "AMEX Inbound Accounting Report", "SnapshotFormat(*.snp)", "g:/user/eporeports/Amex Inbound Accounting Report 022207.snp", False, ""


Accounting_Reports1_Exit:
    Exit Function

Accounting_Reports1_Err:
    MsgBox Error$
    Resume Accounting_Reports1_Exit

End Function
Microsoft Access

Avatar of undefined
Last Comment
jefftwilley
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Good Lord that is ugly.

>Is there a way to add a field from the report to the outpur file name?
You can always build the destination file name dynamically, like this...

Dim sFolder as String, sFileName as String
sFolder = "G:\user\eporeports\"

sFileName = "Customer1"   'Or whatever you want to feed into it.
DoCmd.OutputTo acReport, "report name-1", "SnapshotFormat(*.snp)", sFolder & sFileName

sFileName = "Customer2"   'Or whatever you want to feed into it.
DoCmd.OutputTo acReport, "report name-2", "SnapshotFormat(*.snp)", sFolder & sFileName

You get the idea.

Hope this helps.
-Jim
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Also, define 'add a field'.

If it's a field on a form, you can just replace the sFileName above with Me.YourFormFieldName

If it's a field (column) in a table, then you could loop through it like this (uses ado)

Dim cn as adodb.connection
set cn = currentproject.connection

Dim rs as adodb.recordset
set cn = new adodb.recordset

rs.Open "SELECT sReportName, sReportFileLocation FROM MyReportsTable", cn

If Not (rs.BOF and rs.EOF) then
   Do Until rs.EOF
       DoCmd.OutputTo acReport, rs.sReportName, "SnapshotFormat(*.snp)", rs.sReportFileLocation
       rs.MoveNext
   Loop
End If
Avatar of kdr2003
kdr2003

ASKER

In your first example can I add the define sFileName as a field from report called Field2?  Will it bring what is on the report which is defined with Field2?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>can I add the define sFileName as a field from report called Field2?
Do not grab data from reports.  Only grab data from tables, queries, forms, and using VBA code.

With that in mind, what is the source of data behind your report?
Avatar of kdr2003
kdr2003

ASKER

It is a query.
Avatar of kdr2003
kdr2003

ASKER

If you grap it from a query how would you code it?
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

What Our Expert Jim was saying was you need to create a table to store Report information.

A simple loop will replace the hard coded verbage you posted in your Q.

Your table might look like this

ID (autonumber)
sReportName (Text Field. In your case, why not max out at 255 length)
sReportFileLocation(Text field, again 255 seems worthy)
sReportSource (this is the name of the query that provides your data. vary the size accordingly.)

When you have that, a loop as jim described will open the table, and assign the report name and Output location to the Output Command.

With the name of the Query in the table, we can do a simple dlookup using the query name to find the field you want to add to the report name.


Dim cn as adodb.connection
set cn = currentproject.connection
dim sFieldData as string
dim sQueryName as string
Dim rs as adodb.recordset
set cn = new adodb.recordset
rs.Open "SELECT sReportName, sReportFileLocation FROM MyReportsTable", cn
If Not (rs.BOF and rs.EOF) then
   Do Until rs.EOF
sQueryName = rs!sReportSource
sFielData = dlookup("FieldName",sQueryName)
       DoCmd.OutputTo acReport, rs.sReportName & sFieldData, "SnapshotFormat(*.snp)", rs.sReportFileLocation
       rs.MoveNext
   Loop
End If

You may want to build the report name string the way you want it placing your field data in the right place, then just stick it in the OutPutTo command. And off you go!
J
Avatar of kdr2003
kdr2003

ASKER

Jeff,
              How can I make a table from a report?  The report is based on a query that is pulling from a table.
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

you can't make one from a report, and I'm not sure what We've explained that led you there. What we were explaining was how to move some of your repedative content into a table that you need to create.

This is an example of how you're executing your code:

 DoCmd.OutputTo acReport, "4 Telecomm Giro Paisano (Acct Rpt) May 2004", "SnapshotFormat(*.snp)", "g:\user\eporeports\Giro Paisano 022207.snp", False, ""
    DoCmd.OutputTo acReport, "4 Telecomm Giro (Acct Rpt) May 2004", "SnapshotFormat(*.snp)", "G:\user\eporeports\Giro Telegrafico 022207.snp", False, ""
    DoCmd.OutputTo acReport, "2 Telecomm Trans (Acct Rept) Dia Siguiente May 2004", "SnapshotFormat(*.snp)", "G:\user\eporeports\Dia Siguente 022207.snp", False, ""

There is the same command repeated what...12 times? the only thing that changes within the command are the elements. And it's those elements that we're suggesting you put into a table and "feed" them to your command.

DoCmd.OutputTo acReport, ELEMENT1, "SnapshotFormat(*.snp)", ELEMENT2 , False, ""

And you wanted to add a field to the report Name...so we can get that out of the report's query...so that would be ELEMENT3.


so I proposed a table to store your ELEMENTS

Your table might look like this

ID (autonumber)
sReportName (Text Field. In your case, why not max out at 255 length)
sReportFileLocation(Text field, again 255 seems worthy)
sReportSource (this is the name of the query that provides your data. vary the size accordingly.)


and this small code loop would read the values and apply them to your command, along with grabbing the value that you want to insert into your ReportName

Dim cn as adodb.connection
set cn = currentproject.connection
dim sFieldData as string
dim sQueryName as string
Dim rs as adodb.recordset
set cn = new adodb.recordset
rs.Open "SELECT sReportName, sReportFileLocation FROM MyReportsTable", cn
If Not (rs.BOF and rs.EOF) then
   Do Until rs.EOF
sQueryName = rs!sReportSource
sFielData = dlookup("FieldName",sQueryName)
       DoCmd.OutputTo acReport, rs.sReportName & sFieldData, "SnapshotFormat(*.snp)", rs.sReportFileLocation
       rs.MoveNext
   Loop
End If



Avatar of kdr2003
kdr2003

ASKER

Jeff,
         I created the Table as follows with the following fields.
sReportName            sReportFileLocation      sReportSource      sfielddata                ID
I have 13 records.
Excuse my ignorance, but I don't understand what I have to change in the following line:
DoCmd.OutputTo acReport, ELEMENT1, "SnapshotFormat(*.snp)", ELEMENT2 , False, ""
And where does this line have to go: before the loop or after the loop?

thks
Ken
Avatar of kdr2003
kdr2003

ASKER

Jeff.
          I adjusted the command Line, but when I run the module in Access I get a compile error at this line:
Dim cn As adodb.Connection

Below is what I have in the module completely:
Function AccountingReportstest()

On Error GoTo AccountingReportstest_Err
DoCmd.OutputTo acReport, sReportName, "SnapshotFormat(*.snp)", sReportFileLocation, False, ""

Dim cn As adodb.Connection
Set cn = CurrentProject.Connection
Dim sFieldData As String
Dim sQueryName As String
Dim rs As adodb.Recordset
Set cn = New adodb.Recordset
rs.Open "SELECT sReportName, sReportFileLocation FROM ELEMENTS", cn
If Not (rs.BOF And rs.EOF) Then
   Do Until rs.EOF
sQueryName = rs!sReportSource
sFieldData = DLookup("FieldName", sQueryName)
       DoCmd.OutputTo acReport, rs.sReportName & sFieldData, "SnapshotFormat(*.snp)", rs.sReportFileLocation
       rs.MoveNext
   Loop
End If

AccountingReportstest_Exit:
    Exit Function
End Function
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

I've made the loop a DAO. loop. When you're in your code window, go up to Tools/References and make sure you have the Microsoft DAO.3.6 reference checked.

Function AccountingReportstest()
On Error GoTo AccountingReportstest_Err
Dim rs As DAO.Recordset
Dim strSQL As String
Dim sFieldData As String
Dim sQueryName As String
strSQL = "SELECT * FROM ELEMENTS;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
   Do Until rs.EOF
        sQueryName = rs!sReportSource
        sFieldData = DLookup("FieldName", sQueryName)
        DoCmd.OutputTo acReport, rs.sReportName & sFieldData, "SnapshotFormat(*.snp)", rs.sReportFileLocation
       rs.MoveNext
   Loop
End If

AccountingReportstest_Exit:
    Exit Function
End Function

if you look at the OutPutTo line now, when you're adding the FieldData to the Report Name, you are simply appending them. So if my Report Name was JoeReport and my FieldData is Day12 then you'll end up with a report name like JoeReportDay12. So you may want to play with how you arrange those two ELEMENTS.  so you get the name of the report right.
J
Avatar of kdr2003
kdr2003

ASKER

Jeff,
I keep getting the following at these line: Compile error: Label not defined
On Error GoTo accountingReportstest_Err

thks
Ken
Avatar of kdr2003
kdr2003

ASKER

Actually the following line is highlighted with above compile error.
Function accountingReportstest()
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

I Added the error Handler at the bottom of your function. That's what it was looking for. Also, notice I modified your OutPutTo line so that it references the recorset's fields uwing the ! rather than the .  

Function AccountingReportstest()
On Error GoTo AccountingReportstest_Err
Dim rs As DAO.Recordset
Dim strSQL As String
Dim sFieldData As String
Dim sQueryName As String
strSQL = "SELECT * FROM ELEMENTS;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
   Do Until rs.EOF
        sQueryName = rs!sReportSource
        sFieldData = DLookup("FieldName", sQueryName)
        DoCmd.OutputTo acReport, rs!sReportName & sFieldData, "SnapshotFormat(*.snp)", rs!sReportFileLocation
       rs.MoveNext
   Loop
End If

AccountingReportstest_Exit:
    Exit Function
AccountingReportstest_Err:
MsgBox err.Number & " " & err.Description
End Function
Avatar of kdr2003
kdr2003

ASKER

Almost there.  I get the following error:
The expression you entered as query parameter produced this error: 'Microsoft Access can't find the name 'What is the date' in your expression'

thks
Ken
Avatar of kdr2003
kdr2003

ASKER

Each query prompts the user for a date.   This is the extra field from the report.  It is a text field in this format 1070226

thks
Ken
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

sFieldData = DLookup("FieldName", sQueryName)

would it apply then to this line? because what this is for, is to grab that field data you wanted to add to the report name when you output it.

Do you want the code to supply a date? perhaps all the reports will use the same date? If so, create a field on your form...and pull the date from there...so your users don't have to enter it more than once?
 
The criteria in your query would then look like

forms!myformname!myfieldname

and we can use that same value in the code instead of trying to extract it from the query like we are.
Avatar of kdr2003
kdr2003

ASKER

Unfortunately I don't have a form and sometimes the reports that are being generated use different dates.  It is okay that the users have to enter the date more than once.    How then would we incorporate the query parameter?

thks
Ken
Avatar of kdr2003
kdr2003

ASKER

Jeff,
        I created the form but I still get a prompt to enter in the date.  It is not taking the information from the form.

thks
Ken
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

Hey Ken,
Can you paste in the SQL for your query. you have what exactly in the Criteria field of your query?

Also...did I read it right, your report itself is asking for a date?
Avatar of kdr2003
kdr2003

ASKER

Jeff,
         I got it to work I changed the code as follows but now I am getting an error saying it doesn't recognize the report because of the date added from the form.
Function AccountingReportstest()
On Error GoTo AccountingReportstest_Err
Dim rs As DAO.Recordset
Dim strSQL As String
Dim sFieldData As String
Dim sQueryName As String
Dim SDate As String
Dim start As String
strSQL = "SELECT * FROM ELEMENTS1;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
   Do Until rs.EOF
        sQueryName = rs!sReportSource
                DoCmd.OutputTo acReport, rs!sReportName & [Forms]![Report Date Holder]![start], "SnapshotFormat(*.snp)", rs!sReportFileLocation
       rs.MoveNext
   Loop
End If

AccountingReportstest_Exit:
    Exit Function
AccountingReportstest_Err:
MsgBox Err.Number & " " & Err.Description
End Function
ASKER CERTIFIED SOLUTION
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of kdr2003
kdr2003

ASKER

thankyou Jeff
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

You got it all working like you needed?
Happy to help. You're great to work with by the way...I can tell you learned a lot along the way.
J
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo