Display SELECT query results using QueryDef to screen

pbassett

Active Member
Joined
May 5, 2004
Messages
358
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top