Results 1 to 3 of 3

Display SELECT query results using QueryDef to screen

This is a discussion on Display SELECT query results using QueryDef to screen within the Microsoft Access forums, part of the Question Forums category; My query prompts for 3 fields and I want to use a Form to get this data from the User ...

  1. #1
    Board Regular
    Join Date
    May 2004
    Posts
    307

    Default Display SELECT query results using QueryDef to screen

    My query prompts for 3 fields and I want to use a Form to get this data from the User since one of the fields (Team) has 10 choices. Unfortunately I can't figure out the rest of the code below that would run the query and display the results to the screen just like using CurrentDb.Execute.

    I don't want to create a temp table form the results since I'm going to reuse this Form for many queries and their output field count is different.

    Set qdf = CurrentDb.QueryDefs(QueryName)
    qdf.Parameters(0) = Me.StartDate
    qdf.Parameters(1) = Me.EndDate
    qdf.Parameters(2) = Team

  2. #2
    Board Regular
    Join Date
    Oct 2006
    Location
    Ottawa, Canada
    Posts
    1,218

    Default Re: Display SELECT query results using QueryDef to screen

    Quote Originally Posted by pbassett View Post
    My query prompts for 3 fields and I want to use a Form to get this data from the User since one of the fields (Team) has 10 choices. Unfortunately I can't figure out the rest of the code below that would run the query and display the results to the screen just like using CurrentDb.Execute.

    I don't want to create a temp table form the results since I'm going to reuse this Form for many queries and their output field count is different.

    Set qdf = CurrentDb.QueryDefs(QueryName)
    qdf.Parameters(0) = Me.StartDate
    qdf.Parameters(1) = Me.EndDate
    qdf.Parameters(2) = Team
    See this example of query building using forms : http://www.fontstuff.com/access/acctut17.htm It may help or suggest other ways.
    Last edited by jackd; Dec 17th, 2009 at 09:27 PM.

  3. #3
    Board Regular
    Join Date
    May 2004
    Posts
    307

    Default Re: Display SELECT query results using QueryDef to screen

    Thanks! It took a bit of work but I can run any query that has the same prompts via this approach.

    Because the user's Team selection dictates which query to run, I first get the SQL from that query (stored in gQueryName), save it to a string (strSQL), replace the prompt texts in the string with the user selections, and jam that resulting SQL into a temporary query "Results" that does nothing but run the SQL I give it. Finally I run the query "Results".

    Set qdf = CurrentDb.QueryDefs(gQueryName)
    strSQL = qdf.SQL
    qdf.Close

    strSQL = Replace(strSQL, "[Enter Start Date]", "#" & Me.StartDate & "#")
    strSQL = Replace(strSQL, "[Enter End Date]", "#" & Me.EndDate & "#")
    strSQL = Replace(strSQL, "[Enter Team]", """" & Me.Team & """")
    Set qdf = CurrentDb.QueryDefs("Results")
    qdf.SQL = strSQL
    DoCmd.OpenQuery "Results"

    qdf.Close
    Set qdf = Nothing

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com