Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Query filtering using check boxes

    Hi there,

    I have attached a database i am working on. Created with Access 2007.

    I have two problems:-

    1) I would like the form to show all the parts (records) when initially opened or if non of the filter check boxes have been selected.

    2) Some parts are common across different machines (A to D) Take a look at tbl_Products where i select the check box to show which machines the parts relate to.
    In the form, if i click on say the Filter check box for MachineA, i would like it to display all the parts where A has been selected in the table plus any parts where A and another Machine is also selected. At the moment it will only display parts for MachineA only even though some parts have Machince A and B selected in the table, these parts will not be displayed.

    I think i need an "OR" function in the query? So what I would like to happen is if i click on the "MachineA check box filter" it displays all the parts relating to MachineA plus Machine(A&B) or (A&C) or (A&D) or (A,B &C) or (A&Z) basically any combination involving MachineA.

    Any help would be really gratefull as i've been stuck on this one for 2 days now.

    thanks in advance
    marcus
    Attached Files Attached Files
    Last edited by marcusmacman; 01-09-13 at 18:37.

  2. #2
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Help!! Do I need to explain it better? Sounds complicated but I'm sure it's not, but I'm too thick to figure it out!! Arrrrh!
    I thought I'd attach the database to help people see the problem I am having. Should I add more info about the problem or is it that you are like me (not thick) but confused?

    Cheers
    Marcus

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Remove all the conditions from qry_of_Parts which, in SQL, should become:
    Code:
    SELECT tbl_Products.Part_No
         , tbl_Products.Item
         , tbl_Products.Description
         , tbl_Products.List_Price
         , tbl_Products.Notes
         , tbl_Products.MachineA
         , tbl_Products.MachineB
         , tbl_Products.MachineC
         , tbl_Products.MachineD
      FROM tbl_Products;
    2. In the module of frm_Parts_Selection, add this function:
    Code:
    Private Function ChangeFilter()
    
        Dim strFilter As String
        Dim strName
        Dim i As Long
        
        For i = 1 To 4
            strName = Choose(i, "MachineA", "MachineB", "MachineC", "MachineD")
            If Me.Controls("Lbl_" & strName).Value = True Then
                If Len(strFilter) > 0 Then strFilter = strFilter & " OR "
                strFilter = strFilter & strName & "=True"
            End If
        Next i
        If Len(strFilter) > 0 Then
            Me.Filter = strFilter
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
        
    End Function
    3. for the 4 check boxes Lbl_MachineA, Lbl_MachineB, Lbl_MachineC, Lbl_MachineD (strange names for checkbox controls, by the way), add this as their AfterUpdate property (in the Properties window):
    Code:
    =ChangeFilter()
    4. Change the code in the Clear_all_Click procedure which becomes:
    Code:
    Private Sub Clear_all_Click()
    
    '****************************************
    ' Clears the filters when Reset button is clicked.
    Me.lbl_MachineA.Value = False
    Me.lbl_MachineB.Value = False
    Me.Lbl_MachineC.Value = False
    Me.Lbl_MachineD.Value = False
    
    '****************************************
    
    ' Me.Requery
    ChangeFilter
    
    End Sub
    Have a nice day!

  4. #4
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    OMG!! that worked without a blip!!! I'll probably spend another couple of days figuring out how it is working. I can see you are using the choose function which i've not seen before, i googled it and can see it returns a vaule based on the position value. Not sure what the rest of the code is doing but it will give something to do.

    I'd love to know what your approach, thinking was? I got stuck trying to get the query to do the work, it drove me insane!!!

    many thanks!!!!

    marcus
    Last edited by marcusmacman; 01-11-13 at 12:43.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's rather easy to understand, actually:

    1. Choose() returns a value from from list, based on the value of i:
    Code:
       For i = 1 To 4
            strName = Choose(i, "MachineA", "MachineB", "MachineC", "MachineD")
    So, when i = 1, Choose() returns "MachineA", when i = 2, Choose() returns "MachineB", etc.

    2. There are several methods to reference a control on a form. You can use:
    Code:
    FormName!ControlName
    or
    Code:
    FormName.ControlName
    Both are shortcuts that rely on default properties. However the full syntax is:
    Code:
    FormName.Controls("ControlName")
    This is because every control on a form is a member of the Controls collection. In an array, the index of an element is always numeric:
    Code:
    var = MyArray(5)
    In a collection, the index can be numeric but it can also be a string value, usually the name of the element. As the CheckBox controls you want to address are named "Lbl_MachineA", "Lbl_MachineB", "Lbl_MachineC" and "Lbl_MachineD", we add the prefix "Lbl_" to the string values returned by the Choose() function to perform a logical comparison based on its value:
    Code:
    If Me.Controls("Lbl_" & strName).Value = True Then
    3. The purpose of all this is to assemble a string that will be used to filter on the form (using its Filter and FilterOn properties). Such a filter should look like:
    Code:
    "MachineA=True OR MachineC=True OR MachineD=True"
    In the Function ChangeFilter(), this string will be stored in the String variable strFilter.
    - When code execution begins, strFilter is empty ("" or zero-length string).
    - When the first checked control is found (let's say it's "Lbl_MachineA"), we create the first conditional expression and store it into strFilter:
    Code:
    strFilter = strFilter & strName & "=True"
    strFilter now contains: "Lbl_MachineA=True".
    - When another checked control is found ("Lbl_MachineC", then "Lbl_MachineD" in this example), we cannot simply add the corresponding conditional expression to strFilter because two conditional expression must be separated whith the OR operator. However, the need to add the OR operator only arises when there already is an expression in strFilter, i.e. strFiler is not empty, i.e. its length is not zero. This is the meaning and the purpose of:
    Code:
    If Len(strFilter) > 0 Then strFilter = strFilter & " OR "
    After all 4 chechboxes have been scanned strFilter can still be empty, meaning that no control is checked, or it can contain a simple or composed conditional expression, meaning that one, several or all controls are checked. If strFilter contains such an expression, we use it to filter the form. If it is empty, we "un-filter" the form, i.e. remove any filter that can be previously applied:
    Code:
        If Len(strFilter) > 0 Then
            Me.Filter = strFilter
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
    4. Since we are dealing with a subset of the Controls collection of the form, we also can create our own collection and add the controls we want to monitor to it. This is done only once, when the form is open. From there all we need to do is to deal with the contents of this specific collection, which is more efficient than to scan the whole Controls collection of the form every time a checkbox is checked or unchecked. This is specially interesting when there are many controls on the form.

    Here's the adapted code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_colChck As Collection
    
    Private Sub Form_Open(Cancel As Integer)
    
        Const c_Eval As String = "'@' IN ('Lbl_MachineA', 'Lbl_MachineB', 'Lbl_MachineC', 'Lbl_MachineD')"
        
        Dim ctl As Control
        
        Set m_colChck = New Collection
        For Each ctl In Me.Controls
            If Eval(Replace(c_Eval, "@", ctl.Name)) Then
                ctl.AfterUpdate = "=ChangeFilter()"
                ctl.Tag = Mid(ctl.Name, 5)
                m_colChck.Add ctl
            End If
        Next ctl
            
    End Sub
    
    Private Function ChangeFilter()
    
        Dim strFilter As String
        Dim ctl As Control
        
        For Each ctl In m_colChck
            If ctl.Value = True Then
                If Len(strFilter) > 0 Then strFilter = strFilter & " OR "
                strFilter = strFilter & ctl.Tag & "=True"
            End If
        Next ctl
        Me.Filter = strFilter
        Me.FilterOn = (Len(strFilter) > 0)
        
    End Function
    
    Private Sub Clear_all_Click()
    
        Dim ctl As Control
        
        For Each ctl In m_colChck
            ctl.Value = False
        Next ctl
        Me.Filter = ""
        Me.FilterOn = False
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks Sinndho for the thorough explanation. It really helps people like me who are trying to understand access and programming, rather than been given the answer, (which is obviously great) but to go one step further with the level of detail you have provided is really appreciated
    marcus

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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