Sub-SubForm Reference Problem

[(PeteCresswell)]

I've got a form (frmDeal) that has a subform (frmDeal_Tranch e) that has a
subform (frmDeal_Tranch eCusip).

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_Tranche Cusip

?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]

Re: Sub-SubForm Reference Problem

(PeteCresswell) wrote:

I've got a form (frmDeal) that has a subform (frmDeal_Tranch e) that has a
subform (frmDeal_Tranch eCusip).
>
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_Tranche Cusip
>
?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_Tr ancheCusip.Name ?

[Randy Harris]

Re: Sub-SubForm Reference Problem

On Sun, 03 Sep 2006 14:28:21 -0400, "(PeteCresswell )" <x@y.Invalid>
wrote:

>I've got a form (frmDeal) that has a subform (frmDeal_Tranch e) that has a
>subform (frmDeal_Tranch eCusip).
>
>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_Tranch e
>
>?me.subTranche .Form!subCusips .name
>subCusips
>
>?me.subTranche .Form!subCusips .SourceObject
>frmDeal_Tranch eCusip
>
>?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_Tranche Cusip

?me.subTranche. Form!SubCusips. Form.name
frmDeal_Tranche Cusip


-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com

[(PeteCresswell)]

Re: Sub-SubForm Reference Problem

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)]

Re: Sub-SubForm Reference Problem

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.Sour ceobject
frmDeal_Tranche Cusip

?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.na me
frmDeal_Tranche Cusip

?forms!frmDeal_ TrancheCusip.re cordsource
ttblDealCache_T rancheCusip
. . . . . . . . . . . . . . . .
-------------------------------------------------------

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

[Randy Harris]

Re: Sub-SubForm Reference Problem

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

>On Sun, 03 Sep 2006 14:28:21 -0400, "(PeteCresswell )" <x@y.Invalid>
>wrote:
>

>>I've got a form (frmDeal) that has a subform (frmDeal_Tranch e) that has a
>>subform (frmDeal_Tranch eCusip).
>>
>>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.subTranch e.Form.Name
>>frmDeal_Tranc he
>>
>>?me.subTranch e.Form!subCusip s.name
>>subCusips
>>
>>?me.subTranch e.Form!subCusip s.SourceObject
>>frmDeal_Tranc heCusip
>>
>>?me.subTranch e.Form!SubCusip s.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_Tranch e
>
>?me.subTranche .Form!subCusips .name
>subCusips
>
>?me.subTranche .Form!subCusips .SourceObject
>frmDeal_Tranch eCusip
>
>?me.subTranche .Form!SubCusips .Form.name
>frmDeal_Tranch eCusip
>
>
>-=-=-=-=-=-=-=-=-=-=-=-=
>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)]

Re: Sub-SubForm Reference Problem

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)]

Re: Sub-SubForm Reference Problem

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)]

Re: Sub-SubForm Reference Problem

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!s ubTranche.form. recordsource = "ttblDealCache_ Tranche" (1 record)
?forms!frmDeal! subTranche.Form !subCusips.form .name
frmDeal_Tranche Cusip

forms!frmDeal!s ubTranche.form. recordsource = ""
?forms!frmDeal! subTranche.Form !subCusips.form .name
frmDeal_Tranche Cusip

forms!frmDeal!s ubTranche.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]

Re: Sub-SubForm Reference Problem

"(PeteCresswell )" <x@y.Invalidwro te in
news:a2imf29gph c4bbfntm0e7ag3n 4h2javfv2@4ax.c om:

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]

Re: Sub-SubForm Reference Problem

"(PeteCresswell )" <x@y.Invalidwro te in
news:s2nof25lh7 2vfujtble6nmegc 8q14tortg@4ax.c om:

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.databases.ms-access/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)]

Re: Sub-SubForm Reference Problem

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]

Re: Sub-SubForm Reference Problem

"(PeteCresswell )" <x@y.Invalidwro te in
news:r5grf2tnhp 524u3c8gqebs7vp mtbevofhj@4ax.c om:

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)]

Re: Sub-SubForm Reference Problem

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]

Re: Sub-SubForm Reference Problem

"(PeteCresswell )" <x@y.Invalidwro te in
news:plprf2pqva qks2nbldbbgh9e1 2shjtt4ga@4ax.c om:

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/