Adding an "any" criteria to combobox

[puakenikeni]

I'm using comboboxes to filter the data shown in a subform. However, I would like to add something like an "any" or "all" to the SQL string. I consider it any because I want it be more of a wildcard.

Right now, I'm using:

SELECT * FROM Disposition ORDER BY Disposition;

Thank you in advance.

 

[MarkK]

All is the default. For instance, the SQL you posted returns all the fields for all the records. So if you have an 'all' option in a combo box that functions as a filter, then when the user selects 'all' you simply remove the filter.

 

[namliam]

I think the OP is wanting to know how to add the option to the combobox.

You do this by making a UNION query as sourse for your combobox.

Select Top 1 "All" from AnyTable
Union
Select .... <your normal recordsource for the combobox>

Usually you will want your ALL option to be the top one.
to make sure of this add an extra field to your query, but hide it in your Combobox:
Select top 1 1 as OrderField,"ALL" from AnyTable
Union
Select 2 as OrderField <bla bla rest of the query>
Order by Orderfield, <rest of sort statement>

Good Luck

 

[puakenikeni]

Yes, I wanted to know how to add "all" to the criteria. I could add the selection to the combobox, but it doesn't do anything for my subform. My subform gets its values from a query that has criteria in it. It will display information based on what is selected in the combobox. Since it has criteria, should I change VBA code on the combobox, or should I change the query so that it displays "all" of records?

 

[namliam]

Well if the chosen option is all, you simply dont apply any critertia.
Either remove the where or use *

 

[puakenikeni]

I'm sorry for the many questions, but if I don't apply any criteria, does it mean I need to use separate queries for whether the user selects "*"? I have four separate comboboxes on the my form, and I want them all to be able to choose from all (the "*") or the individual options.

In my Query, this is what it looks like:

SELECT Master.ProgramName, Master.[Item Description], Master.ItemClass, Master.PropertyClass, Master.AcquiredFrom, Master.Gtag, Master.ItemClass, Master.ManufacturerVendor, Master.ModelNumber, Master.SerialNumber, Master.PartNumber, Master.Quantity, Master.UnitCost, Master.CurrentLocation, Master.Disposition, Master.DispositionCloseout, Master.DispositionLocation, Master.DispositionCloseoutDate, Master.DispositionComment
FROM Master
WHERE (((Master.ProgramName)=Forms!frmInventory!cboProgramName_tab2) And ((Master.ItemClass)=Forms!frmInventory!cboItemClass_tab2) And ((Master.PropertyClass)=Forms!frmInventory!cboPropertyClass_tab2) And ((Master.Disposition)=Forms!frmInventory!cboDisposition_tab2));

And in my AfterUpdate event, I have this:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProgramName] = '" & Me.cboProgramName_tab2 & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.cboPropertyClass_Tab2.Requery
Me.cboDisposition_Tab2.Requery
Me.cboItemClass_Tab2.Requery
Me.subMaster.Form.Requery

If Me.Dirty Then Me.Dirty = False

I'm not sure where I can remove the criteria from. Please kindly point me in the right direction.

 

[puakenikeni]

Nevermind. I found an old post on the forum and changed my query so that it used "like" instead of using the "=" sign. I used:

SELECT Master.ProgramName, Master.[Item Description], Master.ItemClass, Master.PropertyClass, Master.AcquiredFrom, Master.Gtag, Master.ItemClass, Master.ManufacturerVendor, Master.ModelNumber, Master.SerialNumber, Master.PartNumber, Master.Quantity, Master.UnitCost, Master.CurrentLocation, Master.Disposition, Master.DispositionCloseout, Master.DispositionLocation, Master.DispositionCloseoutDate, Master.DispositionComment
FROM Master
WHERE (((Master.ProgramName) Like "*" & [Forms]![frmInventory]![cboProgramName_tab2] & "*") AND ((Master.ItemClass) Like "*" & [Forms]![frmInventory]![cboItemClass_tab2] & "*") AND ((Master.PropertyClass) Like "*" & [Forms]![frmInventory]![cboPropertyClass_tab2] & "*") AND ((Master.Disposition) Like "*" & [Forms]![frmInventory]![cboDisposition_tab2] & "*"));

 

[namliam]

Well this "like" works but it is not perfect. I.e. if you have "box" and "Shoe box" and the filter is set to box it will find both...

If that is not a problem or will not happen.

The best way to actually go would be to create the query/where clause on the fly in VBA.

 

[LPurvis]

I agree - I certainly wouldn't recommend relying on the wildcard measures to help you out here.
Not only is it inexact (offers no wy of allowing users to choose an exact match) it won't return rows with Null values in any of the fields with criteria applied.
(And you're getting no love from indexes here either).
You could go with somewhat more optional parameters

SELECT ProgramName, [Item Description], ItemClass, PropertyClass, AcquiredFrom, Gtag, ItemClass, ManufacturerVendor, 
ModelNumber, SerialNumber, PartNumber, Quantity, UnitCost, CurrentLocation, Disposition, DispositionCloseout, DispositionLocation, 
DispositionCloseoutDate, DispositionComment
FROM Master
WHERE (ProgramName Like [Forms]![frmInventory]![cboProgramName_tab2] OR [Forms]![frmInventory]![cboProgramName_tab2] Is Null) 
AND (ItemClass Like [Forms]![frmInventory]![cboItemClass_tab2] OR [Forms]![frmInventory]![cboItemClass_tab2] Is Null) 
AND (PropertyClass Like [Forms]![frmInventory]![cboPropertyClass_tab2] Or [Forms]![frmInventory]![cboPropertyClass_tab2] Is Null) 
AND (Disposition) Like [Forms]![frmInventory]![cboDisposition_tab2] Or [Forms]![frmInventory]![cboDisposition_tab2] Is Null)

But again - at some expense of optimisation.
Building the SQL statement dynamically in code is the way you'll achieve the best results.

 

[puakenikeni]

Yes, you guys are right--Like you mentioned, LPurvis, my subform doesn't return null values (which is driving me nuts!). Your example works, but I would still like to learn how to build it in VBA, since I'm not sure how to do it. Will you provide an example or the theory of writing it in VBA?

 

[LPurvis]

<sighs/>
Seems my website is still down!

The demo I'd have pointed you to otherwise would probably be the example Search form.
I'll attached it here instead. See if it makes any sense.
FWIW - when it's back up the example page is here.

If it doesn't make sense you'll need to ask here.
(I can't remember exactly what code it uses to implement the search lol).

 

[puakenikeni]

Thank you for those examples, LPurvis. I haven't had the chance to look over 'um yet, but I will soon. Those will definitely be of help! Thank you both for your help!