How do I create check boxes to open a report. 2

[Quda]

I don’t know if this should go in the forms or report sections, but here I my question.

I am trying to create a form that has a list of 4 supervisors check boxes, and clicking on the boxes, and then running the report would give me a list of Employees under those selected. I was going to create a report for ever possible combination, but that turns out to be 256 reports. Is there an easier way to get this to work? Thank you.

So far my form has only 4 check boxes, and a OpenReport command button.

 

[GingerR]

why are you thinking of using check boxes?
how is your data table structured?
in the Employees table, is there a field for Supervisor with the SupervisorID or name? or do you really have the data in check boxes in the table?

will there be at time when you want to check multiple supervisors, or will it always just be one at a time?
why does it turn out to be 256 reports?

what i'm envisioning is that you have ONE report set up that is fed from a table of employees. the report gets populated with data from the table, only records meeting a certain criteria (which i'll help you with once you answer above questions). is this what you're thinking? just making sure.

let me know answers--g

 

[Quda]

I was using check boxes as a simple way of selecting mulitple options. I only have two fields in the table, Employees, and Supervisors. Both text fields. What I was wanting was requested by a user and I couldn't figure out how to do it. Make a form where a user can check which supervisors they want the report to display. One user may ask for a report of Supervisors of Don, and Steve's employees. The next user may request a report of Steve and Troy's. Then the next user may select all of them. So you can see the possible combinations with 4 Superviors is 4 to the power of 4.

 

[GingerR]

will the supervisor names ever change, i.e. will there ever be more or fewer supervisors?

how is your report written? if someone chooses Steve and Troy, are all of their employees listed on one page, all lumped together in one list? or are they separated/grouped by SupervisorName in the report?

is it feasible that if people want to see more than one supervisor's employees that they run the report separate times (once for each) or is it madatory that they choose multiple supervisors and run the report only once?

do you have a table which contains ONLY the names of the supervisors?

sorry for all the questions but just need to know.
g

 

[Quda]

The Supervisors will very rarely change, and there is only 4.
So far the report is suppose to show me all the employeess lumped together. There is no grouping needed in the report.

Mandatory to select mulitple supervisors.

And there is a seperate table for the supervisors. it is only one field, and that is the supervisors (PK). I have it linked to the employees, and since the Sup(name) is linked the names of the Sup's get inputed into the employee field.

 

[GingerR]

ok then do this: this will always accomodate any changes in the supervisor names cause it will draw from your Supervisor table instead of hard-coding their names on your form. i know this looks kind of long but don't worry it's not hard.

put a list box on your form (from the tool box). name it lstSupChoice (list Supervisor Choice).
using the wizard, base it on your Supervisor table.
when it's done with the wizard, view your form in form design; you should have a list box containing the names in your Supervisor table. Look at the design view again-in the properties of the list box, go to MultiSelect and choose EXTENDED.

put a button on the form, called btnViewSupReport.
cancel the wizard when it comes up.
right-click on the button and choose Properties from the shortcut menu.
In the OnClick event, choose [Event Procedure] and click the button to the right which has three dots on it.
in the code window that pops up, paste this in there:

Dim strWhere
strWhere = ""
Dim intI As Integer
With Me!lstSupChoice
    For intI = 0 To .ListCount - 1
        If .Selected(intI) Then
            If strWhere = "" Then
                strWhere = "Supervisor = '" & .ItemData(intI) & "'"
                    Else
                strWhere = strWhere & " or Supervisor = '" & .ItemData(intI) & "'"
            End If
        End If
    Next intI
End With
DoCmd.OpenReport "SupReport", acViewPreview, , strWhere

you have to substitute the name of your report in the last line (OpenReport).
what this is doing is this:
a user can now select one or more supervisors from the list box by using the ctrl or shift keys. use ctrl + mouse click to choose two supervisors whose names are not next to each other; choose shift+click to choose a block of supervisors. play with it and you'll see what i mean.

after selecting one or more supervisors, user clicks the button. the code loops thru the list of supervisors, picks out who has been chosen, and concocts a 'where' string, so i.e. picking Tim and Mark will result in

"Supervisor = 'Tim' or Supervisor = 'Mark'"

then you use this 'where' clause when opening the report, so that only records for these are shown.

the only other thing you may have to tweak: if the 'supervisor' field in the table/query is not called 'Supervisor' then substitute whatever it is in the code above for the word 'Supervisor'.

you can look in any book or HELP re: List box, multiSelect or ItemsSelected Property.

 

[Quda]

It worked! Thank you very much.