• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

Open Access report in separate windows


I have a form displaying a list of reports (one lines corresponds to a Customer Code in the report), when user double click one line or selecte multiple lines, the related reports will be opened in a separate window. And in that window, each report can be exported separately based on the selected Customer code.

I used this query for the report query:
...
fltSQL0 = "[Customer_CODE] in ('12', '195')"
...
DoCmd.OpenReport "rptTemplate", acViewPreview, , fltSQL0

But I found the reports for customer code '12' and '195' are in the same page and in the same report. How could I open the report seperately (my purpose is users could save report for '12' and report for '195' separately)?
Access report in seperate windows.
I tried below query, still get the two reports in one page:
fltSQL1 = "[Customer_CODE] in ('12')"
fltSQL2 = "[Customer_CODE] in ('195')"
...
DoCmd.OpenReport "rptTemplate", acViewPreview, , fltSQL1
DoCmd.OpenReport "rptTemplate", acViewPreview, , fltSQL2

Thanks for the help.
0
heyday2004
Asked:
heyday2004
  • 7
  • 6
  • 4
10 Solutions
 
TextReportCommented:
The IN clause will give you a single report as you have discovered.

In Access 2007 (not sure if it was in 2003 or earlier) you can use the WindowMode of AcDialog, this will suspend the VBA code untile the preview is closed.

DoCmd.OpenReport "rptTemplate", acViewPreview, , fltSQL1, acDialog
DoCmd.OpenReport "rptTemplate", acViewPreview, , fltSQL2, acDialog

Cheers, Andrew
0
 
ioanePlanning & Analytics ManagerCommented:
Hi heyday2004,

You can open multiple instances of a report.

Firstly, go to the report properties and set the Popup property to Yes, and the Has Module property to Yes.

Then paste this code into a new module.

I have created a procedure you can pass any number of filters to as a string with each filter criteria separated by a semi-colon (;).

Each filter needs to be written in full.

I have included a test procedure for you also (at bottom) so you can see how to call it.

Let me know how it goes...
'Takes in string expression of filter criteria seperated by semi-colon (;)
Sub OpenMultipleReportInstances(filters As String)
  Dim vntFilters As Variant
  Dim i As Integer
  
  vntFilters = Split(filters, ";")
  
  ReDim rpt(UBound(vntFilters))
  
  For i = 0 To UBound(vntFilters)
    Set rpt(i) = New Report_Table1
    rpt(i).Filter = vntFilters(i)
    rpt(i).FilterOn = True
    rpt(i).Visible = True
  Next i
End Sub

Sub test()
  Call OpenMultipleReportInstances("[Customer_CODE] = '12';[Customer_CODE] = '195'")
End Sub

Open in new window

0
 
ioanePlanning & Analytics ManagerCommented:
Bum, I forgot to change the report name.

You'll need to change line 11 to:

    Set rpt(i) = New Report_rptTemplate

Updated below:
'Takes in string expression of filter criteria seperated by semi-colon (;)
Sub OpenMultipleReportInstances(filters As String)
  Dim vntFilters As Variant
  Dim i As Integer
  
  vntFilters = Split(filters, ";")
  
  ReDim rpt(UBound(vntFilters))
  
  For i = 0 To UBound(vntFilters)
    Set rpt(i) = New Report_rptTemplate
    rpt(i).Filter = vntFilters(i)
    rpt(i).FilterOn = True
    rpt(i).Visible = True
  Next i
End Sub

Sub test()
  Call OpenMultipleReportInstances("[Customer_CODE] = '12';[Customer_CODE] = '195'")
End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
TextReportCommented:
Another thing to consider is that if the user is exporting all the reports after you have previewed then why don't you give them the option to do the export instead of the preview of the report. This would eliminate the need to previewing and getting the user interaction for each customer.

You can replace the OpenReport with a OutputTo or Transfer command that suits.

Cheers, Andrew
0
 
heyday2004Author Commented:
Thanks for the replies, I will try the solutions and will get back.
0
 
heyday2004Author Commented:
Tramtrak:
Seems I had some problem with your code: I got compile error at this line:

Set rpt(i) = New rptTemplate

rptTemplate  is the name of my report, seems it could not be used like this. Could you let me know how to correct it? Thanks.
0
 
heyday2004Author Commented:
TextReport:

You said: "why don't you give them the option to do the export instead of the preview of the report."

This is exactly what I want, but seems if you don't open the preview of the report first, you could not export it. How to export all the reports directly? Please give me more details. Thanks a lot.
0
 
ioanePlanning & Analytics ManagerCommented:
Needs to be as I wrote it:

  Set rpt(i) = New Report_rptTemplate

(The class name has a "Report_" prefix.)
0
 
TextReportCommented:
What format are you trying ti export the file to?
Cheers, Andrew
0
 
heyday2004Author Commented:
Thanks, no compiling error now, but still not working:

Here is simplified code by comparing using DoCmd.OpenReport and your dynamic method:      
    ' Case 1: Working
    fltSQL =  "[Date] = #4/13/2009# AND [Customer_CODE] = ('12')"
    DoCmd.OpenReport "rptTest", acViewPreview, , fltSQL
   
    ' Case 2: Not working: The windows shows "Running query ..." and freeze there
    Dim rpt As Report
    Set rpt = New Report_rptTest
    rpt.Filter = "[Date] = #4/13/2009# AND [Customer_CODE] = ('12')"
    rpt.FilterOn = True
    rpt.Visible = True

Any hint on making case 2 working? Thanks.
0
 
ioanePlanning & Analytics ManagerCommented:
Hmmm....looks like it should be working...

Is Customer_CODE a string value or an integer?

Don't think it would make any difference but try is without the parenthesis:

rpt.Filter = "[Date] = #4/13/2009# AND [Customer_CODE] = '12'"
0
 
heyday2004Author Commented:
Still not working. The Filter query is actually the same with the one in Case 1: fltSQL. So in case 2:
Set rpt = New Report_rptTest  will open a new report like DoCmd.OpenReport ...? Thanks for the help.
0
 
ioanePlanning & Analytics ManagerCommented:
Actually, sounds like you didn't set the Report's Has Module property to 'yes' as in instructions above.

Can you recheck?
0
 
heyday2004Author Commented:
I did check and it is set to YES in form design view (properties). Any hint? Is  the New key word to open a report like DoCmd.OpenReport?
0
 
ioanePlanning & Analytics ManagerCommented:
Hmmm...

Starting to run out of ideas actually.

Can you mock up an example of your table and report and upload it so I can test?
0
 
TextReportCommented:
I would still go back to what you are trying to achieve and how you are trying to achieve this.

What format are you trying to export each report to? When you answer this then I will be able to advise on the way to do this in code.

Cheers, Andrew
0
 
heyday2004Author Commented:
TextReport:

Sorry I missed your answer. I want to export each report (for each line) to two seperate files (one in xls, one in rtf), without the report preview popup. I've opened another similar (but still different) question. Thanks for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now