Sub-SubForm Reference Problem

[(PeteCresswell)]

I've got a form (frmDeal) that has a subform (frmDeal_Tranche) that has a
subform (frmDeal_TrancheCusip).

When I try to refer to properties on the "Cusip" form at the end of the chain,
MS Access is throwing "2455: Expression has invalid reference to Property
Form/Report"
In an Immediate window:
------------------------------------------
?me.Name
frmDeal

?me.subTranche.Form.Name
frmDeal_Tranche

?me.subTranche.Form!subCusips.name
subCusips

?me.subTranche.Form!subCusips.SourceObject
frmDeal_TrancheCusip

?me.subTranche.Form!SubCusips.Form.name
("2455: Expression has invalid reference to Property Form/Report")
------------------------------------------

Am I trying to push MS Access to far?

Or am I just doing something simple-and-dumb?
--
PeteCresswell

[rkc]

(PeteCresswell) wrote:

I've got a form (frmDeal) that has a subform (frmDeal_Tranche) that has a
subform (frmDeal_TrancheCusip).

When I try to refer to properties on the "Cusip" form at the end of the chain,
MS Access is throwing "2455: Expression has invalid reference to Property
Form/Report"
In an Immediate window:
------------------------------------------
?me.Name
frmDeal

?me.subTranche.Form.Name
frmDeal_Tranche

?me.subTranche.Form!subCusips.name
subCusips

?me.subTranche.Form!subCusips.SourceObject
frmDeal_TrancheCusip

?me.subTranche.Form!SubCusips.Form.name
("2455: Expression has invalid reference to Property Form/Report")
------------------------------------------

Am I trying to push MS Access to far?

Or am I just doing something simple-and-dumb?

Aside from the fact that I don't see how you are getting
a reference to the form in the immediate window so that
Me works at all, that should work.

You are already as deep with the forth call as you are
going with the fifth as far as subForms go. As far as
objects go DAO is deeper than that.

Have you tried Mr. Fairfield's recommendation of
skipping dotting your way through the hierarchy
and just using: Form_frmDeal_TrancheCusip.Name?

[Randy Harris]

On Sun, 03 Sep 2006 14:28:21 -0400, "(PeteCresswell)" <[e-mail]>
wrote:

>I've got a form (frmDeal) that has a subform (frmDeal_Tranche) that has a
subform (frmDeal_TrancheCusip).

When I try to refer to properties on the "Cusip" form at the end of the chain,
MS Access is throwing "2455: Expression has invalid reference to Property
Form/Report"
In an Immediate window:
------------------------------------------
?me.Name
frmDeal

?me.subTranche.Form.Name
frmDeal_Tranche

?me.subTranche.Form!subCusips.name
subCusips

?me.subTranche.Form!subCusips.SourceObject
frmDeal_TrancheCusip

?me.subTranche.Form!SubCusips.Form.name
("2455: Expression has invalid reference to Property Form/Report")
------------------------------------------

Am I trying to push MS Access to far?

Or am I just doing something simple-and-dumb?

Perhaps it's something version specific? I just ran this in my
immediate window with Access 2000:
?me.Name
frmDeal

?me.subTranche.Form.Name
frmDeal_Tranche

?me.subTranche.Form!subCusips.name
subCusips

?me.subTranche.Form!subCusips.SourceObject
frmDeal_TrancheCusip

?me.subTranche.Form!SubCusips.Form.name
frmDeal_TrancheCusip
-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com

[(PeteCresswell)]

Per rkc:

>Aside from the fact that I don't see how you are getting
a reference to the form in the immediate window so that
Me works at all, that should work.

I stopped the code during the process of loading the form.

Good catch. I'll go back and try to replicate it from outside of the form -
knowing that if there are no records behind a .RecordSource that MS Access can
throw an error something like (not sure if it's exactly the same) the one I
posted.
--
PeteCresswell

[(PeteCresswell)]

Per (PeteCresswell):

I'll go back and try to replicate it from outside of the form -

Nope: same results

viz:
------------------------------
?forms!frmDeal!subTranche.Form!subCusips.Sourceobj ect
frmDeal_TrancheCusip

?forms!frmDeal!subTranche.Form!subCusips.Form.name
(2455)
------------------------------
Also two more observations:
-------------------------------------------------------
1) When I just plow through all errors in the rest
of the form's processing, the 2455 pops in several
other bits of code - each digging down to that subform.
So I doubt it's just a typo.

2) If I start the subform up by itself, all my reference trials
in Immediate work.
e.g.
. . . . . . . . . . . . . . . .
?forms!frmDeal_TrancheCusip.name
frmDeal_TrancheCusip

?forms!frmDeal_TrancheCusip.recordsource
ttblDealCache_TrancheCusip
. . . . . . . . . . . . . . . .
-------------------------------------------------------

I'm starting to think in terms of just working around this
with some sort of Grid or TreeView control.
--
PeteCresswell

[Randy Harris]

On Sun, 03 Sep 2006 20:35:41 GMT, Randy Harris <ra***@no.spam.net>
wrote:

>On Sun, 03 Sep 2006 14:28:21 -0400, "(PeteCresswell)" <[e-mail]>
wrote:

>>I've got a form (frmDeal) that has a subform (frmDeal_Tranche) that has a
subform (frmDeal_TrancheCusip).

When I try to refer to properties on the "Cusip" form at the end of the chain,
MS Access is throwing "2455: Expression has invalid reference to Property
Form/Report"
In an Immediate window:
------------------------------------------
?me.Name
frmDeal

?me.subTranche.Form.Name
frmDeal_Tranche

?me.subTranche.Form!subCusips.name
subCusips

?me.subTranche.Form!subCusips.SourceObject
frmDeal_TrancheCusip

?me.subTranche.Form!SubCusips.Form.name
("2455: Expression has invalid reference to Property Form/Report")
------------------------------------------

Am I trying to push MS Access to far?

Or am I just doing something simple-and-dumb?

Perhaps it's something version specific? I just ran this in my
immediate window with Access 2000:
?me.Name
frmDeal

?me.subTranche.Form.Name
frmDeal_Tranche

?me.subTranche.Form!subCusips.name
subCusips

?me.subTranche.Form!subCusips.SourceObject
frmDeal_TrancheCusip

?me.subTranche.Form!SubCusips.Form.name
frmDeal_TrancheCusip
-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com

I thought that knowing that it was not a syntax error or an Access
limitation would be of some help. That's why I tested it and posted
the results.

[(PeteCresswell)]

Per Randy Harris:

>I thought that knowing that it was not a syntax error or an Access
limitation would be of some help. That's why I tested it and posted
the results.

That took some time/effort. Thanks!
--
PeteCresswell

[(PeteCresswell)]

Per (PeteCresswell):

>>I thought that knowing that it was not a syntax error or an Access
limitation would be of some help. That's why I tested it and posted
the results.

That took some time/effort. Thanks!

I just replicated it. First on my home office machine, then on the client's
machine. Works A-OK for me too - with a little "playpen" db created for the
purpose. Even works when I import the three nested forms into the "real" app.

I guess now I'm down to:
------------------------------------------------------------------------------
1) Something so dumb/obvious that I'm missing it in the "real" implementation.

2) Some kind of corruption weirdness in the "real" app.
------------------------------------------------------------------------------
--
PeteCresswell

[(PeteCresswell)]

Per (PeteCresswell):

>I guess now I'm down to:
------------------------------------------------------------------------------
1) Something so dumb/obvious that I'm missing it in the "real" implementation.

2) Some kind of corruption weirdness in the "real" app.
------------------------------------------------------------------------------

I think I'm on to something.

The reference works ok if there is no .Recordset in the immediate parent.

The reference works ok if the .Recordset in the immediate parent contains one or
more records.

The reference fails if the .Recordset of the immediate parent is empty!

VIZ:
--------------------------------------------------------------------------------
forms!frmDeal!subTranche.form.recordsource = "ttblDealCache_Tranche" (1 record)
?forms!frmDeal!subTranche.Form!subCusips.form.name
frmDeal_TrancheCusip

forms!frmDeal!subTranche.form.recordsource = ""
?forms!frmDeal!subTranche.Form!subCusips.form.name
frmDeal_TrancheCusip

forms!frmDeal!subTranche.form.recordsource = "ttblDealCache_Tranche" (empty)
?forms!frmDeal!subTranche.Form!subCusips.form.name
(Error 2455)
--------------------------------------------------------------------------------
I'd put that in the category of "weirdnesses".....

--
PeteCresswell

[David W. Fenton]

"(PeteCresswell)" <[e-mail] in
news:a2********************************@4ax.com:

Per rkc:

>>Aside from the fact that I don't see how you are getting
a reference to the form in the immediate window so that
Me works at all, that should work.

I stopped the code during the process of loading the form.

Good catch. I'll go back and try to replicate it from outside of
the form - knowing that if there are no records behind a
.RecordSource that MS Access can throw an error something like
(not sure if it's exactly the same) the one I posted.

I experienced that exact problem recently, trying to refer to a
control on a subform when the parent form had no records. In that
situation, the subform control exists, but the subform does not (the
subform was unbound, so its recordsource didn't matter).

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

[David W. Fenton]

"(PeteCresswell)" <[e-mail] in
news:s2********************************@4ax.com:

The reference fails if the .Recordset of the immediate parent is
empty!

This is the exact same error I encountered a couple of weeks ago,
and posted about here (all on one line, of course):

http://groups.google.com/group/comp..../msg/e9e5a1d17
d4ff92f

It was exactly the same problem. The subform control exists on a
form with no records, but the subform *in* that control does *not*
exist.

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

[(PeteCresswell)]

Per David W. Fenton:

>It was exactly the same problem. The subform control exists on a
form with no records, but the subform *in* that control does *not*
exist.

Did you come up with a workaround?

I'm thinking a front-end-resident dummy table with one empty record in it. Set
..RecordSource to that dummy table before any of the problem code fires, then set
it back to the "real" .RecordSource afterwards.
--
PeteCresswell

[David W. Fenton]

"(PeteCresswell)" <[e-mail] in
news:r5********************************@4ax.com:

Per David W. Fenton:

>>It was exactly the same problem. The subform control exists on a
form with no records, but the subform *in* that control does *not*
exist.

Did you come up with a workaround?

In my case, I just moved my subform reference to after a requery, so
that there were records.

I'm thinking a front-end-resident dummy table with one empty record

in it. Set

.RecordSource to that dummy table before any of the problem code

fires, then set

it back to the "real" .RecordSource afterwards.

Setting the form to allow additions should give you at least one
record. You can disable the control so the user can't type until
they've initiated a legal add.

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

[(PeteCresswell)]

Per David W. Fenton:

>Setting the form to allow additions should give you at least one
record. You can disable the control so the user can't type until
they've initiated a legal add.

You mean disabling the subform control that has the offending form as it's
..SourceObject, right?

I never would have thought of that - and it sounds like the simplest
solution.... even to the point of implementing across all of my forms/subforms
because occurrence of the problem would seem tb data-dependent and therefore
lurking in other forms/subforms. i.e. avoiding it depends on a given subform
having records behind it..

Thanks.
--
PeteCresswell

[David W. Fenton]

"(PeteCresswell)" <[e-mail] in
news:pl********************************@4ax.com:

Per David W. Fenton:

>>Setting the form to allow additions should give you at least one
record. You can disable the control so the user can't type until
they've initiated a legal add.

You mean disabling the subform control that has the offending form
as it's .SourceObject, right?

That's probably the way I'd do it, though others might disable the
controls on the form.

I never would have thought of that - and it sounds like the
simplest solution.... even to the point of implementing across all
of my forms/subforms because occurrence of the problem would seem
tb data-dependent and therefore lurking in other forms/subforms.
i.e. avoiding it depends on a given subform having records
behind it..

I'm surprised you haven't encountered this often enough before to
have hit on this solution before.

Another solution is setting the recordsource to something that
returns one Null record. I do this a lot in forms that I want to
open with no records loaded. The basic method is to do a SELECT TOP
1 on the table with the least records, and make all the fields Null,
something like this:

SELECT TOP 1 Null As FirstField, Null As SecondField
FROM [table]

The result is that your bound controls all show Null, are uneditable
and you have only one record, and you don't have to worry about
setting AllowAdditions.

I've never done it in a subform, though -- only in a parent form.

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