Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Report record source change

    I am a beginner working with access 2007. I have tried several posts regarding my problem in the posts to no avail. I want to change a report's record source property in the report's open event of the report from the record source (Q_query1) selected in design view to a different record source property (Q_query2) in the code as it opens. This way I can use the same report layout with 2 queries. I'v tried this code in the form
    [Reports]![R_REP_Query1].RecordSource = "Q_query2"
    and this in the report
    me.RecordSource = "Q_query2"

    Please help
    Last edited by DJV; 07-14-12 at 13:45.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    In the Report module, add:
    Code:
    Private Sub Report_Open(Cancel As Integer)
    
        If Not IsNull(Me.OpenArgs) Then
            Me.RecordSource = Me.OpenArgs
        Else
            MsgBox "No source provided.", vbExclamation
        End If
        
    End Sub
    Then you can open the report and specify which query it will use as RecordSource, like this:
    Code:
        DoCmd.OpenReport "<ReportName>", acViewPreview, , , , "<QueryName>"
    Where <ReportName> is the name of the report to open and <QueryName> is the name of the query to use.
    Have a nice day!

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Code:
        If Not IsNull(Me.OpenArgs) Then
            Me.RecordSource = Me.OpenArgs
        Else
            MsgBox "No source provided.", vbExclamation
        End If
    I think a better approach would be to use the OpenArgs for both reports (although that may have been Sinndho's intent as well). That way the code is simply
    Code:
    Me.RecordSource = Me.OpenArgs
    The calling program, in both cases, would use the format that Sinndho mentioned.

    You understand that if you rely on the OpenArgs property, you cannot open the report manually (double-clicking on the report icon in the database window); you must only open the report through the calling program. Otherwise, you will get an error message that the OpenArgs is null.

    Sam

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by Sam Landy View Post
    [Otherwise, you will get an error message that the OpenArgs is null.

    Sam
    This is precisely why I test:
    Code:
    If Not IsNull(Me.OpenArgs) Then
    Have a nice day!

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Sinndho View Post
    This is precisely why I test:
    Code:
    If Not IsNull(Me.OpenArgs) Then
    I understand; but then you should add a line to the code:
    Code:
    Private Sub Report_Open(Cancel As Integer)
    
        If Not IsNull(Me.OpenArgs) Then
            Me.RecordSource = Me.OpenArgs
        Else
            MsgBox "No source provided.", vbExclamation
            Cancel = True
        End If
        
    End Sub

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Or you could try:
    Code:
    Private Sub Report_Open(Cancel As Integer)
    
        If Not IsNull(Me.OpenArgs) Then
            Me.RecordSource = Me.OpenArgs
        Else
            MsgBox "No source provided - using default source.", vbExclamation
        End If
        
    End Sub
    Then you can open the report normally during development and testing, and still change the underlying recordsource at runtime.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    MsgBox "No source provided - using default source.", vbExclamation
    I'm afraid I can't agree. When you set a default RecordSource in the property sheet (or anywhere else, for that matter), and you overwrite - or even accept - that property, the record source property in the property sheet will update. In other words, since the record source changes depending on the calling program, the record source entry in the property sheet has been changed any number of times, and there is no default anymore.

    The same goes for any property in the property sheet: the property sheet retains the most recent settings, even if the setting was set in VBA, and the db has been closed.

    Sam

  8. #8
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by Sam Landy View Post
    I'm afraid I can't agree. When you set a default RecordSource in the property sheet (or anywhere else, for that matter), and you overwrite - or even accept - that property, the record source property in the property sheet will update. In other words, since the record source changes depending on the calling program, the record source entry in the property sheet has been changed any number of times, and there is no default anymore.

    The same goes for any property in the property sheet: the property sheet retains the most recent settings, even if the setting was set in VBA, and the db has been closed.

    Sam
    Not is you set the AllowDesignChanges property of the form to False. In such a case, you can dynamically change a property when the form is open (using VBA) but this change is temporary (i.e. its only valid for the time the form remains open). When the form is closed, the value set in the property window when the form is in Design mode is never overwritten.
    Have a nice day!

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Wow! Look at that! I never knew what use that property had. It goes without saying that I never used it. Learn something new every day.

    Thanks mucho,

    Sam

    PS I think I'd still rather set Cancel to true rather than use an original design default. Clicking on the report icon directly is fraught with the danger of not knowing what he's seeing (in this case, I mean), because of the confusion of two pretty-much-identical record sources. He'll see the results, and perhaps think he's looking at one thing instead of the other. Don't you agree?

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    He'll see the results, and perhaps think he's looking at one thing instead of the other. Don't you agree?
    Reply With Quote
    No. If the report is made with one recordset, and the other is only invoked during a program call, then when the report is opened from the database window, it will only show one thing. That's how my mind works, anyway! :P
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by Sam Landy View Post
    Wow! Look at that! I never knew what use that property had. It goes without saying that I never used it. Learn something new every day.

    Thanks mucho,

    Sam
    You're welcome!
    Quote Originally Posted by Sam Landy View Post
    PS I think I'd still rather set Cancel to true rather than use an original design default. Clicking on the report icon directly is fraught with the danger of not knowing what he's seeing (in this case, I mean), because of the confusion of two pretty-much-identical record sources. He'll see the results, and perhaps think he's looking at one thing instead of the other. Don't you agree?
    When I posted my first answer, I just wanted to show how it was possible to dynamically set the data source of a report, the main point being that you can only do it in the Report_Open event. I never intended to provide a complete solution for handling such situations.

    In most of the cases, you're right and something more than just displaying a warning should be done. However, I probably would treat such cases as an error (an exception could be a better word here) that should be processed in a more complex way than just closing the report. Depending on the application, a default RecordSource for the report could be acceptable in some circumstances too.
    Have a nice day!

  12. #12
    Join Date
    Jul 2012
    Posts
    4

    Two RecordSource

    You can use two select query for Report RecordSouce, then using a form with one command button, and two Option Button. In the command button Even Procedure, apply this code:

    Select case OptionButtonName

    case 1
    DoCmd OpenReport, "ReportName",,Query 1

    case 2

    DoCmd OpenReport, "ReportName",,Query 2

    End Select


    Hoppe this help for you!

  13. #13
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by ndphuong View Post
    You can use two select query for Report RecordSouce, then using a form with one command button, and two Option Button. In the command button Even Procedure, apply this code:

    Select case OptionButtonName

    case 1
    DoCmd OpenReport, "ReportName",,Query 1

    case 2

    DoCmd OpenReport, "ReportName",,Query 2

    End Select


    Hoppe this help for you!
    This is not correct. The third parameter of the OpenReport method (here 'Query1') specifies a query that is used for filtering the data of an existing RecordSource, not the RecordSource itself. If no RecordSource was specified when the report was created or later on (in the Property windows or using VBA), the report will open but it will be empty and all bound controls on it will display '#Name?'.

    See: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    Have a nice day!

Posting Permissions

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