List Box options (1 Viewer)

[Jade]

Hi There,

I have a form that has a list box that has the following choices: Crew A, Crew B, Crew C, and Crew D. You pick which crew you want and then choose a report you wish to view. The data is that of the crew. The form is linked to a query that has a parameter to filter the crew picked, then it opens the proper report.
My clients now want to be able to have the choice to pick all crews at once and have the report show all the data for the report that they choose.

The list box is linked to a table that has the crews in it. How do I have an option to pick all the crews?

Any suggestions would be great.

Jen

 

[skipit]

Hi Jen,

I am sorta a newbie at Access but I did notice that Jack Cowley offered you a suggestion to your previous post about this subject. His suggestion will open all of the reports for Crew A B C and D to be viewed. I will repost his code at the bottom.

You could also make a report based on your Crew table that will present all the Crew's info on one report. Then linked this single report to the selection "All" in your list box.

Below is the code that Jack suggested you use......

Code similar to this in the After Update event of the Combo box should work:

Select Case Me.ComboboxName

Case "A"
DoCmd.OpenReport "ReportA"
Case "B"
DoCmd.OpenReport "ReportB"
Case "C"
DoCmd.OpenReport "ReportC"
Case "D"
DoCmd.OpenReport "ReportD"
Case "All"
DoCmd.OpenReport "ReportA"
DoCmd.OpenReport "ReportB"
DoCmd.OpenReport "ReportC"
DoCmd.OpenReport "ReportD"

End Select

Hope this helps,

Skip

 

[Jade]

Thank you for your help, but a case select won't work. I will explain my form a bit clearer.

it has 1 list box, Crew A - D. Then I have 10 check boxes Sick - Vacation. it has a preview button.

you pick the crew, then you pick the reports sick, vaction, Banked time ect. that you wish to view. then click on the preview button for those reports that you chose to come up.

I think the case select would be to complicated. Is there an easy way to have all the crews at once also?

Thanks,
Jen

[This message has been edited by Jade (edited 02-13-2002).]

 

[David R]

Rather than designing 4 separate but identical reports, unless your crews have different information associated with them, you can open the same report with criteria.

An example, though the SQL of the WHERE clause is sketchy:

Select Case [ComboField]
 Case "A", "B", "C", "D"
   DoCmd.OpenReport "OneReport", , , "[CrewmemberCrew] = " & [ComboField]
 Case "All"
   DoCmd.OpenReport "OneReport"
End Select

HTH,
David R

 

[Jade]

Hi David,

Each report has different data in it. For example, the sick report has all their sick time. The vacation report has all their vacation time in it. I had to do separate reports for all the options because the supervisors wanted to be able to choose what data they wanted to view.

 

[David R]

Okay, I am not clear on what the crews A-D have to do with the reports A-D then.

If you truly do have both, then I'd suggest you have one combo with the report type (Sick, Vacation, etc) and one with the Crew ID (Crew A, Crew B...All). Use the code above but draw the report name from your first combo box.

If not, then I'm afraid you're going to have to restate the question. Starting a new topic may have confused me as well, as I didn't see the one where Jack Cowley tried to help you.

David R

 

[Jade]

Hi David,

I appreciate you trying to help me. The people that are using this database work at a power plant. The people work 12 hour shifts, and there is 4 crews to A-B to cover a day. The supervisors want to be able to pick either 1 crew and veiw the reports on just that crew, or pick all the crews together to view the reports on all the crews at once.

Does this help?

Jen

 

[Jack Cowley]

Let me get back in here and really stir up the pot! Is this explanation of what you want correct?

The users can select 1 of 4 crews AND 1 or more of 12 different reports to print. You want to be able to select ALL crews as well as any combination of the 12 reports.

If that is correct how are you now selecting a single crew and printing 1 or more of the selected reports?

 

[Jade]

Hi Jack,

I have 12 different queries and reports from sick, vacation, loss time, ect. In the queries, I have a parameter that filters out the crew and views the report with that crew.

Does this help, is what I want to do possible?

Jen

 

[Jack Cowley]

In your critera you now have something like:

[Forms]![FormName]![FieldOrComboBoxName]

Directly below that criteria put this line:

[Forms]![FormName]![FieldOrComboBoxName] Is Null

Now if the user does not put in a crew (leaves the field blank) all the crews will be selected.

 

[Jade]

Thank you so much Jack, it worked.

Jen

 

[Jack Cowley]

I am glad it did the trick for you....