Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable Report Sources (A2K)

    Once more seemingly simple. I have 15 separate reports. All reports are identical in makeup except for the fact that each report gets its' data from a specific query, ie 15 queries and each report has a heading that reflects the query being used.

    Here is the wish list:

    Using one report layout, from one of 15 selection command buttons, to be able to refer to a specific query and also to change the Report Heading to indicate which of the 15 reports it's supposed to be based on the query selected.

    So, Buttons 1
    Cheers,
    Andy

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

    Re: Variable Report Sources (A2K)

    1. Define two public variables in a standard module:

    Public strRecordSource As String
    Public strHeading As String

    2. Open the report in design view.
    - Place a label lblHeading in the report header section. Its caption can be anything (it will be modified)
    - Clear the Record Source property
    - Create an event procedure for the On Open event:

    Private Sub Report_Open(Cancel As Integer)
    If strRecordSource = "" Then
    MsgBox "No record source specified.", vbExclamation
    Cancel = True
    Exit Sub
    End If

    Me.RecordSource = strRecordSource
    Me.lblHeading.Caption = strHeading
    End Sub

    3. Create event handlers for the On Click event of the command buttons on the form (or let the user select the source from a list box and use one command button.)

    Private Sub cmdReport1_Click()
    On Error GoTo ErrHandler

    strRecordSource = "qryForReport1"
    strHeading = "Summary Report #1"
    DoCmd.OpenReport "rptMasterReport", acViewPreview

    Exit Sub

    ErrHandler:
    ' Ignore error 2501 = report canceled
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    You must of course substitute the appropriate names.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Report Sources (A2K)

    Andy,

    One possible embellishment of Hans' superb guidance you may want to consider -- if it fits your needs -- is to set up a table that has a record for each query, with one field being a yes/no value, one text field filled with the name of the query and one field with the desired title for the report.

    Then set up a form with this table as the record source and use the continuous forms format to display all the records in one view. Before showing the form (e.g., in the Form_Open event), clear the yes/no field for all records in the table using an update query . Then let the user check of which reports (queries) they want. Have a button to "Generate Reports" -- it's On_Click event can the step through the table looking for True values and then use Hans' scheme (more or less) to generate the selected reports.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Report Sources (A2K)

    Hans & Tom,

    My apologies in being so slow in getting back to you. Had to go right after I'd posted my problem. Just got back, so now I'll try to apply your words of Access wisdom and get back to you. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Cheers,
    Andy

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Report Sources (A2K)

    Hans:
    In the words of the ancient prophets, "Bloody Marvelous and Smashing!". Worked totally like, as always when suggested by yourself, dead perfect. Many thanks.
    <img src=/S/smash.gif border=0 alt=smash width=30 height=26>
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Report Sources (A2K)

    Tom,
    Again, many thanks for taking the time to follow up on my problem. I'm going to try your version as another option and run it by those who'll be using it. I won't say pearls before .... but, I always like to give them options. Thanks again,
    <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>
    Cheers,
    Andy

Posting Permissions

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