Displaying a temporary recordset

[Dan]

I have a form which allows the user to select various options, which
options I then use to dynamically generate a query into the database.

I then want to display this query to the user. However, no matter
which way I go about it, I seem to run into a snag. Currently, I'm
doing this:

Dim ReportQuery As String
'Code to generate dynamic query

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef "CreateReportQry", ReportQuery
qdf.OpenRecordset

DoCmd.OpenQuery "CreateReportQry", , acReadOnly

This works once but fails if run a second time because a query named
"CreateReportQry" already exists. I can modify the CreateQueryDef
function call to use "" as the name and create a temp query. However,
if I do that I can't find a way to use the OpenQuery function because
the query doesn't have a name to pass to the function. I've tried
deleting the record set at the end of the function but that gives me an
error "Object variable or with block variable not set" I'm not
positive but I suspect it may be because I'm trying to delete the
record set while the query is still being displayed. I'd try deleting
the record set when the query form is closed, but it's a dynamic query
and I can't find a way to trap the form close event. There's probably
a very simple way to do this but I can't seem to track it down. Any
assistance would be greatly appreciated.

[Br]

Dan <da*********@gmail.com> wrote:

I have a form which allows the user to select various options, which
options I then use to dynamically generate a query into the database.

I then want to display this query to the user. However, no matter
which way I go about it, I seem to run into a snag. Currently, I'm
doing this:

Dim ReportQuery As String
'Code to generate dynamic query

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef "CreateReportQry", ReportQuery
qdf.OpenRecordset

DoCmd.OpenQuery "CreateReportQry", , acReadOnly

This works once but fails if run a second time because a query named
"CreateReportQry" already exists.
You should be testing that it exists and delete it if it does...
I can modify the CreateQueryDef
function call to use "" as the name and create a temp query. However,
if I do that I can't find a way to use the OpenQuery function because
the query doesn't have a name to pass to the function.
Depends how you want to use the recordset.....
I've tried
deleting the record set
You should be deleting the query, not the recordset.....
at the end of the function but that gives me
an error "Object variable or with block variable not set" I'm not
positive but I suspect it may be because I'm trying to delete the
record set while the query is still being displayed.
As above....
I'd try deleting
the record set when the query form is closed, but it's a dynamic query
and I can't find a way to trap the form close event. There's probably
a very simple way to do this but I can't seem to track it down. Any
assistance would be greatly appreciated.

CurrentDB.QueryDefs.Delete "CreateReportQry"

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Salad]

Dan wrote:

I have a form which allows the user to select various options, which
options I then use to dynamically generate a query into the database.

I then want to display this query to the user. However, no matter
which way I go about it, I seem to run into a snag. Currently, I'm
doing this:

Dim ReportQuery As String
'Code to generate dynamic query

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef "CreateReportQry", ReportQuery
qdf.OpenRecordset

DoCmd.OpenQuery "CreateReportQry", , acReadOnly

This works once but fails if run a second time because a query named
"CreateReportQry" already exists. I can modify the CreateQueryDef
function call to use "" as the name and create a temp query. However,
if I do that I can't find a way to use the OpenQuery function because
the query doesn't have a name to pass to the function. I've tried
deleting the record set at the end of the function but that gives me an
error "Object variable or with block variable not set" I'm not
positive but I suspect it may be because I'm trying to delete the
record set while the query is still being displayed. I'd try deleting
the record set when the query form is closed, but it's a dynamic query
and I can't find a way to trap the form close event. There's probably
a very simple way to do this but I can't seem to track it down. Any
assistance would be greatly appreciated.

This may or may not be what you need

Dim rstFilter As Recordset
Dim rst As Recordset
Set rst = qdf.OpenRecordset
rst.Filter = "ID > 1 and ID < 1000"
rst.Sort = "ID Desc"
Set rstFilter = rst.OpenRecordset