Help with null or inserting <All> into cascading combo boxes please ...

sp00kster

New Member
Joined
Nov 25, 2006
Messages
35
Being honest here I have not opened up a database for over 25 years and feel I have as much understanding as a dishwasher right now - lol

I have a search form using some cascading comboboxes and need to return all records if left null or a viable <All> option. My first combobox that I am stuck on- "cboYear" my current row source = "SELECT DISTINCT CustomerSpecDb.InstallYear FROM CustomerSpecDb;" I have read numerous articles about strictly a VB approach others about inserting a union into the row source and yet trying to apply anything seems to leave me with either a empty dropdown selection or a visable <All> that does not pass on any records when selected

Thanks for your patience and time reading this - any help is much appreciated
Larry
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
Building the combo rowsource is easier enough. That just uses the UNION you mentioned.
It is what you do with the ALL option that matters?

One way might be to have a default SQl string "Select * FROM tablename" and you set that as the recordsource for the subform or where you want the data.?
If the combo value is not ALL then add " WHERE fieldname = '"& Me.combo& "'" and then set the recordsource?

Another way might be to use the Like operator on the field to compare

Code:
Like "*" & [descparam] & "*"
and if empty it will produce all records?.

HTH
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
468
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Following on what welshgasman said, when I want an 'all' option, I set the first row of the combobox to "*" so that I can reference it in the queries directly like the other options.

Users seem to instinctively understand the meaning well-enough.
 

sp00kster

New Member
Joined
Nov 25, 2006
Messages
35
Thanks for the responses - Please forgive my ignornance but I am not still not exactly clear on how to apply what you are saying to do. Looks like much much more studying
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
See if this helps.


There are links to get the code to inspect.

Lots of tutorials on YouTube.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top