using a checkbox on form to determine criteria in query

[Tuckejam]

My apologies if this should have been posted in "Forms"



I am trying to create a "Report Generator" that will generate a report based on controls entered from a form.

I understand how to some of the controls to the query using the builder. this will work for dates and single options like Trans_type (choosing Deposit OR Withdraws)

but I also have Trans_account (or categories, like food, storage, loans, bed dues etc)

How could I have a form that would list each of the Trans_acount items with a checkbox for each item and have that check box determine whether or not the query uses it as a criteria

the image below is just sort of a Mock up of what I am trying to do

Thanks for the help

[pbaldy]

I would use a multiselect listbox rather than checkboxes. It will be more dynamic as you add items.

http://www.baldyweb.com/multiselect.htm

[Tuckejam]

pbaldy,

That was amazing and surprisingly easy to implement. Thank You!

I do have a few questions, My list is 27 items in total so first question is what would the code for a "Select all Items" button look like (if you don't mind please and thank you)

and Second, any thoughts on how I might bust that list box up into two smaller boxes?
meaning rather that one list box 27 items long, two side by side boxes with about 14 items each. (this is not that important just for formatting the report to look better)


Thanks

[accesstos]

Alternatively, you can use subforms instead of multiselect listboxes in report generator.
Add a new True/False field in tbl_TransAccount (say [Checked]) and create the subforms based on simple queries:
For the first group/subform:

SELECT TransAccountName, Checked FROM tbl_TransAccount WHERE (TransAccountID mod 2=1)

For the second group/subform:

SELECT TransAccountName, Checked FROM tbl_TransAccount WHERE (TransAccountID mod 2=0)

So, you can use the [Checked] field in your criteria in any case. For example, in qryFinRepGen, you could add in criteria the condition

"AND [Checked]=-1".

To select/deselect all, you can just execute an UPDATE query via an unbound checkbox like:

CurrentDb.Execute "UPDATE tbl_TransAccount SET [Checked]=" & Me!chkAll

This approach offers more versatility in interface techniques than the listbox and the criteria could be independent of any user interface.

Hope it helps,
John

[pbaldy]

You can loop the listbox and select all items, but I would just test a checkbox and skip the wherecondition if it is checked. Your 2 listboxes could have row sources like:

SELECT Blah
FROM TableName
WHERE TransAccoutID < 18


SELECT Blah
FROM TableName
WHERE TransAccoutID > 17

Using a subform with a bound checkbox would work but could be problematic in a multiuser environment.

[accesstos]

Using a subform with a bound checkbox would work but could be problematic in a multiuser environment.

Good point! Needs local handling.

[Tuckejam]

Pbaldy,

Thank you for the help but I am still a little confused.
I have given up on the idea of a two smaller list boxes and made the form work with just one (see image below)

I dont understand the SQL statement you mentioned

SELECT Blah
FROM TableName
WHERE TransAccoutID < 18


SELECT Blah
FROM TableName
WHERE TransAccoutID > 17

I do understand the what you are saying with the SQL statement, but I dont understand how I would make that work with the button on the form

thank you again for the help

[pbaldy]

With a single listbox the code would be like the link in post 2. That SQL was when you wanted to have 2 list boxes. In that case, the code would get a little more complicated but not hard.