Results 1 to 4 of 4
  1. #1
    haggisns is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    9

    Question open a report using VBA from dynamic query?

    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

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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 .....

  3. #3
    haggisns is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    9

    Question

    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

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamic Report Caption
    By Crypto in forum Reports
    Replies: 3
    Last Post: 11-17-2010, 03:17 PM
  2. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  3. Dynamic Report
    By vCallNSPF in forum Reports
    Replies: 0
    Last Post: 12-08-2009, 04:19 PM
  4. Dynamic Query
    By pushpm in forum Queries
    Replies: 0
    Last Post: 04-22-2009, 12:58 PM
  5. Dynamic SQL Query
    By Squeaner in forum Queries
    Replies: 0
    Last Post: 09-25-2008, 02:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums