Help with Code to Load Subform as Tabs Change

[EternalMyrtle]

Hello,

I am using the following code I found to hide and show my subforms as the tab control pages are changed:

Private Sub TabControlMain_Change()

'Set the source object for the subforms as the tab control pages change.  This should help to decrease load times on open

    Dim pgn As Access.Page

    For Each VarCtlSubform In Array(Me.subformProjectsBuildingTypes, Me.subformProjectsClients, Me.subformProjectsClientContacts, Me.subformProjectsConsultants, subformProjectsContacts)
    Set pgn = VarCtlSubform.Parent
        If pgn.PageIndex <> Me.TabControlMain.Value Then
            If VarCtlSubform.SourceObject <> "" Then
                VarCtlSubform.SourceObject = ""
            End If
        Else
            If VarCtlSubform.SourceObject <> VarCtlSubform.Tag Then
                VarCtlSubform.SourceObject = VarCtlSubform.Tag
                
            End If
        End If
    Next

Basically, you remove the subform's source object and put the name into the tag property.

The code itself works great EXCEPT my subforms cannot be edited or allow new records when in use. The subforms' data properties are set to Allow Additions = Yes and Allow Edits = Yes.

Is there some way to use this code AND have subforms that can be edited and allow new records or is it a lost cause?

Is there a better way? Maybe doing the same with the subform's recordsource rather than using the source object property?

BTW, I am doing this to help with load times in subform-heavy forms. Also, I have been getting a "Cannot open anymore databases" error 3048 when several control-heavy forms are open at once. I was hoping this could help with that, too.

I hope someone can help. Thank you!!

 

[Minty]

I think some of your problem is that it appears that you have the tab page names set to the same as you subform names, and it may be getting a little confused.

I have used a similar method and it works fine, except I don't loop around all the pages automatically (Although it looks very similar)

Five subforms shouldn't be causing a huge burden, when your main form loads have you tried loading it with 0 value record?
How many records are in your parent level and child recordsets?
Is this a split database - where is the back end? How are you connected?

(Lots of questions )

 

[EternalMyrtle]

Hi Minty,

Thanks for your reply. I will try to answer your questions:

Yes, the database is split. The backend is on our computer network. The front end is local. I don't have much more information than that --it's just a normal LAN connection.

I am not dealing with huge recordsets at all. There are only about 275 records in the parent form. Not sure about the child level but no more than a couple thousand for each.

How do I load with a 0 value? That sounds promising...

Yes, the tag names are the same as the subform names. I will try renaming and see if that helps.

Thanks!

 

[Minty]

Are you on a SQL back end or access?

Do you have lots of combo boxes on your forms?
When I researched a speed problem we had here with a remote site, moving the record source for the combo boxes to a local copy of the table for any mainly fixed values made a big difference.

Check your network connectivity - LAN on 100Mbps should be minimum for decent performance. We have noticed a difference even between 100Mb and 1Gb Network speeds, although we are handling bigger record sets than you are dealing with.

0 Value record - Assuming your main form is bound, set your forms record set to SELECT Field1, Field2 etc etc WHERE PK_Field = 0 this will load no data but your fields will exist. I'm assuming it not a data entry form, and that end user will then search for a record using tools on the form?

 

[jdraw]

Take a look at the database sample in CJ's response in this thread.
It may help with subform and sourceobject.

 

[EternalMyrtle]

Hello again,

Access backend :/

Yes, there are lots of combo boxes...moving the record sources for the combo boxes to a local table sounds like a lot of effort but perhaps worth considering if I cannot find another way.

It is actually a data entry form but perhaps I can load just a single record at first and then load the rest of recordset later using a timer control? Something else to consider.

Thanks for all your help.

 

[EternalMyrtle]

Well, it took some figuring out but I *think* I found a solution. I added code to specifically set the subform property to allow additions. Here is the final code with the red being the changes:

 Dim pgn As Access.Page
[COLOR="red"] 
 Dim VarCtlSubform As Variant
 Dim strSubForm As String
 Dim sbf As SubForm
 [/COLOR]

For Each VarCtlSubform In Array(Me.subformProjectsBuildingTypes, Me.subformProjectsClients, Me.subformProjectsClientContacts, Me.subformProjectsDesignTeam, Me.subformProjectsConsultants, subformProjectsContacts)
    Set pgn = VarCtlSubform.Parent
        If pgn.PageIndex <> Me.TabControlMain.Value Then
            If VarCtlSubform.SourceObject <> "" Then
                VarCtlSubform.SourceObject = ""
            End If
        Else
        
            If VarCtlSubform.SourceObject <> VarCtlSubform.Tag Then
                VarCtlSubform.SourceObject = VarCtlSubform.Tag
                
               [COLOR="Red"] strSubForm = VarCtlSubform.Name
                Set sbf = Me.Controls(strSubForm)
                sbf.Form.AllowAdditions = True
                sbf.Form.AllowEdits = True[/COLOR]
            End If
        End If
    Next
    
 End Sub

Now I am wondering if I have to Set sbf to nothing somewhere??

 

[EternalMyrtle]

Obviously, to allow edits you have to do the same for that property so

sbf.Form.AllowEdits = True

 

[Minty]

If it's data entry on the parent level I assume you have set the form to data entry it will then only load on a new record, Can I ask why you would need so many sub forms at data entry point ?
You may have a perfectly legitimate reason, but it might be that if you are putting default entries into those sub forms this could be achieved in the background without even having to load the related tables?

E.g. if you set up test results or a status you can use code to add to the underlying child record without having to have the sub form there?

 

[EternalMyrtle]

Hi,

If it's data entry on the parent level I assume you have set the form to data entry it will then only load on a new record, Can I ask why you would need so many sub forms at data entry point ?

The main form is not set to data entry. The properties of the main form are:

data entry = no
allow edits = yes
allow additions = yes

When the user clicks a button to add a new record it opens data entry only. The main point is to be able to add data for an existing project.

 

[EternalMyrtle]

Another tweak to the code in case someone else ever has reason to use it...

It is a good idea to declare the VarCtlSubform variable and you will have to if Option Explicit is on.

I will update post # 7 accordingly