Reports for different type of Facilities

[rodeoboy]

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

[pbaldy]

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.

[rodeoboy]

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

[pbaldy]

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.

[rodeoboy]

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

[pbaldy]

No problem, and feel free to post back if you get stuck.

[rodeoboy]

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

[pbaldy]

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.

[rodeoboy]

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

[pbaldy]

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

[rodeoboy]

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

[pbaldy]

No problemo; post back if you get stuck.