Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Reports (97/2000/2002/2003)

    I have an Access database with 400+ reports. I want to start consolidating reports, but I don't know what reports are being used the most. Is there something I can do to capture how often a report is opened? For a few reports I put a little piece of code in the OnOpen to update a table with the report name and the user who opened it, but because I have so mane reports I don't want to edit each one to insert this piece of code.

    I simply want to know what the most popular reports are in my system....Any ideas?

    Thanks!

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

    Re: Access Reports (97/2000/2002/2003)

    Welcome to Woody's Lounge!

    Access does not have a "global" event that occurs when any report is opened, so I'm afraid you're stuck with either putting code in the On Open event of each individual report, or if the user can only open reports using VBA procedures (for example from a form), adding code to those procedures.

  3. #3
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Reports (97/2000/2002/2003)

    Is there a way to "mass update" all of my reports and add the code to all of them - instead of opening each one individually

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

    Re: Access Reports (97/2000/2002/2003)

    You could use code to do so. The following should work in Access 2000 and higher (not in Access 97):

    Sub UpdateReports()
    Dim strCode As String
    Dim obj As AccessObject
    Dim strName As String
    Dim rpt As Report
    Dim mdl As Module

    strCode = "Private Sub Report_Open(Cancel As Integer)" & vbCrLf & _
    vbTab & "' your code goes here" & vbCrLf & _
    "End Sub"

    For Each obj In CurrentProject.AllReports
    strName = obj.Name
    DoCmd.OpenReport strName, acViewDesign
    Set rpt = Reports(strName)
    Set mdl = rpt.Module
    mdl.InsertText strCode
    DoCmd.Close acReport, strName, acSaveYes
    Next obj
    End Sub

    You must replace the line

    ' your code goes here

    with the lines you want to add to the Report_Open procedure. Surround all lines in quotes "...", use vbCrLf to indicate a line break, vbTab to indicate indentation, and if you need quotes within the lines, double them.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Reports (97/2000/2002/2003)

    Hans,

    Will this technique work if there is already a Report_Open procedure?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Access Reports (97/2000/2002/2003)

    No, you'd have to check for that, and add the code to the existing Report_Open procedure instead of creating a new procedure.

    The complete text of the module is in mdl.Lines(1, mdl.CountOfLines). You could use InStr to see if "Procedure Report_Open" is present.

  7. #7
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Reports (97/2000/2002/2003)

    Thanks, Hans.

    One last question.... How do I capture the name of the report? In the code that I had originally I specified the name of the report. Any ideas?

    Thanks again.

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

    Re: Access Reports (97/2000/2002/2003)

    What was the code you originally used?

  9. #9
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Reports (97/2000/2002/2003)

    DoCmd.OpenForm "rbcount"
    [Forms]![rbcount]![Test] = "rptBalanceSheet"
    [Forms]![rbcount]![Count] = [Forms]![rbcount]![Count] + "1"
    DoCmd.Close

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

    Re: Access Reports (97/2000/2002/2003)

    You shouldn't need to open a form for this. You can execute a query, or open a recordset and add a record. What is the name of the table containing the report names, and what is its structure?

  11. #11
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Reports (97/2000/2002/2003)

    It is called "Count" and it has two columns - "Test" and "Count". The 'Test' column represents the report name and 'Count' indicates how many times the report was opened/viewed. I should rename the columns "Report_Name" and "Viewed".

    Thanks for your help on this.

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

    Re: Access Reports (97/2000/2002/2003)

    You could use code like this:
    <code>
    strCode = "Private Sub Report_Open(Cancel As Integer)" & vbCrLf & _
    vbTab & "Dim strSQL As String" & vbCrLf & _
    vbTab & "strSQL = ""UPDATE [Count] SET Viewed = Viewed + 1 "" & _" & vbCrLf & _
    vbTab & """WHERE Report_Name = "" & Chr(34) & Me.Name & Chr(34)" & vbCrLf & _
    vbTab & "DoCmd.SetWarnings False" & vbCrLf & _
    vbTab & "DoCmd.RunSQL strSQL" & vbCrLf & _
    vbTab & "DoCmd.SetWarnings True" & vbCrLf & _
    "End Sub"
    </code>
    This will generate the following On Open procedure:
    <code>
    Private Sub Report_Open(Cancel As Integer)
    Dim strSQL As String
    strSQL = "UPDATE <!t>[Count]<!/t> SET Viewed = Viewed + 1 " & _
    "WHERE Report_Name = " & Chr(34) & Me.Name & Chr(34)
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    End Sub
    </code>
    This assumes that the Count table already contains the names of all reports.

Posting Permissions

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