Subform filtered by 3 combo boxes ?

Topic

New Reply

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! 🙂

Reply | Quote

Replies

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.:cheers:

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.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote