Output To Using Recordset or Filter

[SusanV]

Hi all,

I have a new user request which I can't seem to find an easy way to
accommodate. Currently users have a button they click and a report opens for
printing. The report is filtered to only show data for the current selected
record. This works wonderfully. However, now they need to output the data
for just the selected record to Excel.

I know I can use a stored parameter query, but I have a couple of users who
like to mess with my queries, and unfortunately, queries aren't protected
via MDE (user level security is NOT an option, as per management).

Is there a way to either filter an output to or use a recordsource for the
output?

If not i suppose i can dump the data to a temp table and use that for an
output source, but would prefer to avoid the database bloat...


TIA for any suggestions,

SusanV

 

[Sheila D]

You could alwayd hide the query - select the query and choose properties
button on toolbar then hide

HTH - Sheila

 

[SusanV]

Hi Sheila,

I already went this route before - and I have a couple of users who know
enough to change their options to show hidden using a different database
even though I hide the Tools>>Options in my MDE.

I'll just dump out the related records to a temp table and delete thetable
after, I have nightly compaction set up already anyways.

Thanks for your input!

Susan

 

[John Vinson]

Is there a way to either filter an output to or use a recordsource for the
output?

If not i suppose i can dump the data to a temp table and use that for an
output source, but would prefer to avoid the database bloat...

Create and save the Query, use it, and delete it:

Dim qd As DAO.Querydef
Dim db As DAO.Database
Dim prm As Parameter
Set db = CurrentDb
On Error Resume Next ' ignore error if there's nothing to delete
db.Querydefs.Delete "TempQuery"
On Error Goto 0 ' or to your error handler code
<create the SQL string for your filtered query>
Set qd = db.CreateQuerydef("TempQuery", strSQL)
For Each prm in qd.Parameters
prm.Value = Eval(prm.Name) ' fill in parameters
Next prm
DoCmd.TransferSpreadsheet...

John W. Vinson[MVP]

 

[SusanV]

Thanks John!

I tried to do this for an earlier issue and couldn't seem to get the
querydef created (I don't remember the error now).

I will definitely give your code a go when I get a few minutes to go back to
this project and let you know how it works out.

SusanV