Invisible Forms Revisited: Park Your Global Values Here

<< Click to Display Table of Contents >>

Navigation:  VBA >

Invisible Forms Revisited: Park Your Global Values Here

Tobi K. Hoffman

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: In Access 2007, 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:

DoCmd.OpenForm "frmCurrent", , , , _

    acFormReadOnly, acHidden

Forms!frmCurrent!txtUserID = lngUserID

Forms!frmCurrent!txtUserLevelID = Me.cboName.Column(3)

Forms!frmCurrent!txtOfficeID = Me.cboName.Column(4)

Forms!frmCurrent!txtCD = Me.cboName.Column(6)

Me.txtReport = ""

Me.txtCustomers = "X"

Me.txtOffices = "X"

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:

Dim intOpen As Integer

intOpen = acFormEdit

Select Case Forms!frmCurrent!txtUserLevel

  Case 1 ' Read only, single office

     intOpen = acFormReadOnly

     Me.Filter = "OfficeID=" & _


     Me.cboOffice.Visible = False

  Case 2 ' Office

     Me.cboOffice.Visible = False

     Me.Filter = "OfficeID=" & _


     Me.FilterOn = True

  Case 3 ' Supervisor

    Me.cboOffice.Visible = True

  Case 4 ' Finance

    Me.cboOffice.Visible = True

    Me.txtFinanceField.Enabled = True

    Me.txtContractsField.Enabled = False

  Case 5 ' Contracts

    Me.cboOffice.Visible = True

    Me.txtFinanceField.Enabled = False

    Me.txtContractsField.Enabled = True

  Case 6 ' Developer

     Me.cboOffice.Visible = True

End Select

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:

  Forms!frmCurrent!txtForm = Me.Name

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:

Sub GoToProject()

    Dim strForm As String

    strForm = Forms!frmCurrent!txtForm

    Forms(strForm).Filter = "ProjectName='" & _

                Me.txtProject & "'"

    Forms(strForm).FilterOn = True

    DoCmd.Close acForm, "frmFindProject"


End Sub

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:

strS = "SELECT tblProjects.ProjectID, _

  "tblProjects.SiteName " & _

  "FROM tblProjects " & _

  "WHERE tblProjects.OfficeID=" & _

  "[Forms]![frmCurrent]![txtOffice]; "

Me.cboSites.RowSource = strS

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):

If Forms!frmCurrent!txtUserLevel = 1 Then

' won't actually get here from MainMenu, so see error trap

End If





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:

DoCmd.OpenReport "rptLateDates", acViewPreview

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:

Private Sub Report_NoData(Cancel As Integer)

    Cancel = True

    Forms!frmCurrent!txtReport = ""

End Sub

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:

Dim intOpen As Integer

Dim intlevel As Integer

intlevel = Forms!frmCurrent!txtUserLevel

intOpen = acFormEdit

Select Case intlevel

    Case 1  ' Read Only

       strDocName = "frmInternalAudit"

       intOpen = acFormReadOnly

    Case 2

       strDocName = "frmInternalAudit"

    Case 3,4,5

       strDocName = "frmInternalAuditMulti"

    Case 6

       If Me.chkChoice Then

            strDocName = "frmInternalAuditMulti"


            strDocName = "frmInternalAudit"

       End If

End Select

DoCmd.OpenForm strDocName, , , , intOpen

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:

If Forms!frmCurrent!txtUserLevel = 6 Then

  Me.lblPassword.Visible = True

  Me.txtPassword.Visible = True

End If

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:

If [Forms]![frmCurrent]!chkUseTitle = False Then

   Me.txtTitle.Visible = False

End If


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:

Public Function ListBoxItems(strFill As String) _

                As String

  Dim DB As DAO.Database

  Dim ctl As Control

  Dim frm As Form

  Dim varItm As Variant

  Dim strS As String

  Dim strC As String

  strS = ""

  strC = ""

  Set DB = CurrentDb

  Set frm = Forms!frmReports

  Select Case strFill

    Case "Offices"

      If Forms!frmCurrent!txtUserLevelID < 3 Then

        ListBoxItems = "(" & _

            Forms!frmCurrent!txtOfficeID & ")"

        Exit Function


        Set ctl = frm!lstOffices

          End If

    Case "Programs"

      Set ctl = frm!lstPrograms

  End Select

  For Each varItm In ctl.ItemsSelected

    strS = strS & strC & ctl.Column(0, varItm)

    strC = ","

  Next varItm


  Set frm = Nothing

  Set DB = Nothing

  ' if no selection made, let user know

  If strS = "" Then

    Call MsgBox(_

      "There is no data for this report." & _

       vbCrLf & "Please make at least one " _

       & vbCrLf & "selection from the " & _

       strFill & " list.", _

       vbOKOnly + vbExclamation, "Select " & _

        strFill & " for Report")

        ListBoxItems = "X"

     Exit Function

   End If

  ListBoxItems = "(" & strS & ")"

   ' Fill fields on frmCurrent

   Select Case strFill

      Case "Offices"

        Forms!frmCurrent!txtOffices = ListBoxItems

      Case "Programs"

        Forms!frmCurrent!txtPrograms = ListBoxItems

   End Select

End Function

Figure 2


Keeping track of reports

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

Private Sub Report_Activate()

    Forms!frmCurrent!txtReport = Me.Name

End Sub

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:

Sub OutputFile(strName As String, _

        intSaveAs As Integer)

  On Error GoTo PROC_ERR

  Dim strOutputFormat As String

  Dim strReport As String

  Dim strExt As String

  DoCmd.Hourglass True

  strReport = Forms!frmCurrent!txtReport

  If Len(strName) > 0 Then

    Select Case intSaveAs

      Case 1

        strOutputFormat = acFormatSNP

        strExt = ".snp"

      Case 2

        strOutputFormat = acFormatRTF

        strExt = ".rtf"

      Case 3

        strOutputFormat = acFormatTXT

        strExt = ".txt"

      Case 4

        strOutputFormat = acFormatXLS

        strExt = ".xls"

    End Select

    If Right$(strName, 4) <> strExt Then

      strName = strName & strExt

    End If

    DoCmd.OutputTo acOutputReport, strReport, _

        strOutputFormat, strName

    End If


  DoCmd.Hourglass False

  Exit Sub


  If Err = 2024 Then      ' invalid path name

    Call MsgBox("Invalid path or file name; " _

      "please check and" & vbCrLf & "re-enter.", _

      vbOKOnly + vbExclamation, "File Save Error")


    MsgBox Err.Description

  End If

  Resume PROC_EXIT

End Sub

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 410HOFFMAN.ZIP in the file

This can be purchased with all the other downloads on this page

Search Smart Access for TempVars if you are using 2007+ as this is a good way for saving varialble