ComboBox with Where clause question

[d9pierce1]

Hi all,


Could I please get some assistance in writing two SELECT Statement on two Combo Boxes?

What I would like is to:
On cboFilterRole I would like to make the select statement so that If the cboFilterIndustry Is NotNull,
then BusinessPrimaryRoleID, tblBusinessPrimaryRole, BusinessPrimaryIndustryID FROM tblBusinessPrimaryRole
WHERE BusinessPrimaryIndustryID= cboFilterIndustry and if it is null, then BusinessPrimaryRoleID, tblBusinessPrimaryRole, BusinessPrimaryIndustryID FROM tblBusinessPrimaryRole

On cboFilterFunction I would like to make the select statement so that If the cboFilterIndustry Is NotNull OR cboFilterRole Is NotNull ,
then BusinessPrimaryRoleID, tblBusinessPrimaryRole, BusinessPrimaryIndustryID FROM tblBusinessPrimaryRole
WHERE BusinessPrimaryIndustryID= cboFilterIndustry OR BusinessPrimaryRoleID= On cboFilterRole and if it is null, then BusinessPrimaryFunctionID, BusinessPrimaryFunction, BusinessPrimaryRoleID, BusinessPrimaryIndustryID FROM tblBusinessPrimaryFunction

This way if I select anything from the second and third combos it only shows me results that match the previous combo
Here is the table, row source info currently:

'tblBusinessPrimaryIndustry
'tbl Fields:
    BusinessPrimaryIndustryID
    BusinessPrimaryIndustry
'ComboBox:
    cboFilterIndustry
'Row Source:
    SELECT BusinessPrimaryIndustryID, BusinessPrimaryIndustry FROM tblBusinessPrimaryIndustry ORDER BY BusinessPrimaryIndustry; 


'tblBusinessPrimaryRole
'tbl Fields:
    BusinessPrimaryRoleID
    tblBusinessPrimaryRole
    BusinessPrimaryIndustryID
'ComboBox:
    cboFilterRole
'Row Source:
    SELECT BusinessPrimaryRoleID, tblBusinessPrimaryRole, BusinessPrimaryIndustryID FROM tblBusinessPrimaryRole ORDER BY BusinessPrimaryRole; 


'tblBusinessPrimaryFunction
'tbl Fields:
    BusinessPrimaryFunctionID
    BusinessPrimaryFunction
    BusinessPrimaryRoleID
    BusinessPrimaryIndustryID
'ComboBox:
    CboFilterFunction
'Row Source:
    SELECT BusinessPrimaryFunctionID, BusinessPrimaryFunction, BusinessPrimaryRoleID, BusinessPrimaryIndustryID  FROM tblBusinessPrimaryFunction ORDER BY BusinessPrimaryFunction;

Thank you all!
Dave

[pbaldy]

Either

http://www.theaccessweb.com/queries/qry0001.htm

or

https://www.baldyweb.com/CascadingCombo.htm

[d9pierce1]

Thanks pbaldy,
I pretty much have the cascading combos down but what I dont have is how if I dont select from the first, i still want to be able to select from second or third. But if I do select from the first then I want to filter down the list on the second and so on.

[pbaldy]

Did you try anything? The requery method would probably work with the tweak in the query criteria described in the first link. You could also test the first in code and set the second's row source either way depending on whether a selection had been made.

[d9pierce1]

I have tried all sorts of things and couldnt get it to work. I can cascade it but I cant get it not to cascade?
I am stumped...

[pbaldy]

We can't fix what you don't post.

The query criteria tweak should make it pull all records if nothing is selected in the combo.

[d9pierce1]

I guess I dont understand the query tweek as this is a row source on a combo box and does not have a query it is pulling info from?
I am not real good at these things so I may not just understand it. I thought there was a way to like nest the Select statement?
Sorry

[pbaldy]

The row source is itself a query, just not a saved one you can see in the navigation pane. You can click on the ellipsis and put in design view like any other. Try

SELECT BusinessPrimaryRoleID, tblBusinessPrimaryRole, BusinessPrimaryIndustryID
FROM tblBusinessPrimaryRole
WHERE BusinessPrimaryIndustryID = cboFilterIndustry OR cboFilterIndustry Is Null
ORDER BY BusinessPrimaryRole;

[davegri]

See this for an approach. If any combobox changes, the rowsource for all the others is updated as well as the recordsource for the form.

Ultimate Dynamic Multiple Cascading Comboboxes, synchronized in any order (accessforums.net)

[d9pierce1]

Hi pbaldy
OK, I got some of it working now and thank you for this!
I am having an issues with the 3rd combo (CboFilterFunction)
Below is the code I am using on this but there is something wrong with the code as it just gives all results. doesnt create an error though?
But I am close I believe!

'cboFilterRole: RowSource and works Great
SELECT BusinessPrimaryRoleID, BusinessPrimaryRole, BusinessPrimaryIndustryID FROM tblBusinessPrimaryRole WHERE BusinessPrimaryIndustryID = cboFilterIndustry OR cboFilterIndustry Is Null;
 
'CboFilterFunction: RowSource and does not work?
'This is just not the correct method but not sure how to write it?
SELECT BusinessPrimaryFunctionID, BusinessPrimaryFunction, BusinessPrimaryIndustryID, BusinessPrimaryRoleID FROM tblBusinessPrimaryFunction WHERE BusinessPrimaryFunctionID AND BusinessPrimaryRoleID = cboFilterIndustry OR cboFilterIndustry Is Null AND cboFilterRole OR cboFilterRole Is Null;




Private Sub cboFilterIndustry_AfterUpdate()
    Call bListBoxFilter
    Me.cboFilterRole.Requery
    Me.CboFilterFunction.Requery
End Sub
Private Sub cboFilterRole_AfterUpdate()
    Call bListBoxFilter
    Me.CboFilterFunction.Requery
End Sub

Thank you
Dave

[d9pierce1]

Hi davegri
Thank you for this and will continue reviewing it as it was pretty intimidating for me! I will continue to try to learn this for future reference
Thanks again
Dave

[davegri]

Hi davegri
Thank you for this and will continue reviewing it as it was pretty intimidating for me! I will continue to try to learn this for future reference
Thanks again
Dave

We're always learning. I encounter plenty of coding myself that is intimidating!

[pbaldy]

Hi pbaldy
OK, I got some of it working now and thank you for this!
I am having an issues with the 3rd combo (CboFilterFunction)
Below is the code I am using on this but there is something wrong with the code as it just gives all results. doesnt create an error though?
But I am close I believe!

'cboFilterRole: RowSource and works Great
SELECT BusinessPrimaryRoleID, BusinessPrimaryRole, BusinessPrimaryIndustryID FROM tblBusinessPrimaryRole WHERE BusinessPrimaryIndustryID = cboFilterIndustry OR cboFilterIndustry Is Null;
 
'CboFilterFunction: RowSource and does not work?
'This is just not the correct method but not sure how to write it?
SELECT BusinessPrimaryFunctionID, BusinessPrimaryFunction, BusinessPrimaryIndustryID, BusinessPrimaryRoleID FROM tblBusinessPrimaryFunction WHERE BusinessPrimaryFunctionID AND BusinessPrimaryRoleID = cboFilterIndustry OR cboFilterIndustry Is Null AND cboFilterRole OR cboFilterRole Is Null;




Private Sub cboFilterIndustry_AfterUpdate()
    Call bListBoxFilter
    Me.cboFilterRole.Requery
    Me.CboFilterFunction.Requery
End Sub
Private Sub cboFilterRole_AfterUpdate()
    Call bListBoxFilter
    Me.CboFilterFunction.Requery
End Sub

Thank you
Dave

When you mix And with Or you have to use parentheses to clarify the logic. You've also got a few stray items in there. Try

SELECT BusinessPrimaryFunctionID, BusinessPrimaryFunction, BusinessPrimaryIndustryID, BusinessPrimaryRoleID
FROM tblBusinessPrimaryFunction
WHERE (BusinessPrimaryIndustryID = cboFilterIndustry OR cboFilterIndustry Is Null)
AND (BusinessPrimaryRoleID = cboFilterRole OR cboFilterRole Is Null)

[d9pierce1]

Hi pbaldy
Thank you so much! I dont know how you keep all these syntaxes, brackets and parentheses straight.
But I sure am glad you can!
Again, Thank you for all your help, it worked perfectly!
Dave
Marked Solved

[pbaldy]

Hi pbaldy
Thank you so much! I dont know how you keep all these syntaxes, brackets and parentheses straight.
But I sure am glad you can!
Again, Thank you for all your help, it worked perfectly!
Dave
Marked Solved

Happy to help Dave! Once you've made the same mistakes time after time like I have, it slowly starts to sink in.