Print list box

[Mark S]

I am trying to use the full contents of a listbox as a source to print a
report. On the form with the listbox is a button to print the list with the
following code tied to it.

Sub cmdPrint_Click()
Dim stDocName As String
stDocName = "PrintReport"
strWhereClause = fnListBox(Me!lstProjects)
DoCmd.OpenReport stDocName, acViewPreview, , strWhereClause
End Sub

Also,

Public Function fnListBox(lst As ListBox) As Variant
Dim varValue As Variant
varValue = Null
For i = 1 To lst.ListCount - 1
varValue = varValue & " or " & lst.ItemData(0) & " = " &
lst.ItemData(i)
Next i
fnListBox = Mid(varValue, 4)
End Function

I don't recall where I got fnListBox() from but it was originally intended
for getting selected items from a list box. I modified it significantly.

Everything works fine for smaller lists, but I get a "The filter would be to
long" error when testing large lists.

Access Help for Open Report says the WhereClause can be up to 32,768
characters. My test that failed was about 3,000 characters. Any suggestions?

I will probably go back to what most of you suggest and use the same filter
source for the listbox as I do for the report. But I would really like to
see this work, with the listbox being the source for the report. Sort of a
truer WYSIWYG.

One other quick question. In an IF statement, do you have to make a compare
with logicals. For example, IF logical THEN ... or does it have to be IF
logical = TRUE THEN ...

 

[Mark S]

Because the listbox is not based on a hard query. It is based on a variety
of selections that dynamically write the RowSource for the listbox.

While duplicating the code to generate the RowSource is feasible, I don't
think that is the best option in this case.

Maybe if instead of writing the RowSource, I were to re-write the query
everytime. Can that be done? OK, just talking to myself here. Can't do
that either because this will be multi-user.

Further complicating this is the Select portion of the SQL for the
RowSource. It actually contains different fields based on the selections
made. Maybe I can play with keeping that in code but moving the Where
portion into a query and connecting it all the the From.

This brings up another question. I have some check boxes in the form that
is the source for all this that I want to act as filters. So, if they are
checked, only select the records that the related field is true. But if they
are not checked, select all the records. How do you put that into the
criteria of a select query?