Posts Tagged ‘Access VBA’
This article demonstrates how to create a co-ordinated set of Access forms that could be used, among other things, as a Dashboard or Launching Pad in an Access User Interface. Form content depends on the needs of the application. The forms in the demonstration application are just simple examples to illustrate what can be done. They are there just to show what the code behind the scenes does.
The concept is similar to Frames in HTML Documents.
There are six forms in the demonstration application:
The other forms take turns populating either frmShell’s subform control second level subforms. The form name indicates each form’s relative position in the overall hierarchy.
|The content forms can be loaded as individual standalone forms.|
frmContent and its two child forms
frmShell_ContentTabs with Mouse pointing at the Child Tab
frmShell populated with frmShell_ContentTabs (populated with frmShell_ContentA)
frmShell_ContentA_1 and frmShell_ContentA_2 populate two subform controls on frmChild when that form is the main child form of frmShell
frmShell_ContentTabs is an alternate child form to frmShell. This form uses a tabbed style of interface to swap its subform content in and out.
frmShell_ContentWelcome populates frmShell with startup information.
Behind the Scenes – the Code
The main code that makes it all work is quite simple as long as you get the notation right. Content screens are swapped by assigning a form to the SourceObject property of the desired subform control.
For example, if a form has a subform control named fsbMainWindow, then a content form (for example, frmContent1) can be assigned to the control with this statement:
me!fsbMainWindow.SourceObject = “frmContent1”
Once the content form has been assigned to the subform control you can then refer to any controls or public properties in one of two ways:
me!fsbMainWindow.Form.[name of property or control]
or by creating a form object and assigning the subform object to the form object. Of course you have to declare a from object.
Dim frm as Form
then you can use the object by assigning the subform control to it.
Set frm = Me!fsbmMainWindow.Form
You can then use the object to refer to the content form and its controls and properties.
frm.txtDate = Now()
assigned the current date and time to a textbox named txtDate on the content form.
If there are several ‘windows’ on the form, then the object approach makes it easier to keep track of which window content you are referring to.
A subform can talk back to the form that contains it using its own parent property.
me.parent.CurrentChild = me.name
is a trivial example. Of course you would have to declare a public variable or property, CurrentChild in the parent form.
It is possible to nest up to seven levels (Access XP and later, three in Access 2000) of subforms on a form. The notation can be confusing because you have to have the bang operator (!) in all the right places. For example,
refers to the subform one level down. If you want to refer to a subform nested on the form one level down, the notation is
Me!fsbMainWindow.Form![name of subform control].Form
For a general reference on referring to sub forms and sub reports see this page on The Access Web.
If you need to set the focus to a control on a sub form, first set focus to the sub form control and then set focus to the control you want to have focus.
General Development Guidelines
If you will be swapping several forms in and out of the same subform control, it helps to first create a simple empty form that is set up with the appearance that you would like all of the forms that you will use in that particular subform. Use that form as a template for each of the others.
A naming convention also helps keep the various forms straight in you mind. For example begin the name of each related form with frm followed by a generic name for the group of forms. For each sub form, append an underscore followed by a meaningful name for the particular sub form. Using this approach, all of the related forms are listed together in the database window or navigation pane depending on your version of Access.
Using a shell form to ‘house’ the main forms of a user interface makes it easier to create and maintain a consistent ‘look’ and feel for the application.
The demonsration file can be downloaded from:
Microsoft MVP Walter Niesz has put together a nice FAQ (Frequently Asked Question) article over at UtterAccess.com to introduce new Access programmers to de-bugging VBA Code. The article illustrates three basic debugging techniques that should be in every VBA programmer’s toolkit. If you are new to VBA or Access have a look at Debugging Techniques. While you are at UtterAccess.com have a look around at the thousands of opportunities to learn from other’s questions and to find answers to your own questions. (disclaimer: as a forum administrator at UtterAccess I enthusiastically support the forum which is, “without doubt is the only source for all of your Microsoft Access help needs…and more! ")