Check Box On Form - Select All Records

[razorking]

I have attached an example database of something I am trying to create. In the database there is a form: frmPrintLabels. If you open the form there is a list box called Series, if you select a series it loads the optional colors for the series in another listbox. If you select a color it populates the sub-form on the report with all of the combinations for the Series+Color. What I want is a way to also select all Series/Color combinations instead of selecting each individually - if desired.

I added a checkbox to the form, I was thinking there would be a way in the underlying query (qrySelect2Print) to tell it that if the checkbox is selected "Like *" , otherwise use the value from the form - if that makes sense. But it is not working.

Solutions?

Thanks!

 

[Pyro]

There are a ton of ways to do this. Attached are two quick examples.

Test 1 takes the reverse path of displaying all colours when a series is first selected, then drills down once a colour is selected.

Test 2 is slightly more complicated and uses a union query to show "*Show All" at the top of your colour list. Selecting this option shows all colours.

Neither use any code, although you probably could do more things with a it of VBA.

 

[razorking]

Hello, and thank you very much, this is pretty much what I was looking for. Figured there were many ways but I was stumped. I like your option using the union query and am adopting that one.

One more hurdle that I am hoping to overcome. I have uploaded the newest version of the database. There is a third listbox in the equation. The third listbox is looking at a field on a table that may or may not have a value in the field for the records, and that is presenting me problems with the record selection and subsequent display in the subform.

As an example: if you open the form frmPrintLabels and select the series "Bamboo Tile" everything will navigate as I would prefer - so all good with these records. But if you select the Series Balboa or Beach Tile, you can only select/show in subform the results for *Show All, but not the individual colors - that is to say; selecting an individual color of these series will not display the results in the subform - because there is no finish record value for these records.

I am guessing there is a way to overcome this but again I am stuck

 

[razorking]

I think I figured out the last piece of the puzzle, I think this works for all possible scenarios.
see attached - hope maybe this can help someone else.

Thanks again Pyro for getting me over the main sticking point.

 

[Pyro]

Glad you are happy with your result.

One thing i might suggest is clearing the "finish" listbox when an alternate color is displayed. If i click on Bamboo Tile > Desert > Gloss, then change the Color to *Show All, the previously selected finish is retained. Maybe this is the desired outcome, but *Show All does not have any finish types listed.

Since you are using macros, a simple SetProperty on the after update event of List4 should do the trick.

Control = List5
Property = Value
Value = null