Force Open Unlinked SubForm

[gray]

Hi All

Access2002/2007
WinXpPro Sp2

I've an unlinked subForm that I make reference to in the Form_Open event of my mainForm. When the table for the mainForm is empty (e.g. vanilla system) the reference fails because the subForm is not yet loaded. I need therefore to 'force' it open.

I thought this would do the trick in the mainForm Form_Open event:

If My_Form_Is_Loaded("My_subForm") = False Then
        Me.My_subForm.SourceObject = "My_subForm_Form"
        Me.My_subForm.Form.SetFocus
End If

I was wrong.... .... anyone know if/how this can be done plse?

Thanks

 

[gray]

Hi

Just wondered if anyone had any general ideas on the handling of empty subforms?

I make dozens of references between my main and subforms and I'm plagued by errors when subforms don't get opened at start up... or are closed when a change to the mainform filter does not return any records.

To test a subform is open/has records would be a good start.. I've tried:-

if me.My_Subform.form.recordset.recordcount > 0 then 'Proceed..

if me.My_Subform.form.recordsetclone.recordcount > 0 then 'Proceed...

if not isNull(me.My_Subform.form.recordset.recordcount) then 'Proceed...

if IsNumeric(me.My_subform.Form.Recordset.RecordCount) then 'Proceed...

if not isEmpty(me.My_subform.Form.Recordset) then 'Proceed...

If not me.My_subform.Form.Recordset is Nothing then 'Proceed...

etc etc ad infinitum... none them are reliable.... and the only alternative I can find (which I use at the moment) of cycling around the Forms collection to see if a given subform is open seems insanely labourious and slow...

So I guess I have three questions?

1. Does anyone know of a reliable test that can be done to see if a form/subform is open (other then checking the Forms collection).

2. Can a subform be 'forced' open even if it does not have any records so that a least a ecordcout of 0 can be tested

3. I'm considering adding a 'dummy' record in each of my tables with a text of "No Records Found", hiding that record but making it visible if a given form/subform has 0 records. Anyone tried anything similar?

This is advice I think all relatvely new Access coders would benefit from.

Thanks

 

[Jeanette Cunningham]

As far as I know, a subform always loads before the parent form loads. It has always been that way.

It is more likely that the main form is the problem - the detail section will go blank if there are no records for the main form in some circumstances.
See if this helps
http://www.allenbrowne.com/casu-20.html

 

[gray]

HI

Thanks.... Yes, I guess that makes sense...

So when a call such as this is made

If IsNull(Me.My_SubForm.Form.Controls("Main_TextBox") then xyz ..

it would fail with
err 2455 you entered an expression that has an invalid reference to the property Form/Report

because none of the controls (including the subform control) would be available....

Thanks

 

[gray]

Hi

I seem to be stuck on this problem.

When I load a filter into my mainform that does not yield records, my subForm appears to unload and therefore any references to it, of which there are many, fail.

To confirm this I've tested the "loaded" status of my subform before and after the (offending) filter.

The setting of allowadditions = true as suggested in Allan Browne's article (above) doesn't seem to work. I have AllowAdditions permanently set to Yes for both main and subforms.

I have have even set Cancel=True in the Unload event of the subform but it still gets unloaded when the filter is applied.

There must be someway to re-open the subform ??

I tried to do a goto newrecord in the subform but of course it fails when trying to set the focus to it in order to call a newrecord.

Catch 22? Any more ideas anyone?

Thanks in desperation....

 

[boblarson]

As far as I know, a subform always loads before the parent form loads. It has always been that way.

Yes, that is correct (just thought I would pop that in as an extra bit of support for that statement because sometimes it seems counterintuitive).

 

[boblarson]

Actually, can you upload a copy of your database (with bogus data, of course) so we can take a look? It would probably be faster.

 

[gray]

Ok Bob.. thanks... It'll take me a while to strip it out.... back to you as soon as I have... cheers

 

[gray]

Hi Bob

Well... I think I've spotted it....Whilst stripping out the Db, I noticed an odd 'Order By' floating around... it was one of those:-

'[Lookup__My__Control__Name__Textbox].[Category]'

types (with double underscores). . I took out the above OrderBy string and my form began behaving itself!
I think what's happened...

To lose 'phantom' sorts and filters, I store them when they are first requested by writing them as a string in a table... I then re-apply them when opening any given form.

When re-applying the test filter (which does not yeild results), the ApplyFilter event also re-asserted the above OrderBy ..... and even though it was quite happy to add it to the form in the first place, it seems it did not like it when re-applied..... which is odd?? They are literally copied out and back as a string... I'll have to look at storing them some other way..

Thanks for assisting Bob...