DoCmd.OutputTo acReport, stDocName

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

Using vb how would I make the stDocName a query only in code?

vSql = "select distinct to_cont from viaware_cm_f where wave = """ & vWave &
""""
Set db = CurrentDb()
Set rs = db.OpenRecordset(vSql)

stDocName = "vsql"
DoCmd.OutputTo acReport, stDocName
 
That is not going to work the way you are trying to do it. stDocName is the
name of the report to open. I think what you are trying to do is define the
Where criteria for the report. The correct way to do this would be to build
a query for the report with no criteria at all. Then in your code, build
only the Where part without the word where or the closing ;

Dim strWhere as String
Dim stDocName as String

strWhere = "wave = '" & vWave & "'"
strDocName = "MyReportName"
DoCmd.OpenReport stDocName, , , strWhere
 
Thanks for the info.

This answers a lot of question I have. I didn't know you could send the
"Where" clause to a query via vb.
 
You are not sending it to the query. You are sending it to the report and
the report does it for you. Now, here is a cute trick for modifying a query
using VBA. Let's say you have a query that you will want to modify or add
the where criteria programmatically. All queries are stored as an SQL
string. If you switch to SQL view in the query builder, you will see exactly
what is stored. So, the approach is to create a query with no criteria. The
you can add it in VBA by getting the SQL into a varialbe and adding the
criteria:

Dim rst As RecordSet
Dim strWhere As String
Dim strSQL As String

strSQL = CurrentDb.QueryDefs("MyQueryName").SQL
strWhere = " WHERE CLOWN_NAME = '" & Me.cboClown & "';"
'Here we put the two strings together and make sure we keep the ending ; in
place
strSql = Replace(strSQL, ";", strWhere)
Set rst = CurrentDb.OpenRecordset(strSQL)
 
Back
Top