Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening Report with different RecordSources (2002)

    I am trying to open a report with different record sources based on what choice you make in a form option box. Depending on what option you select the report should open based on a different query. I realize that there are several ways you could do this. I tried doing it from select case statements using various code but no luck.

    Private Sub optSelectForm_AfterUpdate()
    Select Case optSelectForm
    Case Is = 1 (one approach I tried)
    Reports!rptMissingForms.RecordSource = "qryEmergInfo"
    DoCmd.OpenReport "rptMissingForms", acViewPreview


    Case Is = 2 (another approach)
    DoCmd.OpenReport "rptMissingForms", acViewPreview, "qryEmergInfo"

    End Select

    End Sub

    I'm open to any approach that will work.

    Thanks - Paul

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

    Re: Opening Report with different RecordSources (2002)

    Hi

    The way I have done this (and probably not the best way) is to:

    a) Create two public variables, one for the record source and the other for report caption.
    [img]/forums/images/smilies/cool.gif[/img] In report open_form add something like:

    Me.RecordSource = QueryResult
    Me.Caption = ReportCaption

    Where QueryResult and ReportCaption are the declared variables

    c) In the Form in the appropriate place use something like:
    If IsReportLoaded("rptNotificationLetter") Then
    MsgBox "Report already in use. Try again later"
    Exit Sub
    End If

    If Caseis = 1 then
    QueryResult = "qryreportNotificationetterall"
    ReportCaption = "Notification letter for all manufacturers"
    else
    QueryResult = "qryreportNotificationetterselected"
    ReportCaption = "Notification letter for selected manufacturers"
    End if
    Dim stDocName As String

    stDocName = "rptNotificationLetter"
    DoCmd.OpenReport stDocName, acPreview

    Obviously you could use select case etc as the option for selecting different recordsources etc.

    Regards
    WTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Opening Report with different RecordSources (2002)

    <hr>Reports!rptMissingForms.RecordSource = "qryEmergInfo"
    DoCmd.OpenReport "rptMissingForms", acViewPreview<hr>
    This doesn't work because you can't change the recordsource that way. Instead, you would have to open the report in design view, change the recordsource and then save the change and close the report. Then you could open it with the appropriate recordsouce. That is messy, however.

    <hr>DoCmd.OpenReport "rptMissingForms", acViewPreview, "qryEmergInfo"<hr>
    This doesn't work because the argument you're trying to use is for a filter, not for the recordsource of the report.


    In 2002, reports have an OpenArgs property. You could use that to pass the name of the query to use to the report and then let it set its own datasource.

    <pre>DoCmd.OpenReport "rptMissingForms", acViewPreview, , , , , "qryEmergInfo"</pre>


    In the report's Report_Open event, you could do something like this:

    <pre>If Not IsNull(Me.OpenArgs) Then
    Me.Recordsource = Me.OpenArgs
    End If</pre>

    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening Report with different RecordSources (2002)

    What I do is create a user table (tblUserRpts) that my reports run against. For each reporting criteria I run a make query to create tblUserRpts. The report then uses this as its source. (You must have a split database if multiple users are are using this database.)
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Opening Report with different RecordSources (2002)

    Instead of a MakeTable query, create the table you want to use and then simply delete its records and repopulate it with an append query. You can update the data in that table in installments if necessary in order to get the data you need for the reports. By reusing the same table, you reduce the probability of it going wrong or being missing when you need it. We use this technique in our commercial apps for creating graphs that compare multiple sets of values. You still need to do with a local table it in the front end, since that will give each user who loads the application a separate copy of that table. That would allow several users to run different reports simultaneously and doesn't require the user to have permissions to create or delete tables.
    Charlotte

Posting Permissions

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