Main/SubForm Correct Sequence of Opening

[gray]

Hi

Access 2007 .mdb file - Split FE/BE

Can anyone tell me the correct (reliable) sequence for the opening of mainform/subforms?I have been plagued by problems for months. My Forms as ADODB RS based.

As we know, opening a main form automatically fires the opening of the subforms first. In the Open event of the subforms I build an SQL String and apply it to the respective subform's .Source param.. e.g. as below.

Public Frm_CnnADO As ADODB.Connection
Public Frm_RstADO As ADODB.Recordset
 
Public Sub Form_Open(Cancel As Integer)
Dim SQLLine As String
 
SQLLine = "SELECT * FROM xyz WHERE " blah blah
 
Set Frm_CnnADO = CurrentProject.AccessConnection
Set Frm_RstADO = New ADODB.Recordset
 
With Frm_RstADO
        Set .ActiveConnection = Frm_CnnADO
        .Source = SQLLine
        .LockType = adLockOptimistic
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .Open
End With
 
Set Me.Form.Recordset = Frm_RstADO    
Set Frm_RstADO = Nothing
Set Frm_CnnADO = Nothing

When the main form Open event fires, I similarly build an SQL string and use it in the .Source parm. BUT... at the point at which I set the mainform's RS (i.e. Set Me.Form.Recordset = Frm_RstADO), Access unloads all the Subforms... and leaves them closed so that all subsequent references to them fails!?! In fact, if I try to force open the subform with

Call Subform_Control.Form.Form_Open(0)

It crashes cos' there's an invalid property reference to form/report....

Any idea how I can open these to open reliably please?

 

[MarkK]

You can programmatically load a subform after the main form opens by setting the SourceObject property of the subform control.
1) Open the main form in design view
2) Delete the subform's name from the SourceObject property of the subform control. The SourceObject property is the first item on the Data tab of the property sheet of the subform control.
3) Add code on the main form to load the subform...

Private Sub Form_Open(Cancel as Integer)
[COLOR="Green"]  'programmatically loads a form into the subform control[/COLOR]
  Me.MySubform.SourceObject = "NameOfFormToLoad"
End Sub

HTH
Mark

 

[gray]

Hi Lagbolt... thanks for the tip.... I'll give it a whirl!

 

[gray]

Hi

Alas... no joy... I added the code as suggested but unfortunately it didn't force the subform to open...

I also tried a Cancel=Vbtrue in the unload event but it still seems to drop the subform...

 

[MarkK]

Not the dreaded 'no joy' error. That one's hard to troubleshoot.

 

[gray]

'Fraid so.... ... The sourceobject gets added OK (I put an acsaveyes in the mainform close to check it had been added ok) .. but it does not fire the subform open event.... I've put days on end into trying to get this to work properly.... I don't think our friends in Seattle really thought this one thru' so well.. it's Catch22it seems..

thnx

 

[MarkK]

If you want to post a stripped down version of your DB I'll take a look. This can't be that hard. Are you sure the OnOpen property is set to "[Event Procedure]"?
Cheers,

 

[gray]

Thnx for the offer... it's getting a little late here but I can do that in the morning.... if you can spare a few mins 2moro that is?.. cheers!

 

[gray]

Hi Lagbolt
I've chopped out loads from the attached so it may ultimately fail with some other error... however, you can see the form opening problem. Some quick notes about form opening:-
1. All Forms are ADO based; their RecordSources are therefore left blank (or , I think, they will try to load with DAO)
2. There is a fair bit of ADO.RS.Source string manipulation (based on users' requirements) but, generally, each Subform gets loaded with an entire tables worth.
3. There is a preferences table in which successful ADO.RS.Source params are stored so they can be picked up in the next load of the form.
4. The Main form and the Tab(0) Subform share the same ADO.RS.Source parameters. They are kept in step by syncing their bookmarks.
5. For the Tab(1) subforms, ADO filters are applied during the Current Event of the Main form such that only subrecords relevant to the current Main form record are displayed. This is to replicate Master/Child links
6. To speed up the initial loads, the first Subform 'opens' are exited early. The subforms are re-opened again after the Mainform is populated (but this is where things fail at the moment!).

If you add debug in all the forms' Open events you'll see the problem i.e.
i) Open the 'Addrs_Dtls_Form'
ii) Subforms are opened
iii) main form is populated
iv) when the Main formRs is reset, the subforms are unloaded
v) all subsequent references to the subforms fail.

Thanks very much for your assistance.

 

[gray]

Please ignore the above... I've sussed it thanks.....

I'd housekept my test tables recently and as a consequence there were no appropriate records for the Main form to load. I think what was happening was; when resetting the main form recordset, Access unloaded the subforms but, because there were no records in the main form, did not re-open them... I've added a few more records into the table and all works well again.

Thanks for the assistance....

 

[MarkK]

Glad you could get it solved.