RecordsetClone

**Tim Marshall** · Jan 15 '07, 03:15 PM

Re: RecordsetClone

d.francis@orcf. co.nz wrote:

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

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.recordset clone

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

forms.frm1.sub1 .form.recordset clone

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@orcf.co.nz** · Jan 15 '07, 07:05 PM

Re: RecordsetClone

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.francis@orcf. co.nz wrote:
>

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

>
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.recordset clone
>
Some would not use the bangs at all and this works:
>
forms.frm1.sub1 .form.recordset clone
>
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** · Jan 15 '07, 07:55 PM

Re: RecordsetClone

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.Fo rm!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.francis@orcf .co.nzwrote in message
news:1168887241 .117546.170650@ v45g2000cwv.goo glegroups.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.francis@orcf. co.nz wrote:
>>

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

>>
>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.sub 1.form.recordse tclone
>>
>Some would not use the bangs at all and this works:
>>
>forms.frm1.sub 1.form.recordse tclone
>>
>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@orcf.co.nz** · Jan 15 '07, 10:15 PM

Re: RecordsetClone

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.Recordset Clone
Set rst = Forms!frm1.frm2 .Form.Recordset Clone
Set rst = Forms.frm1.frm2 .Form.Recordset Clone

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.Fo rm!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.francis@orcf .co.nzwrote in message
news:1168887241 .117546.170650@ v45g2000cwv.goo glegroups.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.francis@orcf. co.nz wrote:
>
Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2 .Form.Recordset Clone
>
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.recordset clone
>
Some would not use the bangs at all and this works:
>
forms.frm1.sub1 .form.recordset clone
>
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** · Jan 15 '07, 10:35 PM

Re: RecordsetClone

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.francis@orcf .co.nzwrote in message
news:1168898606 .174841.162690@ q2g2000cwa.goog legroups.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.Recordset Clone
Set rst = Forms!frm1.frm2 .Form.Recordset Clone
Set rst = Forms.frm1.frm2 .Form.Recordset Clone
>
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.Fo rm!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.francis@orc f.co.nzwrote in message
>news:116888724 1.117546.170650 @v45g2000cwv.go oglegroups.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.francis@orcf. co.nz wrote:
>>
Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2 .Form.Recordset Clone
>>
>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.sub 1.form.recordse tclone
>>
>Some would not use the bangs at all and this works:
>>
>forms.frm1.sub 1.form.recordse tclone
>>
>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@orcf.co.nz** · Jan 16 '07, 01:55 AM

Re: RecordsetClone

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.francis@orcf .co.nzwrote in message
news:1168898606 .174841.162690@ q2g2000cwa.goog legroups.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.Recordset Clone
Set rst = Forms!frm1.frm2 .Form.Recordset Clone
Set rst = Forms.frm1.frm2 .Form.Recordset Clone

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.Fo rm!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.francis@orcf .co.nzwrote in message
news:1168887241 .117546.170650@ v45g2000cwv.goo glegroups.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.francis@orcf. co.nz wrote:
>
Dim rst as DAO.recordset
Set rst = Forms!frm1!frm2 .Form.Recordset Clone
>
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.recordset clone
>
Some would not use the bangs at all and this works:
>
forms.frm1.sub1 .form.recordset clone
>
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** · Jan 16 '07, 02:15 PM

Re: RecordsetClone

d.francis@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** · Jan 16 '07, 05:05 PM

Re: RecordsetClone

d.francis@orcf. co.nz wrote in
news:1168887241 .117546.170650@ v45g2000cwv.goo glegroups.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** · Jan 16 '07, 05:05 PM

Re: RecordsetClone

"Larry Linson" <bouncer@localh ost.notwrote in
news:V8Tqh.6804 $Gj5.1703@trndd c01:

(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** · Jan 16 '07, 05:05 PM

Re: RecordsetClone

Tim Marshall <TIMMY!@PurpleP andaChasers.Moe rtheriumwrote in
news:eoik4q$k3d $1@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/

RecordsetClone

RecordsetClone

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment