Reuse Report and Query and OutputTo - Must be a Way! | | |
I have reports that run from a form where the user can choose a date
range, or they run automatically for a week in the "Weekly Reports"
option.
I created 2 queries and 2 reports - one query uses the WeekEndDate to
filter, and its associated report heading says "For the week Ending " &
forms!frmPrintReports.getWeekEndDate.
The other query selects the date range using "Between" and the 2 dates
on the form. Its associated report header says "for dates between " &
forms!.....
I thought that using the Where clause I could eliminate one of the
queries and somehow eliminate the second report as well. I want the
Page Header of the report to repeat the report title information.
I also Output the reports right to a snapshot file on the server, so
the user doesn't have to Export them.
I can't figure out how to get a "Where" clause in the OutputTo
statement.
I can get the report to display on my screen with the proper dates and
heading - using DoCmd.OpenReport, but I want it to snap (or PDF
automatically if there's a way).
I have tried everything I can think of. I am now spinning in circles,
I think.
I want to use One report definition, One query, and be able to select
the range of data, or data for just one date, and also have the proper
report heading print.
I can't find any posts about this, though I've tried many searches.
Thanks
sara
| | | | re: Reuse Report and Query and OutputTo - Must be a Way!
sara,
you cannot put a where clause in the OutputTo statement.
But if you first execute a OpenReport statement with a where-clause you
can leave out the report parameter in the next OutputTo statement and
it will use the currently open report with the current where-clause.
You have to specify a snapfilename inclusing the .snp extension to be
able to open it in the following example-code.
The example also make use of missing startdate and enddate parameters
with the current week as default-values.
You will have to adapt this example for your report and datasource of
it. I used a table with "datum" as a date-column.
another condition is that the filter for the report is enabled
beforehand (filter=on).
another method of filtering could be to make use of different named and
previously stored queries
i hope these examples will help you to a solution.
Marc
Public Function makesnapshot(ReportName As String, SnapFileName As
String, Optional StartDate, Optional EndDate)
Dim ActDate As Date
Dim LastDate As Date
Dim FirstDate As Date
Dim LastDateText As String
Dim FirstDateText As String
ActDate = Date
'compute the next sunday
LastDate = ActDate + 7 - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(EndDate) Then
LastDateText = Format(LastDate, "dd/mm/yyyy")
Else
LastDateText = Format(EndDate, "dd/mm/yyyy")
End If
'compute start of week (sunday)
FirstDate = ActDate - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(StartDate) Then
FirstDateText = Format(FirstDate, "dd/mm/yyyy")
Else
FirstDateText = Format(StartDate, "dd/mm/yyyy")
End If
DoCmd.OpenReport ReportName, acViewPreview, , "datum >= #" &
FirstDateText & "# and datum < #" & LastDateText & "#"
DoCmd.OutputTo acOutputReport, , acFormatSNP, SnapFileName
End Function
Sub test()
makesnapshot "tabel1", "testfile.snp", #1/8/2006#
End Sub
sara schreef: Quote:
I have reports that run from a form where the user can choose a date
range, or they run automatically for a week in the "Weekly Reports"
option.
>
I created 2 queries and 2 reports - one query uses the WeekEndDate to
filter, and its associated report heading says "For the week Ending " &
forms!frmPrintReports.getWeekEndDate.
>
The other query selects the date range using "Between" and the 2 dates
on the form. Its associated report header says "for dates between " &
forms!.....
>
I thought that using the Where clause I could eliminate one of the
queries and somehow eliminate the second report as well. I want the
Page Header of the report to repeat the report title information.
>
I also Output the reports right to a snapshot file on the server, so
the user doesn't have to Export them.
>
I can't figure out how to get a "Where" clause in the OutputTo
statement.
I can get the report to display on my screen with the proper dates and
heading - using DoCmd.OpenReport, but I want it to snap (or PDF
automatically if there's a way).
>
I have tried everything I can think of. I am now spinning in circles,
I think.
>
I want to use One report definition, One query, and be able to select
the range of data, or data for just one date, and also have the proper
report heading print.
>
I can't find any posts about this, though I've tried many searches.
>
Thanks
sara
| | | | re: Reuse Report and Query and OutputTo - Must be a Way!
I am nearly stunned! I did it - almost!
I got the report to print snapshot, with the Where Clause. I can't
thank you enough for the concept and the code - neither of which I
could have done without.
I've only done it on the one report, but now that my "concept education
and alpha test" is over, I can apply it in many places.
One problem:
Now, I ran the report to acPreview, then immediately tried to close it
Dim strWhere As String
strWhere = "[CancelDate] >= #" _
& dtmStartDate & "# And [CancelDate] < #" & dtmEndDate & "#"
DoCmd.OpenReport strDocName, acViewPreview, , strWhere
DoCmd.Close acReport, strDocName
But it didn't close..>!!!
Is there a way to close the report? Or maybe open it hidden and close
it? Or, best, not preview it at all, but still run it with the Where
clause?
Again, many thanks. This opens up a whole new world for me. As well,
I'm going to investigate the "optional" parameter - which I never knew
existed.
Sara
MarcHG wrote: Quote:
sara,
>
you cannot put a where clause in the OutputTo statement.
But if you first execute a OpenReport statement with a where-clause you
can leave out the report parameter in the next OutputTo statement and
it will use the currently open report with the current where-clause.
You have to specify a snapfilename inclusing the .snp extension to be
able to open it in the following example-code.
The example also make use of missing startdate and enddate parameters
with the current week as default-values.
You will have to adapt this example for your report and datasource of
it. I used a table with "datum" as a date-column.
another condition is that the filter for the report is enabled
beforehand (filter=on).
another method of filtering could be to make use of different named and
previously stored queries
i hope these examples will help you to a solution.
>
Marc
>
Public Function makesnapshot(ReportName As String, SnapFileName As
String, Optional StartDate, Optional EndDate)
Dim ActDate As Date
Dim LastDate As Date
Dim FirstDate As Date
Dim LastDateText As String
Dim FirstDateText As String
ActDate = Date
'compute the next sunday
LastDate = ActDate + 7 - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(EndDate) Then
LastDateText = Format(LastDate, "dd/mm/yyyy")
Else
LastDateText = Format(EndDate, "dd/mm/yyyy")
End If
'compute start of week (sunday)
FirstDate = ActDate - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(StartDate) Then
FirstDateText = Format(FirstDate, "dd/mm/yyyy")
Else
FirstDateText = Format(StartDate, "dd/mm/yyyy")
End If
DoCmd.OpenReport ReportName, acViewPreview, , "datum >= #" &
FirstDateText & "# and datum < #" & LastDateText & "#"
DoCmd.OutputTo acOutputReport, , acFormatSNP, SnapFileName
End Function
>
>
Sub test()
makesnapshot "tabel1", "testfile.snp", #1/8/2006#
End Sub
>
sara schreef:
> Quote:
I have reports that run from a form where the user can choose a date
range, or they run automatically for a week in the "Weekly Reports"
option.
I created 2 queries and 2 reports - one query uses the WeekEndDate to
filter, and its associated report heading says "For the week Ending " &
forms!frmPrintReports.getWeekEndDate.
The other query selects the date range using "Between" and the 2 dates
on the form. Its associated report header says "for dates between " &
forms!.....
I thought that using the Where clause I could eliminate one of the
queries and somehow eliminate the second report as well. I want the
Page Header of the report to repeat the report title information.
I also Output the reports right to a snapshot file on the server, so
the user doesn't have to Export them.
I can't figure out how to get a "Where" clause in the OutputTo
statement.
I can get the report to display on my screen with the proper dates and
heading - using DoCmd.OpenReport, but I want it to snap (or PDF
automatically if there's a way).
I have tried everything I can think of. I am now spinning in circles,
I think.
I want to use One report definition, One query, and be able to select
the range of data, or data for just one date, and also have the proper
report heading print.
I can't find any posts about this, though I've tried many searches.
Thanks
sara
| | | | re: Reuse Report and Query and OutputTo - Must be a Way!
Marc -
Sorry - I just stepped through the code again and I see where I was
executing the Preview, then closing the report, then opening it in
Preview AGAIN in your function. I've taken care of that problem.
Still wondering if there's a way to not even preview the report - is
there a hidden option or something and then I could close it? Or is
Preview and immediate close the only way?
Again, thanks a million!
Sara
MarcHG wrote: Quote:
sara,
>
you cannot put a where clause in the OutputTo statement.
But if you first execute a OpenReport statement with a where-clause you
can leave out the report parameter in the next OutputTo statement and
it will use the currently open report with the current where-clause.
You have to specify a snapfilename inclusing the .snp extension to be
able to open it in the following example-code.
The example also make use of missing startdate and enddate parameters
with the current week as default-values.
You will have to adapt this example for your report and datasource of
it. I used a table with "datum" as a date-column.
another condition is that the filter for the report is enabled
beforehand (filter=on).
another method of filtering could be to make use of different named and
previously stored queries
i hope these examples will help you to a solution.
>
Marc
>
Public Function makesnapshot(ReportName As String, SnapFileName As
String, Optional StartDate, Optional EndDate)
Dim ActDate As Date
Dim LastDate As Date
Dim FirstDate As Date
Dim LastDateText As String
Dim FirstDateText As String
ActDate = Date
'compute the next sunday
LastDate = ActDate + 7 - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(EndDate) Then
LastDateText = Format(LastDate, "dd/mm/yyyy")
Else
LastDateText = Format(EndDate, "dd/mm/yyyy")
End If
'compute start of week (sunday)
FirstDate = ActDate - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(StartDate) Then
FirstDateText = Format(FirstDate, "dd/mm/yyyy")
Else
FirstDateText = Format(StartDate, "dd/mm/yyyy")
End If
DoCmd.OpenReport ReportName, acViewPreview, , "datum >= #" &
FirstDateText & "# and datum < #" & LastDateText & "#"
DoCmd.OutputTo acOutputReport, , acFormatSNP, SnapFileName
End Function
>
>
Sub test()
makesnapshot "tabel1", "testfile.snp", #1/8/2006#
End Sub
>
sara schreef:
> Quote:
I have reports that run from a form where the user can choose a date
range, or they run automatically for a week in the "Weekly Reports"
option.
I created 2 queries and 2 reports - one query uses the WeekEndDate to
filter, and its associated report heading says "For the week Ending " &
forms!frmPrintReports.getWeekEndDate.
The other query selects the date range using "Between" and the 2 dates
on the form. Its associated report header says "for dates between " &
forms!.....
I thought that using the Where clause I could eliminate one of the
queries and somehow eliminate the second report as well. I want the
Page Header of the report to repeat the report title information.
I also Output the reports right to a snapshot file on the server, so
the user doesn't have to Export them.
I can't figure out how to get a "Where" clause in the OutputTo
statement.
I can get the report to display on my screen with the proper dates and
heading - using DoCmd.OpenReport, but I want it to snap (or PDF
automatically if there's a way).
I have tried everything I can think of. I am now spinning in circles,
I think.
I want to use One report definition, One query, and be able to select
the range of data, or data for just one date, and also have the proper
report heading print.
I can't find any posts about this, though I've tried many searches.
Thanks
sara
| | | | re: Reuse Report and Query and OutputTo - Must be a Way!
Sara,
Closing the report is possible using the docmd.close, acReport,
<reportname>,<acCloseSave>
acCloseSave is a parameter to save, not save or prompt the user for
that. The options are showed when typing the code.
Hiding should also be possible (i didn't test it) referring to the
report in VBA using the syntax Reports!<reportname>.visible= false.
With hiding the report is still open, but just not visible in a
Access-window.
Success,
Marc
sara schreef: Quote:
Marc -
>
Sorry - I just stepped through the code again and I see where I was
executing the Preview, then closing the report, then opening it in
Preview AGAIN in your function. I've taken care of that problem.
>
Still wondering if there's a way to not even preview the report - is
there a hidden option or something and then I could close it? Or is
Preview and immediate close the only way?
>
Again, thanks a million!
>
Sara
>
>
MarcHG wrote: Quote:
sara,
you cannot put a where clause in the OutputTo statement.
But if you first execute a OpenReport statement with a where-clause you
can leave out the report parameter in the next OutputTo statement and
it will use the currently open report with the current where-clause.
You have to specify a snapfilename inclusing the .snp extension to be
able to open it in the following example-code.
The example also make use of missing startdate and enddate parameters
with the current week as default-values.
You will have to adapt this example for your report and datasource of
it. I used a table with "datum" as a date-column.
another condition is that the filter for the report is enabled
beforehand (filter=on).
another method of filtering could be to make use of different named and
previously stored queries
i hope these examples will help you to a solution.
Marc
Public Function makesnapshot(ReportName As String, SnapFileName As
String, Optional StartDate, Optional EndDate)
Dim ActDate As Date
Dim LastDate As Date
Dim FirstDate As Date
Dim LastDateText As String
Dim FirstDateText As String
ActDate = Date
'compute the next sunday
LastDate = ActDate + 7 - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(EndDate) Then
LastDateText = Format(LastDate, "dd/mm/yyyy")
Else
LastDateText = Format(EndDate, "dd/mm/yyyy")
End If
'compute start of week (sunday)
FirstDate = ActDate - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(StartDate) Then
FirstDateText = Format(FirstDate, "dd/mm/yyyy")
Else
FirstDateText = Format(StartDate, "dd/mm/yyyy")
End If
DoCmd.OpenReport ReportName, acViewPreview, , "datum >= #" &
FirstDateText & "# and datum < #" & LastDateText & "#"
DoCmd.OutputTo acOutputReport, , acFormatSNP, SnapFileName
End Function
Sub test()
makesnapshot "tabel1", "testfile.snp", #1/8/2006#
End Sub
sara schreef: Quote:
I have reports that run from a form where the user can choose a date
range, or they run automatically for a week in the "Weekly Reports"
option.
>
I created 2 queries and 2 reports - one query uses the WeekEndDate to
filter, and its associated report heading says "For the week Ending " &
forms!frmPrintReports.getWeekEndDate.
>
The other query selects the date range using "Between" and the 2 dates
on the form. Its associated report header says "for dates between " &
forms!.....
>
I thought that using the Where clause I could eliminate one of the
queries and somehow eliminate the second report as well. I want the
Page Header of the report to repeat the report title information.
>
I also Output the reports right to a snapshot file on the server, so
the user doesn't have to Export them.
>
I can't figure out how to get a "Where" clause in the OutputTo
statement.
I can get the report to display on my screen with the proper dates and
heading - using DoCmd.OpenReport, but I want it to snap (or PDF
automatically if there's a way).
>
I have tried everything I can think of. I am now spinning in circles,
I think.
>
I want to use One report definition, One query, and be able to select
the range of data, or data for just one date, and also have the proper
report heading print.
>
I can't find any posts about this, though I've tried many searches.
>
Thanks
sara
| | | | re: Reuse Report and Query and OutputTo - Must be a Way!
I think I've got it. Thanks again!
sara
MarcHG wrote: Quote:
Sara,
>
Closing the report is possible using the docmd.close, acReport,
<reportname>,<acCloseSave>
acCloseSave is a parameter to save, not save or prompt the user for
that. The options are showed when typing the code.
>
Hiding should also be possible (i didn't test it) referring to the
report in VBA using the syntax Reports!<reportname>.visible= false.
With hiding the report is still open, but just not visible in a
Access-window.
>
Success,
>
Marc
>
sara schreef:
> Quote:
Marc -
Sorry - I just stepped through the code again and I see where I was
executing the Preview, then closing the report, then opening it in
Preview AGAIN in your function. I've taken care of that problem.
Still wondering if there's a way to not even preview the report - is
there a hidden option or something and then I could close it? Or is
Preview and immediate close the only way?
Again, thanks a million!
Sara
MarcHG wrote: Quote:
sara,
>
you cannot put a where clause in the OutputTo statement.
But if you first execute a OpenReport statement with a where-clause you
can leave out the report parameter in the next OutputTo statement and
it will use the currently open report with the current where-clause.
You have to specify a snapfilename inclusing the .snp extension to be
able to open it in the following example-code.
The example also make use of missing startdate and enddate parameters
with the current week as default-values.
You will have to adapt this example for your report and datasource of
it. I used a table with "datum" as a date-column.
another condition is that the filter for the report is enabled
beforehand (filter=on).
another method of filtering could be to make use of different named and
previously stored queries
i hope these examples will help you to a solution.
>
Marc
>
Public Function makesnapshot(ReportName As String, SnapFileName As
String, Optional StartDate, Optional EndDate)
Dim ActDate As Date
Dim LastDate As Date
Dim FirstDate As Date
Dim LastDateText As String
Dim FirstDateText As String
ActDate = Date
'compute the next sunday
LastDate = ActDate + 7 - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(EndDate) Then
LastDateText = Format(LastDate, "dd/mm/yyyy")
Else
LastDateText = Format(EndDate, "dd/mm/yyyy")
End If
'compute start of week (sunday)
FirstDate = ActDate - DatePart("w", ActDate, vbMonday, vbUseSystem)
If IsMissing(StartDate) Then
FirstDateText = Format(FirstDate, "dd/mm/yyyy")
Else
FirstDateText = Format(StartDate, "dd/mm/yyyy")
End If
DoCmd.OpenReport ReportName, acViewPreview, , "datum >= #" &
FirstDateText & "# and datum < #" & LastDateText & "#"
DoCmd.OutputTo acOutputReport, , acFormatSNP, SnapFileName
End Function
>
>
Sub test()
makesnapshot "tabel1", "testfile.snp", #1/8/2006#
End Sub
>
sara schreef:
>
I have reports that run from a form where the user can choose a date
range, or they run automatically for a week in the "Weekly Reports"
option.
I created 2 queries and 2 reports - one query uses the WeekEndDate to
filter, and its associated report heading says "For the week Ending " &
forms!frmPrintReports.getWeekEndDate.
The other query selects the date range using "Between" and the 2 dates
on the form. Its associated report header says "for dates between " &
forms!.....
I thought that using the Where clause I could eliminate one of the
queries and somehow eliminate the second report as well. I want the
Page Header of the report to repeat the report title information.
I also Output the reports right to a snapshot file on the server, so
the user doesn't have to Export them.
I can't figure out how to get a "Where" clause in the OutputTo
statement.
I can get the report to display on my screen with the proper dates and
heading - using DoCmd.OpenReport, but I want it to snap (or PDF
automatically if there's a way).
I have tried everything I can think of. I am now spinning in circles,
I think.
I want to use One report definition, One query, and be able to select
the range of data, or data for just one date, and also have the proper
report heading print.
I can't find any posts about this, though I've tried many searches.
Thanks
sara
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
