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
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\Baname
DoCmd.OutputTo acReport, "9 Banamex US FX adjusted", "SnapshotFormat(*.snp)", "G:\user\eporeports\Baname
DoCmd.OutputTo acReport, "CEMECA Acct Rept 021904", "SnapshotFormat(*.snp)", "G:\user\eporeports\CEMECA
DoCmd.OutputTo acReport, "6 Commercial Mexicana Accounting Report Foreign", "SnapshotFormat(*.snp)", "G:\user\eporeports\Commer
DoCmd.OutputTo acReport, "6 Commercial Mexicana Accounting Report US", "SnapshotFormat(*.snp)", "G:\user\eporeports\Commer
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
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
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\Baname
DoCmd.OutputTo acReport, "9 Banamex US FX adjusted", "SnapshotFormat(*.snp)", "G:\user\eporeports\Baname
DoCmd.OutputTo acReport, "CEMECA Acct Rept 021904", "SnapshotFormat(*.snp)", "G:\user\eporeports\CEMECA
DoCmd.OutputTo acReport, "6 Commercial Mexicana Accounting Report Foreign", "SnapshotFormat(*.snp)", "G:\user\eporeports\Commer
DoCmd.OutputTo acReport, "6 Commercial Mexicana Accounting Report US", "SnapshotFormat(*.snp)", "G:\user\eporeports\Commer
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
Last Comment
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
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
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?
>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?
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?
ASKER
It is a query.
ASKER
If you grap it from a query how would you code it?
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",sQuery
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
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",sQuery
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
ASKER
Jeff,
How can I make a table from a report? The report is based on a query that is pulling from a table.
How can I make a table from a report? The report is based on a query that is pulling from a table.
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",sQuery
DoCmd.OutputTo acReport, rs.sReportName & sFieldData, "SnapshotFormat(*.snp)", rs.sReportFileLocation
rs.MoveNext
Loop
End If
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",sQuery
DoCmd.OutputTo acReport, rs.sReportName & sFieldData, "SnapshotFormat(*.snp)", rs.sReportFileLocation
rs.MoveNext
Loop
End If
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
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
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
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
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(st
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
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(st
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
ASKER
Jeff,
I keep getting the following at these line: Compile error: Label not defined
On Error GoTo accountingReportstest_Err
thks
Ken
I keep getting the following at these line: Compile error: Label not defined
On Error GoTo accountingReportstest_Err
thks
Ken
ASKER
Actually the following line is highlighted with above compile error.
Function accountingReportstest()
Function accountingReportstest()
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(st
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
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(st
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
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
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
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
thks
Ken
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!myfieldna
and we can use that same value in the code instead of trying to extract it from the query like we are.
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!myfieldna
and we can use that same value in the code instead of trying to extract it from the query like we are.
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
thks
Ken
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
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
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?
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?
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(st
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
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(st
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
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.
ASKER
thankyou Jeff
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
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 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
TRUSTED BY
>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