Select records for report using listbox 2

[EdmCath]

I have several reports that I need to compare a selection of records for. To do this, I have created a multiselect listbox and populated it with records from a database (100 records). What I want to do is select some or all of the records and open a report based on the selection. I have created a query that works in opening a few records. My problem is that it won't open a lot of records. Access tells me the filter would be too long. I can get about 25 records to work, but it takes a long time to process this. Opening the report the normal way is far quicker. Does anybody out there have a more efficient way of doing this? Here is what I am using.(Watch word wrap)

sSQL = "(("
For Each Item In Me.List0.ItemsSelected
sSQL = sSQL & "(Table_Facilities.Info_FacilityCode)=" & """" & Me.List0.ItemData(Item) & """ Or "
Next
sSQL = Mid(sSQL, 1, (Len(sSQL) - 4))
sSQL = sSQL & "))"
DoCmd.OpenReport "Barrier Free Access Information", acViewPreview, "", sSQL

 

[gol4]

drop the or and change the string you are building to a comma seperated values and use the in clause to select

Table_Facilities.Info_FacilityCode in (1,3,4,7)

 

[FancyPrairie]

Check out this FAQ FAQ181-5497. What the code does is loops thru all of the controls on your form and builds and returns the Where clause for you. It works for single and multi-select list boxes, combo boxes, text boxes, date ranges, option groups, and check boxes.

All you have to do to make it works is to create a new module, copy and paste the function from the FAQ into the new module and define the tag property as specified in the header of the FAQ. Don't worry about understanding the code. You don't need to.

To open a report, all you have to do is type:

DoCmd.OpenReport "YourReport",,,BuildWhere(Me)

The key to making it work is setting the Tag property of each of the controls correctly.

What's nice about this function is that you can use it in other database you create without having to write custom code for each report criteria form you create.

Also, in this thread thread181-1084127 I also showed how to add an ALL label to work with your list box and provided the code for handling it.

 

[TheAceMan1]

How are ya EdmCath . . . . .

My Idea is this:
[ol][li]Add a Boolean field ([purple]Yes/No[/purple]) to table [blue]Table_Facilities[/blue].[/li]
[li]Base the report on a [blue]query that includes this field.[/blue] The criteria of the boolean field in this query will be set to [blue]true![/blue], via an [blue]Update[/blue] query, for those specific records selected in the listbox.[/li]
[li]In the form, after first updating this boolean field to [blue]all FALSE[/blue] with an Update query, we then loop thru selected items in the listbox, updating the boolean field as necessary to [blue]true[/blue] as we go.[/li]
[li]Finally you open the report . . . [purple]Bada Bing[/purple] . . . [purple]Bada Boom[/purple] . . .[/li][/ol]
[purple]Your thoughts?[/purple]

See Ya! . . . . . .

 

[EdmCath]

Thank you Gol4, you're answer is exactly what I was looking for. Thank you everyone else for your very quick responses. I will look into your suggestions as well