Invisible Forms Revisited: Park Your Global Values Here

Back in Feb-2003, Tobi Hoffman wrote an article about using invisible forms in conjunction with Citrix or Terminal Services based Access databases. Since then, Tobi has discovered that adding an invisible form to your application can make a number of difficult tasks much easier.

Note:You should look at TempVars.  Search for TempVar

Over the past year, my article on invisible forms has brought me a fair number of inquiries from Smart Access readers, though most questions were about the use of Citrix rather than the invisible form itself. That’s too bad, because I’ve found that including an invisible form in my application is a useful tool, so much so that it’s a topic worth revisiting to show what an invisible form can do for you.

Two years ago, I had a problem with an application that was running on Citrix, which is a virtual desktop that many people can come into in order to use a single copy of a program from several different locations. The problem was that all users were going into the same actual directory and using the same actual front-end database. In general, this is a bad idea in database design because, on a normal network, it’s often slow and can lead to database corruption (though that’s never occurred in my experience with Citrix). The invisible form was something that grew out of the need to keep some pieces of data unique to each user so that people didn’t interfere with each other. While it worked superbly for this purpose, I eventually found a way to temporarily copy the front end to each user’s Citrix desktop, and the original need for the invisible form disappeared. But, in the meantime, a host of other uses popped up.

How many uses? I did a search in my application on frmCurrent, as I named the invisible form in my application, and found 288 references–288 places where I found using an invisible form solved a problem for me. These 288 places fell into roughly eight categories, and once I started thinking about how I used the invisible form, I found even more uses for this strange object!

To use my invisible form, I have to know who the current user is. This isn’t new: In any application, logging the user in is a critical step. My login form uses a combo box that list the users. The combo box’s RowSource extracts a number of fields whose data I eventually store in controls on my invisible form (see Figure 1 for the query behind the combo box). The user’s id and password are checked and, if valid, frmCurrent, the invisible form, is opened, and a number of controls get set to the values in the combo box. Here’s the code that updates the controls on my invisible form from the login form:

Figure 1

Setting user permissions

What do I do with this information that’s hanging around in the form? To begin with, while it’s sometimes necessary to design different forms for different users, I often use one form for all users and just set some controls on the form to be visible for some users and not visible for others. One of my more recent applications divides its users into two independent levels: Contracts and Finance. These groups have a mutual “you can see my data–but don’t touch!” attitude to their data. In many forms in this application, I have code that checks the txtUserLevel control on the invisible form and sets visible and read-only attributes based on that information:

You could, of course, use global variables to hold this information. But the benefit of the invisible form is that I can choose to make the form visible during development. This makes it considerably easier not only to check these critical values, but also to change the values directly as part of my testing process. Also, by caching this data in my invisible form, I avoid repeated trips to my database to extract information: I just retrieve the data once and store it in my invisible form. Also, unlike a global variable, I can put code in my invisible form to take action, based on the data in the form (sort of an Access user interface way of implementing the equivalent of a database’s Trigger).

Keeping track of the current form

In another application, the main piece of data that I dealt with was the Project entity. For billing purposes, each Project was assigned a Charge Number and Task Number, a unique combination for each project. Since any Project could have multiple Charge/Task Number combinations, I needed a subform to show that detail. However, when the Charge/Task subform is on a non-Project form (for example, the Audits form), I needed to give my users a way to find the Project associated with the Charge and Task. I built a form to show each Charge/Task Number and the Project associated with it. A double-click on any Charge/Task entry would find the associated Project.

But how to integrate this form with the Audits form, the Financial Summary form, or any of the other forms that listed Charges and Tasks? Furthermore, I had to keep the Contracts and Financial data separate (remember, “look but don’t touch my data, please”).

Instead of making multiple copies of the find-it form, which would be launched from a customized Find button on many forms, I made my find-it form a Menubar item. But now the question was how to tell which form my find-it routine was being called from.

When the Project, Audits, or Financial Summary form was brought up, its OnActivate event would include this line that stores the form’s name on my invisible form:

Me.Name is always the name of the current form, so I could just copy and paste this same line into every form’s On_Activate event. I use the line in both Form_Load and On_Activate. Putting the code in the On_Activate event makes sure that the form that the user has just switched to–the form on top–was the current form for the search function. That way, if the user opens Form A, then Form B, then goes back to A and uses the Menubar search item, I can use the setting in my invisible form to know that I’m to find the record on A. Here’s the code that uses the information from my find-it form:

Setting the ControlSource

I frequently use the controls in the invisible form to control the data displayed in combo boxes, listboxes, and dropdown lists. A typical combo box’s RowSource looks like this in my applications:

Blocking unauthorized users

Are there people out there thinking they can get into places they shouldn’t by opening the database window and opening a form directly? I have the Form_Load event kick them out if they have the wrong user level. Since frmCurrent won’t be loaded if a user goes directly to the database window, this code is going to generate an error message (you can add more sophisticated error trapping than I have):

Customizing reports and forms

Sometimes reminders are needed to get things done. Adding this code to my main menu’s Form_Load event opens a list of overdue items. The query behind this report uses the information on the invisible form to customize the report for the user and his or her user level:

You need to add some code in the report to suppress a “Report cancelled” message for the managers who don’t have any overdue items (it could happen!). In addition, I clear a control on the invisible form to prevent a report with no data from participating in a process that I’ll describe later:

Some of my forms go beyond just making objects visible or invisible, depending on the user. In one of my forms I have a Select Case structure that checks the data in the invisible form and sets the form’s DataMode to either acFormEdit or acFormReadOnly. Here’s a sample of code that selects the form appropriate to the user’s level:

For one application, I had built a Users report that included the users’ passwords. This wasn’t a problem when I was the only person who could see the report (well, it wasn’t a problem for me). However, some of my managers wanted to see the list, just to see who was at what level. Rather than create a new report, I added this code in the Report_Open event to hide the password fields:

One note about this code: Don’t let IntelliSense fool you. You won’t see the Visible property appear in the IntelliSense dropdown list when using this code; the Visible property is there, but is itself invisible (at least in Access 95, 97, and 2000). It won’t pop up in the list, but you can use it anyway.

Another report was a proposal letter, which pulled the signer’s title from a table. Not everyone wanted their title to show, so I added a checkbox to my invisible form and put this code in the Report_Open event:

Creating SQL strings

I also store data to be used in SQL clauses and form filters in my invisible form. You can’t pass a string like “OfficeID in (11,18,26)” to a query’s parameter, though it works fine in a form’s Filter property. Instead, I store the Office ID and Program ID numbers, complete with parentheses, directly on frmCurrent (see Figure 2). Throughout my application, when the user changes data that controls a report’s data, I store the data on frmCurrent. When the user later opens a report, code in the OpenReport method retrieves the data and uses the strings in a Where clause. From the user’s point of view, it appears that the reports always reflect their latest choices. Here’s the routine that builds those criteria strings for me:

Figure 2

Keeping track of reports

Do you remember that field txtReport? Every one of my reports has this bit of code:

The reason is that I have a Print menubar that includes a “Save to” option. When a user selects this option, a form pops up allowing the user to save the report in any of four formats: Snapshot, RTF, text, or Excel. To make this work, I need to know which report is the current report–and I can get this from the txtReport field:

In this case, I’ve expanded what is meant by “current report.” I now know what the last report the user looked at was (what the user thinks of as the “current report”) and can work with that report even if the user doesn’t have it open on the screen.

Think of the invisible form as a space for storing global variables, with the advantage that you can easily check what they are while you’re developing your application. Think of it as a cache for frequently used data that will make your application run faster because you won’t have to keep querying your database. Think of it as a very useful tool.


Your download file is called Hoffman_InvisibleV2.accdb
This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.