Cascading combo box with All to query (1 Viewer)

[KathCobb]

I really need help with this...Ive been struggling forever and just need to complete this....

I am attempting to make a Form that sets criteria for a query that when it all works correctly well eventually export to Excel. I am mostly getting stuck trying to Add "All" to my dropdown choices. I have googled for days on this and all it does is confuse me. Here is what I have so far for selection on the form:

cboAgent: unbound Drop down box to select an Agent or All Agents.
Row Source is qryAgentUnionAll (SELECT AgentLastName & ", " & AgentFirstName,pkAgentID FROM tblAgentDetail UNION SELECT '(All)', null FROM tblAgentDetail
Bound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works. When I go to my qryListExport, under fkAgentID from TablePolicySales I have this criteria: [Forms]![frmSelectToExport]![cboAgent] Or [Forms]![frmSelectToExport]![cboAgent] Is Null
When I select All in the combo box, the word all does not stay there but the query runs and All agents show up.


cboCounty: unbound drop down box to Select specific County or All Counties
RowSource is qryCountyUnionAll (SELECT CountyName, pkCountyID FROM tblCounty UNION SELECT '(All)' , null FROM tblCounty
Bound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works. When I go to my qryListExport, under fkCountyID from TableAddresses I have this criteria: [Forms]![frmSelectToExport]![cboCounty] Or [Forms]![frmSelectToExport]![cboCounty] Is Null
When I select All in the combo box, the word all does not stay there but the query runs and All counties show up.

cboCompany--> this is where the problem starts. I need this box to do two things. It needs to select company or All companies, then based on that selection, limit the choices in the next( and last) combo box.
RowSource: qryUnionCompanyAll (SELECT Company, pkInsuranceCompanyID FROM tblInsuranceCompanies UNION SELECT '(All)' , null FROM tblInsuranceCompaniesBound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works if I select a specific company. Its AfterUpdate event is this code:

Private Sub cboSelectCompany_AfterUpdate()

    Dim strSQL As String
    Dim strWhere As String
    strWhere = "WHERE fkInsuranceCompanyID=" & Me.cboSelectCompany & " "
    
    strSQL = "SELECT DISTINCT tblInsurancePlanType.pkInsurancePlanTypeID, tblInsurancePlanType.PlanType " & _
             "FROM tblInsurancePlanType LEFT JOIN tblInsuranceCompanyPlanLink ON tblInsurancePlanType.pkInsurancePlanTypeID = tblInsuranceCompanyPlanLink.fkInsurancePlanTypeID " & _
             strWhere & _
             "ORDER BY tblInsurancePlanType.PlanType;"
    Me.cboSelectPlanType.RowSource = strSQL
    Me.cboSelectPlanType.Requery
    Me.cboSelectPlanType = 0

End Sub

What doesn't work is if I select ALL in Company. I am assuming that is because I don't have an ALL in my cboSelectPlanType row source. Thats row source is based on the code above and my table PlanType.
I do have a query named qryPlanTypeUnionAll. That allows for an All choice but I am afraid to change anything in case the whole thing stops working.

Can anyone tell me how to fix this?
I am using Access 2007. I am an intermediate beginner, I can follow along figuring out what most vba is trying to say but once it gets complicated, I have to guess too much and resort to googling. I di not write all the code above, I had help with the unions and after update event.
The query will return results that combine info from several tables. I know all my tables and relationships are correct.

I'd appreciate any help I could get on this.
Thank you so much

 

[plog]

You need to build your strWhere smarter. If a user selects ALL for an option, you simply don't use that criteria. So what you should do is use an If statements to see if criteria is used and then compile strWhere accordingly:

strWhere =" WHERE (1=1)"

If (IsNull(Me.cboSelectCompany)=False) Then strWhere = strWhere & " AND (fkInsuranceCompanyID=" & Me.cboSelectCompany & ")"

 

[Uncle Gizmo]

I'm not sure that this will work in your particular case, but I thought that I should mention it, as someone else finding this thread might find it useful...

Combo Filter < All> or Some

 

[KathCobb]

You need to build your strWhere smarter. If a user selects ALL for an option, you simply don't use that criteria. So what you should do is use an If statements to see if criteria is used and then compile strWhere accordingly:

strWhere =" WHERE (1=1)"

If (IsNull(Me.cboSelectCompany)=False) Then strWhere = strWhere & " AND (fkInsuranceCompanyID=" & Me.cboSelectCompany & ")"

After writing all that out I knew I needed an If statement, but I don't understand all of the code you gave me. What does " WHERE (1=1)" do? I see the If Is Null is false then strWhere continues as my original did...am I reading that correctly? Where exactly is the part that says if it Is Null?

I apologize for being ignorant, I last worked with vba in 2012

 

[plog]

No problem. " WHERE (1=1)" is a trick I use to make it easier to add conditions later on in the code. 1=1 is always true so it has no effect, it acts as a place holder so I can successfully add more criteria if needed by adding " AND (More Criteria Here)". Otherwise I would have to test strWhere to see if I had stuff in it and needed to use an " AND " or if the criteria I was adding was the first criteria in which case putting an " AND" before it would screw it up.

If your value is NULL then there's nothing to do--you are not adding any restrictions to that field. You simply just don't use that field in the criteria--it will let everything through.

 

[KathCobb]

No problem. " WHERE (1=1)" is a trick I use to make it easier to add conditions later on in the code. 1=1 is always true so it has no effect, it acts as a place holder so I can successfully add more criteria if needed by adding " AND (More Criteria Here)". Otherwise I would have to test strWhere to see if I had stuff in it and needed to use an " AND " or if the criteria I was adding was the first criteria in which case putting an " AND" before it would screw it up.

If your value is NULL then there's nothing to do--you are not adding any restrictions to that field. You simply just don't use that field in the criteria--it will let everything through.

Thank you, I try to keep these things in my notes so if I need to refer back later I have it.

I added your code to my AfterUpdate on cboCompany and it worked great. Is there any way to also include in that list and "all". I have an union query for that box and included it as my RowSource but it still doesn't give me All. It must have something to do with the code I have in cboCompany?

 

[plog]

Yes, you would UNION the actual list with a dummy ALL record like you did cboAgent

 

[KathCobb]

I now have something very strange going on in my query. Every time I use my form to make selections and run the query, my query it changed. It doesn't show up until I after I close the query and reopen it. Please see picture attached. I am not changing the criteria...it is adding all those rows by itself and other columns too. I don't know what is making it do that??

 

[plog]

I don't really understand how this relates. Initially you were running a query from VBA, now you have a stored query that gets changed? Also, is this causing some issue or just an oddity you noticed?