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.
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=" & _ Forms!frmCurrent!txtOffice Me.cboOffice.Visible = False Case 2 ' Office Me.cboOffice.Visible = False Me.Filter = "OfficeID=" & _ Forms!frmCurrent!txtOffice 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" Forms(strForm).SetFocus 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 . . Form_ErrorTrap DoCmd.Quit
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" Else 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
Else
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
DB.Close
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
PROC_EXIT:
DoCmd.Hourglass False
Exit Sub
PROC_ERR:
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")
Else
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.
