DoCmd.OpenReport Method

Question

Hello,

What's the difference between the FilterName, WhereCondition and OpenArgs?  I've seen posts where each one of those are used to assign a SQL statement.

I've created a temporary QueryDef using the CreateQueryDef method, and then set the SQL property to my query string which is listed below.  Then I used that QueryDef as the FilterName of the OpenReport method.  My SQL statement has a "WHERE" clause in it, and the OpenReport method has an optional WhereCondition parameter, which is very confusing.  Then I've seen posts that say you can assign the SQL statement to the OpenArgs parameter.

My problem is that I can't get the report to open using the filter that I specified by the SQL statement.  The report opens with the query that the report was created with.

The query statement:

"SELECT [Ancillary Training Query].Member," & _

"[Ancillary Training Query].[Training Event], [Ancillary Training Query].Type, *" & _

"From [Ancillary Training Query] WHERE ((([Ancillary Training Query].[Training Event])" & _

"<> 44 And ([Ancillary Training Query].[Training Event])" & _

"<> 49 And ([Ancillary Training Query].[Training Event])" & _

"<> 53 And ([Ancillary Training Query].[Training Event])" & _

"<> 54 And ([Ancillary Training Query].[Training Event])" & _

"<> 57 And ([Ancillary Training Query].[Training Event])" & _

"<> 58 And ([Ancillary Training Query].[Training Event])" & _

"<> 59 And ([Ancillary Training Query].[Training Event])" & _

"<> 60 And ([Ancillary Training Query].[Training Event])" & _

"<> 62 And ([Ancillary Training Query].[Training Event])" & _

"<> 63 And ([Ancillary Training Query].[Training Event])" & _

"<> 68) And (([Ancillary Training Query].Status) =" & """30 days or less""" & _

" Or ([Ancillary Training Query].Status) =" & """incomplete""" & _

" Or ([Ancillary Training Query].Status) =" & """Overdue""" & ")) " & _

"ORDER BY [Ancillary Training Query].Member;"))

The code to open a report using the CreateQueryDef method:

With dbs

    Set newQueryDef = .CreateQueryDef("")

    newQueryDef.SQL = queryString

    With newQueryDef

        Set rstExempt = .OpenRecordset(dbOpenSnapshot)

        DoCmd.OpenReport reportName, acViewReport, "newQueryDef", , acWindowNormal

    End With

    .Close

End With

Answer 1

Filtername refers to stored Filter basically the name of a query. Frankly I've never used it and am not sure how or if it works

WhereCondition is the same as a SQL WHERE clause without the WHERE keyword

OpenArgs is totally different, it simply passes a value to the object being opened. 

If you are generating a query in code using a QueryDef, then you are probably building your WHERE clause as part of that query in code. Save some steps and just buld the WHERE clause and pass that using the WHERECondition argument. That's how I always do it.

Another point, rather than a long set of ANDs, this would be easier by using:

strFilter = "([Training Event] NOT IN(44,49,53,54,57,58,59,60,62,63,68) AND " & _

                 "(Status ='30 days or less' " & _

                 "Or Status ='incomplete' " & _

                 "Or Status ='Overdue'" & ") "

Then use:

DoCmd.OpenReport reportName, acViewReport,, strFilter 

I don't see the neccessity of using Query Def unless you need to save the query for some other purpose and I see no reason for opening a Recordset at all.

Answer 2

Yes, when Access added the OpenArgs property to Reports (it was previously only available for forms), it was a major step to creating dynamic reports. While using a WHERE clause can usually be sufficient sometimes replacing the Recordsource is a necessity. If that is the case, you can just add the code I gave you in the WHERE clause and then pass the whole string in OpenArgs.

Answer 3

Thank you very much for your expertise.  I was trying to use code from the Access help, but your code is much simpler.  I found a post that explained how to assign the SQL statement string to the OpenArgs property, and then assign the OpenArgs property to the report's RecordSource within its Open event.  This configuration seems to work very well for dynamically setting the query criteria for the reports.

Dean Hornsby