Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25

    Multiple combo boxes for aggregated filtering

    Hi everyone,



    I wanted to make combo boxes, 5 of them, that when I make a record selection in any of the combo boxes, it locks to that selection and filters the data accordingly. So for example, if I have a combo box named "City:, and I select "Portland", then all of the Portland data shows, then in my next combo box called "Names" when I select the record "Peter Eldridge", it will show all the data for Peter Eldridge, in Portland. I have a few more combo boxes like this, including one for dates. Any help or advice? not sure where to start this other than creating the combo boxes and date textboxes.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    youd have to build the filter dynamically ‎via vb code:
    if 1 combo affects another then the next combo must be refreshed
    Code:
    sub cboCity_afterupdate()
       cboNames.requery
    end sub
    You cant use form boxes in a query if there's nothing in them..so..
    Test all controls for a possible filter then build the where clause.

    Code:
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    
    if IsNull(cboState) then
      me.filterOn = false
    else
           'remove 1st And
        sWhere= mid(sWhere,4)
    
         'just use the filter
    
      me.filter = sWhere
      me.filterOn = true
    endif

  3. #3
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by Mohibullah View Post
    Hi everyone,

    I wanted to make combo boxes, 5 of them, that when I make a record selection in any of the combo boxes, it locks to that selection and filters the data accordingly. So for example, if I have a combo box named "City:, and I select "Portland", then all of the Portland data shows, then in my next combo box called "Names" when I select the record "Peter Eldridge", it will show all the data for Peter Eldridge, in Portland. I have a few more combo boxes like this, including one for dates. Any help or advice? not sure where to start this other than creating the combo boxes and date textboxes.
    First off, thank you so much for getting back to me, and so quickly. So I tried this, but it's not "locking" to a selected value. So for example, if I select "Portland" from the combo box "City names" which is taken from the "City" table, it still just goes to the next record in order, even though I selected "Portland" in my combo box. For example: I select Portland from my combo box, and 1 record displays of Portland, but when I hit the arrow to the next record, it shows "Seattle". Hope I'm making sense. I need it so that whatever I select in my combo box is locked in, until I make another combo box selection.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Remove the value in the Control Source in the combo boxes to make them unbound.

  5. #5
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by Bulzie View Post
    Remove the value in the Control Source in the combo boxes to make them unbound.
    Hi Bulzie, thanks for the tip, but it's totally unlinked now.

    I used this method =

    sub cboCity_afterupdate()
    cboNames.requery
    end sub

    but shouldn't there be something connecting the combo boxes to the form..?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If you followed Ranman's advice then the combobox's are connected to the form through the filter. The filter will stay there until it is changed, so all your records will be filtered.

  7. #7
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by Mohibullah View Post
    Hi Bulzie, thanks for the tip, but it's totally unlinked now.

    I used this method =

    sub cboCity_afterupdate()
    cboNames.requery
    end sub

    but shouldn't there be something connecting the combo boxes to the form..?
    Hi,

    So here's where I'm at; I'm using two combo boxes to start with, a combo box to filter by line name, and another to filter by Project Type, however I keep getting error '2766" "The object doesn't contain the Automation object 'BaseDataToUsed.". "BaseDataToUsed" is my main table.

    Here's my code:
    Option Compare Database
    Private Sub cboLineName_AfterUpdate()
    Dim myLine As String
    myLine = "Select * from Line_Name where ([LineName] = " & Me.cboLineName & ")"
    Me.SubformQuery_subform.Form.RecordSource = myLine
    Me.SubformQuery_subform.Form.Requery
    Me.ProjectTypeCbo = Null
    End Sub

    Private Sub Form_AfterUpdate()
    End Sub
    Private Sub ProjectTypeCbo_AfterUpdate()
    Dim myProject As String
    myProject = "Select * from Project_Type where ([ProjectType] = " & Me.ProjectTypeCbo & ")"
    Me.SubformQuery_subform.Form.RecordSource = myProject
    Me.SubformQuery_subform.Form.Requery
    Me.cboLineName = Null
    End Sub

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Where's the code from post #2?

  9. #9
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by aytee111 View Post
    Where's the code from post #2?
    I wasn't exactly understanding it. The first part I got, but I got a but lost at the use of textboxes and checkboxes.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Go back to the beginning, then, don't try anything complicated until you understand what it going on.

    1 - make a new form with all the fields (use the wizard, this is purely a learning exercise)
    2 - add a combobox for city
    3 - add a combobox for person_name

    In the AfterUpdate event of cboCity:
    Code:
    Call SetFilter
    In the AfterUpdate event of cboName:
    Code:
    Call SetFilter
    Add this subroutine:
    Code:
    Private Function SetFilter()
    
    Dim strFilter as String
    
    If Not IsNull(cboCity) Then  strFilter = "[City]='" & Me!cboCity & "'"
    If Not IsNull(cboName) Then
    If IsNull(strFilter) Then
    strFilter="Person_Name='" & Me!cboName & "'"
    Else
    strfilter=strfilter & " AND Person_Name='" & Me!cboName & "'"
    End If
    
    If IsNull(strFilter) then
      Me.FilterOn = false
    Else
      Me.Filter = strFilter
      Me.FilterOn = true
    End If
    Me.Requery

  11. #11
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Edit: Sorry Aytyee111, didn't know you were providing more code.

    The dynamic Where code as aytee111 listed is best if you have multiple search fields. You put it in the After Update of each combo box or you can make it a function and call that function it in the AfterUpdate of each combo box:

    sWhere = null
    if not isnull(cboState) then sWhere = sWhere & " and [LineName]='" & me.cboLineName & "'"
    if not IsNull(txtName) then sWhere = sWhere & " and [ProjectType]='" & Me.ProjectTypeCbo & "'"

    So lets say at this point you selected "AA" for LineName and "BB" for ProjectType in your combo boxes, the sWhere line would read:
    sWhere =" and [LineName]='AA' and [ProjectType]='BB'"

    if IsNull(sWhere) then
    me.filterOn = false
    else
    'remove 1st And
    sWhere= mid(sWhere,4) 'this line gets rid of the beginning " and" in the where clause. So now it reads
    sWhere =" [LineName]='AA' and [ProjectType]='BB'"
    'just use the filter
    me.filter = sWhere
    me.filterOn = true
    end if

    So the above only uses 2 search fields(combo boxes) but you see the benefit in that you can add as many search lines as you want and the rest of the code builds the where clause as needed. If you had 5 search combo boxes and they select combobox 2 and combobox 5 and leave others blank, then this code will create a where clause for only those 2 fields to search on.

  12. #12
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by aytee111 View Post
    Go back to the beginning, then, don't try anything complicated until you understand what it going on.

    1 - make a new form with all the fields (use the wizard, this is purely a learning exercise)
    2 - add a combobox for city
    3 - add a combobox for person_name

    In the AfterUpdate event of cboCity:
    Code:
    Call SetFilter
    In the AfterUpdate event of cboName:
    Code:
    Call SetFilter
    Add this subroutine:
    Code:
    Private Function SetFilter()
    
    Dim strFilter as String
    
    If Not IsNull(cboCity) Then  strFilter = "[City]='" & Me!cboCity & "'"
    If Not IsNull(cboName) Then
    If IsNull(strFilter) Then
    strFilter="Person_Name='" & Me!cboName & "'"
    Else
    strfilter=strfilter & " AND Person_Name='" & Me!cboName & "'"
    End If
    
    If IsNull(strFilter) then
      Me.FilterOn = false
    Else
      Me.Filter = strFilter
      Me.FilterOn = true
    End If
    Me.Requery
    Thanks. I did exactly what you said, but at the end I'm getting an error with the Me.Requery being after the End If.

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What error????????

  14. #14
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oh wait, there is an End If missing!

    Code:
    If Not IsNull(cboName) Then
    If IsNull(strFilter) Then
    strFilter="Person_Name='" & Me!cboName & "'"
    Else
    strfilter=strfilter & " AND Person_Name='" & Me!cboName & "'"
    End If
    End If

  15. #15
    Mohibullah is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    Quote Originally Posted by Bulzie View Post
    Edit: Sorry Aytyee111, didn't know you were providing more code.

    The dynamic Where code as aytee111 listed is best if you have multiple search fields. You put it in the After Update of each combo box or you can make it a function and call that function it in the AfterUpdate of each combo box:

    sWhere = null
    if not isnull(cboState) then sWhere = sWhere & " and [LineName]='" & me.cboLineName & "'"
    if not IsNull(txtName) then sWhere = sWhere & " and [ProjectType]='" & Me.ProjectTypeCbo & "'"

    So lets say at this point you selected "AA" for LineName and "BB" for ProjectType in your combo boxes, the sWhere line would read:
    sWhere =" and [LineName]='AA' and [ProjectType]='BB'"

    if IsNull(sWhere) then
    me.filterOn = false
    else
    'remove 1st And
    sWhere= mid(sWhere,4) 'this line gets rid of the beginning " and" in the where clause. So now it reads
    sWhere =" [LineName]='AA' and [ProjectType]='BB'"
    'just use the filter
    me.filter = sWhere
    me.filterOn = true
    end if

    So the above only uses 2 search fields(combo boxes) but you see the benefit in that you can add as many search lines as you want and the rest of the code builds the where clause as needed. If you had 5 search combo boxes and they select combobox 2 and combobox 5 and leave others blank, then this code will create a where clause for only those 2 fields to search on.

    What's confusing about this, is I'm not sure where "cboState" comes in, as well as the "txtname" textbox. Basically, I have a split form(the top half will have the combo boxes and buttons, the bottom has a raw data sheet view of all the data), and want the data sheet bottom half to pull up records when I select whatever entries from the combo boxes. Also, I think what may be complicating this, is my relational database. Should I use the primary keys of any of my tables in any of this code?

    I have 4 drop downs I need to use to "aggregate filter"; City, Project Type, Engineer, Region.

    the secondary table for city is tblCity, the field that relates to the main table from this is key CityID, and the actual city names field in this is CityName

    the secondary table Project Type is tblProjectType, the key field that relates to the main table from this is ProjectTypeID, and the actual project types field in this is ProjectType

    and the secondary table for Region is tblRegion, the key field that relates to the main table from this is RegionID, and the actual Region names field in this is Region

    the secondary table for engineer data is tblEngineer, the key field that relates to the main table from this is EngineerID, and the actual Engineer names field in this is EngineerNames. However, this goes through the tblLeadEngineer table to get to the main table. The tblLeadEngineer has a connection to the tblEngineer with WorkerID, and the tblLeadEngineer has another field called JobLogID that then goes to the JobLogID in the main table.

    The main table that has the primary and bulk data(dates, comments, descriptions, etc.,), and the primary keys that relate to these tables, is tblBaseDataToUsed.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-21-2016, 09:53 AM
  2. Filtering Using Combo Boxes
    By Beanie_d83 in forum Access
    Replies: 4
    Last Post: 05-19-2016, 06:34 AM
  3. Combo Boxes Filtering
    By gatsby in forum Forms
    Replies: 1
    Last Post: 07-22-2014, 12:46 AM
  4. Replies: 5
    Last Post: 07-26-2012, 02:30 PM
  5. Filtering options using Cascading Combo boxes
    By Lincoln in forum Database Design
    Replies: 3
    Last Post: 07-15-2011, 08:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums