Multiselector form

[TrekBiker]

I'm trying to build a selector form to pull out data from a table where each record can have just one of a number of tick boxes selected, then picking records containing combinations of ticks. The form would look like this, and allow the combinations of ticks to be set up at the top then showing the matching records in the subform. So for example all those with STO OR CGG ticked.

I can do a similar AND version where there can be more than one tick per row, and to extract records that match the chosen combinations. So ones with ticks in column 1 and 3, for example.

But I run into errors like 'too complicated' when trying to adapt it to the OR condition I now need.

 

[Andrzejek]

What does your table look like?

"a table where each record can have just one of a number of tick boxes selected" - please don't tell me you have:

[pre]
FirstName STP CTP SGG CGG
Nikki 0 0 0 1
Tony 1 0 0 0
Claire 0 0 1 0
[/pre]



---- Andy

There is a great need for a sarcasm font.

 

[TrekBiker]

Don't understand but I think you may mean that the table should have a single combo with four options, not tick boxes?

I started this way but the client wanted separate tick boxes.

 

[dhookom]

I would use an option group bound to one field.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for views, which gave useful pointers.

I've reverted to using a combo, not separate tickboxes. What I included earlier was a mock-up but the real table is called HData and the combo is Circumstance, which has 5 options. The selections are made in a frmMultiSelect which contains these options as tickboxes, to allow easy picking.

This then does the selection and works a treat.

SELECT DISTINCT *
FROM HData
WHERE (HData.Circumstance="Homecare" And Forms!frmMultiSelect!chkHomecare=True) 
Or (HData.Circumstance="Released" And Forms!frmMultiSelect!chkReleased=True) 
Or (HData.Circumstance="Secure" And Forms!frmMultiSelect!chkSecure=True) 
Or (HData.Circumstance="Hospital" And Forms!frmMultiSelect!chkHospital=True) 
Or (HData.Circumstance="Other" And Forms!frmMultiSelect!chkOther=True);