Adding an All option to my combo box that filters a report

[isaacski]

Hi Guys,
I have uploaded my database here. I have everything set up exactly how I want it except ONE thing. There is a report card report that is opened by a report card filter form. The report is filtered by a query. I want the user to be able to filter the report card by what's already on the filter form; class name, and start date. I want to also be able to add an Employee Name combo box on the form but I want it to have an all option. I want to see everyone in the class or just one person at a time on the report. I cannot for the life of me figure out how to do this in Access 2010.

Please help?



ps. thanks to a lot of you who have helped me build this guy

 

[boblarson]

I didn't see that you wanted it on the report itself (which I don't think will work anyway) but I added it to your form and I changed how it works. The report is more generic - not using the criteria in the report's query but uses a where condition when opening the report. So the click on the button now has VBA which hopefully you will see what I did.

 

[isaacski]

Thank you very much for your reply and code!!!! Putting it on the form was actually prefered for me as long as there is an all option. Although very very new at this I usually pick up on the logic behind the code pretty quickly... this one is definately going to take some researching

There are two issues I'm seeing with this setup. The person combo box was not set up to cascade from the date and class name. So I added another "class" and the cascade works except it doesn't display All in that list. It now only displays "All" if a date is not chosen... The date is basically what my trainers use to differentiate classes and who is in what class so it is crucial that they not see a list of total students but only those that belong to that start date. Also, the All needs to refer to all in that class (so all students that match the class name and start date), not All students in the database. I've looked at your code to see if I might be able to understand how to manipulate it from here but I don't think I understand it enought to fuss with it. Any ideas?

 

[boblarson]

It now only displays "All" if a date is not chosen...

That's because you didn't do like I did in the original and use a Union for adding it. So what you currently have here in the after update of the date combo (and it would apply for the class name one as well) is:

Private Sub cboClassStartDate_AfterUpdate()
    On Error Resume Next
    cboPerson.RowSource = "SELECT DISTINCT tblAll.NewEmployee " & _
        "FROM tblALL " & _
        "WHERE tblAll.ClassStartDate = #" & cboClassStartDate.Value & "#" & _
        "ORDER BY tblAll.NewEmployee;"
End Sub

But it SHOULD be:

Private Sub cboClassStartDate_AfterUpdate()
    On Error Resume Next
    cboPerson.RowSource = "SELECT DISTINCT tblAll.NewEmployee [B][COLOR="red"]As Person "[/COLOR][/B] & _
        "FROM tblALL " & _
        "WHERE tblAll.ClassStartDate = #" & cboClassStartDate.Value & "# " & _
[B][COLOR="Red"]        "UNION Select ' ALL' As Person FROM tblAll " & _[/COLOR][/B]
        "ORDER BY tblAll.NewEmployee;"
End Sub

And notice the extra space I put after the # at the end of this line:
"WHERE tblAll.ClassStartDate = #" & cboClassStartDate.Value & "# " & _