Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Report Source on the Fly (XP SP2)

    In searching the Lounge, I have found several articles pertaining to having variable report sources. I'm wondering what is the simplest approach. Is there a way to have the report setup, then have a prompt or dropdown to choose the query from when it's opened from the switchboard?

    Thanks in advance for ideas.
    egghead

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Report Source on the Fly (XP SP2)

    Most of the threads you found probably deal with reports based on crosstab queries. With a crosstab query, the general structure is fixed, but the number of columns may be different each time you open the report.

    I don't think a completely general approach will be easy or attractive. Each report has its own requirements; to make this all dynamic is a lot of work.

    What does work very well is a form on which the user can select restrictions for the report, for example a period of time, or a category, You can find many examples of this in the Access forum, and also in several sample databases from Microsoft.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Report Source on the Fly (XP SP2)

    Hi

    I agree with Hans, however, I on occasion use a report template where the report source (using different queries) can be changed. The source queries have the same information, but the selection criteria differs so you don't run into that problem mentioned by Hans. I use this method rather than using forms to select different criteria because they are standard reports.

    There are probably a number of ways of doing it, but I use the following (which probably isn't the most efficient way) :

    Create a module with

    Option Compare Database
    Option Explicit

    Public QueryResult As String
    Public ReportCaption As String

    Function IsReportLoaded(ByVal strName As String) As Boolean
    Dim P As Integer
    If SysCmd(acSysCmdGetObjectState, acReport, strName) Then
    On Error Resume Next
    P = Reports(strName).Page
    IsReportLoaded = (Err = 0)
    End If
    End Function

    Function PrintReport_report1()
    If IsReportLoaded("report1") Then
    MsgBox "Report already in use. Try again later"
    Exit Function
    End If
    QueryResult = "qryreport1"
    ReportCaption = "Report One Caption"
    DoCmd.OpenReport "report1", acViewPreview
    End Function

    Function PrintReport_report2()
    If IsReportLoaded("report1") Then
    MsgBox "Report already in use. Try again later"
    Exit Function
    End If
    QueryResult = "qryreport2"
    ReportCaption = "Report Two Caption"
    DoCmd.OpenReport "report1", acViewPreview
    End Function

    In the report in the on_open event procedure add:

    Private Sub Report_Open(Cancel As Integer)
    Me.Caption = ReportCaption
    Me.RecordSource = QueryResult
    End Sub

    If you wish to run the reports from the switchboard, then you can create macros to run the appropriate function above and set the switchboard to the relevant macro.

    Regards
    WTH

Posting Permissions

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