Qualifier Must Be Collection

[hlock]

Access 2007 - VBA working last time I used it. Now I'm getting a compile
error - "Qualifier must be collection" where the filter is turned on. It's
highlighting from the exclamation point to the ] after the report name. I
haven't changed anything. When I step through it, the Report doesn't open at
the DoCmd.OpenReport which is odd. When I comment out the Filter line and
then step through, the report opens, but I get an error message later in
another function because the filter isn't on. What's going on? Any
suggestions?

Function cmdSendReport_Click()
On Error GoTo PROC_ERR

' Declare variables
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAcountStatus As String
Dim strEmail As String
Dim strUserID As String
Dim fOk As Boolean

' Build our SQL string
strSQL = "SELECT Examiner, Email2 From [Q: Get Email Addresses]"

' Set our database and recordset objects
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

' Open the report
DoCmd.OpenReport "R: $3 Reserve for Non-Supervisors", acViewPreview

'set filter on
Reports![R: $3 Reserve for Non-Supervisors].FilterOn = True

' Loop the recordset
Do While Not rst.EOF

' Grab the Email string
strEmail = rst.Fields("Email2")

' Grab the UserID string
strUserID = rst.Fields("Examiner")

' Call the procedure used to filter the report based on the Current employee
Call FilterReport("R: $3 Reserve for Non-Supervisors", strUserID)

' Allow the report to refresh after filtering
DoEvents

' Send the snapshot of the report to the current employee
fOk = SendReportByEmail("R: $3 Reserve for Non-Supervisors", strEmail)

' Display message if failure
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If

' Move and loop
rst.MoveNext
Loop

' Clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Function

 

[Tokyo Alex]

Hi,

Try:

Reports("R: $3 Reserve for Non-Supervisors").FilterOn = True

and see if it still fails. If so I'm afraid your problem is beyond my
ability to help. Both ways work in a quick test I just ran.

Alternatively (or as a workaround) you can set the report's "Filter on Load"
property to "Yes". In that case you don't need to turn it on from code,
because it the filter will be aplied whenever the report opens.

Cheers and HTH,
Alex.