Solved Populate Combo based on Cont. Form & Ckbox (1 Viewer)

[Weekleyba]

I have a database that I want a combo box's selection to be based on data from a subform and a ckbox.
Subform:
The subform is SF_LocationForSelectedProject and is linked to the main from by the ContractID.
It's record source is a query Q_LocationForSelectProject.
The subform is continuous.
I want the combobox to look at the LocationNumber in the that subform.
CheckBox:
In addition, I want the combo box selection to be based on the selection of a checkbox, ckUFMSProject.

For the combobox, cboCAN, the record source would be a select query based from table called T_CAN_Crosswalk.

If the checkbox, ckUFMSProject is True, then I want it to filter selection CAN Project Flag = "Y", if False then filter , CAN Project Flag = "N".
In addition, if the checkbox is False, then I want to filter the CANs base on LocationNumber in the SF_LocationForSelectedProject.

How can I set this up?

Part of the solution may be the following select query for the CAN row source, but it doesn't account for the Location.
How to include the Location?

 

[theDBguy]

Hi. Are you able to post a sample copy of your db with test data?

 

[Weekleyba]

I'll do that. Give me a couple minutes.

 

[Weekleyba]

On opening the database, click "Project Search/Add" button.
Then double click on the contract, "TEMP-2021-0001", shown below.

This takes you to the F_Contract where I'm trying to figure out how to develop a select query for the CAN combo box based off of the checkbox and LocationNumber.
If the check box is checked, then I want only those Locations with a CAN Project Flag of "Y" from the T_CAN_Crosswalk.
If the check box is not checked, then I want only those Locations that match the Location Numbers in the SF_LocationsForSelectedProject.

 

[theDBguy]

Hi. Thanks! See if this is what you mean...

 

[Weekleyba]

That's beautiful!
I've never used the In(select ....) in a query criteria before.
Thanks so much for your help!

 

[theDBguy]

That's beautiful!
I've never used the In(select ....) in a query criteria before.
Thanks so much for your help!

You're welcome. Good luck with your project.