Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2013
    Posts
    8

    Unanswered: Check box Filters/Queries

    Hello,

    I'm fairly new to Access and need assistance with a project I'm working on for my employer.

    I have created a form based off a table. I would like to filter the options available to choose from in a drop down menu based on my check box selection.

    Table name: tblVCC
    Form name: frmVCC

    Check box 1: TAA
    Check box 2: QTAA

    I would like to be able to select both or either check boxes in my form that would filter the results for my form fields Vendor and LOB based on the value listed under my field "Group" (which is also in tblVCC).

    So for example, if I check the "TAA" check box, it would only list the Vendors and LOB associated with "TAA" in the Group field in my tblVCC. If I check the "QTAA" check box, it would only list the Vendors and LOBs associated with "QTAA" in the Group field in my tblVCC. If I check both "TAA" and "QTAA", it would list all the Vendors and LOBs from my tblVCC.

    After hours of scouring the Internet for answers, I can't seem to find any examples that are similar to mine. Can you provide some insights as to what I need to do? I am fairly new with building queries and coding VBA, so please use the KISS method if you could.

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not clear if you need a query or a filter. from what you are saying about your experience then a filter is probably a better bet

    what we are going to do is put two check boxes on your form, then when those checkboxes change state/value we are going to set or unset a filter that acts onthe current recordset/rowsource for the form.

    place you two checkboxes in wither the footer or header of your form, the reason for doing thios is so that there are not part of the forms data area. so its clea to users that these checkboxes do soemthign different. if you have already defined you check boxes then move them there. make certain these checkboxes do no have a column bound to them.

    next we write a function which builds the filter

    Code:
    private sub BuildFilter()
    'ok we have two states, filter on and filter off (filter off is where are two checkboxes are unchecked and will show all records. the other state is where we will apply a filter and that has 3 sub states
    'TAA itself, display rows with TAA in the group field
    'QTAA itself, display rows with QTAA in the group field
    'TAA & QTAA, display rows with TAA or QTAA in the group field
    me.filter=""
    me.filteron = false
    if taa.checked = true or qtaa.checked = true then
      if taa.checked = true then
        me.filter = "[whatever the column in yourdb that referes to this] = taa"
      endif
      if qtaa.checked = true then
        if len(me.filter)>5 then
          me.filter = me.filter & " or "
        endif
        me.filter = "[whatever the column in yourdb that referes to this] = qtaa"
      endif
      me.fiteron = true
    endif
    end sub
    then in both the checkboxes on click events call the buildflter sub routine
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    8
    Thanks so much for your response, healdem.

    I'm getting Run-time error '424' when I try selecting either of the check boxes.

    Again, I'm trying to filter the options available in a drop down combo box (which uses the Vendor field and LOB field to query contact info in the rest of the form) based on the Group field, which is broken into two sub-groups: TAA and QTAA. Some vendors are associated with the TAA group and other vendors are associated with the QTAA group.

    Here is the function I input based on your first post. Can you pinpoint where there is an error?

    Code:
    Public Sub BuildFilter()
    'ok we have two states, filter on and filter off (filter off is where are two checkboxes are unchecked and will show all records. the other state is where we will apply a filter and that has 3 sub states
    'TAA itself, display rows with TAA in the group field
    'QTAA itself, display rows with QTAA in the group field
    'TAA & QTAA, display rows with TAA or QTAA in the group field
    Me.Filter = ""
    Me.FilterOn = False
    If taa.Checked = True Or qtaa.Checked = True Then
      If taa.Checked = True Then
        Me.Filter = "[whatever the column in yourdb that referes to this] = taa"
      End If
      If qtaa.Checked = True Then
        If Len(Me.Filter) > 5 Then
          Me.Filter = Me.Filter & " or "
        End If
        Me.Filter = "[whatever the column in yourdb that referes to this] = qtaa"
      End If
      Me.fiteron = True
    End If
    End Sub
    
    Private Sub qtaa_Click()
    Call frmVCC.BuildFilter
    End Sub
    
    Private Sub taa_Click()
    Call frmVCC.BuildFilter
    End Sub
    Please let me know if I can make this easier by attaching my database.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the LOBfield?
    what line is the error being reported on

    dusting down my creystal ball its probably
    Code:
    If taa.Checked = True Or qtaa.Checked = True Then
    or
    Code:
    Me.Filter = "[whatever the column in yourdb that referes to this] = taa"
    which incidentally probably shoudl be
    Code:
    Me.Filter = "[whatever the column in yourdb that referes to this] = 'taa'"
    im guessing "[whatever the column in yourdb that referes to this] shoudl refer to your group column
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2013
    Posts
    8
    Quote Originally Posted by healdem View Post
    the LOBfield?
    what line is the error being reported on

    dusting down my creystal ball its probably
    Code:
    If taa.Checked = True Or qtaa.Checked = True Then
    or
    Code:
    Me.Filter = "[whatever the column in yourdb that referes to this] = taa"
    which incidentally probably shoudl be
    Code:
    Me.Filter = "[whatever the column in yourdb that referes to this] = 'taa'"
    im guessing "[whatever the column in yourdb that referes to this] shoudl refer to your group column
    D'oh!

    Well I added the correct column name. Though it didn't seem to correct my error yet.

    The errors I'm getting seem to be associated with the custom call subs I added myself:

    Code:
    Private Sub qtaa_Click()
    Call frmVCC.BuildFilter
    End Sub
    
    Private Sub taa_Click()
    Call frmVCC.BuildFilter
    End Sub
    Did I do those right? Sorry for so many questions, I'm still very green in terms of VBA coding and database design in general.

  6. #6
    Join Date
    Oct 2013
    Posts
    8

    Sample DB attached

    I've attached a sample of my database that I'm working on. Hopefully this will help clear up what I'm trying to do.
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2013
    Posts
    8
    Anybody have any insights on how to correct this? I provided my database in the post above for your review. I'm trying to get the check boxes in the form to filter the results in the drop down menu based on the two options under the Group column in tblVCC.
    Last edited by tekkie; 10-31-13 at 13:56.

  8. #8
    Join Date
    Oct 2013
    Posts
    8
    Hello all, I'm still trying to get this filter issue resolved. I've attached my database above so that you can see what I'm working with. Please let me know if you are able to assist.

  9. #9
    Join Date
    Oct 2013
    Posts
    8
    Quote Originally Posted by healdem View Post
    the LOBfield?
    what line is the error being reported on

    dusting down my creystal ball its probably
    Code:
    If taa.Checked = True Or qtaa.Checked = True Then
    I believe this is the line that is causing the issue. Since I'm very new to VBA, I'm not sure what is wrong with the syntax. Can someone advise what might be wrong with this code?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If taa and qtaa are checkbox controls, the correct syntax is:
    Code:
    If taa.Value = True then
    and:
    Code:
    If qtaa.Value = True then
    This type of control (nor any that I know, for that matter) does not have a "Checked" property.
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    8
    Quote Originally Posted by Sinndho View Post
    If taa and qtaa are checkbox controls, the correct syntax is:
    Code:
    If taa.Value = True then
    and:
    Code:
    If qtaa.Value = True then
    This type of control (nor any that I know, for that matter) does not have a "Checked" property.
    Thank you so much, Sinndho. That appeared to resolve that issue.

    Next issue.

    On the attached Access db, is there a way to use these filters to filter out the options in the drop down menu I have in the form?

    For example, if I choose the TAA filter check box, I want my results in the drop down combo box to only reflect those vendors listed with the Group name "TAA" from tblVCC.
    Attached Files Attached Files

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    Private Sub qtaa_AfterUpdate()
    
        ChangeComboSource
    
    End Sub
    
    Private Sub taa_AfterUpdate()
    
        ChangeComboSource
        
    End Sub
    
    Sub ChangeComboSource()
    
        Const c_SQL As String = "SELECT Record, Vendor, LOB, OrderSubmission, OrderStatus, PrimaryFirst, PrimaryLast " & _
                                "FROM tblVCC"
        Dim strSQL As String
        
        If Me.taa.Value = True Then strSQL = "[Group] = 'TAA'"
        If Me.qtaa.Value = True Then
            If Len(strSQL) > 0 Then
                strSQL = strSQL & " OR [Group] = 'QTAA'"
            Else
                strSQL = "[Group] = 'QTAA'"
            End If
        End If
        If Len(strSQL) > 0 Then strSQL = " WHERE " & strSQL
        Me.Combo65.RowSource = c_SQL & strSQL & " ORDER BY Vendor, LOB;"
        Debug.Print Me.Combo65.RowSource
        
    End Sub
    Notes:
    - When specifying a criteria for a Text column, you must enclose the value in single or double quotes. Your BuildFilter() procedure does not work because of that.
    - The class module of the form has references to an object named cboVendor, while this object does not exist. You cannot compile the code because of that (the name of the combo is Combo65).
    - Group is a reserved word in SQL. You must enclose it in square brackets when using it in a SQL expression where it represents a column name.
    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
  •