[AccessD] Sub Subform not showing

[David Emerson]

Finally found a solution.  Sorry Bill not as imaginative as your suggestion
:)

Added the following line just before resetting the sub subform Recordset:

        Me!sfrQualitySurvey.Form!sfrQualitySurveyResult.SourceObject =
"sfrClientQualitySurveyResult"
        Set Me!sfrQualitySurvey.Form!sfrQualitySurveyResult.Form.Recordset =
rstTemp

Not sure why it works because printing the value of the SourceObject
property just before running the new line returns the same value.

Thanks for all the suggestions everyone - we finally got it nailed.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Wednesday, 8 July 2015 10:03 a.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Sub Subform not showing

The things we do for love!

On Tue, Jul 7, 2015 at 5:53 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> Ahhgggggg :)
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Bill Benson
> Sent: Wednesday, 8 July 2015 9:51 a.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Sub Subform not showing
>
> Could you do as doug suggests, lock down the controls on that form, 
> but with a command button, take them to another (popup?) form, set to 
> the same record, to do edits? Then the popup won't be a subform and 
> won't be exhibiting the same behaviour (admittedly odd) you are seeing 
> with the present subform. and maybe on unloading of the popup, trigger 
> a refresh of the subform which is already open.
>
> On Tue, Jul 7, 2015 at 5:14 PM, David Emerson <newsgrps at dalyn.co.nz>
> wrote:
>
> > Hi Doug,
> >
> > We want the users to be able to edit existing records - just not add 
> > new ones.
> >
> > David
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of Doug Murphy
> > Sent: Wednesday, 8 July 2015 8:16 a.m.
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Sub Subform not showing
> >
> > David,
> >
> > I think you can set the form in the subform control to allow 
> > additions but set the subform control locked property to true. This 
> > will allow your users to see the records but not change them. Don't 
> > have Access open at this time but memory seems to recall that is what I
have done.
> >
> > Doug
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of David Emerson
> > Sent: Tuesday, July 07, 2015 12:39 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Sub Subform not showing
> >
> > Hi Charlotte,
> >
> > The problem is not when there are no records but when there are records.
> > The forms work as expected when there are no records in the parent
> object.
> > To quote from my original post:
> >
> > "If I move to a record in frmClient which does not have records in 
> > sfrSurvey then sfrSurvey shows no records and the details section is 
> > blank (which is expected).  Because sfrSurvey is blank then 
> > sfrResults is not shown as well (as expected).
> >
> > Here is the problem: If I now move to a record in frmClient which 
> > does have records in sfrSurvey the records are showing in sfrSurvey 
> > but where the sfrResults subform should be is a white box.  It looks 
> > like the sfrResults control doesn't have the Source Object property 
> > but using VBA I can display the SourceObject property just before 
> > the recordset is updated and it appears fine."
> >
> > As noted in my original email it seems to be related to the 
> > AllowAdditions property.  If I change this to true then the subforms 
> > show (but in this case I don't want the user to be able to add
> > records)
> >
> > Regards
> >
> > David
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of Charlotte Foust
> > Sent: Wednesday, 8 July 2015 5:42 a.m.
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Sub Subform not showing
> >
> > First off, if a parent object has no records, then its subform will 
> > have no records.  It's always been that way, and if the Allow 
> > additions is set off, you won't see either the parent or subform.  
> > The simplest way I can think of to handle this is to set Allow 
> > Additions on by default in the form/subform and then use code on the 
> > Enter event to disable additions.
> >
> > Charlotte Foust
> > (916) 206-4336
> >
> > On Sat, Jul 4, 2015 at 2:51 PM, David Emerson <newsgrps at dalyn.co.nz>
> > wrote:
> >
> > > Hi Listers,
> > >
> > > I have an Access 2010 FE and SQL 2008 BE.  I have a form
> > > (frmClients) that has a tab control with a number of tabs and 
> > > subforms
> in each tab.
> > > Most of the subforms have subforms of their own.  Record sources 
> > > for all the forms and subforms are set by assigning a 
> > > ADODB.Recordset when a record is selected from the main form 
> > > (these filter the records according to the main record selected).
> > >
> > > All is working well except for one subform (sfrSurvey) which has 
> > > its own subform (sfrResults).  All of the other subforms are set 
> > > so that records can be added but sfrSurvey has its Allow Additions 
> > > property set to no (records are added through a different process).
> > >
> > > When I open frmClient the first record happens to have a record in 
> > > sfrSurvey and this in turn shows records for sfrResults.  If I 
> > > move to another record in frmClient which has records in sfrSurvey 
> > > then records also show in sfrResults.
> > >
> > > If I move to a record in frmClient which does not have records in 
> > > sfrSurvey then sfrSurvey shows no records and the details section 
> > > is blank (which is expected).  Because sfrSurvey is blank then 
> > > sfrResults is not shown as well (as expected).
> > >
> > > Here is the problem: If I now move to a record in frmClient which 
> > > does have records in sfrSurvey the records are showing in 
> > > sfrSurvey but where the sfrResults subform should be is a white 
> > > box.  It looks like the sfrResults control doesn't have the Source 
> > > Object property but using VBA I can display the SourceObject 
> > > property just before the recordset is updated and it appears fine.
> > >
> > > The line of code where the problem appears is:
> > >
> > >         Set
> > > Me!sfrQualitySurvey.Form!sfrQualitySurveyResult.Form.Recordset
> > > =
> > > rstTemp
> > >
> > > The error number is 2467 "The expression you entered refers to an 
> > > object that is closed or doesn't exist.".
> > >
> > > There is nothing in my code that purposely changes the 
> > > SourceObject property
> > > - the only changes are to the recordsource of the subforms.  None 
> > > of the other subforms have this problem.  I have tested by 
> > > changing one of the other subforms to not allow additions and it 
> > > showed the same
> > problem.
> > > Changing it back to allowing additions solved the problem.  
> > > However, for this particular subform I don't want to allow 
> > > additions and would prefer not to have the screen indicate that 
> > > additions can be made only to have the user told they can't if they
try (by using code).
> > >
> > > Has anyone come across anything like this before?
> > >
> > > Regards
> > >
> > > David Emerson
> > > Dalyn Software Ltd
> > > Wellington, New Zealand