Split form with Combo boxes

[carmenv323]

I am using a split form using a query for its datasource (I have to re-arrange the fields so pardon the messiness on that)



I have three combo boxes and they should filter with or without each other, meaning any of the filters can be selected on their own or with the others (cascading)

I have the following code and an image of what I'm doing: The thing is that I can't get any of the boxes to filter the data. at one point the first item was filtering but now none of them are. When I step through the code it and highlight over the values, the values in the combo box that I selected are being captured but the filter isn't happening.

Option Compare Database
Private m_Where As String
 
Sub FilterThis()
If left(m_Where, 4) = " and" Then
m_Where = Mid(m_Where, 5)
End If
Me.Filter = m_Where
'Me.FilterOn = True
 
End Sub

SELECT tbl_ReqOrg.RequestingOrganization
FROM tbl_ReqOrg INNER JOIN Query4 ON tbl_ReqOrg.[RequestingOrganization] = Query4.cboRequestByOrganization
GROUP BY tbl_ReqOrg.RequestingOrganization;

SELECT tbl_RequestorID, tbl_Requestors.txtFirstName, tbl_Requestors.txtLastName, tbl_Requestors.txtResolvedName
FROM tbl_Requestors RIGHT JOIN Query4 ON tbl_Requestors.RequestorID = Query4.RRequestorID
GROUP BY tbl_Requestors.RequestorID, tbl_Requestors.txtFirstName, tbl_Requestors.txtLastName, tbl_Requestors.txtResolvedName
HAVING (((tbl_Requestors.txtResolvedName)<>""));

Private Sub cboOrg_AfterUpdate()
Me.FilterOn = False
'm_Where = ""
Me.cboOrg = ""
'Me.cboReq = ""
m_Where = m_Where & " and [cboRequestbyorganization] = '" & Me.cboOrg & "'"
  FilterThis
End Sub

Private Sub cboSoL_AfterUpdate()
Me.FilterOn = False
'm_Where = ""
'Me.cboOrg = ""
Me.cboReq = ""
m_Where = m_Where & " and [StationOrLine] = '" & Me.cboSoL & "'"
End Sub

Private Sub cboReq_AfterUpdate()
Me.FilterOn = False
'm_Where = ""
'Me.cboOrg = ""
'Me.cboReq = ""
m_Where = m_Where & " and [RRequestorID] = '" & Me.cboReq & "'"
FilterThis
End Sub

[Welshgasman]

You are setting the filter, but not switching it on?
You also appear to be clearing cboOrg and then using it? Bit hard to see the wood for the trees with all those commented out

[carmenv323]

You are setting the filter, but not switching it on?
You also appear to be clearing cboOrg and then using it? Bit hard to see the wood for the trees with all those commented out

I see your point and updated it like so:

Pri

vate Sub cboOrg_AfterUpdate()
Me.FilterOn = False
Me.Requery
M_Where = ""
Me.cboSoL = ""
Me.cboReq = ""
 m_Where = m_Where & " and [cboRequestbyorg] = '" & Me.cboOrg & "'"
FilterThis
Me.FilterOn = True
End Sub

I did this for each one. Would I now add If/ElseIf to get all the other different combinations?

[Welshgasman]

Why not set filter on in just one place, filterthis?
Also I am not sure why you are clearing the other combos in each combo? Remember you can select these combos in any order.
Might be better to just refer to the combos in the query?