Cascading Combos - How to show ALL in 2nd box if 1st box is blank

[Danick]

I have two cascading unbound combo boxes.
That is, the second box shows filtered items depending on what the user selected in the first combo.
But if the user did not select anything in the first combo, I would like the 2nd combo to show ALL items.

I found one example that does this - but it uses complex VBA I would like to avoid.

http://www.datawright.com.au/access_resources/access_combo_boxes.htm

Does anyone have a simpler way of doing this?

 

[pbaldy]

If you're just requerying:

http://access.mvps.org/access/queries/qry0001.htm

 

[Danick]

If you're just requerying:

http://access.mvps.org/access/queries/qry0001.htm

Not sure I understand what this does. The combo boxes are unbound. Like the images below.

Basically, how can you see the ALL the items in the products combo if the supplier combo is left null.

 

[Simon_MT]

Try Like [Forms].[frmChooseProducts![cmbSupplier] & "*"

Alternatively you can use VB and first test if the Supplier combi is populated and only have a Lookup with Supplier when available. This is a little bit complicated.

On the Product Combi you will need to requery cmbProduct On Entry.

Simon

 

[arnelgp]

try also:

IIF(Trim([Forms]![frmChooseProducts]![cmbSupplier] & "")<> "", [Forms]![frmChooseProducts]![cmbSupplier], [SupplierID])

 

[pbaldy]

Not sure I understand what this does.

It does exactly what you want.

 

[Danick]

It does exactly what you want.

Well pbaldy, you were right.
After spending a couple of hours working on VBA in the GotFocus of the second combo to change it's row source depending if the first combo was null, I finally got it to work.
Then came back here to re-post and then the light bulb appeared over my head when I re-read your post. :banghead:
So I went back to the second combo, removed all the code and added a simple "Is Null" expression to the second combo row source query. Bingo - does exactly what I wanted without all that code.

Thanks again...

 

[pbaldy]

Happy to help!