Sub-SubForm Reference Problem

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

re: Sub-SubForm Reference Problem

(PeteCresswell) wrote: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?

re: Sub-SubForm Reference Problem

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

re: Sub-SubForm Reference Problem

Per rkc: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

re: Sub-SubForm Reference Problem

Per (PeteCresswell):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

re: Sub-SubForm Reference Problem

On Sun, 03 Sep 2006 20:35:41 GMT, Randy Harris <randy@no.spam.net>
wrote:
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.

re: Sub-SubForm Reference Problem

Per Randy Harris:That took some time/effort. Thanks!
--
PeteCresswell

re: Sub-SubForm Reference Problem

Per (PeteCresswell):
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

re: Sub-SubForm Reference Problem

Per (PeteCresswell):
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

re: Sub-SubForm Reference Problem

"(PeteCresswell)" <x@y.Invalidwrote in
news:a2imf29gphc4bbfntm0e7ag3n4h2javfv2@4ax.com:
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/

re: Sub-SubForm Reference Problem

"(PeteCresswell)" <x@y.Invalidwrote in
news:s2nof25lh72vfujtble6nmegc8q14tortg@4ax.com:
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/

re: Sub-SubForm Reference Problem

Per David W. Fenton: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

re: Sub-SubForm Reference Problem

"(PeteCresswell)" <x@y.Invalidwrote in
news:r5grf2tnhp524u3c8gqebs7vpmtbevofhj@4ax.com:
In my case, I just moved my subform reference to after a requery, so
that there were records.
in it. Setfires, then setSetting 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/

re: Sub-SubForm Reference Problem

Per David W. Fenton: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

re: Sub-SubForm Reference Problem

"(PeteCresswell)" <x@y.Invalidwrote in
news:plprf2pqvaqks2nbldbbgh9e12shjtt4ga@4ax.com:
That's probably the way I'd do it, though others might disable the
controls on the form.
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/