VBA to open report on a recordset 
Author Message
 VBA to open report on a recordset

What I need to do is a bit more complex, but here goes:

(Note all in VBA Access97)

I have got a recorset which is originally derived from a query, but it has
had certain records adjusted, deleted etc. based on rules within the VBA,
too complicated to put into the query. I now need to open a report I have
based on this record set (not the query). Is there a way without copying the
data to a temporary table to set the RecordSource (or whatever) of the
report to my recordset?

Thanks in advance
Paul Higgs



Mon, 12 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset
The only way I can see it works is to use the initial query as the recordset
but use VBA to fill or skip the record.  Take a look at the Solutions
database, there's a report based on a crosstab query that used this method
to fill the text box.

The temp table solution is gonna be a lot faster I can tell you, I even saw
a case where using a temp table cut the running time of a process from over
an hour to less than 2 minutes.  And as a bonus, there's no programming
involved!

HTH

RB

Quote:

>What I need to do is a bit more complex, but here goes:

>(Note all in VBA Access97)

>I have got a recorset which is originally derived from a query, but it has
>had certain records adjusted, deleted etc. based on rules within the VBA,
>too complicated to put into the query. I now need to open a report I have
>based on this record set (not the query). Is there a way without copying
the
>data to a temporary table to set the RecordSource (or whatever) of the
>report to my recordset?

>Thanks in advance
>Paul Higgs




Mon, 12 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset
in the onopen or loan event of the report, set the recordsource of the
report to rs.name.  Dev's site has an example:
http://home.att.net/~dashish/

Keep in mind that in this case, the SQL string is limited in size to the max
in the rs's name property...


Quote:
> What I need to do is a bit more complex, but here goes:

> (Note all in VBA Access97)

> I have got a recorset which is originally derived from a query, but it has
> had certain records adjusted, deleted etc. based on rules within the VBA,
> too complicated to put into the query. I now need to open a report I have
> based on this record set (not the query). Is there a way without copying
the
> data to a temporary table to set the RecordSource (or whatever) of the
> report to my recordset?

> Thanks in advance
> Paul Higgs




Mon, 12 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset
Are you under the impression that this will give the
filtered version that the actual recordset has?

If so, you should correct yourself as the impression is
incorrect.

--

Michael

-------------------------------
random junk of dubious value is at:
http://www.trigeminal.com



Quote:
> in the onopen or loan event of the report, set the
recordsource of the
> report to rs.name.  Dev's site has an example:
> http://home.att.net/~dashish/

> Keep in mind that in this case, the SQL string is limited
in size to the max
> in the rs's name property...



> > What I need to do is a bit more complex, but here goes:

> > (Note all in VBA Access97)

> > I have got a recorset which is originally derived from a
query, but it has
> > had certain records adjusted, deleted etc. based on

rules within the VBA,
Quote:
> > too complicated to put into the query. I now need to

open a report I have

- Show quoted text -

Quote:
> > based on this record set (not the query). Is there a way
without copying
> the
> > data to a temporary table to set the RecordSource (or
whatever) of the
> > report to my recordset?

> > Thanks in advance
> > Paul Higgs




Mon, 12 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset
Hi mike,

I am not sure what you mean about filtered, I read the part below and know
that this can be done, I have populated a report from a recordset using the
rs.name property, which is the SQL string used to create the report.  The
string can be verified by:

msgbox rs.name

Whether the rs needs to be recreated, I would have to try it out.  Now,
after a second read, I realize that the recordset may not be doing actual
updates to the data tables, in which case I could be wrong.  I did not read
that in my initial review of the post...Nonetheless, I conceed to your
expertise.  If you say it cannot be done, it probably can't be done.

"...I now need to open a report I have
based on this record set (not the query). Is there a way without copying the
data to a temporary table to set the RecordSource (or whatever) of the
report to my recordset?..."


Quote:
> Are you under the impression that this will give the
> filtered version that the actual recordset has?

> If so, you should correct yourself as the impression is
> incorrect.

> --

> Michael

> -------------------------------
> random junk of dubious value is at:
> http://www.trigeminal.com



> > in the onopen or loan event of the report, set the
> recordsource of the
> > report to rs.name.  Dev's site has an example:
> > http://home.att.net/~dashish/

> > Keep in mind that in this case, the SQL string is limited
> in size to the max
> > in the rs's name property...



> > > What I need to do is a bit more complex, but here goes:

> > > (Note all in VBA Access97)

> > > I have got a recorset which is originally derived from a
> query, but it has
> > > had certain records adjusted, deleted etc. based on
> rules within the VBA,
> > > too complicated to put into the query. I now need to
> open a report I have
> > > based on this record set (not the query). Is there a way
> without copying
> > the
> > > data to a temporary table to set the RecordSource (or
> whatever) of the
> > > report to my recordset?

> > > Thanks in advance
> > > Paul Higgs




Mon, 12 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset
There is never any good reason to use the rs.Name property
as opposed to getting the querydef from the rs and using its
.SQL property.... as it avoids the name limitations, for
example.

The fact that the rs.Name property will return the very
original query on which the rs is based is my reason for
believing that the advice has nothing to do with helping the
problem that the original posts refers to (he did state he
did not want the original query). Can you show me any such
case where your suggestion does not give him exactly that?

--

Michael

-------------------------------
random junk of dubious value is at:
http://www.trigeminal.com



Quote:
> Hi mike,

> I am not sure what you mean about filtered, I read the
part below and know
> that this can be done, I have populated a report from a
recordset using the
> rs.name property, which is the SQL string used to create
the report.  The
> string can be verified by:

> msgbox rs.name

> Whether the rs needs to be recreated, I would have to try
it out.  Now,
> after a second read, I realize that the recordset may not
be doing actual
> updates to the data tables, in which case I could be

wrong.  I did not read

- Show quoted text -

Quote:
> that in my initial review of the post...Nonetheless, I
conceed to your
> expertise.  If you say it cannot be done, it probably
can't be done.

> "...I now need to open a report I have
> based on this record set (not the query). Is there a way
without copying the
> data to a temporary table to set the RecordSource (or
whatever) of the
> report to my recordset?..."


in message

> > Are you under the impression that this will give the
> > filtered version that the actual recordset has?

> > If so, you should correct yourself as the impression is
> > incorrect.

> > --

> > Michael

> > -------------------------------
> > random junk of dubious value is at:
> > http://www.trigeminal.com



> > > in the onopen or loan event of the report, set the
> > recordsource of the
> > > report to rs.name.  Dev's site has an example:
> > > http://home.att.net/~dashish/

> > > Keep in mind that in this case, the SQL string is
limited
> > in size to the max
> > > in the rs's name property...



> > > > What I need to do is a bit more complex, but here
goes:

> > > > (Note all in VBA Access97)

> > > > I have got a recorset which is originally derived
from a
> > query, but it has
> > > > had certain records adjusted, deleted etc. based on
> > rules within the VBA,
> > > > too complicated to put into the query. I now need to
> > open a report I have
> > > > based on this record set (not the query). Is there a
way
> > without copying
> > > the
> > > > data to a temporary table to set the RecordSource
(or
> > whatever) of the
> > > > report to my recordset?

> > > > Thanks in advance
> > > > Paul Higgs




Mon, 12 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset
I am assuming that he might have meant he had done filters
or other changes that make the rs different that the
original query. Or maybe he had a different sort. Or maybe
he did not want to run the query again?

In any case rs.CopyQueryDef gets you a querydef, and qdf.SQL
gets you the full sql statement with no size
restrictions.....

--

Michael

-------------------------------
random junk of dubious value is at:
http://www.trigeminal.com


Quote:
> Hi Michael,

> I was the source for that rs.Name post on Dev's site. The
question
> there amounted to, "I am given a DAO recordset. Is there
any way to
> base a report on it?" Since not too many folks knew about
the Name
> property, I guess the answer was interesting. Of couse, in
most cases
> you would be better off using the original source of the
recordset, if
> you knew it, as the recordsource of the report.

> What bugs me about this thread is that the opening message
referred to
> "a recorset which is originally derived from a query, but
it has
> had certain records adjusted, deleted etc. based on rules
within the
> VBA". It seems to me that basing a report on the original
query would
> now deliver the latest data, reflecting the changes that
were made by
> that VBA code.

>  -- Andy



> >There is never any good reason to use the rs.Name
property
> >as opposed to getting the querydef from the rs and using
its
> >.SQL property.... as it avoids the name limitations, for
> >example.

> >The fact that the rs.Name property will return the very
> >original query on which the rs is based is my reason for
> >believing that the advice has nothing to do with helping
the
> >problem that the original posts refers to (he did state
he
> >did not want the original query). Can you show me any
such
> >case where your suggestion does not give him exactly
that?

> >--

> >Michael

> >-------------------------------
> >random junk of dubious value is at:
> >http://www.trigeminal.com



> >> Hi mike,

> >> I am not sure what you mean about filtered, I read the
> >part below and know
> >> that this can be done, I have populated a report from a
> >recordset using the
> >> rs.name property, which is the SQL string used to
create
> >the report.  The
> >> string can be verified by:

> >> msgbox rs.name

> >> Whether the rs needs to be recreated, I would have to
try
> >it out.  Now,
> >> after a second read, I realize that the recordset may
not
> >be doing actual
> >> updates to the data tables, in which case I could be
> >wrong.  I did not read
> >> that in my initial review of the post...Nonetheless, I
> >conceed to your
> >> expertise.  If you say it cannot be done, it probably
> >can't be done.

> >> "...I now need to open a report I have
> >> based on this record set (not the query). Is there a
way
> >without copying the
> >> data to a temporary table to set the RecordSource (or
> >whatever) of the
> >> report to my recordset?..."


wrote
> >in message

> >> > Are you under the impression that this will give the
> >> > filtered version that the actual recordset has?

> >> > If so, you should correct yourself as the impression
is
> >> > incorrect.

> >> > --

> >> > Michael

> >> > -------------------------------
> >> > random junk of dubious value is at:
> >> > http://www.trigeminal.com


in
> >> > message


- Show quoted text -

Quote:
> >> > > in the onopen or loan event of the report, set the
> >> > recordsource of the
> >> > > report to rs.name.  Dev's site has an example:
> >> > > http://home.att.net/~dashish/

> >> > > Keep in mind that in this case, the SQL string is
> >limited
> >> > in size to the max
> >> > > in the rs's name property...



> >> > > > What I need to do is a bit more complex, but here
> >goes:

> >> > > > (Note all in VBA Access97)

> >> > > > I have got a recorset which is originally derived
> >from a
> >> > query, but it has
> >> > > > had certain records adjusted, deleted etc. based
on
> >> > rules within the VBA,
> >> > > > too complicated to put into the query. I now need
to
> >> > open a report I have
> >> > > > based on this record set (not the query). Is
there a
> >way
> >> > without copying
> >> > > the
> >> > > > data to a temporary table to set the RecordSource
> >(or
> >> > whatever) of the
> >> > > > report to my recordset?

> >> > > > Thanks in advance
> >> > > > Paul Higgs




Mon, 12 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset
Hi Michael,

I was the source for that rs.Name post on Dev's site. The question
there amounted to, "I am given a DAO recordset. Is there any way to
base a report on it?" Since not too many folks knew about the Name
property, I guess the answer was interesting. Of couse, in most cases
you would be better off using the original source of the recordset, if
you knew it, as the recordsource of the report.

What bugs me about this thread is that the opening message referred to
"a recorset which is originally derived from a query, but it has
had certain records adjusted, deleted etc. based on rules within the
VBA". It seems to me that basing a report on the original query would
now deliver the latest data, reflecting the changes that were made by
that VBA code.

 -- Andy

Quote:

>There is never any good reason to use the rs.Name property
>as opposed to getting the querydef from the rs and using its
>.SQL property.... as it avoids the name limitations, for
>example.

>The fact that the rs.Name property will return the very
>original query on which the rs is based is my reason for
>believing that the advice has nothing to do with helping the
>problem that the original posts refers to (he did state he
>did not want the original query). Can you show me any such
>case where your suggestion does not give him exactly that?

>--

>Michael

>-------------------------------
>random junk of dubious value is at:
>http://www.trigeminal.com



>> Hi mike,

>> I am not sure what you mean about filtered, I read the
>part below and know
>> that this can be done, I have populated a report from a
>recordset using the
>> rs.name property, which is the SQL string used to create
>the report.  The
>> string can be verified by:

>> msgbox rs.name

>> Whether the rs needs to be recreated, I would have to try
>it out.  Now,
>> after a second read, I realize that the recordset may not
>be doing actual
>> updates to the data tables, in which case I could be
>wrong.  I did not read
>> that in my initial review of the post...Nonetheless, I
>conceed to your
>> expertise.  If you say it cannot be done, it probably
>can't be done.

>> "...I now need to open a report I have
>> based on this record set (not the query). Is there a way
>without copying the
>> data to a temporary table to set the RecordSource (or
>whatever) of the
>> report to my recordset?..."


>in message

>> > Are you under the impression that this will give the
>> > filtered version that the actual recordset has?

>> > If so, you should correct yourself as the impression is
>> > incorrect.

>> > --

>> > Michael

>> > -------------------------------
>> > random junk of dubious value is at:
>> > http://www.trigeminal.com



>> > > in the onopen or loan event of the report, set the
>> > recordsource of the
>> > > report to rs.name.  Dev's site has an example:
>> > > http://home.att.net/~dashish/

>> > > Keep in mind that in this case, the SQL string is
>limited
>> > in size to the max
>> > > in the rs's name property...



>> > > > What I need to do is a bit more complex, but here
>goes:

>> > > > (Note all in VBA Access97)

>> > > > I have got a recorset which is originally derived
>from a
>> > query, but it has
>> > > > had certain records adjusted, deleted etc. based on
>> > rules within the VBA,
>> > > > too complicated to put into the query. I now need to
>> > open a report I have
>> > > > based on this record set (not the query). Is there a
>way
>> > without copying
>> > > the
>> > > > data to a temporary table to set the RecordSource
>(or
>> > whatever) of the
>> > > > report to my recordset?

>> > > > Thanks in advance
>> > > > Paul Higgs




Tue, 13 Nov 2001 03:00:00 GMT  
 VBA to open report on a recordset

Quote:
>In any case rs.CopyQueryDef gets you a querydef, and qdf.SQL
>gets you the full sql statement with no size
>restrictions.....

Not in any case. CopyQueryDef only works when the recordset was
originally opened on a querydef object. If you use db.openrecordset,
which is most common, then CopyQueryDef won't work. But I agree that
in those cases where it does work, it is better.

 -- Andy



Wed, 14 Nov 2001 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Opening recordsets(OpenRecordset vs. Recordset.Open)

2. Opening a query as a recordset using VBA Code

3. Newbie: Connecting Report to open recordset?

4. OT: Syntax for opening a Report (.rpt File) in VBA

5. Opened Recordsets don't show up in Recordsets Collection

6. Recordset Experts...SQL statement on an open recordset?

7. Open ADO recordset on another ADO recordset - possible?

8. Open a recordset of another recordset

9. open recordset, change connection, update recordset?

10. ADO fails to release Recordset cursor after opening disconnected Recordset

11. Opening a File Open window for file selection in VBA

12. Using VBA Automation to Open Word at the File Open Locatiion

 

 
Powered by phpBB® Forum Software