I have the following on-click event from a multiselect listbox
(lst_pillars) form to run a report. I added additional multiselect
list boxes (1st_stakeholders, lst_programs, lst_managers) but am stuck
trying to write code that takes the selections from the rest of the
multiselect list boxes for the criteria for the report. Can I repeat
parts of the code below to collect all the criteria? How? Also I want
to add a date range criteria to this form as well.

thanks.


Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click
    Dim MyDB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim i As Integer, strSQL As String
    Dim strWhere As String, strIN As String
    Dim flgAll As Boolean
    Dim result As String, varItm As Variant
    
    Set MyDB = CurrentDb()
    
    strSQL = "SELECT * FROM qryPSummaryforlistbox"
    
' create the IN string by looping thru the Lst_Pillars listbox
   For i = 0 To lst_pillars.ListCount - 1
      If lst_pillars.Selected(i) Then
        If lst_pillars.Column(0, i) = "All" Then
                flgAll = True
          End If
           strIN = strIN & "'" & lst_pillars.Column(0, i) & "',"
       End If
    Next i
     
     
    'create the WHERE string, stripping off the last comma of the IN
string
    strWhere = " WHERE [pillar name] in (" & Left(strIN, Len(strIN) -
1) & ")"
    
    'if "All" was selected, don't add the WHERE condition
    If Not flgAll Then
        strSQL = strSQL & strWhere
    End If
    
    
    
    MyDB.QueryDefs.Delete "qryLocalAuthority"
    Set qdf = MyDB.CreateQueryDef("qryLocalAuthority", strSQL)
    
    DoCmd.OpenReport "Project Reports-FS", acPreview


Exit_cmdRunReport_Click:
    Exit Sub
    
Err_cmdRunReport_Click:
    If Err.Number = 3265 Then   '*** if the error is the query is
missing
        Resume Next             '*** then skip the delete line and
resume on the next line
    ElseIf Err.Number = 5 Then
        MsgBox "You must make a selection"
        Resume Exit_cmdRunReport_Click
    Else
        MsgBox Err.Description      '*** write out the error and exit
the sub
        Resume Exit_cmdRunReport_Click
    End If

End Sub

---

Request for Question Clarification by hammer-ga on 23 Jul 2003 05:13 PDT

Which version of Access?

- Hammer