Listbox filtered by checkbox

[nrk]

I have created a listbox that will filter a report based on the listbox contents. There are times that I want to further filter the report by only allowing the report to show the listbox contents when their status = 'x'.

So, as an example, if there are 3 records in the listbox and I click 'cmdFilter', the report will show all three records. If I select a checkbox, then I would expect to see only two records (as they both have a status = "x").

For some reason, this filter only works the first time. If I don't use the checkbox, the filter will work each time. If I want to use the checkbox, it will only work the first time.

I would appreciate any thoughts that you may have...I am stumped!

 

[Topher]

I've had similar problems to what you describe. The only real solution I could figure out was to create a string variable with the SQL statement for the report. I would then append to it the filter which was selected - when the report is opened you can program what the recordsource by point the variable to it. thats the only workaround i've bin able to come up with.

but in your case - how are you passing the filter to the report? do you have a command button that opens the report? if you have it setup this way - you want to pass the filter through the docmd.openreport command and ensure that the filter changes depending on which checkbox you select. how are you changing them??

sorry if this is unclear I'm just trying to get a better picture of what you are doing.

- Topher

 

[nrk]

Topher,
Thanks for the follow-up. I initially started this project by creating a SQL string to pass to the report. Unfortunately, users are selecting so many records (up to the hundreds) that I maxed out the SQL string limit. So, my workaround was to create dynamic queries (via VB) to filter report. Here is the process that I have.

1. Open form (opens report with no records)
2. User selects 'Category' from combobox, which populates a listbox (#1).
3. User selects records from listbox (#1) and, on Cmd, moves records from listbox (#1) to listbox (#2).
-behind the scenes - on moving from #1 to #2, two checkboxes on the underlying table are changed from False to True for specified records.
4. Click cmdFilter and the query finds records on the table where the checkboxes are equal to 'True'.

Hopefully, this better defines my situation. I am still stuck so any feedback would be greatly appreciated.