Results 1 to 12 of 12
  1. #1
    rodeoboy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    20

    Reports for different type of Facilities

    I am trying to help with an Access issue that was brought to me and I am not very Access Savoy and I wanted to make sure it could be done before I spend hours searching the wrong solution.

    My boss wants (1) report that when accessed; it will prompt you to choose (with a drop down list) which type of facility will be shown on the report. We have a large table setup that lists all different types of projects we have done and the type of facility it is.

    Can this be done and how might I go about?



    Just wondering,
    Rodeoboy

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    Would the report show the same fields for different facilities? If so, fairly simple. If not, trickier, but may be possible. We would need more info about how the report would differ. In other words, if I had a contacts table that included personal and work contacts, it would be simple to restrict the report to one or the other, as it would be showing the same fields (name, phone, email). If the nature of the data was different, I'd have to change the structure of the report, which gets harder.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rodeoboy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Quote Originally Posted by pbaldy View Post
    Would the report show the same fields for different facilities? If so, fairly simple. If not, trickier, but may be possible. We would need more info about how the report would differ. In other words, if I had a contacts table that included personal and work contacts, it would be simple to restrict the report to one or the other, as it would be showing the same fields (name, phone, email). If the nature of the data was different, I'd have to change the structure of the report, which gets harder.
    The report would have the following:
    Name of Facility
    Description of the work performed
    City
    State
    Sq footage
    Facility Type

    The fields will be the same for all types of facilities. These are all in the Table already.

    Thanks,
    Rodeoboy

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    Then it's fairly easy. I typically "push" the criteria to the report, using a technique like this:

    http://www.baldyweb.com/wherecondition.htm

    That would require the form to be open first, but I typically have a criteria form anyway. You can force the report to open a form and wait for the user, but I prefer the reverse. At it's simplest, you can also base the report on a query that includes a criteria on the facility field like:

    [Enter facility]

    That will prompt the user as the report opens. Most of us prefer to use a form to gather user input, as you have more control.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    rodeoboy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Quote Originally Posted by pbaldy View Post
    Then it's fairly easy. I typically "push" the criteria to the report, using a technique like this:

    http://www.baldyweb.com/wherecondition.htm

    That would require the form to be open first, but I typically have a criteria form anyway. You can force the report to open a form and wait for the user, but I prefer the reverse. At it's simplest, you can also base the report on a query that includes a criteria on the facility field like:

    [Enter facility]

    That will prompt the user as the report opens. Most of us prefer to use a form to gather user input, as you have more control.
    Thanks for the information, I truly appreciate it.

    I may have some more questions though.

    Thanks again,
    Rodeoboy

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    No problem, and feel free to post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    rodeoboy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    20

    Reports for different types of Facilities

    Quote Originally Posted by pbaldy View Post
    No problem, and feel free to post back if you get stuck.
    OK I didn't get the form to work yet but I was explaining the procedure to the person I am helping with this issue and she was wondering if it can be done by accessing a report. Her boss specifically stated he wanted it done by accessing a report.

    Just wondering,
    Rodeoboy

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    What exactly do they mean by "accessing a report"? If they want to be able to open a report from the Navigation Pane and have it open the form for the user to select from a list, sure. Like I said, it's just a little more complicated. In the report's open event, you open the form in dialog mode (which makes it wait). In the form, after the user makes a selection you hide rather than close the form, which will let the report continue. The report would take the value from the hidden form and use it to filter itself. In the report's close event, close the form.

    Most of us don't let users into the Navigation Pane.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    rodeoboy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Quote Originally Posted by pbaldy View Post
    What exactly do they mean by "accessing a report"? If they want to be able to open a report from the Navigation Pane and have it open the form for the user to select from a list, sure. Like I said, it's just a little more complicated. In the report's open event, you open the form in dialog mode (which makes it wait). In the form, after the user makes a selection you hide rather than close the form, which will let the report continue. The report would take the value from the hidden form and use it to filter itself. In the report's close event, close the form.

    Most of us don't let users into the Navigation Pane.
    Yes basically he wants to navigate to the specified report - when clicking on it to open it he wants it to ask him to pick (not type in) the type of facility and only report on those chosen type. The reported items will be pulled from a table which has all of our projects and their type of facility.

    Rodeoboy

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    Okay; above is the shell of how it can work. Dive in and see how it goes. The filter code looks like this for a numeric value:

    Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
    Me.FilterOn = True
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    rodeoboy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    20
    Quote Originally Posted by pbaldy View Post
    Okay; above is the shell of how it can work. Dive in and see how it goes. The filter code looks like this for a numeric value:

    Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
    Me.FilterOn = True
    Thanks for sticking with me on this. I will give it shot and see what happens.

    Thanks again,
    Rodeoboy

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    No problemo; post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reports w/ sub-reports very slow to open
    By vaikz in forum Reports
    Replies: 2
    Last Post: 02-27-2011, 08:41 AM
  2. How do I fix the #type!
    By Shortone in forum Access
    Replies: 3
    Last Post: 12-02-2010, 11:34 PM
  3. Access Reports drop sub-reports
    By Kevin Ellis in forum Reports
    Replies: 0
    Last Post: 11-19-2010, 03:28 PM
  4. Replies: 0
    Last Post: 05-14-2010, 07:28 AM
  5. Replies: 2
    Last Post: 03-18-2010, 08:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums