Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Unanswered: Filtering for "All" in dependent cascaded combo box

    Hi,

    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:

    --------------------------------------------
    cboName.RowSource = "Select tblEmployeeSupervisor.Employee " & _
    "FROM tblEmployeeSupervisor " & _
    "WHERE tblEmployeeSupervisor.Supervisor = '" & cboSupervisor.Value & "' " & _
    "ORDER BY tblEmployeeSupervisor.Employee;"
    ---------------------------------------------

    The above code is in cboSupervisor_AfterUpdate().

    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 & "';") & "'"
    End If

    Else
    strFilter = strFilter & " AND [Responsible]='" & cboName & "'"
    End If

    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?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the answer is pretty simple...

    when "All" is selected, don't include that condition in the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if cboName is "All" then do not include this in your WHERE clause --

    ''AND [Responsible]='" & cboName & "'"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Moreover strFilter = "1=1" is useless.

    Have a nice day!

  6. #6
    Join Date
    Aug 2009
    Posts
    7
    That portion is under the Else statement, which perhaps more clearly written would be If cboName<>"All" Then...

    Sorry about the poor indentation when I copied and pasted. I do have a new idea though, I will post result if i succeed in case anybody has the same desired functionality in the future.

    Thanks for the quick responses and help.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sinndho
    Moreover strFilter = "1=1" is useless.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If I ever stop outright cheating and using LINQ instead, I'll be tucking that one away...

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Aug 2009
    Posts
    7
    Here is my solution for anybody wishing to achieve the same or a similar setup:

    -----------------------------------------------
    Private Sub cboName_AfterUpdate()

    Dim strFilter As String

    strFilter = "1=1"

    If cboName = "All" Then
    If cboSupervisor <> "All" Then
    strFilter = strFilter & " AND [Responsible] In (" & "Select tblEmployeeSupervisor.Employee " & _
    "FROM tblEmployeeSupervisor " & _
    "WHERE tblEmployeeSupervisor.Supervisor = '" & cboSupervisor.Value & "';" & ")"
    End If

    Else
    strFilter = strFilter & " AND [Responsible]='" & cboName & "'"
    End If

    Me.Filter = strFilter
    Me.FilterOn = True

    End Sub

    --------------------------------------------

    The syntax problem came from having

    [Responsible]=(SQL statement)
    instead of
    [Responsible] In (SQL statement).

    I failed to mention previously that the "Responsible" field is from the table tblDetailedActionsList (and as a result a field in the form as well), and is what cboName uses to filter the form.

    I hope I haven't left anything out this time and this solution is clear.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •