Subforms are one of the great features of Microsoft Access. If you have ever tried to replicate the Form/Subform paradigm of Access in Visual Basic, you will appreciate how simple the process really is.

The down side is that it is too simple to add subforms. Once a developer gets started creating subforms, they often don't know when to quit. Before long, they have a dozen subforms pointing to a dozen related tables referencing thousands of related records. Is it any wonder performance suffers?

So, how do you get around this performance hit? Simple; don't load any data until the user actually asks to see it. That is what this month's article is all about and as usual, the code is available in the download file.

The image below tells the whole story. My form has a tab control with four pages, one for each of the tables referenced. The first page has fields of the master form embedded in it while the other three pages will reference its corresponding subform. However, as you can see from the design-view image of the form below, there aren't any subforms embedded on these tab-pages. There is one unbound Subform object floating around in the upper right-hand corner where it appears out of place.  Through code, it is dynamically resized, moved and loaded with the correct subform, depending on which page is selected. Let's take a look at the code that manipulates this simple control.

Dynamically Load Subform on Page Change

The code for this process is very, very simple. If there is a trick, it is knowing which event receives the code. When I was preparing the code for this article, I instinctively selected the Click() event instead of the Change() event. Needless to say, it did not behave as I had intended. Once I corrected that issue, things proceeded pretty smoothly. Check out the code, and keep in mind this is all the code in the entire application.

   Private Sub tabPublisher_Change()
      On Error Resume Next
 
      Dim strSubformName As String
      
      ' Based on the page selected, set the Subform Name variable
      Select Case Me.tabPublisher
         Case 0: strSubformName = ""
         Case 1: strSubformName = "sfrmAdditionalInfo"
         Case 2: strSubformName = "sfrmEmployeeInfo"
         Case 3: strSubformName = "sfrmPublisherTitles"
         Case Else: strSubformName = ""
      End Select
   
      ' If a subform exists, then set the properties, otherwise
      ' just hide the generic subform object
      If strSubformName = "" Then
         Me.objSubform.Visible = False
      Else
         Me.objSubform.SourceObject = strSubformName
         Me.objSubform.LinkMasterFields = "pub_id"
         Me.objSubform.LinkChildFields = "pub_id"
         
         Me.objSubform.Top = Me.lblAnchor.Top
         Me.objSubform.Left = Me.lblAnchor.Left
         Me.objSubform.Width = 0.95 * Me.tabPublisher.Width
         Me.objSubform.Height = 0.75 * Me.tabPublisher.Height
         
         Me.objSubform.Visible = True
      End If
   
   End Sub

Notice the tight code method used in the Select Case statement. This syntax makes sense in our application since each case performs a simple variable assignment and the code is clean and easily readable. I could have combined the Select statement with the code in the If branch, but that would have resulted in repetition of the subform property assignments. However, if your subforms need to be sized or placed differently, that is exactly what you would do.

Of special interest is the pair of properties that set the LinkMasterFields and LinkChildFields. All of my subforms share the same common field, pub_id. In most cases, the Primary Key of the master form is likely going to be the one on which the subforms are linked, but there may be times the subform properties are different. In that case, you may set the link properties dynamically.

Final Thoughts

By implementing this paradigm, the number of table connections was cut from guaranteed four references to either one when page p00 is selected, or two when one of the other pages is active. While this performance gain is not likely noticeable with this little demo application, it is very appreciable where there are tens of thousands of records and ten or more subforms.

This trick alone was enough to completely solve performance issues for one of my clients.  After months of "why is this application so slow?" complaints, and hours of pouring over server settings, the basic issue described above of loading data only when it's requested solved my problems and made me a hero.