RecordsetClone

[d.francis]

I have converted an Access 97 database to Access 2003

The following code now fails and returns Run-time error '3420'

Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2.Form.RecordsetClone

I have installed the latest updates to Access and Jet
I have checked the references and they are fine
I have disabled the 'Sandbox' mode
I have imported the database into a new shell

I have run the following as a test

Dim rst as DAO.recordset
Set rst = Forms!frm1.RecordsetClone
Set rst = Forms!frm1!frm2.Form.RecordsetClone

The code defines the higher level forms 'frm1' recordset but fails to
define the subforms 'frm2' recordset with the 'Object invaild or no
longer set' error

This is starting to drive me insane and I would really appreaciate any
help from the enlightened masses

[Tim Marshall]

d.*******@orcf.co.nz wrote:

Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2.Form.RecordsetClone

Presumeably, frm2 is the name of the form that acts as the subform.
Don't use that name, use the name of the subform. Thus if frm1 has a
subform named sub1 which has as its source object, frm2, then you refer
to it like this:

forms!frm1.sub1.form.recordsetclone

Some would not use the bangs at all and this works:

forms.frm1.sub1.form.recordsetclone

This was the same in Access 97, BTW.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[d.francis]

Tim - thank you for your reply, however no joy with your suggestion

I have run a few more tests and if I create a new form and subform in
the now Access 2003 db the code has no problem establishing a
recordsetclone of the subform.

When I insert the subform into a new master form the code then works,
therefore it appears the master form is corrupt in someway.

I have tried to copy and paste the controls of the old form into a new
master form, but the error still occurs. The problem is only resolved
when a new master form and new controls are created.

The problem now is the master form contains a tab control with numerous
fields, subforms, etc and would take forever to recreate.

Has anyone had this problem before and are there any suggestions on how
to replicate the master form without having to recreate every control
including formating, etc
Tim Marshall wrote:

d.*******@orcf.co.nz wrote:

Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2.Form.RecordsetClone

Presumeably, frm2 is the name of the form that acts as the subform.
Don't use that name, use the name of the subform. Thus if frm1 has a
subform named sub1 which has as its source object, frm2, then you refer
to it like this:

forms!frm1.sub1.form.recordsetclone

Some would not use the bangs at all and this works:

forms.frm1.sub1.form.recordsetclone

This was the same in Access 97, BTW.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Larry Linson]

I am in agreement that, if Tim's assumption is correct, your addressing is
incorrect.

To address control C, in Form F2, which is embedded in Subform Control SFC,
in Form F1, you'd use:

Forms!F1!SFC.Form!C

Note that the name of the Form embedded in the Subform Control does not
appear in that address.

"no joy with your suggestion" is emphatic, but it doesn't give details of
exactly what you used, where and how you used it, what the exact error was,
and does not help us to help you.

Larry Linson
Microsoft Access MVP

<d.*******@orcf.co.nzwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...

Tim - thank you for your reply, however no joy with your suggestion

I have run a few more tests and if I create a new form and subform in
the now Access 2003 db the code has no problem establishing a
recordsetclone of the subform.

When I insert the subform into a new master form the code then works,
therefore it appears the master form is corrupt in someway.

I have tried to copy and paste the controls of the old form into a new
master form, but the error still occurs. The problem is only resolved
when a new master form and new controls are created.

The problem now is the master form contains a tab control with numerous
fields, subforms, etc and would take forever to recreate.

Has anyone had this problem before and are there any suggestions on how
to replicate the master form without having to recreate every control
including formating, etc
Tim Marshall wrote:

>d.*******@orcf.co.nz wrote:

Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2.Form.RecordsetClone

Presumeably, frm2 is the name of the form that acts as the subform.
Don't use that name, use the name of the subform. Thus if frm1 has a
subform named sub1 which has as its source object, frm2, then you refer
to it like this:

forms!frm1.sub1.form.recordsetclone

Some would not use the bangs at all and this works:

forms.frm1.sub1.form.recordsetclone

This was the same in Access 97, BTW.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[d.francis]

I apologise for being vague

The name of the subform control and the subform are the same.

I have tried the following reference combinations, which have all
failed

Set rst = Forms!frm1!frm2.Form.RecordsetClone
Set rst = Forms!frm1.frm2.Form.RecordsetClone
Set rst = Forms.frm1.frm2.Form.RecordsetClone

I believe this is not a referencing problem. When I create a new
master form within the database and insert the above subform I can get
the code to work.

Could it be possible, that with the subform name and the subform
control names being the same, during the upgrade from '97 to '03 access
got confussed and cannot now differentiate between the subform control
and subform causing a referencing problem?

Clutching at straws and still struggling. Your comments are apreciated

Larry Linson wrote:

I am in agreement that, if Tim's assumption is correct, your addressing is
incorrect.

To address control C, in Form F2, which is embedded in Subform Control SFC,
in Form F1, you'd use:

Forms!F1!SFC.Form!C

Note that the name of the Form embedded in the Subform Control does not
appear in that address.

"no joy with your suggestion" is emphatic, but it doesn't give details of
exactly what you used, where and how you used it, what the exact error was,
and does not help us to help you.

Larry Linson
Microsoft Access MVP

<d.*******@orcf.co.nzwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...

Tim - thank you for your reply, however no joy with your suggestion

I have run a few more tests and if I create a new form and subform in
the now Access 2003 db the code has no problem establishing a
recordsetclone of the subform.

When I insert the subform into a new master form the code then works,
therefore it appears the master form is corrupt in someway.

I have tried to copy and paste the controls of the old form into a new
master form, but the error still occurs. The problem is only resolved
when a new master form and new controls are created.

The problem now is the master form contains a tab control with numerous
fields, subforms, etc and would take forever to recreate.

Has anyone had this problem before and are there any suggestions on how
to replicate the master form without having to recreate every control
including formating, etc
Tim Marshall wrote:

d.*******@orcf.co.nz wrote:

Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2.Form.RecordsetClone

Presumeably, frm2 is the name of the form that acts as the subform.
Don't use that name, use the name of the subform. Thus if frm1 has a
subform named sub1 which has as its source object, frm2, then you refer
to it like this:

forms!frm1.sub1.form.recordsetclone

Some would not use the bangs at all and this works:

forms.frm1.sub1.form.recordsetclone

This was the same in Access 97, BTW.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Larry Linson]

I no longer name Subform Controls and the Form embedded in them the same. As
you have no problem if you create a new Form using the same names, that's
unlikely to be the problem.

You could possibly have had some problem in converting; you could possibly
have corruption in your Forms.

The simplest approach might be to re-create the main Form and delete the old
one.

If that doesn't work or is difficult, it is possible that one of the
following might help:

(1) Compact and Repair the database containing the Forms.

or

(2) Create a new Database, and import all the Objects, except the Form in
Question
(a) Save as Text from the old database
(b) Load from Text into the new database
* NOTE: Save as Text and Load from Text are undocumented
features, but have been discussed in this newsgroup and
others,
so you should be able to get information by searching at
http://groups.google.com.

A great source of information on corruption, avoiding it, and curing it is
MVP Tony Toews' site at htttp://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

<d.*******@orcf.co.nzwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...

>I apologise for being vague

The name of the subform control and the subform are the same.

I have tried the following reference combinations, which have all
failed

Set rst = Forms!frm1!frm2.Form.RecordsetClone
Set rst = Forms!frm1.frm2.Form.RecordsetClone
Set rst = Forms.frm1.frm2.Form.RecordsetClone

I believe this is not a referencing problem. When I create a new
master form within the database and insert the above subform I can get
the code to work.

Could it be possible, that with the subform name and the subform
control names being the same, during the upgrade from '97 to '03 access
got confussed and cannot now differentiate between the subform control
and subform causing a referencing problem?

Clutching at straws and still struggling. Your comments are apreciated

Larry Linson wrote:

>I am in agreement that, if Tim's assumption is correct, your addressing
is
incorrect.

To address control C, in Form F2, which is embedded in Subform Control
SFC,
in Form F1, you'd use:

Forms!F1!SFC.Form!C

Note that the name of the Form embedded in the Subform Control does not
appear in that address.

"no joy with your suggestion" is emphatic, but it doesn't give details of
exactly what you used, where and how you used it, what the exact error
was,
and does not help us to help you.

Larry Linson
Microsoft Access MVP

<d.*******@orcf.co.nzwrote in message
news:11**********************@v45g2000cwv.googleg roups.com...

Tim - thank you for your reply, however no joy with your suggestion

I have run a few more tests and if I create a new form and subform in
the now Access 2003 db the code has no problem establishing a
recordsetclone of the subform.

When I insert the subform into a new master form the code then works,
therefore it appears the master form is corrupt in someway.

I have tried to copy and paste the controls of the old form into a new
master form, but the error still occurs. The problem is only resolved
when a new master form and new controls are created.

The problem now is the master form contains a tab control with numerous
fields, subforms, etc and would take forever to recreate.

Has anyone had this problem before and are there any suggestions on how
to replicate the master form without having to recreate every control
including formating, etc
Tim Marshall wrote:
d.*******@orcf.co.nz wrote:

Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2.Form.RecordsetClone

Presumeably, frm2 is the name of the form that acts as the subform.
Don't use that name, use the name of the subform. Thus if frm1 has a
subform named sub1 which has as its source object, frm2, then you
refer
to it like this:

forms!frm1.sub1.form.recordsetclone

Some would not use the bangs at all and this works:

forms.frm1.sub1.form.recordsetclone

This was the same in Access 97, BTW.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[d.francis]

Thank you for your time and comments

I will try the options you have suggested

Regards
Larry Linson wrote:

I no longer name Subform Controls and the Form embedded in them the same. As
you have no problem if you create a new Form using the same names, that's
unlikely to be the problem.

You could possibly have had some problem in converting; you could possibly
have corruption in your Forms.

The simplest approach might be to re-create the main Form and delete the old
one.

If that doesn't work or is difficult, it is possible that one of the
following might help:

(1) Compact and Repair the database containing the Forms.

or

(2) Create a new Database, and import all the Objects, except the Form in
Question
(a) Save as Text from the old database
(b) Load from Text into the new database
* NOTE: Save as Text and Load from Text are undocumented
features, but have been discussed in this newsgroup and
others,
so you should be able to get information by searching at
http://groups.google.com.

A great source of information on corruption, avoiding it, and curing it is
MVP Tony Toews' site at htttp://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

<d.*******@orcf.co.nzwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...

I apologise for being vague

The name of the subform control and the subform are the same.

I have tried the following reference combinations, which have all
failed

Set rst = Forms!frm1!frm2.Form.RecordsetClone
Set rst = Forms!frm1.frm2.Form.RecordsetClone
Set rst = Forms.frm1.frm2.Form.RecordsetClone

I believe this is not a referencing problem. When I create a new
master form within the database and insert the above subform I can get
the code to work.

Could it be possible, that with the subform name and the subform
control names being the same, during the upgrade from '97 to '03 access
got confussed and cannot now differentiate between the subform control
and subform causing a referencing problem?

Clutching at straws and still struggling. Your comments are apreciated

Larry Linson wrote:

I am in agreement that, if Tim's assumption is correct, your addressing
is
incorrect.

To address control C, in Form F2, which is embedded in Subform Control
SFC,
in Form F1, you'd use:

Forms!F1!SFC.Form!C

Note that the name of the Form embedded in the Subform Control does not
appear in that address.

"no joy with your suggestion" is emphatic, but it doesn't give details of
exactly what you used, where and how you used it, what the exact error
was,
and does not help us to help you.

Larry Linson
Microsoft Access MVP

<d.*******@orcf.co.nzwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...
Tim - thank you for your reply, however no joy with your suggestion

I have run a few more tests and if I create a new form and subform in
the now Access 2003 db the code has no problem establishing a
recordsetclone of the subform.

When I insert the subform into a new master form the code then works,
therefore it appears the master form is corrupt in someway.

I have tried to copy and paste the controls of the old form into a new
master form, but the error still occurs. The problem is only resolved
when a new master form and new controls are created.

The problem now is the master form contains a tab control with numerous
fields, subforms, etc and would take forever to recreate.

Has anyone had this problem before and are there any suggestions on how
to replicate the master form without having to recreate every control
including formating, etc
Tim Marshall wrote:
d.*******@orcf.co.nz wrote:

Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2.Form.RecordsetClone

Presumeably, frm2 is the name of the form that acts as the subform.
Don't use that name, use the name of the subform. Thus if frm1 has a
subform named sub1 which has as its source object, frm2, then you
refer
to it like this:

forms!frm1.sub1.form.recordsetclone

Some would not use the bangs at all and this works:

forms.frm1.sub1.form.recordsetclone

This was the same in Access 97, BTW.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

d.*******@orcf.co.nz wrote:

The name of the subform control and the subform are the same.

Eeeek! Rename them. I'm sure you're familar with what happens when a
bound control on a form/report has the same name as its controlsource
and you try to use an expression with the field name or control. I
would think the same could happen with forms and source objects.

Years ago on this group, I was introduced to the concept of consistent
naming conventions, ie, prefixing a control name with a couple of few
small case letters that indicate what kind of controlit is. For me,
text boxes are always txtTextBox, combos are cboComboBoxName and so on.
I always prefix subform controls with "sub". In your case, then,
frmMain would have a subform control, subFrm1 with a source object Frm1.

Access will turn around an nip you in the behind very sharply if yoyu're
not careful with naming.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

d.*******@orcf.co.nz wrote in
news:11**********************@v45g2000cwv.googlegr oups.com:

The problem now is the master form contains a tab control with
numerous fields, subforms, etc and would take forever to recreate.

Eh? Can't you just copy the tab control to the clipboard and paste
it into the new form? That would copy every control and subform
embedded in the subform.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Larry Linson" <bo*****@localhost.notwrote in
news:V8Tqh.6804$Gj5.1703@trnddc01:

(2) Create a new Database, and import all the Objects,

If you do this, decompile and compact before doing so, because
imports can bring in corrupted compiled code (even though,
theoretically, it's not supposed to).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertheriumwrote in
news:eo**********@coranto.ucs.mun.ca:

I'm sure you're familar with what happens when a
bound control on a form/report has the same name as its
controlsource and you try to use an expression with the field name
or control. I would think the same could happen with forms and
source objects.

I would think it *wouldn't*. It's not at all the same situation.
With controls and fields, you have two collections combined into one
as the form's default collection, and that's the source of the
naming conflict. In the case of a subform control and its
sourceobject, you don't at all have a naming conflict between two
collections, as the sourceobject is a property of the subform
control, and can't possibly conflict.

That said, I always name the subform controls differently from the
subforms themselves. A subform will be subMyForm, and the control
for it will be MyForm.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/