running a query created in code

  • Thread starter Thread starter Paul Mars
  • Start date Start date
P

Paul Mars

strWhere = "SELECT EmailAddress FROM Main WHERE Name =
'Forms!MainData.Filter'"

Now how do I output it??

DoCmd.OutputTo acOutputQuery, strWhere, acFormatRTF, "Email.rtf", True
Looks for a query with the name strWhere, which will not work.

Related question, why can I never find these things in help?
 
You need to store the query as a temporary query, or write the results of
the query to a table and then export the table. We'll focus on the first
option in this example.

Here is code to create and save a query, use it for your routine, and then
delete it when you're routine is done:


Dim strWhere As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDefs

strWhere = "SELECT EmailAddress FROM Main WHERE [Name] = '" & _
"Forms!MainData.Filter'"
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("NewQueryName", strWhere )

DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatRTF, "Email.rtf", True

qdf.Delete
Set qdf = Nothing
dbs.Close
Set dbs = Nothing


Note that it's not a good idea to use Name as the name of a field, etc. See
this Knowledge Base article for more information:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335
 
Back
Top