Subforms on tab control slow to open - Access 2007

JSSenior
JSSenior used Ask the Experts™
on
I have 7 tabs each with subforms.  Each is child to a combo on the main form.  The main form is slow to open is there a best way of activating a subforms independantly when a tab is selected?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could try using an OnChange event in your tab control to load the recordsource of the corresponding subform.  For example:

Private Sub NameOfTabControl_Change()
      Select Case NameOfTabControl.value
            Case 0
                  Me.NameOfSubform1.Form.RecordSource = "Insert recordsource here"
            Case 1
                  Me.NameOfSubform2.Form.RecordSource = "Insert recordsource here"
            Case 2
                  Me.NameOfSubform3.Form.RecordSource = "Insert recordsource here"
      End Select
End Sub

You would also call the procedure when you open your main form so that the first visible subform would be populated.

Author

Commented:
Would the record source for each subform then need to be deleted when closing the application for this to work?
Not at all.  Instead of populating all the sub-forms at once when you open your main form, each sub-form gets populated individually as you go to the specific tab.  If you click each tab and all the sub-forms gets a RecordSource, it would be then be the same as your current setup.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
But once the record source of subform1 is populated wont it always then be there each time the application is opened?
If you close and reopen your main form that contains your sub-forms, everything is reinitialized.  The RecordSources will all be empty.

Author

Commented:
Thank you for your help i will give this a go ehen i get to the office in the morning.

Author

Commented:
I tried it but had errors which I think are a result of my subforms having vba running from Form_Current() to drive the checkboxes etc on the subforms.  So when the parent Combo on the main form changes the checkboxes refresh.  Any idea's? maybe i could take a differnt approach.
You could try putting the process you have in each subform's OnCurrent event in the subform's OnOpen event.

Author

Commented:
Sorry how would I do that?
Well unless I misunderstood you, you mentioned that each subform had an OnCurrent event. If that is the case you would move the process to the subform's On Open event.

Author

Commented:
Using the OnOpen event did'nt work either.

I solved it by creating empty dummy tables adding  "_TEMP" to the end of there names and putting this in the Record Source which then enabled your code to work.

I also got it to work another way using your code but driving the Source Object instead of the Record Source.

I timed the opening times, bear in mind I did this out of peak time so the network drive was not so busy.  The original took 12 secs to open, the version changing the Record Source was 4 secs and the winner was the version changing the Source object which took 2 secs.

Are you aware if messing which the properties I have listed negatively impact's anything?
That's great that you got it to work.  Changing the SourceObject is another option that I neglected to mention.  This would not have any negative impact.  I'm not sure about the need for the dummy tables and I probably won't be able to figure out how to do without them without looking at your design.  But if you're happy with it then that's good.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial