Is it possible to set sub-form loading order? 2

[inso18]

Hello there.
I need some help in VBA form coding.

I have a main form, which has many sub-forms. The some subforms take data from other subforms, so they are dependent, and so it is important in which order they all load. I've noticed they load in the opposite order of they creation time, meaning, the last created subform loads first.

That interrupts with some data dependences.

Is it possible to set their loading order in a custom way?

Thanks in advance,
inso18.

 

[TheAceMan1]

How are ya inso18 . . .

Yes its possible!

Normally a form with subform(s) opens from the subform(s) out to the mainform (mainform is last to open).

What you need to do is open the subforms sequentially, in the order you require. You do this by:
[ol][li]In design view of the mainform, copy to notepad the recordsource of each subform. Then delete each subform recordsource!
Perform a Save.[/li]
[li]In the [blue]On Load[/blue] event of the mainform, open each subform as necessary by reassigning the recordsource:

[blue]   [subFormName1].Form.RecordSource = "RecordSourceText1"
   Do Events
   [subFormName2].Form.RecordSource = "RecordSourceText2"
   Do Events
   '
   '
   [subFormNameN].Form.RecordSource = "RecordSourceTextN"
   Do Events[/blue]

Do Events just gives time for the subForm to fully open.[/li][/ol]

If any subForms are dependent on the mainform, just do the same with the mainform 1st!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[inso18]

A shrewd method!
Thanks and thanks for explaining what the DoEvents does.

Cheers, inso18.

 

[inso18]

TheAceMan, sorry
the solution seemed logical, but it actually doesn't work.

subforms keep loading by their creation on main form date order. To veryfiy this I code a debug.pring on each subforms load event.

 

[Remou]

Rather than loading the record source, load the forms themselves into the subform control. However, it is possible that you can sort out most of your difficulties by referring in your design to controls (named, let's say, according to RVBA naming conventions), rather than fields. For example, the Link Master and Link Child Fields:

Link Master Field: txtMainID 'rather than MainID
Link Child Fields: MainID 'The field should be ok here

 

[inso18]

Maybe it is because first the subforms are being loaded and only then mainforms load event is being activated?

 

[Remou]

That is correct, the load order is subforms first then main.

It is often not necessary to load more than a few subforms at a time, as subforms can be loaded at runtime:

Me.sfrmSubformControlName.SourceObject="sfrmSomeForm

 

[inso18]

Well, thanks, I'll try to figure it out how to both name the controls as you suggested, and load them in correct order, as some subforms' controls refer to other subforms' controls.

 

[Remou]

If you want to go with the RVBA naming convention:

http://www.xoc.net/standards/rvbanc.asp

Here is a discussion on other methods:

http://www.granite.ab.ca/Access/tablefieldnaming.htm

Just to be clear, my point was that the control should be named to something other that the field that it contains and that often it is the control that should be referenced rather than the field.

 

[TheAceMan1]

inso18 . . .

Post what you've tried! . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Knicks]

Don't know if this helps but my understanding is that you cannot load the recordsource of a subreport at run-time so the work around is to actually open the subform in design view first and then set the recordsource. I used a button to first open the subreport rptstatecontacts in design view and then set the recordsource. Save and close, then open the rptstate3 which is the main form with the linked subreport. The on load event for the main report sets the recordsource for the main report then but as you can see the subreport is handled first in design view.


Dim stDocName As String

DoCmd.OpenReport "rptStateContacts", acViewDesign
Reports("rptStateContacts").RecordSource = "ContactDate" & Right(Forms!frmobject!list2, 21)
DoCmd.Close acReport, "rptStateContacts", acSaveYes


stDocName = "rptState3"
DoCmd.OpenReport stDocName, acPreview

 

[TheAceMan1]

inso18 . . .

I have done this in A2K (currently searching for the Dbs where the method was tried). I'll report back but it won't be until this evening 7:00pm USA EST.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[inso18]

I'll try for now to construct the main form with the subform using correct naming conventions and then will try to determine the order of subforms' loading by changing the subforms' container recordsource.

TheAceMan, thanks for checking up, tell me if it works for you.

Knicks, thanks for the information, I didn't know you can refer to a form/report by Report("rptName").ControlName, now I do, thanks.

 

[TheAceMan1]

inso18 . . .

[green]Confirmed![/green] I have a mainform with one bound subform and linked to the mainform via master/child link properties. There are also two unbound subforms whose recordsources are dependent on data from the bound subform.

Normally, one ofthe unbound subforms tries to load 1st but errors because the bound subform isn't open yet (data not available). I switch the order as prescribed earlier and they load no problemo.

Do post what you've tried ... I'm very curious!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[inso18]

TheAceMan
Sorry for the long delay, I've been without a computer for a few days.

I've tried what you've said and it's working allright.
Thanks!