how do I clone a forms recordset using ADO or how do I get dao.filter to work

[kelly d via AccessMonster.com]

2 questions. I'll be happy with an answer to either one if anybody knows.

1. how do I create a recordset clone of an open form using ADO in an .MDB or

2. how do I get the DAO .filter propery to work. cuz currently it wont do
anything.
I do a dim RS as dao.recordset, set RS=me.recordsetclone, then RS.filter =
"some filter parameters"
And I still get the full recordset. No filter. Thats why I tried using ADODB,
cuz the .filter property works but
RecordSetClone doesnt. So I appear to be darned if I DAO and darned if I
ADODB.

thanks.

 

[Marshall Barton]

2 questions. I'll be happy with an answer to either one if anybody knows.

1. how do I create a recordset clone of an open form using ADO in an .MDB or

2. how do I get the DAO .filter propery to work. cuz currently it wont do
anything.
I do a dim RS as dao.recordset, set RS=me.recordsetclone, then RS.filter =
"some filter parameters"
And I still get the full recordset. No filter. Thats why I tried using ADODB,
cuz the .filter property works but
RecordSetClone doesnt. So I appear to be darned if I DAO and darned if I
ADODB.

I can't help with ADO, but I also see no reason for it.

When you set the form's Filter property to a where
expression, you also need to use:
Me.FilterOn = True

 

[kelly d via AccessMonster.com]

well, the idea is, i have a form in my app, it's a simple word processor
about the level of functionality of word pad, it's function is to allow users
to create in-app form letters on the fly by dragging and dropping tags onto
the letter that the letter will convert to info out of a table (kinda like
words mail merge, so why dont I use word and it's mail merge ability? users
dont know how to insert fields, how to connect to a database, how to create
queries for the info they need, and they really dont want to learn. Nor do
they want to call me everytime they need a new form letter. I presented them
with the idea of a simple but functional in-app word processor with drag and
drop ability and they liked it. said do it) so, anyhow, what happens is they
drag and drop tags from a listview on to a letter, some tags are straight
fields from a table like name address, some are basic functions like date,
some are a little more involved functions like all of a contractors insurance
policies formatted a certain way and associated with one tag or all a
contractors technicians, etc. when it comes to my code processing those
functions (when a user clicks the button to turn those tags into data for
print) the more complex ones have to open a recordset and compile a few
records into a string to be returned to the letter, actually, doing an
OpenRecordset is preferred to me cuz it's simple, open a table, filter it,
get what you need. all good. problem, backend is on a fairly slow network
connection so opening new recordsets takes a while, 5-10 seconds each time.
that adds up when code is processing a letter-full of tags. I discovered that
it is infinitely faster (like instant) to do a RecordSetClone of a main form
that is already open and get records out of it than it is to open a new
recordset that will have all of the same records that this other main form
will already have open anyway. so I'm thinking RecordSetClone and then filter
that clone for the specific ones that the form letter tags will need and i
discovered that ADODB.filter works and DAO.filter does not. the only reason
I'm doing it this way is because of speed. painfully slow OpenRecordset vs.
blazingly fast RecordSetClone. OpenRecordset takes about 15 seconds to
convert a letter-full of tags times 10-20 letters. I just wont be able to get
that type of time delay to fly with my people where as RecordsetClone takes
about 1 second per letter. I'm pretty sure I"m gonna have to do it this way.

thanks.

 

[David C. Holley]

Can you edit the post to be a bit more sustinct(sp)? While detail is
good, too much runs the risk of obsuring the question/problem. Trust me,
if we have questions or don't understand we'll let you know.

Answering the question in the subject line...

I might be wrong, but I do not believe that ADO supports the
..RecordSetClone method. I have only personally seen it used with a FORM
object. The code is basically...

Set rs = [formObject].RecordSetClone

If you are using ADO to access data, such as inserting data or
duplicating a record, it would be possible to grab data from a form
object's recordset and insert it via an ADO.RecordSet object. However,
I'm thinking that this would be a bit odd to do so, it sounds like it at
least.

adoRS.fields([fieldName]) = frmRS.field([fieldName])

 

[Marshall Barton]

well, the idea is, i have a form in my app, it's a simple word processor
about the level of functionality of word pad, it's function is to allow users
to create in-app form letters on the fly by dragging and dropping tags onto
the letter that the letter will convert to info out of a table (kinda like
words mail merge, so why dont I use word and it's mail merge ability? users
dont know how to insert fields, how to connect to a database, how to create
queries for the info they need, and they really dont want to learn. Nor do
they want to call me everytime they need a new form letter. I presented them
with the idea of a simple but functional in-app word processor with drag and
drop ability and they liked it. said do it) so, anyhow, what happens is they
drag and drop tags from a listview on to a letter, some tags are straight
fields from a table like name address, some are basic functions like date,
some are a little more involved functions like all of a contractors insurance
policies formatted a certain way and associated with one tag or all a
contractors technicians, etc. when it comes to my code processing those
functions (when a user clicks the button to turn those tags into data for
print) the more complex ones have to open a recordset and compile a few
records into a string to be returned to the letter, actually, doing an
OpenRecordset is preferred to me cuz it's simple, open a table, filter it,
get what you need. all good. problem, backend is on a fairly slow network
connection so opening new recordsets takes a while, 5-10 seconds each time.
that adds up when code is processing a letter-full of tags. I discovered that
it is infinitely faster (like instant) to do a RecordSetClone of a main form
that is already open and get records out of it than it is to open a new
recordset that will have all of the same records that this other main form
will already have open anyway. so I'm thinking RecordSetClone and then filter
that clone for the specific ones that the form letter tags will need and i
discovered that ADODB.filter works and DAO.filter does not. the only reason
I'm doing it this way is because of speed. painfully slow OpenRecordset vs.
blazingly fast RecordSetClone. OpenRecordset takes about 15 seconds to
convert a letter-full of tags times 10-20 letters. I just wont be able to get
that type of time delay to fly with my people where as RecordsetClone takes
about 1 second per letter. I'm pretty sure I"m gonna have to do it this way.

If the form's dataset already has all the needed records,
then I would think you can use the forms RecordsetClone and
filter that as needed (instead of filtering the form):

With Me.RecordsetClone
. . .
.Filter = "somefield = " somevalue
Set rsf = .OpenRecordset()
' do your work
rsf.CloseL Set rsf = Nothing
End With

 

[kelly d via AccessMonster.com]

thats pretty much the answer i kept finding too. ado + recordsetclone = no.
you are correct about my reply being long winded but after reading marshalls
first reply, i got the impression that if i didnt justify why I needed such
an answer about ADO, that this thread wasnt going to go anywhere. so i
limbered up my fingers and began to type.

anywho. thanks for the reply.
plus i did find an answer on how to make dao.filter work. kind of annoying
tho. it seems to require one to make a recordset of a recordset clone

rs1=form.RecordSetClone
rs1.filter = "some filter stuff"
rs2=rs1.OpenRecordSet

display your rs2 records.

but it worked none-the-less.

thanks.

 

[kelly d via AccessMonster.com]

funny marshall.
I was typing my reply at the same time you was typing yours, and I think we
arrived at the same conclusion about how to get dao.filter to work.

thanks.

 

[David C. Holley]

But why is it that you were wanting to use .RecordsetClone with ADO?
From what I could tell of the post, cloning the recordset of the form
would work.