Unanswered: Filtering for "All" in dependent cascaded combo box
I am using Access 2000 to build a database that tracks the progress of projects. I have a form whose records include information on tasks to be completed. In the header I have cascaded combo boxes cboSupervisor and cboName, which is populated based on the cboSupervisor selection as follows:
The form pulls its information from a table tblDetailedActionsList. Another table tblEmployeeSupervisor stores who each employee’s supervisor is, in which I have included “All” so that when cboSupervisor = “All” and cboName = “All” the form includes all records. Within each supervisor I have also included “All” so that a supervisor can select their name in the first combo box and then choose to see individual employees or all their employees based on selection in second combo box, cboName.
The problem: I do not know how to implement the “All” selection within a given supervisor. My attempt was this:
Dim strFilter As String
strFilter = "1=1"
If cboName = "All" Then
If cboSupervisor <> "All" Then
strFilter = strFilter & " AND [Responsible]='" & ("Select tblEmployeeSupervisor.Employee " & _
"FROM tblEmployeeSupervisor " & _
"WHERE tblEmployeeSupervisor.Supervisor = '" & cboSupervisor.Value & "';") & "'"
strFilter = strFilter & " AND [Responsible]='" & cboName & "'"
Me.Filter = strFilter
Me.FilterOn = True
This returns a syntax error (missing operator) prompt after a supervisor has been selected in cboSupervisor and "all" has been selected in cboName. Any suggestions on how to solve or ideas for alternate solutions?
When "All" is selected in cboSupervisor it works fine. The problematic condition is when cboSupervisor = "John Smith" and cboName = "All". Meaning John Smith wants to see all of HIS employees, not all employees in the database. Am I interpreting your response correctly?
on the contrary, the efficacy of this device is unparalleled in constructing this type of dynamic WHERE clause
what it does for you, if you are not familiar with it, is that it allows you to dispense with tons of unnecessary coding for each parameter to decide whether it is the first condition or a subsequent one
in effect, you no longer have to decide which condition gets the WHERE keyword and which ones get the AND keywords
they simply all get the AND keyword
as i say, you have to be familiar with this coding device in order to see how useful it is