Search/Filter on more than one list/combo box

[marion34s]

Hello Access Masters,

I've been using access for almost a month now with some success with the basics but still have much I do not know and have been learning by scouring google and this forum. I have not been able to find an answer to this challenge yet and I'm hoping you all can help.

I am creating a form that will return information pulled from my tables and/or queries to show project details. I currently have a ComboBox for "Project Number_Selection" which will return the info seen below. However, my boss would also like to be able to search/filter by the "Project Name" or "Client" or "Project Status" or "PM".

I have seen information on how to create cascading ComboBoxes BUT I need each one to be able to be the first (or only) box selected. Example: search by project number only OR search for all projects by PM Lou THEN the boxes would show info relating only to projects that belong to Lou and can be selected. I hope I have explained this adequately.

For another example (in a different layout for the sake of clarity):

Step 1	Select PM: Lou
Step 2		Select Project Status: Installation
Step 3			Select Client: Nightingale
				Displays all projects for Nightingale that is in the Installation phase and has Lou as a PM (could be 1 or more projects to be returned)

Thank you all in advance for your wisdom.

 

[MajP]

See post reply 2, 17

Multi field search returning nil value

Hey experts! I have a form that I want the user to be able to search for relevant jobs with a number of fields. I want to use unbound text boxes for project name and client then (at this stage while I am testing) get a query to return relevant records. I've used wildcards in each of two fields...

www.access-programmers.co.uk

 

[Ranman256]

every combo's query looks at the other combo box to filter.
you just need to refresh the next box after user picks an item in the combo...

sub cboCountry_AfterEvent()
cboState.requery
end sub

sub cboState_AfterEvent()
cboCity.requery
end sub

cboState query = qsStates1Country
cboCity query = qsCities1State

qsStates1Country =
select State from table where [CountryName]=forms!fMyForm!cboCountry

qsCities1State =
select City from table where [state]=forms!fMyForm!cboState

 

[Pat Hartman]

Cascading combos are only used when the combos are dependent on each other in a hierarchy such as country, state, city. In this case, you want to select based on one or more of the choices but picking a choice in one combo doesn't alter the visible choices in another combo. You can write code to build the WHERE clause on the fly or since you have only three options, you can use a saved querydef:

WHERE (PM = Forms!yourform!cboPM OR Forms!yourform!cboPM is Null)
AND (ProjectStatus = Forms!yourform!cboProjectStatus OR Forms!yourform!cboProjectStatus is Null)
AND (Client = Forms!yourform!cboClient OR Forms!yourform!cboClient is Null);

NOTICE that each compound condition is enclosed in parantheses. It is the "OR" conditions that are what make the arguments optional. If a particular combo is null, that condition will return true so the coumpound condition will be true due to the OR
I.e. (A or B) -- if either A is true or B is true, the condition itself is true.

 

[marion34s]

Cascading combos are only used when the combos are dependent on each other in a hierarchy such as country, state, city. In this case, you want to select based on one or more of the choices but picking a choice in one combo doesn't alter the visible choices in another combo. You can write code to build the WHERE clause on the fly or since you have only three options, you can use a saved querydef:

WHERE (PM = Forms!yourform!cboPM OR Forms!yourform!cboPM is Null)
AND (ProjectStatus = Forms!yourform!cboProjectStatus OR Forms!yourform!cboProjectStatus is Null)
AND (Client = Forms!yourform!cboClient OR Forms!yourform!cboClient is Null);

NOTICE that each compound condition is enclosed in parantheses. It is the "OR" conditions that are what make the arguments optional. If a particular combo is null, that condition will return true so the coumpound condition will be true due to the OR
I.e. (A or B) -- if either A is true or B is true, the condition itself is true.

Pat, thank you for this suggestion. Would I put this in VBA code for each box or somewhere else? Thanks!