Multiple combo boxes to filter a Listbox

[nosferatu26]

hello, this is my first post so if i leave out information or word this poorly then i apologize.

I have a form where I am trying to use 4 combo boxes(nomenclature,BPN,vendor, and reference) to filter a list box containing part numbers. The way I have it set up right now is in the listbox it is searching for each field and then in the criteria section i have [Forms]![myform]![respectedFieldsCombo]. This works when selections are made from the combo boxes but when one is blank (not being used to filter) then I assume it passes null for that value and the listbox doesnt return anything. I have tried to make it so the listbox ignores null values but im still having this problem.

for example: If i only have a selection for the vendor combo box then i want the list box to show all respective part numbers for that vendor, where nomenclature or any of the other fields are irrelevant. I also want to be able to stack these filters upon every new combo box selection.


Any help will be greatly appreciated!

 

[spikepl]

show your filter

 

[nosferatu26]

Here is the row source for the listbox. there are 2 more fields after vendors for nomenclature and refrence, they just arent in the picture.

 

[spikepl]

Copy the SQL of the query used as row source for the listbox and paste it here. I don't want to have to retype a lot of your stuff. Please make it easy for people to help you.

 

[nosferatu26]

SELECT tbl_Part_Number.[Part Number ID], tbl_Part_Number.[Part Number], tbl_Base_Part_Number.Base_Part_Number_ID, tbl_Vendors.[Vendor ID], tbl_Part_Nomenclature.[Part Nomenclature ID], tbl_Ref_Des.[Ref Des ID]

FROM tbl_Ref_Des INNER JOIN ((tbl_Part_Nomenclature INNER JOIN ((tbl_Vendors INNER JOIN (tbl_Base_Part_Number INNER JOIN tbl_Part_Number ON tbl_Base_Part_Number.Base_Part_Number_ID = tbl_Part_Number.[Base Part Number]) ON tbl_Vendors.[Vendor ID] = tbl_Base_Part_Number.Vendor) INNER JOIN tbl_Nomenclature_to_BPN ON tbl_Base_Part_Number.Base_Part_Number_ID = tbl_Nomenclature_to_BPN.[Base Part Number]) ON tbl_Part_Nomenclature.[Part Nomenclature ID] = tbl_Nomenclature_to_BPN.[Part Nomenclature]) INNER JOIN tbl_RefDes_to_Nomenclature ON tbl_Part_Nomenclature.[Part Nomenclature ID] = tbl_RefDes_to_Nomenclature.[Part Nomenclature]) ON tbl_Ref_Des.[Ref Des ID] = tbl_RefDes_to_Nomenclature.[Ref Des ID]

WHERE (((tbl_Base_Part_Number.Base_Part_Number_ID)=[Forms]![CR_Part_Remove_Form]![bpnCombo]) AND ((tbl_Vendors.[Vendor ID])=[Forms]![CR_Part_Remove_Form]![vendorCombo]) AND ((tbl_Part_Nomenclature.[Part Nomenclature ID])=[Forms]![CR_Part_Remove_Form]![nomenclatureCombo]) AND ((tbl_Ref_Des.[Ref Des ID])=[Forms]![CR_Part_Remove_Form]![refDesCombo]))

ORDER BY tbl_Part_Number.[Part Number];


anything else i can do just let me know, thank you very much for responding i appreciate it

 

[spikepl]

WHERE 
(tbl_Base_Part_Number.Base_Part_Number_ID=[Forms]![CR_Part_Remove_Form]![bpnCombo] OR [Forms]![CR_Part_Remove_Form]![bpnCombo] Is Null)
AND
(tbl_Vendors.[Vendor ID])=[Forms]![CR_Part_Remove_Form]![vendorCombo] OR [Forms]![CR_Part_Remove_Form]![vendorCombo] Is Null)
AND
(tbl_Part_Nomenclature.[Part Nomenclature ID]=[Forms]![CR_Part_Remove_Form]![nomenclatureCombo] OR [Forms]![CR_Part_Remove_Form]![nomenclatureCombo] Is Null)
AND
(tbl_Ref_Des.[Ref Des ID]=[Forms]![CR_Part_Remove_Form]![refDesCombo] OR [Forms]![CR_Part_Remove_Form]![refDesCombo] Is Null)

 

[nosferatu26]

you are a saint!!!! thanks again and god bless

 

[nosferatu26]

Hello again, I am having touble with this code again and I don't know why. I created a new database because I want to clean up what work was done in the other database. In my new form, I am using the same code, but I changed the names of everything accordingly. The issue now is that nothing will show up in the listbox at all. In case you want the code here it is:



WHERE (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND ((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND ((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND ((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo])) OR (((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND ((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND ((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo]) AND (([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null)) OR (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND ((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND ((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo]) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null)) OR (((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND ((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo]) AND (([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null)) OR (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND ((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND ((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo]) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null)) OR (((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND ((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo]) AND (([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null)) OR (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND ((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo]) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null)) OR (((tbl_Ref_Des.Ref_Des_ID)=[Forms]![FRM_acbPartRemoval]![refDesCombo]) AND (([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null)) OR (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND ((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND ((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null)) OR (((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND ((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND (([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null)) OR (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND ((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null)) OR (((tbl_Part_Nomenclature.Part_Nomenclature_ID)=[Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) AND (([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null)) OR (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND ((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null)) OR (((tbl_Vendors.Vendor_ID)=[Forms]![FRM_acbPartRemoval]![vendorCombo]) AND (([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null)) OR (((tbl_Base_Part_Number.BPN_ID)=[Forms]![FRM_acbPartRemoval]![bpnCombo]) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null)) OR ((([Forms]![FRM_acbPartRemoval]![bpnCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![vendorCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![nomenclatureCombo]) Is Null) AND (([Forms]![FRM_acbPartRemoval]![refDesCombo]) Is Null))
GROUP BY tbl_Part_Number.Part_Number_ID, tbl_Part_Number.Part_Number
ORDER BY tbl_Part_Number.Part_Number;


Sorry if it looks really messy. If there is anything you can help with I would appreciate it very much