Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    75

    Unanswered: report record source

    Question 1: How do I (or can I) declare the record source of a report on open?

    I have one report that will work for different queries... it seems a waste to build separate reports for each one I need. This seems easy, but I can't figure out how to do this.

    Question 2: How can I determine by code if a form is open?

    So... what I want...
    If Form1 is open, open Report with Query1 as record source
    if Form2 is open, open Report with Query2 as record source


    Any help? Please?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Here's a handy little function for checking if a form is loaded:

    Code:
    Function IsLoaded(ByVal strFormName As String) As Integer
    ' Returns True if the specified form is open in Form view or Datasheet view.
    
    Const conObjStateClosed = 0
    Const conDesignView = 0
    
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
       If Forms(strFormName).CurrentView <> conDesignView    Then
          IsLoaded = True
       End If
    End If
    
    End Function
    Now you can drop that into the OnOpen event of your report and specify your source accordingly:
    Code:
    If IsLoaded("form1") Then
        Me.RecordSource = "sql goes here"
    ElseIf IsLoaded("form2") Then
        Me.RecordSource = "other sql goes here"
    Else
        'rest of logic handling
    End If

  3. #3
    Join Date
    Jan 2004
    Posts
    75
    I pasted the first part (verbatim) into a module.

    Then put the followin in the on open event of said report:

    Option Compare Database

    Private Sub Report_Open(Cancel As Integer)
    If IsLoaded("commandops") Then
    Me.RecordSource = "cosearch"
    ElseIf IsLoaded("trainingops") Then
    Me.RecordSource = "search by all"
    End If
    End Sub

    It doesn't work... the report lists all records...?

    "cosearch" and "search by all" are the names of queries

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by sublimsos
    I pasted the first part (verbatim) into a module.

    Then put the followin in the on open event of said report:

    Option Compare Database

    Private Sub Report_Open(Cancel As Integer)
    If IsLoaded("commandops") Then
    Me.RecordSource = "cosearch"
    ElseIf IsLoaded("trainingops") Then
    Me.RecordSource = "search by all"
    End If
    End Sub

    It doesn't work... the report lists all records...?

    "cosearch" and "search by all" are the names of queries
    Ok, there's a few things to check.

    First off you need to make sure that the IsLoaded() is returning true for one of the conditions. I would suggest putting a debug MsgBox into each condition. Secondly, I would debug the sql itself to ensure that it is indeed what you selected.

    Code:
    Private Sub Report_Open(Cancel As Integer)
    If IsLoaded("commandops") Then
        Me.RecordSource = "cosearch"
        MsgBox "cosearch selected"
    ElseIf IsLoaded("trainingops") Then
        Me.RecordSource = "search by all"
        MsgBox "search by all selected"
    End If
    
    MsgBox Me.RecordSource
    
    End Sub

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: report record source

    Originally posted by sublimsos
    Question 1: How do I (or can I) declare the record source of a report on open?

    I have one report that will work for different queries... it seems a waste to build separate reports for each one I need. This seems easy, but I can't figure out how to do this.

    Question 2: How can I determine by code if a form is open?

    So... what I want...
    If Form1 is open, open Report with Query1 as record source
    if Form2 is open, open Report with Query2 as record source


    Any help? Please?
    Access Version?

    You could setup a GLOBAL Parameter variable that you set the RecordSource to ...

    If this were 2002 then the report has the OpenArgs parameter ...

  6. #6
    Join Date
    Jan 2004
    Posts
    75
    I just upgrade to Access 2002 last week... so I'm unfamiliar with OpenArgs... I saw it in the help file, but as usual, the help file isn't that helpful. I tried putting something like "reports!searchrpt.recordsource = 'cosearch'" for OpenArgs... didn't do doodly.

    Teddy's way looks perfect for what I need. It will work the first time, but if I close the report, then try again, it doesn't work any more. I'll play around with it more.

    Thanks so much for the help as always!!

  7. #7
    Join Date
    Apr 2004
    Posts
    1
    Well I am going crazy with the same problem. There is an MSDN article that sort of discribes this but is not correct. It says its fixed after access 97.

    The problem seems to be that sometime after a report is opened the record source is set and cannot be changed. With a form this is easily handled but with a report the open event is fired when the report is first used after access is opened and NEVER it would seem again. This means that using OPENARGS in the on open event will work ONLY the first time you use the report. So I have tried any number of ways including using global variables and setting the record source in the OnOpen event handler and this works only the FIRST time. The nearest I have come is to do the following

    Dim Rpt As Report
    gReportDataSource = "qry_MyData"
    gReportFilter = MyFilter

    Set Rpt = New Report_rpt_NonSPS_Abbr
    Rpt.Visible = True

    When you do this the OnOpen event fires at the Set Rpt line and code in the OnOpen can use the global vars to set the appropriate report properties. This is a lousy hack (WHY DIDN'T the wizards at MS treat a report like a form which requires a specific open call??) Anyway the problem with the code above (besides the attrocious hack of using global vars) is that I want to print not view the report. This causes it to be displayed. The display is as desired (contains the correct data) but I want to print it. Also the Rpt var above must not be a function var as the window goes away as soon as the function is closed.

    If you have any solutions PLEASE let me know I really need a fix to this

    CharlieB

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok ... I'll put you out of your misery ... Here is some code exampling the OpenArgs parameter in 2002 ( I have it commented out because my target DB is 2000 ...)

    Code:
    Private Sub Report_Open(Cancel As Integer)
        DoTblDelete = False
    '    If IsNull(Me.OpenArgs) Then
    '        MsgBox "This report can only be run from the Clock Card Entry form.", vbExclamation, "System Monitor"
    '        Cancel = 1
    '        Exit Sub
    '    End If
        
        Dim ParmListString() As String
        
        '
        ' Format:
        '
        ' Missing Clock Cards Table, Timesheet Day
        '
    '    ParmListString = Split(Me.OpenArgs, ",", -1, vbTextCompare)
        ParmListString = Split(Me.OpenArgs, ",", -1, vbTextCompare)
        MissingCCTbl = ParmListString(0)
        Me.RecordSource = ParmListString(0)
        TSDayLbl.Caption = ParmListString(1) & ""
        DoTblDelete = True
    End Sub

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    [SIZE=1]Originally posted by CharlieB
    ...with a report the open event is fired when the report is first used after access is opened and NEVER it would seem again.
    I can assure you that's not correct.

    I dynamically set quite record sources for quite a few reports in the onopen event. I can run and re-run a report as many times as I want with as many different sources as I want, which are fired in the on-open.

Posting Permissions

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