open a report using VBA from dynamic query?

[haggisns]

Hi,

I have a form that contains drop down boxes to capture parameters values chosen by the user.



The form has a command button with VB code to execute the query.
It reads values from the dropdown combo boxes and dynamically builds sqlstring.

Here is last bit of code.

strSQL = "SELECT dbo_serverlist.* " & _
"FROM dbo_serverlist " & _
"WHERE dbo_serverlist.server_role" & str_serverrole & _
"AND dbo_serverlist.server_type" & str_servertype & _
"AND dbo_serverlist.server_host" & str_serverhost & _
"AND dbo_serverlist.OS" & str_serverOS & _
"AND dbo_serverlist.program" & str_program & _
"AND dbo_serverlist.project" & str_project & _
"AND dbo_serverlist.line_of_bussiness" & str_LOB & _
"AND dbo_serverlist.manager" & str_manager & _
"ORDER BY dbo_serverlist.server_name,dbo_serverlist.server_f unction;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrycustomserverlist") = acObjStateOpen Then
DoCmd.Close acQuery, "qrycustomserverlist"
End If
' Open the query
DoCmd.OpenQuery "qrycustomserverlist"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing

How do I take this and point the result set to already existing report that does not have a data query bound to it yet. Such that it will accept the query at run time and open up populated.

Is there any special settings for the report that I would need to build before hand?

Thanks

Haggisns

[weekend00]

You may bind the report with a query (e.g. "Query2000"), then you change the query in your code:

currentdb.querydefs("Query2000").sql= strSQL
docmd.openreport .....

[haggisns]

Hi Weekend00,

Thanks for your response.

What would the syntax be to connect the report to the newly created query.
Your statements don't seem to relate the two together.

Also, I have put in my report field textboxes for the source the name of the fields that will be used. When the report is opened will the querydefs feed into the textboxes the field values from the recordset "automagically"?

thanks

haggisns

[weekend00]

the report connects to query "query2000" when you design the report, you don't change the report and the query name, only change the SQL of the query. new SQL must have same field name with the original one. then open the report.