Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Subform filtered by 3 combo boxes ?

    I shouldn't browse posts here, it gives me ideas that I then have to ask for help about.

    Is it possible to filter down a subform or list by having up to 3 values selectable via combo boxes?

    My purchase order records have supplier, customer and jobnumber fields. My idea is to create combo boxes for each of these that will progressively filter the records in the subform/list so that I can end up with:
    eg all the orders on supplier X for customer Y
    all the orders on supplier X for job Y
    all the orders on supplier X for job Y for customer Z

    As each combobox is used it would disable to lock that filter and all three boxes would be enabled and the subform/list returned to full listing with a reset button.

    As I type this, cloning recordsets comes to mind, which I was pointed to once before. Am I on the right track?
    How would I do it?

    Thanks in advance!
    "Heading for the deep end"

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Try using the AfterUpdate event on the combo boxes to reset the filter value of the sub form. Below is code is use for my coin collection that filters on Country and then on Type, e.g. Coin, Bill, Set, etc. Works like a charm should do what you want. Actually, what I did is include the Country and Type combos in the form header then I didn't even need a sub form.
    Code:
    Private Sub ddCountry_AfterUpdate()
    
    '*** ReQuery to establish filtered detail
       Me.Filter = "{Code} = '" & Me![ddCountry] & "'"
       Me.FilterOn = True
       
    End Sub
    
    Private Sub ddTypes_AfterUpdate()
    
    '*** ReQuery to establish filtered detail
         Me.Filter = "{Code} = '" & Me![ddCountry] & "'" & _
                     "and [Type] = '" & Me![ddTypes] & "'"
         Me.FilterOn = True
         
    End Sub
    Note: curly braces around the Code value replacements for square brackets because Code with square brackets is used to create the code box in the post. You need to change them to square brackets like the ddCountry identifier.
    Last edited by RetiredGeek; 2011-05-25 at 18:31.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thanks for that, but I notice that the order that the combo boxes are used has to be concreted in place. I could do it that way but I was hoping for a more dynamic sort of filtering where it wouldn't matter as to which order the combos were selected in.
    "Heading for the deep end"

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by weyrman View Post
    Thanks for that, but I notice that the order that the combo boxes are used has to be concreted in place. I could do it that way but I was hoping for a more dynamic sort of filtering where it wouldn't matter as to which order the combos were selected in.
    Not really, it just requires more logic in the event handlers to test which combo boxes have been set and which have not.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    My approach to this is to have a procedure that builds the filter by checking the values of all the combos, and the after update event for each of them just calls this procedure.

    Because this procedure needs to work whether 1 or 3 or perhaps 10 combo boxes have data in them, it consists of a series of statements like:

    Code:
    if not isnull(me!comboCountry) then 
    strfilter = strfilter &  " ([Country] = " & chr(34)  & Me![comboCountry] & chr(34) & ") and"
    end if
    You see above I like to bracket each clause.

    So each line that executes adds an " and" to the end. This always results in an extra " and" at the end so it needs to be removed.

    Code:
    If Len(strfilter) > 4 then
       strfilter= Left(strtfilter, Len(strfilter)-4)
    end if
    Regards
    John



Posting Permissions

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