Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports in table (A2k)

    Does anyone have any examples or methods on populating a table with report names.

    I know I can use the

    SELECT msysobjects.Name FROM msysobjects WHERE (((msysobjects.Type)=-32764));

    method but want to take this a little further.
    My end result will select certain reports and not all as the above SQL will, using criteria of a form or query.

    I would like to create a form with a list that would only show reports relevant to the form from what it is called from.

    I did similar with form names which opened a form selected from a combo, but Access didn't like the coding (as follows).


    Private Sub cmbPrinting_AfterUpdate()
    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    strMsgPrompt = "Continue"
    strMsgTitle = "Printing"
    intButType = vbYesNo + vbDefaultButton1
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)
    If intButSelected = vbYes Then
    Dim stDocName As String
    stDocName = Me.cmbPrinting.Value
    DoCmd.OpenReport stDocName, acNormal
    Forms!mnuworkshopmanager.SetFocus
    Me.cmbPrinting = Null
    Else
    Me.cmbPrinting = Null
    End If
    End Sub

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

    Re: Reports in table (A2k)

    You can expand the WHERE clause of the SQL statement you posted, but of course it depends on how you determine that a report is "relevant". If you can provide details about that, we might be able to help.

    The code you posted looks correct, but it will try to send the report immediately to the printer, without displaying it. I don't know if that was your intention. To display a report in preview mode, use acViewPreview instead of acNormal.
    Also, this code will open/print a report each time the user selects an item in the combo box; that leaves little room for correcting mistakes.
    You wrote that "Access didn't like the coding". What was the error message, and what was highlighted?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports in table (A2k)

    Hans
    The code was from an older version of my database. I only messed with it and never implemented it fully.
    I'm sure there could be many tweaks added. I may have already solved the errors, I really can't remember.

    There would be two buttons, one for "acViewPreview" and one for "acViewNormal"

    About the criteria:


    Parts department would pull reports relevant to parts.
    Accounts would pull reports relevant to accounts.

    This can be achieved using different tags in the table:

    rptPartsOrders = 1
    rptPartsDelays = 1
    rptAccountsLedgers = 2
    rptAccountsOverDues = 2 etc etc

    This is where the query involved could use the Tags# for criteria or filter
    So if we had a table

    Table: tblReports
    Fields: [Description] [Report] [Area]

    Description would be the name the administrator would want to call the form ie. Parts Orders.
    Report would be the name as in the naming convention ie: rptPartsOrders
    Area would be the are from which to pull the reports ie Parts = 1. Accounts = 2 etc.

    One form would be ideal as opposed to numerous forms.

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

    Re: Reports in table (A2k)

    Dave,

    You can only get at the Tag property of a report if it is open in design view or preview mode, so that is problematic. You could use custom DAO properties instead; you can get at them without opening a report. If you're interested, Charlotte has an example of that, if I remember correctly, but you would have to ask her to re-post it.

    If you are consistent in naming, you could use the first part of the name: rptParts, rptAccounts, etc. to distinguish the reports.

    A dedicated table with report names and types will work fine, but you will have to make sure that it stays up-to-date.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports in table (A2k)

    Sorry Hans, I should have used a better word than "Tag". This wasn't meant to mean Tag as Access see's it behind a form etc, more as the Switchboard uses [SwitchboardID] as an identifier.

    ie, Fill list box with reports if the Area is 1, that way I could determine who has accessto which reports.

    I would be interested to see Charlottes example and how it's implemented.

    Constant naming is something I have become quite used to these days but wouldn't want the users to see "rptPartsOrders" etc etc, I could explain it to them and the fundementals behind the naming convention, but they would still say "Why don't you call it Parts Orders instead ?" ie description field.

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

    Re: Reports in table (A2k)

    Dave,

    Using a dedicated table may well be the easiest way to do it. You could use an SQL statement such as "SELECT * FROM tblReports WHERE Area =1" as row source of a list box or combo box. The first column (containing the actual report name) would be hidden, the second column (the diescription) would be displayed.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports in table (A2k)

    Thanks again Hans

    I'll start looking into it today

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports in table (A2k)

    I have been wondering about this problem myself. Could you place code on the opening form for your database, that would check ht elist of reports in the system tables against the dedicated table, and warn you to add any new reports that werer created?
    Just a thought. Have not yet implemented it
    Zave

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

    Re: Reports in table (A2k)

    In theory, you could, but you wouldn't want the average user to see this warning, so you'd have to display it only if a user with permission to design objects logs in. Personally, I don't think it would be worth the effort - if I as designer add a report, I might as well add it to the table.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports in table (A2k)

    Zave
    As Hans says, I wouldn't want my users to have control over either the database window or the design module of the database.
    This can only be implemented by the administrator on an update of some sort.

    When I've sorted this, I'll try to post a demo for yourself and others who may be interested

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports in table (A2k)

    Here you go Zave
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports in table (A2k)

    thanks, it looks interesting

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

    Re: Reports in table (A2k)

    You could loop through the AllReports collection of the CurrentProject object, or use DAO, and loop through the documents of the Reports container. The following
    is just meant as an illustration. I assumes that you have a table tblReports with a field named Report that contains the names of reports.

    First example: uses AllReports, so only works in Access 2000 and up.

    Sub Example1()
    Dim obj As AccessObject
    For Each obj In CurrentProject.AllReports
    If DCount("*", "tblReports", "Report=" & Chr(34) & obj.Name & Chr(34)) = 0 Then
    MsgBox "Report " & obj.Name & " has not been entered in tblReports"
    End If
    Next obj
    Set obj = Nothing
    End Sub

    Second example: uses DAO, so works in older versions of Access too. Needs a reference to the Microsoft DAO n.n Object Library (n.n = 3.5 for Access 97, n.n = 3.6 for Access 2000 and 2002)

    Sub Example2()
    Dim dbs As DAO.Database
    Dim ctr As DAO.Container
    Dim doc As DAO.Document
    Set dbs = CurrentDb
    Set ctr = dbs.Containers!Reports
    For Each doc In ctr.Documents
    If DCount("*", "tblReports", "Report=" & Chr(34) & doc.Name & Chr(34)) = 0 Then
    MsgBox "Report " & doc.Name & " has not been entered in tblReports"
    End If
    Next doc
    Set doc = Nothing
    Set ctr = Nothing
    Set dbs = Nothing
    End Sub

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports in table (A2k)

    Even more interesting !!

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

    Re: Reports in table (A2k)

    Dave,

    I addressed this problem differently. I created a custom property of reports called DisplayName and assigned a user-friendly display name to the reports I wanted to see in a list. That eliminated subreports and administrative reports and allowed me to easily control what reports users could select. I've attached a demo in 97 format. It contains two forms, one to allow you to create display names for reports and one that demonstrates showing those names in a list. The file is in 97 format to get it small enough to post.
    Attached Files Attached Files
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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