OnChange Late Bind a Subform on a Tab Control

Online Data Entry Jobs

Online Data Entry Jobs

Get Instant Access

The degradation of performance is a never-ending battle for the Access developer as users want more and more information, on the same form no less. But, no matter how accommodating you are to satiate their every need and desire, the smallest of performance hits causes your phone to ring and e-mail inbox to overflow with complaints. Even when you tell them ahead of time!

The reduction of Access performance sometimes sneaks up on you slowly, other times it's instantaneous. In case you're new, the instantaneous ones are a blessing, as you can quickly reverse whatever change you just made. The sneaky ones are typically a combination of a myriad of causes, of which you can spend several hours trying to revert, reprogram, or find a suitable workaround to restore the application to a usable performance level.

Now, with this kind of introduction, you might assume that this section is going to discuss some incredibly complex topics such as benchmarking, timing execution, or some other such fancy methodology. Unfortunately, that is the farthest thing from the truth. The objective of this particular example is to demonstrate how to work around a very typical performance drain.

In Access, with multiuser applications, it is not feasible to have data remain on each user's local machine, as the synchronization of the data would be very difficult. So, the next obvious step is to place a single database file (.mdb) on the network, and let all users share it. If you have ever done this, you have learned about the concept of corruption and know that it doesn't take long for users to begin complaining about the performance. The next step a developer can take is to split all of the user interface (UI) objects from the data into separate database files. Then each user can have a copy of the UI.mdb on his or her local machine, and only the data is shared. This action causes a significant increase in performance as form, query, and report definitions no longer need to pass from the network to each user machine. This reduction of network traffic is a significant factor in the increase. So, when all of these steps are taken, the only thing remaining to cause performance problems is the retrieval of data from the network.

When a form is fairly small, lightly populated with few controls, the ability for that form to load is fairly snappy. But as users ask for more, you find yourself adding a combo box here, a subform there, and the next thing you know, the form requires 30 seconds just to open. Of course your development machine has both databases on your local hard disk, so all of your tests don't net the same results. You might argue with the user about their sense of time, then physically walk to their machine, dramatically snap your arm to display your calculator wristwatch, and start your stopwatch as they try to open the form. You stare in horror as your regular load time is surpassed then abused, and you start looking for the nearest fire alarm to cause a distraction while you slip back to your office.

Don't let this be you.

It is not uncommon to create a tab control, and on each tab, place a different subform. In even the simplest of forms, the number of subforms can quickly increase. For example, customers can have multiple addresses, telephone numbers, e-mail addresses, contact journal entries, payment history, and of course the orders themselves. If a subform is made for each of these data points, you can imagine that the form is going to begin to get sluggish.

One final note to bring this point home is that maybe not every tab is used during every data entry session. There may be one set of users who only use one subset of the tabs, while another set of users uses a different subset. Also, in the example of surveys, there may be some tabs that are skipped if a question is answered a certain way.

For all of these reasons, never assume that all subforms need to be populated every time the form is opened. On the basis of this concept, it stands to reason that there is a way to prevent the data from being loaded to the subform. Being able to prevent it first requires you to understand how it works.

A subform control is just as generic of a control as a text box or combo box, but has no functionality until several properties are set. The following properties are necessary for a subform to load and display the data.

Property

Description

SourceObject

The name of the form to be displayed in the subform control

Link Child Field

The name of a field (or fields) on the subform that matches a

field on the main form

Link Master Field

The name of a field (or fields) on the main form that matches

a field on the subform

If the only way that you have created a subform on a main form is by using the Form Wizard, the Subform Control Wizard, or by dragging and dropping a form object from the Database window to the main form, then you may not have ever had the need to populate these properties. As you can see from the numerous techniques, Access does a very good job of insulating a user from having to deal with the minute details. This is good for development in speed and ease of use, but not so good when it is time to crawl under the hood and figure out what makes the motor run.

When a main form loads, and it encounters a subform, the SourceObject property is examined, and then all of the event procedures of that form (the subform) have to be executed. When it is finished, the main form returns to finish any remaining load tasks. Imagine having 10 subforms, each based on a complex query, and each with some code that needs to execute during the Load, Open, and/or Current event procedures. You can see that the ticks of the clock will quickly add up to a nasty e-mail from an unhappy user.

So, suppose there was a way to prevent the data from loading, and any of the event procedures from triggering, until such a time that the user absolutely needed to view that data. With each subform located on a separate tab, then the On Change event of the tab control itself could be used to implement an On Demand load system for the form.

Imagine the joy of the user when they go from 45 seconds of load time to only 5 seconds. (It has been done.) They'll think you're a genius. The remainder of this chapter will demonstrate how to implement a rather generic On Demand subform retrieval system that utilizes the concept of binding the subform when needed, and not automatically. This concept is also known as Late Binding, and can also be applied to combo boxes, list boxes, OLE bound object frames, charts, and any other object that requires data retrieval or has code associated to it.

The following sub procedure is a generic routine that binds a subform to the main form and then resets the LinkChildField property.

Sub BindSubform ( _

psfrm As SubForm, _ pstrSourceObject As String, _ pstrLinkChildField As String) 'Generically Bind a Subform to a Main Form

'Example Proc Call: BindSubform(sfrmTest, "frmSome_Test", "TestID") With psfrm

'Only Bind if not previously bound If Len(.SourceObject) = 0 Then

'Specify the name of the subform .SourceObject = pstrSourceObject 'Set the Link Child Field .LinkChildFields = pstrLinkChildField End If End With End Sub

It's important to note that for this workaround (to save load time) to be successful, all of the subforms that are placed on the main form must not have a value entered for the SourceObject properties. (During development, this is not needed, but just before deployment, be sure to remove the entries.) By not having the subforms connected, there is no load time incurred against their existence. A caveat to this is that you may have one or two subforms on the first tab that the user absolutely insists on displaying the first tab. If that is the case, then populate it or them via the normal method.

In the code sample above, there is a check to see if the SourceObject has been populated. If not, then it gets populated, which will cause the loading of the subform data to be executed. This generic procedure can be called any number of times from within the forms, and saves the repetition of the same code being written for every subform that needs to be bound.

The code to handle the call to the BindSubform procedure, when the user changes the Tab on the Tab control, is in the next example.

Private Sub tabProj_Change()

'This procedure handles any needs of the user after

'they select a different tab on a tab control

'Using the control name of each tab, determine what

needs to

'happen on that tab.

Select Case tabProj.Pages(tabProj.Value).Name

Case "pgStates"

'Perform data validation that prevents loading

this tab

'until all data entry is correct on Tab1

If IsValidate_Tab1 Then

'Bind the States subform

BindSubform sfrmStates, MfrmProject_StatesM,

"ProjectID"

End If

Case "pgLocations"

'Bind Locations

BindSubform sfrmLocation, _

"frmProject_Locations", "ProjectStateID"

Case Else

'Performing any special processing for other tabs

End Select

End Sub

This example starts with the declaration of the On Change event procedure for a tab control that is named tabProj . The prefix tab specifies that the control is a tab control. A Select...Case...End Select structure is used, because there may be many subforms on the main form, so an If...Then...Else...End If structure would become quite convoluted.

The Select structure examines the Pages collection of the tab control. tabProj.Value is the number value found in the PageIndex property of the tab. By using that number as the index for the Pages collection, it is the same as referring directly to the tab itself. From there, the inspection of the Name property returns the tab's assigned control name.

What is gained by taking this route is the ability to refer to the assigned tab name, instead of a number, which adds to the readability and debug-ability of the code. For starters, the PageIndex value starts at 0, which can be easily forgotten, or overlooked, and cause a problem. Next, the PageIndex of a tab can change, as it specifies the order of the tabs, but the Name stays the same. So, if you base your code on the name and then if you decide to move the order of the tabs, your code does not need to be changed to retain the same functionality.

The call to the BindSubform procedure requires some manual intervention in that you need to know the exact names of the subform control, the source object form that is to be bound, and the LinkChildField to be used.

This example is very long, and probably difficult for even intermediate developers to understand completely. But, hopefully the knowledge that there is this kind of control over a seemingly uncontrollable situation may change your outlook of Access and VBA programming. Knowing that you are in control may make other programming challenges easier for you to master.

Was this article helpful?

+1 0

Responses

  • katariina
    How to bind a subform to a main form?
    8 years ago
  • michael
    How to test many controls in a subform access VBA?
    8 years ago

Post a comment