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 ...

 

[Damon Heron]

Why not just print the source of the listbox contents?
Lets say it is a query called qryLst
Design your report with the source as qryLst. The command button opens the
report. No need for the function.

Damon

 

[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?

 

[Damon Heron]

I solved a similar problem by creating a base query (without any where
conditions) and calling it "zqryMyName" and another query called
"qryMyName". Then I dynamically created the criteria using the
BuildWhereCondition function and attaching it to the zqry and renaming it to
"qryMyName".
Step 1: call the function BuildWhereCondition, with the control being a
listbox with zero, 1, or multiple selections:

Public Sub cmdDoQuery(Mystr As String, Myqry As String) 'Mystr is the field
name with the condition
Dim strSQL As String
Dim strWhereLst As String
Dim qdf As QueryDef
Dim strCatList As String
Dim myqryZ As String
strSQL = ""
strCatList = ""
strWhereLst = ""

strCatList = BuildWhereCondition("mylstbox")
The strCatList now holds all of the selections
...................
'Here is the function
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control
Set ctl = Forms!frmAllReports!(strControl)' the strControl is one of
several listboxes
Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("
With ctl
For Each varItem In .ItemsSelected
strWhereLst = strWhereLst & "'" & .ItemData(varItem) &
"', "
Next varItem
End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
BuildWhereCondition = strWhereLst
End Function

Step 2: create the query from same routine that called the
BuildWhereCondition
.....................
'Get the template query
myqryZ = "z" & Myqry
strSQL = CurrentDb.QueryDefs(myqryZ).sql

'Add Items if any selected. If none selected, all included
If Len(strCatList) > 0 Then
strWhereLst = strWhereLst & strCatList
Else
Mystr = ""
End If

'See if any criteria selected
If Len(strWhereLst) > 0 Then
strWhereLst = " WHERE " & Mystr & strWhereLst & ";"
End If

'Update the query with the criteria
strSQL = Replace(strSQL, ";", strWhereLst)
CurrentDb.QueryDefs(Myqry).sql = strSQL
End Sub

The reasoning behind all this is that the zqry always has the base query
saved, from which you build the other query with the criteria.

Perhaps you can adapt some of this to your problem.

Damon