Docmd.OpenQuery (1 Viewer)

Is there a way to call

Docmd.OpenQuery

on a parameter query and pass the parameter in instead of having the user type it in?


Or even better is there a way to take a DAO recordset and just put it on the screen for the user?

Here is the reason why I ask. I want to test to see if the query returns results or not so i use an input box to get the parameter from the user. Then I set up a DAO QueryDef and set qdf.Parameter to the value from the input box. I can then test to see if the recordset returns any results or not as rst.RecordCount. This is all fine and dandy. If I get zero for an answer I can tell the user there are no results. But if I get >0 then I want to show the resulting recordset to the user. I don't want to call DoCmd.OpenQuery because that will just prompt the user for the parameter again. I'm trying to avoid that.

Any suggestions? I want to either open the query with the parameter passed in or just take the recordset I already have and shoe it to the user.

Assign the inputbox to a variable and set the parameter to that variable
OR
create a dynamic SQL and run that.

Fizzio--You said here that displaying the recordset is not a problem. If you can tell us how to display this DAO recordset, then the problem goes away--we can just display it after the inputbox gathers the variable, and after we test the recordset to make sure it's returning > 0 records.

So, is there a way to do that? Unlike in your previous post above, it is not necessary (or in fact desirable) to be able to edit the recordset--we just need to be able to SHOW the thing!

Thanks--

The recordset I was referring to there was that produced by a query, not a DAO recordset!
You can display the recordset, but not in the way that you normally populate a form. You can display the records in a listbox / combobox easily by looping through the recordset and adding items to the listbox rowsource but it would take a fair bit coding to display the records as you normally would in a form.

vba,

I would base the form on a query, with the criteria being a
combobox, or whatever.

On the AfterUpdate of the combo, I'd use a DLookup (or DCount)
to see how many records. Then I'd requery or MsgBox that there
are no matching records.

Not anything to write home about performance-wise, but easy
to implement.

Wayne

Here's an example of passing parms to a DAO recordset.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryQCMainSizeCheck
qd.Parameters![YourParm1] = "SomeValue"
qd.Parameters![YourParm2] = "SomeValue"
Set rst = qd.OpenRecordset

I get at where you are all coming from. It just seems so odd to me that there are no provisions within Access for something that seems really, really straightforward. That is:

taking a series of pre-compiled Access queries with parameters
passing in the parameters
checking for records
showing the results to the user


It's even easy to write and comprehend. Yet the solution requires:

a) cutting and pasting the SQL text of dozens of queries, thereby making them dynamic instead of compiled, or

b) building a temporary table with a form based on it (would this even work if the queries returned different fields), or

c) creating a table with query name and SQL text, and an elaborate coding scheme to run and display results.

Yet I read here so often about the performance gains of pre-compiled queries over SQL-in-code. An exhaustive search of this forum yielded over 30 examples of opening parameter pre-compiled queries yet not a single one involved letting the user see the complete result of the query!

Just seems odd. But the voices have spoken. So it's back to the drawing board.

cutting and pasting the SQL text of dozens of queries, thereby making them dynamic instead of compiled

Click to expand...

- where did you get that from my example? I NEVER use dynamic SQL unless it cannot be avoided.

building a temporary table with a form based on it (would this even work if the queries returned different fields)

Click to expand...

- where did this idea come from?

Yet I read here so often about the performance gains of pre-compiled queries over SQL-in-code. An exhaustive search of this forum yielded over 30 examples of opening parameter pre-compiled queries yet not a single one involved letting the user see the complete result of the query!

Click to expand...

- for one simple reason - it is POOR PRACTICE to present the user with a "raw" recordset. You have no control over anything because there are no events where you can hang code. It is much better to present the user with a form. Use a DataSheet style form if you want it to look like a "raw" query.

When you use a query as the recordsource for a form, you don't even need parameters in the query, you can use the where argument of the OpenForm method to "filter" the recordset. You can use user-defined functions, prompting, form fields, or literal values to supply the parameters. I really don't understand what your problem is. If you don't want the user to be prompted multiple times for the parameters, prompt once with VBA rather than from the query itself.

The resolution to this very civilly-toned discussion can be found here. Pat, I think what VBAhole22 was getting at was that there are multiple, complex solutions to what should be a simple problem, but he didn't necessarily get all or any of them from your post.

Thanks for everyone's help, it eventually paid off!