Cascading combo boxes (multiple filters)

razovsuki

Registered User.
Local time
Today, 22:42
Joined
Feb 17, 2015
Messages
17
Hi,

I'm having some issues using the cascading combo box technique on my form.
I have a form, which contains a subform in continuous view, which contains a few combo boxes.
One of those combo boxes (available resources) should be filtered depending on the value of 3 other combo boxes (task types, source languages, target languages).

What I would like to be able to do is run the filtering routine on this resources cbo (currently VBA code that changes the row source value) when the user clicks on it.
It's kind of working right now: when I click on the arrow to open up the drop down list, the values are indeed filtered. The problem I'm having though is that, if I then click on that same cbo for another record (or any other cbo in another record for that matter), the resource cbo of the record I previously set gets deleted.

Any idea why that would happen?
Please let me know if I missed any crucial detail.

Many thanks in advance!
 
the rowsource for a combo is a bit like an unbound control - it will be the same for each record

so say in record 1 your rowsource is something like

SELECT ProductID, ProductName From tblProducts WHERE ProductType=1

and you make a selection

then you move to another record and your rowsource changes to

SELECT ProductID, ProductName From tblProducts WHERE ProductType=2

Now the first record has a selection that cannot be displayed - so it appears blank, although the value selected is stored in the record.

To solve this, you need to move your code which creates the rowsource string from wherever it is to the combo enter event, or try gotfocus or mousedown- it depends on how your form is working as to which is right for you

Then in the combo exit event (or lostfocus or mouseup) based on the above example, you would set the combo rowsource to

SELECT ProductID, ProductName From tblProducts
 
Thanks CJ_London and spikepl.
I quickly tried your solution CJ_London, but it doesn't seem to work at first. I'll have a closer look and try different implementations (GotFocus, MouseDown, etc) when I have more time and will let you know.

spikepl, I've actually heard about this trick, but originally thought it was too complicated for the seemingly easy thing I meant to do. Looks like it's not so easy, and that the solution is not so complicated either :)
Also, I must say I got a bit lost in the explanation. I'm still quite new to Access after all.

Anyway, I'll give both solutions a go and report back when it's working!
Cheers.
 
My method works for datasheet
biggrin.gif

I'm like Thomas the Unbeliever - gotta see it /feel it. I'll have a go and check it out (in a few days).
 
small confession - it only works in certain situations:o

The dropdown works fine and it's fine if you are simply entering data or if you are displaying the bound column, but if you are hiding the bound column and go up and down clicking on the column it 'hides' the value for the record just exited.

I thought I had a working copy somewhere, but couldn't find it and tried to recreate it but there seems to be a conflict between the exit event of the control for the record just left and the enter event for the same control in another record.

I'll have another go with a glass of wine later!
 
small confession - it only works in certain situations

That's a "relief" :D

I thought I had missed a trick. For datasheets I use an alternative approach - for all records I display all the options in the dependent combo (that way nothing disappears) but only allow selection of combo rows valid for that particular record. Not super elegant but it works and is comprehensible for users. I show two columns in the combo, the second column clearly indicating whether the given item is or is not allowed for the particular record.
 
Found the problem, needed to let the requery finish before filtering the rowsource.

The attached was developed in 2010 and saved as 2003 - hope it still works.

The form with the tricks is a subform of the form that opens - one subform in continuous view and one in datasheet view. I don't get any prizes for presentation, but it demonstrates the technique for a single column rowsource (i.e. bound column is displayed) and one where the displayed value is a different column to the bound column in both continuous and datasheet view

@Spike
I thought I had missed a trick.
Maybe you have - tell me what you think
 

Attachments

Looks good, but how do you ensure data consistency? I.e. if you change a combobox upstream, that may render the downstream choices in the record invalid, yet they remain displayed. I've been fiddling with this looking into After Update and requrying downstream combos, but that upsets the display completely.
 
good point - they need to be rest, I'll take a look
 
I like it, but will of course try to you-know-what ... :D

Seems you have found the Holy Grail Of Cascading Combos :D
 
@Spike - Do you think it is worth tidying it up and sticking in the code repository?
 
This seems to be working for me, thanks again CJ_London!
I just added a quick check in the code, before putting the value of the cbo to nothing (0) in case there's any change upstream. I just added a re-filtering in the upstream cbo AfterUpdate event, and checks if the currently selected value is part of the new filtered list, leaves it as is if it is, or sets it to 0 if it's not.
 
@razovsuki - I'm going to post the db to the code repository for others to use, but thought it would be work adding the additional check you did - to save me some time, any chance you could post your amendment to the afterupdate event?

Thanks
 
Sure, here you go:
(nb: I'm not the best programmer there is, but it works :))


Code:
Private Sub cb_taskType_AfterUpdate()
Dim resource As Long
resource = cb_resource

filterResources (True) [COLOR="green"]'run the filtering routine again[/COLOR]

Dim i As Integer
For i = 0 To cb_resource.ListCount - 1 [COLOR="Green"]'Loop through the values of the newly filtered combo box[/COLOR]
    If cb_resource.Column(0, i) = resource Then [COLOR="green"]'and check if the value before filtering is part of the new, filtered list[/COLOR]
        cb_resource = resource [COLOR="green"]'set it to this value if yes, and exit the subroutine[/COLOR]
        Exit Sub
    End If
Next i

cb_resource = 0 [COLOR="green"]'put it to 0 if not[/COLOR]

End Sub
 
Great thanks - I'll modify the db in the code repository when I get a chance - I'll credit you with the improvement
 

Users who are viewing this thread

Back
Top Bottom