Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Instance of a Report?

    Hi I am new to MS Access and I am using MS Access 97.
    I have a main form which has a drop-down combo box (with options 1 to 9, for example) and a button. When I click the button, there is a module that creates a query that executes a stored procedure with the parameter based on the option selected in the main form.
    Then the code handler for the button launches a report say, rptA, whose data is extracted from the stored procedure launched by the dynamically created query. Therefore the flow sort of looks like this:

    frmMain-->query-->storedprocedure-->report

    What I would like is to display the report 9 times for each of the options in the drop-down listbox when no option is selected (i.e. left blank).
    I have tried this by iterating DoCmd.OpenReport "rptA", acViewPreview, but the problem is it only displays the first one. When I step-into the code, I had to close the first report before the next DoCmd is executed in order for the next "instance" of the report to display. Anyone know what is the right way of doing this is? Your help would be much appreciated. Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Are you sure you want 9 copies open simultaneously?

    Anyhoo - you can do this by using the VB (rather than Access) method of opening opjects:
    Code:
    Dim TheReports() As Access.Report
     
    Sub OpenSameReportSeveralTimes()
     
        Const NUMBER_OF_REPORTS As Byte = 9
     
        Dim i As Integer
        Dim rpt As Report_MyReportName
     
        ReDim TheReports(NUMBER_OF_REPORTS - 1)
     
        For i = 0 To NUMBER_OF_REPORTS - 1
     
            Set TheReports(i) = New Report_MyReportName
     
            TheReports(i).Visible = True
     
        Next i
     
    End Sub
     
    Sub CloseReports()
    On Error Resume Next
     
        Dim rpt As Access.Report
        Dim i As Integer
        Dim j As Integer
     
        j = UBound(TheReports)
     
        For i = 0 To UBound(TheReports)
     
            DoCmd.Close acReport, TheReports(i).name
     
        Next i
     
        ReDim TheReports(0)
     
    End Sub
    HTH

    EDIT - Weclome to the forum BTW
    Last edited by pootle flump; 07-17-06 at 09:37.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    Hi pootle_flump

    Thank you for the welcome and the code snippet you posted.
    I tried the code and it is creating and displaying multiple instances of the report, in this case

    Report_MyReportName. However, I am encountering the following error:


    "Run-time error '2191':

    You can't set the Record Source property after printing has started.

    Try setting this property in the OnOpen event.



    Going back to my simple diagram, it is actually:

    frmMain-->query-->storedprocedure-->subReport(1 to 4)-->Report_MyReportName

    Therefore, there are 4 subReports "fed" into the Details section of Report_MyReportName and the source data of

    these subreports come from the query which executes a stored procedure. I think the error happens here because in

    the Report_Open subroutine of each of the subReports there is a line that sets the RecordSource to the query.
    i.e. Me.RecordSource = "Query_SubRpt1"
    How do I solve this so that the RecordSource is set before printing has started (assuming that the error

    description accurately describes the real source of the errror) or any other solution that will eliminate the error

    I am getting.
    (I've tried quite a few combinations of commands to make it work but I think (being a noob) there's something I

    don't know about how access works)


    HTHYHM(Hope this helps you help me :-) )
    Thank you again...

    cheers!
    g11DB

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi g11DB

    one quick thing- did you get this working for a single report? Is it the multiple reports that are causing the problem or is this unrelated to multiple report thing?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh hang on - you can't have a pass through query as a subreport recordsource either
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah yes - also the "printing" starts before a sub report's OnOpen event fires (as I recall) so setting a sub report's record source dynamically is tricky at least and impossible at worst.... can't quite remember which....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2006
    Posts
    111
    Hi PF,

    Thanks for your quick replies. Actually there is an existing access database (.mdb file) with the report, MyReportName, composed of 4 subreports. These subreports have the following line:
    Me.RecordSource = "Query_SubRpt1"

    and Query_SubRpt1 has a body similar to:

    exec stored_proc1 param1, param2

    The report MyReportName works with:

    DoCmd.OpenReport "MyReportName", acViewPreview

    When I added your code as I remember, I didn't use the DoCmd.OpenReport but instead (with just essential bits):
    Code:
    Dim TheReports() Access.Report
    
    ReDim TheReports(NUMBER_OF_REPORTS - 1)
    
    For i = 0 To NUMBER_OF_REPORTS - 1
     
            Set TheReports(i) = New Report_MyReportName
     
            TheReports(i).Visible = True
     
    Next i
    Now, when I added the above code to what I already have, it works only when I comment out the bit that sets the recordsource for each of the subreports:

    'Me.RecordSource = "Query_SubRpt1"

    And MyReportName displays the number of times I set NUMBER_OF_REPORTS to. Of course this will not do because I will have no data inside MyReports if I comment out the record source.

    Look forward to your reply. You've been very helpful. Thank you again.

    regards,
    g11DB
    Quote Originally Posted by pootle flump
    Hi g11DB

    one quick thing- did you get this working for a single report? Is it the multiple reports that are causing the problem or is this unrelated to multiple report thing?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you confirm that you had the report working before my code:
    1) where the subreport set its own recordsource dynamically
    2) the recordsource (Query_SubRpt1) was a passthrough query that executed a stored procedure

    just to be certain I understand you. Because both those things are impossible ASFAIK
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2006
    Posts
    111
    Hi PF,

    Thank you for your reply.
    Following are my answers...
    1) I think it is dynamic (not sure of the exact definition of the term in Access but...) because
    a) there is a function that declares a Querydef and CreateQueryDef is called on the current database to set the this QueryDef to say "Query_SubRpt1"
    i.e. Set QryVar1 = MyDB.CreateQueryDef("Query_SubRpt1")

    Then the following is called:

    QryVar1.SQL = "EXEC stored_proc1 " & param1 " " & " " param2

    b) param1 and param2 are variables that are set on the fly so each time I open the query in the Queries tab, I will see "EXEC stored_proc1 a 1" one time and next time I will see "EXEC stored_proc1 b 2"

    2) Yes, The recordsource of the subreports are pass-through queries that execute stored procedures.

    Look forward to being enlightened.

    regards,
    g11DB

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    would you be able to strip out everything irrelevent, zip and post your db? No matter that the data is in SQL Server.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2006
    Posts
    111
    Hey PF,

    i'll try zip and send you a stripped copy of the access database .mdb as soon as i can. thank you :-)

    best!
    -g11DB

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    If you still want some help please post your db

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •