Open Multiple reports using For Loop (1 Viewer)

paramesium · Oct 25, 2013

Hi All,

I wanted to print multiple reports using for loop but I am not sure how to start with.

This is basically my idea:
1. Create a hidden indicator, I name it txtHidden.
2. I have one table, there are one column for "report name".
3. I have one query, filtered the "report name" column according to txtHidden, criteria written in the design view.
4. Using For loop, browse through all the existing report.
5. Every loop, txtHidden will equals to every reportItem.Name. Query will run.
6. If Query is no result, dont open report, if query returns some result, open the report.

I wanted to code something as below, but I dont know what to put in the [Dont know what to type] field...

Code:

 For Each [COLOR="Red"][Dont know what to type][/COLOR] In Access.Reports
    
    [Forms]![frmStartup]![txtHidden] =[COLOR="red"][ Dont know what to type][/COLOR] .Name
    If DCount("*", "ReportQuery", "") > 0 Then
    DoCmd.OpenReport [Forms]![frmStartup]![txtHidden], acViewPreview
    End If

    Next

p/s" Can ignore the query part. It is working fine.

thanks in advance!

Roku · Oct 25, 2013

Try this:

Code:

Dim rpt As Report
For Each rpt In Access.Reports
    [Forms]![frmStartup]![txtHidden] = rpt.Name
    If DCount("*", "ReportQuery", "") > 0 Then
      DoCmd.OpenReport [Forms]![frmStartup]![txtHidden], acViewPreview
    End If
Next

If I read your logic correctly, you don't really need the hidden TextBox. If you open the table filtered as you suggest, you can take the report name directly from the query field.

paramesium · Oct 25, 2013

Roku said:
Try this:

Code:

Dim rpt As Report For Each rpt In Access.Reports [Forms]![frmStartup]![txtHidden] = rpt.Name If DCount("*", "ReportQuery", "") > 0 Then DoCmd.OpenReport [Forms]![frmStartup]![txtHidden], acViewPreview End If Next

If I read your logic correctly, you don't really need the hidden TextBox. If you open the table filtered as you suggest, you can take the report name directly from the query field.

Thank you for your reply, I will try to take it directly from Query later.

However, I tried the code, It dont open a single report.
But If I do it without a for loop, that means I copy the code one by one, it works. I found it weird.

Did I miss out any syntax error?

Roku · Oct 26, 2013

If I do it without a for loop, ..., it works

The question then becomes "what is the value in txtHidden when you don't use the loop?"

I don't understand what your DCount test is for. In effect, that is a constant within the loop as written (i.e. it is always true or always false during loop iteration). If the DoCmd is not executed in the loop, then this statement must be false. When you omit the loop, the value must be true if the report is opened - in other words, something else has changed as well.

Perhaps you could use something like this:

Code:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tReports;")
Do Until rst.EOF
  If Not IsNull(rst!rtName) Then DoCmd.OpenReport ReportName:=rst!rtName, View:=acViewNormal, WindowMode:=acWindowNormal
  rst.MoveNext
Loop
rst.Close
set rst = Nothing

Here, the variable 'rst' is probably the equivalent of your 'ReportQuery' - I can't say for sure, as you don't show how the query is constructed.

If you need more help with your solution, it would be better to include more of your code.

paramesium · Oct 26, 2013

Thanks for your help, Roku.
Your method is working!

Open Multiple reports using For Loop (1 Viewer)

paramesium

Registered User.

Roku

MBCS CITP

paramesium

Registered User.

Roku

MBCS CITP

paramesium

Registered User.

Similar threads

Users who are viewing this thread