Thanks, Doug, for responding. I am struggling with matching up the examples
to my criteria. Here is my complete code (including the portion that works
properly for generating the report using two multi-select boxes on the form).
What I want to do is be able to access the resulting query from this same
criteria selection so I can export it to Excel, etc. Please review the code
and my notes. I tried your suggested method and the you referenced in the
link, but I ran into the same problem - I don't understand what fields,
names, etc., are being referenced.
Thank you so much for your help. Here is my code with annotations...
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004.
http://allenbrowne.com
' SUSAN: This part works great!
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Multiple Criteria Report"
'Loop through the ItemsSelected in the list box.
With Me.FirstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With
With Me.SecondCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(0, varItem) & ""","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "([IND_ID] IN (" & Left$(strWhere, lngLen) & ")) AND
([STATE_ID] IN (" & Left$(strWhere, lngLen) & "))"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
' SUSAN: Here is the code for the "Run Query" command button on my form
Private Sub cmdquy_Click()
On Error GoTo Err_cmdquy_Click
Dim stDocName As String
stDocName = "Multiple Criteria Output"
DoCmd.OpenQuery stDocName, acNormal, acEdit
strSQL = "Select * from FirstCategory where [IND_ID] In ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem
strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
' SUSAN:
' What I want to do is replicate the selection process that generated
the report
' and send it to a query as well for exporting. So I need to add the
additional
' criteria (additional multi-select box/es).
' Currently, I have one command button set up to run the report and
another to
' run the query - is there any easier way or is it best to keep the
seperate?
' Regardless, the query is not running correctly. Any help would be most
' appreciated!!!
Exit_cmdquy_Click:
Exit Sub
Err_cmdquy_Click:
MsgBox Err.Description
Resume Exit_cmdquy_Click
End Sub
Douglas J. Steele said:
Check
http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for
one approach, although to be honest I prefer to use:
strSQL = "Select * from Employees where [EmpID] In ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem
strSQL=Left$(strSQL,len(strSQL)-2)) & ")"
Note that if the field is text, you need to include quotes around the value.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
SusanForson said:
I successfully set up a form to use two multi-select list boxes to generate
a
report using the Northwind example posted for previous inquiries. I would
also like to be able to preserve the query generated in this process so I
can
export the query to Excel (or just generate the query and not the report).
I tried setting up a query to read the form, but am not getting the
criteria
set up correctly to read multiple choices.
Any suggestions? I need help with writing the code.
