Error 2585 This action cannot be carried out while processing a form or report event.

[cindygo]

Hi, thanks in advance for any help on this.

I need to use a multi-select list box to filter a report. I would like to run the report, have the form display to enter criteria, select a preview bottom that would then display the filtered report.

Here is what I have done.
I created an unbound form with a multi-select list box. The preview bottom On Click Event code is as the below. (I got this off the web and modified it).

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
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 = "Products by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
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

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] 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

This works perfectly it opens the report filtered. Since this is working I go to the report and add the following to the On Open & On Close Event.
Private Sub Report_Close()
DoCmd.Close acForm, "Report Category"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Category", , , , , acDialog, "Product by Category"
If Not IsLoaded("Report Category") Then
Cancel = True
End If
End Sub
Test: Open the report, the form is displayed, I select one or many items, click preview and receive: Error 2585 This action cannot be carried out while processing a form or report event. I understand that the form should be closed but I can figure out how to close it to eliminate this error.

Anyone have any ideas? Thanks! Cindy

 

[xenou]

Wouldn't you just open the form as the "interface" for setting the report parameters and opening the report? I don't see the point of this code in the report open event - why do you need it?

Otherwise it's an error I've never seen before. Most posts I can find about it suggests that it crops up when you try to do certain kinds of things from certain events. But I can't really tell what you want to do here - open the form if you open the report but cancel it if the form doesn't load, so you can open the report (?). Maybe I'm tired...

 

[cindygo]

Good question, I didn't provide all the information. I agree with you about using the form as the interface, that was my first thought but the department is using an inventory template, http://office.microsoft.com/en-us/templates/CT010142603.aspx?tl=2#ai:TC001018458|, which has a View Reports feature. When creating the requested report, the one in this thread, I was following the other examples in the report list. When I added the “form name” to the report list and tried running it from View Reports that code didn’t recognize the “form name”. Make sense? This is the kind of troubles someone runs into when they know just enough but NOT enough.

Regardless I came up with a workaround. I removed the code from the On Open & On Close Event from the “Products by Category” report and added it to a same events in a blank report. I then added this new blank report to the report list and it works, I don’t know why but I’m going with that solution for now.

Thanks for your input.

 

[xenou]

I see. Possibly another approach would be to edit the View Reports so that if the selected report is this one, it opens your form rather than the actual report (the user then selects criteria and opens the report from the form). But your blank form sounds like a decent patch up job - you're just using that blank report as a stand in for your form in the reports list.