Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Forms (2000)

    Hi. I am a newbie. I have a database with multiple reports and I would like to create a form with list of these reports you can click on one in the list and then click preview or print to print that report. I have looked at the Northwind Database example which has a Sales by Category Detail which is what I want but I can not figure out how it gets it's information and how to make it work. Please help I am stumped. Than you in advance for any help you can give.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Forms (2000)

    Here's a basic outline of one approach (reply back if you need more detail):

    1. Create a table which includes your report names (this table could be populated by inspecting the CurrentDb().Containers("Forms").Documents collection -- if you're familiar with VBA and navigating the object model -- otherwise, just fill in the table by hand).

    2. Create a form that uses this table as its Record Source. Include a text control that references the table's report name field.

    3. On this form create a "Preview" button. As its OnClick event, specify the following command:

    DoCmd.OpenReport Me!txtReportName, acViewPreview

    (txtReportName is the Name for the text control on the form that displays the selected report name).

    4. Create a similar "Print" button with a simiar OnClick event, but specify acViewNormal instead of acViewPreview

    I think something like that would get you what you want. I've left out the gory details. If you need them, holler.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Access Forms (2000)

    ... Another option is to create a form with checkboxes, one for each report. Put the report name next to the checkbox as text for the user to reference. Then build an event for each checkbox that will open the report whenever the checkbox is checked. See Below:



    Private Sub Check94_Click()
    If Forms!FormName!Check94.Value = True Then
    stDocName = "ReportName"
    DoCmd.OpenReport stDocName, acPreview
    End If

    End Sub

    ... Or you could create a preview button that opens all reports checked using the same logic.
    Good luck.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Forms (2000)

    I think I must not be too smart because I am lost......LOL. I have a table I created with 2 fields first one is report second is report name. I typed in the Report in the first field but what do I put in the second. Second... I have the list box on my form and a preview and print button but when I created the buttons they attached themselves to a specific report HELP! Thanks.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Forms (2000)

    I think you're confused. First, you create all of your reports. Next, create a form and, as Mr. Swanson so expertly advised, add 1 control button to the form for each report. Finally, 'program' each button with the code Mr. Swanson supplied, replacing 'Report Name' with the actual name of the report. To access the Code Builder for each control button, highlight the button (not the label), right click on the button, select 'Build Event...', select 'Code Builder', and paste Mr. Swanson's code in, make sure you specify the correct report name. You should be good to go.

    I'm not sure what you're trying to do with the table you created.

  6. #6
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Forms (2000)

    I do not want a check box or button for each report there are 25 reports. I want to use a list box for the reports and click on one to highlight it and then hit a print button or hit preview to see the reports. Thank you. I need to know how to do this in a list box. Thanks

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Access Forms (2000)

    It sounds as if you are looking for Tom's method. Tom is creating a listbox that links to a table with your report names.

    First, create a table with the names of the reports. The table can have two fields, ID (use autonumber and set as primary key) and name (set as text, size as needed to store the report name) Input the report names in the table such that the report name in the table matches the report name object (i.e, include spaces, commas, etc).

    Create the listbox (use the wizard, its easier) and connect to the table so it gets the data for the listbox from the table. Thus, when using the listbox, you will be able to select the name of the report you want.

    Then follow the rest of Toms' directions. This is not as fancy as the Northwinds database, but it works.

    Good Luck
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Forms (2000)

    Thanks. I got that far to set up the List box and it is using the table but I didn't understand the rest of the directions that TOM gave me. I guess I don't know half of what I thought I knew about access. Can you please explain in more detail the rest of the process? Thank you again for bearing with a newbie.

  9. #9
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Forms (2000)

    Hoorayyyy......I figured it out with all you guys help. Thank you everyone for the help. I love the Woody's Lounge. All the people here are so helpfull every time I need help. Thanks again all.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access Forms (2000)

    Hi,
    This may be too late, but if you want a dynamic list of reports (i.e. not having to add new reports to your table as you create them) you can set your listbox row source to:
    SELECT Name from MSysObjects WHERE Type = -32764
    and this will then pick up ALL reports in your database. This does have the disadvantage that it will include any subreports in your database, but it's less effort to maintain!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Forms (2000)

    Neil,

    Glad you got it figured out. Sorry I didn't check back sooner to see if you needed more help. Obviously there's lots of help here in the Lounge, ready and waiting! I knew I had left lots of gaps in my suggestion -- sometimes you learn more by figuring things out rather than following a recipe, right? Frustrating at times, sure, but more satisfying in the end.

    You might try Rory's suggestion for "populating" your list box. Rather clever, I think. It keeps the list up to date as you add or remove reports or change report names, and it doesn't require a separate table. I was trying to figure out a way of doing that but was unsuccessful. If you want to exclude certain reports (e.g., subreports as Rory mentions or other reports used for other purposes), just name your reports in a clever way. For example, begin the names of the ones you want to include with something like "List", then add the following filter to Rory's query:

    SELECT Name from MSysObjects WHERE Type = -32764 <font color=red>AND Left(Name,4)="List"</font color=red>;

    Good luck.

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Access Forms (2000)

    Hey Rory,

    You Said:
    SELECT Name from MSysObjects WHERE Type = -32764


    This is pretty cool. I wish I knew about this sooner. Do you use or can you recommend any reference manuals that review items like this???
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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