Possible to clone a reports recordset?

[markmarko]

After creating a report that summarizes a handful of orders, I'd like to make
a change to a certain field in those orders, and no others.

Originally, I was making the report based on a form (Sales Orders), so user
filtered the form (ala Allen Brown's method of changing the recordsource, not
actually a filter), and my VBZ would clone that forms recordset.

Now, I'm using a standalone form for the user to select the orders they
want, and it opens the report using those criteria as the filter (using the
filter property).

I need to now clone that reports recordset in order to apply that change to
each of those jobs. But my code errors out on this line...

Set rs = [Reports]![Qualifying].Recordset.Clone

The error message is "this feature is only available in ADP"

Any idea how I can clone a reports recordset in order to maniplulate the
records?

 

[Duane Hookom]

I don't think it is possible to clone a report's record source and I don't
think I would want to. I generally don't use a report other than to
display/publish information. Manipulating records should be done outside of
reports.

If you really want to do some updating, I would do so prior to opening the
report or after it has successfully printed.

 

[markmarko]

Well, yes, what I'm essentially trying to do is, after it's successfully
printed, I wanted to mark all the Sales Orders that were on the report to
indicate so. And yes, there is a reason to do this.

The problem I'm having is how to make a recordset of just those jobs, when
the report's recordset is made from a saved query with a filter applied? (The
saved query is the query used for our order form, and the filter is what is
used to select only the appropriate jobs for the report).

Perhaps I should instead have VBA make a new query, instead of using that
first query with a filter.

 

[markmarko]

Incidentally, this is how the report is opened...

DoCmd.OpenReport "Qualifying", acViewReport, "Query-SalesOrderForm",
WhereString

WhereString is concatenated to apply all the criteria to select the
appropriate jobs.

 

[Duane Hookom]

You could just add code to the On Print event of the SalesOrder header or
detail section that runs and update query in the table that marks the record
as printed. The report's record source could serve as the recordset.

I still don't like this solution. I would use the query and filtering to
create an update query.

 

[markmarko]

I would use the query and filtering to create an update query.

That sounds fine to me... Any advice on how to do this?

 

[Duane Hookom]

I don't know what field(s) in which table(s) you want to update to what
value(s) based on what criteria.

 

[markmarko]

Well, it's somewhat complicated to explain... I really only need the syntax
and general idea of how to do it. Specifically, how can I change the sql of a
saved query?

I can see from this article that queryDef objects have an sql property....

http://msdn.microsoft.com/en-us/library/bb208681.aspx

So suppose I have a saved query called "ActiveSalesOrders"... What would be
the syntax to set or return the sql property of it?

Specifically, how do refer to a saved queryDef? I can find alot of info on
how to create one from scratch, but nothing on refering to one that is
already saved.

This article(http://msdn.microsoft.com/en-us/library/bb177500.aspx), for
instance says
----------------------
To refer to a QueryDef object in a collection by its ordinal number or by
its Name property setting, use any of the following syntax forms:

QueryDefs(0)

QueryDefs("name")

QueryDefs![name]
------------------------------------------
However, this code returns an error of

Dim SalesOrderQueryString As String
SalesOrderQueryString = QueryDefs![ActiveSalesOrders].sql

What am I doing wrong?

And thank you very much for your help!

 

[Duane Hookom]

You will need to set a reference in a module to the DAO object library:

Dim SalesOrderQueryString As String
SalesOrderQueryString = CurrentDb.QueryDefs("ActiveSalesOrders").sql

This will retrun the SQL property of the saved query. You can dynamically
set the SQL property like:
CurrentDb.QueryDefs("ActiveSalesOrders").sql = "SELECT ...."

 

[markmarko]

Ahhhhh, I needed the currentdb. bit... That got it working.

Many thanks!