Display SELECT query results using QueryDef to screen

[pbassett]

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

 

[jackd]

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.

 

[pbassett]

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