Linking Combo Boxes

[Glenn77]

Newbie in need of assistance, here!

I have a Subform named frmOverview subform based in a Form named frmOverview. The datasheet view of the subform is as attached:

In frmOverview, I have two combo boxes - Combo20 is used to filter the subform by Area and Combo22 is used to filter the subform by Discipline.

The problem I have is linking the combo boxes together so that, when both are utilised, the correct row(s) are shown in the subform. At the moment, if I select "North" in Combo20, only the rows containing North appear in the subform but, if I then select "Animals" in Combo22, I would like to see just the one row that contains both these values. However, I see all rows that contain "Animals" as a value, regardless of whatever Area is selected in Combo20.

My code is currently like this:

Private Sub Combo20_Change()
Me.Combo22.Requery
Me.Combo22 = ""
Me.Combo22.Visible = True
Me.[frmOverview subform].Form.FilterOn = True
Me.[frmOverview subform].Form.Filter = "Area= '" & Me.Combo20 & "'"
End Sub
Private Sub Combo22_Change()
Me.[frmOverview subform].Form.FilterOn = True
Me.[frmOverview subform].Form.Filter = "Discipline= '" & Me.Combo22 & "'"
End Sub


Could a kind person out there modify the code for me before I tear any more hair out? I really don't want to look like Karl Pilkngton.

Many thanks!

 

[DKO]

Going by your code - the user will never use Combo22 unless they've already selected a filter from Combo20...correct?

If that's the case, you could just change the "Filter" in the Combo22 event procedure to something like this:

Filter = "Discipline = '" & Me.Combo22 & "'" & "AND Area = '" & Me.Combo20 & "'"

It may be better to move the procedure to the "AfterUpdate" event instead of the "Change" event as well. Your code for the Combo20_Change event could actually trigger the Change event of Combo22 (if there was already a value selected in Combo22)...because of the line: Me.Combo22 = "" ...If there was already a value in Combo22, it would Change - triggerring the event procedure for Combo22...

And - it would be better/faster if you move the "FilterOn" command to the bottom after you've established what you want the new filter to be. The way it is now, it filters the form based on the current filter, then changes the filter and filters it again.

But, before you do all that - what do you really want to do with the filters? Is the subform based on a table or a query? Why do you requery Combo22, then set the value to null?

If you want to be able to filter by both combo boxes independantly as well as together, the solution will be quite different...

For example:

Dim StrFilter As String
 
Me.Refresh
 
If IsNull (Me.Combo20) = False and IsNull (Me.Combo22) = True Then
     StrFilter = "Area = '" & Me.Combo20 & "'"
     Me.Filter = StrFilter
     Me.FilterOn = True
ElseIf IsNull (Me.Combo20) = True and IsNull (Me.Combo22) = False Then
     StrFilter = "Discipline = '" & Me.Combo22 & "'"
     Me.Filter = StrFilter
     Me.FilterOn = True
ElseIf IsNull (Me.Combo20) = False and IsNull (Me.Combo22) = False Then
     StrFilter = "Discipline = '" & Me.Combo22 & "'" & "AND Area = '" & Me.Combo20 & "'"
     Me.Filter = StrFilter
     Me.FilterOn = True
Else
     Me.FilterOn = False
End If
 
Me.Refresh

You could put that same code on the AfterUpdate event of both combo boxes. Then you'd have no need to make Combo22 invisible...the user could choose either filter, or both together...

Or an even simpler method...

Base the subform on a query and use the following as criteria:

WHERE ((([Discipline] = Forms!frmOverview!Combo20) or (Forms!frmOverview!Combo20 Is Null)) And (([Area] = Forms!frmOverview!Combo22) OR (Forms!frmOverview!Combo22 Is Null)))

Then, on the AfterUpdate event of each combo box simply:

Me.Requery

 

[Glenn77]

I've taken on board your suggestions and my filters now appear to be running perfectly.

Thank you very much for your time and assistance, DKO. You're a superstar.

Best wishes,

Glenn77