Open Select Parameter Query as DATASHEET From VBA?

[Micron]

This seems like it should be so simple, yet I have not found a solution anywhere. Using VBA, I want to pass 2 parameters to a stored select query and open a datasheet. Using VBA, I can pass parameters to stored action queries, or to sql created in code, or if they are select queries that create recordsets. What I can't figure out is how to pass them to a stored query and open a datasheet. I suppose I could create a continuous form and set its record source to a recordset, but presenting the records in datasheet format is all the user needs. It should be so simple to do. What am I missing?


Thanks.

[Micron]

I have solved my problem without having an answer to the question. I didn't create the database, so I delved into the underlying parameter query to see what was being used by the query I was trying to run from code. Turns out it was only one field, which I could get from the same table as the underlying query. I re-designed the second query's data sources and referenced the form's two textbox controls for the criteria. Docmd.OpenQuery is all that's needed to open a datasheet. Removing the parameters from the underlying query wasn't an option.

If anyone knows how to pass parameters to a select query and open it in datasheet view from VBA, I'd still love to know.

[June7]

Users should not interact with tables and queries, only forms and reports.

I don't use dynamic parameterized queries. I prefer: http://www.allenbrowne.com/ser-62.html

VBA can modify query objects by manipulating QueryDefs. But I don't recommend this as a routine practice, although I have resorted to in one case.

[Micron]

I'm aware of the design elements in the link you sent but I still thank you for your input. I'm not sure I see a need to go to those lengths in this case, nor can I dicker with the query def sql since the parameters are in an underlying query. Also, the user is using a form to supply the query criteria in the typical manner used by mostly everyone out there. This means they are interacting with a query, so I'm not sure what you mean by your statement. I think the desired result, how it is used as well as other considerations need to be factors in design. Either the form or datasheet view can present records that are updatable by the user if design limits are not used to prevent such interaction. In my case, the ODBC tables used are read-only, so there is no concern for the type of interaction I think you are referring to. I just wanted a quick and simple method to present non-editable, temporary data for decision making purposes without having to do it in a form. I may end up with one anyway, but if anyone knows how to show parameter query results in datasheet view, I'd still like to know for future reference.
Thanks.

[June7]

The OpenQuery method is the only way I know to open a SELECT query object.

Many developers use code behind forms to manage data integrity so allowing users to interact directly with editable tables and queries could corrupt data. Apparently that is not an issue in your case and seems users are comfortable with viewing records in table/query datasheet.

[Micron]

One nice thing about a datasheet is the ability to apply a filter or alter the sort by clicking on the header. I've done this in forms, but it's just more coding. I lament the apparent inability to pass parameters to a stored select query and open in datasheet view!

[June7]

The header filter/sort can also be utilized in a form or subform set for Datasheet view.

[ItsMe]

I don't understand what the problem is with passing a literal string to a form's RecordSource and opening the form in DS view.

[Micron]

I don't understand what the problem is with passing a literal string to a form's RecordSource and opening the form in DS view.

There is no problem except there is no form (yet).