Something Not Entirely Unlike Access
June 15, 2007
"We want you to build an application in MS Access, but we don't want it to look like Access. In fact, we want it to look like a web page ... like a browser application."
Can that be done? Judge for yourself. Below is the screen shot and here is the download with the working code for the application I'm calling Something Not Entirely Unlike Access. This application demonstrates the following browser-ish features:
If any of these topics interest you, download the demo application and give it a whirl. It wasn't built with the intention of being ascetically pleasing, so please don't send me feedback about how ugly it is. It's all about the code ... and there's plenty of that in this app. More than I can realistically cover in a single article, but I'll break out pieces for future articles as the spirit moves me. In the mean time, you can get it all, provided you're not afraid to dig into the code.
Hide Access Stuff
The first thing you want to do is to get rid of the things that make Access look like Access. Menus are the first to go, and here's the code that gets rid of them. I put this code on my startup form and the first thing I do is to set the form Visible property to FALSE and turn off the screen painting (DoCmd.Echo False), so that I work my magic without the user seeing the flashing. I also maximize my startup form, but that has more to do with other aspects of this application.
Me.Visible = False DoCmd.Echo False DoCmd.Maximize DoCmd.ShowToolbar "Web", acToolbarNo DoCmd.ShowToolbar "Menu Bar", acToolbarNo DoCmd.ShowToolbar "Form View", acToolbarNo
Icon and Title
The function does the equivalent of setting startup properties in VBA code. If, for example, no application icon has been set, the property doesn't really yet exist. An error will be thrown when you try to set it for the first time. Same for the Application Title property. This function will create the property if it's missing and set them to some predefined values.
The advantage of this is that when you assign an icon to the application, the pretty Microsoft Access key icon goes away. Also, if you don't supply an application title, it defaults to Microsoft Access. So, if you're seeking to hide all things Access, you'll need to supply a title.
Public Function SetAppProperties() As Boolean On Error GoTo Err_Handler Dim strFile As String Dim strTitle As String Dim dbs As DAO.Database Dim prp As DAO.Property Const cAPP_ICON = "AppIcon" Const cAPP_TITLE = "AppTitle" Set dbs = CurrentDb strFile = CurrentProject.Path & "\dbj.ico" strTitle = "My Web App" On Error Resume Next dbs.Properties(cAPP_ICON) = strFile If Err.Number = 3270 Then Err.Clear Set prp = dbs.CreateProperty(cAPP_ICON, dbText, strFile) dbs.Properties.Append prp End If dbs.Properties(cAPP_TITLE) = strTitle If Err.Number = 3270 Then Err.Clear Set prp = dbs.CreateProperty(cAPP_TITLE, dbText, strTitle) dbs.Properties.Append prp End If Exit_Here: Set dbs = Nothing Application.RefreshTitleBar Exit Function Err_Handler: Select Case Err Case 3270 'Property not found Case Else MsgBox Err.Description, vbCritical End Select Resume Exit_Here End Function
Application.SetOption "Show Status Bar", False
This is a convenient bit of syntax to keep handy. I use it to set all kind of options in code, especially Error Handling. If you set some public constants, you can conveniently toggle the error handling based on who is logged in. For users, I set it to Break In Class Mode or Break on Unhandled, but when I'm testing, I want it to Break On All. This code sets that option:
Public Const cBreakOnAll As Long = 0 Public Const cBreakInClass As Long = 1 Public Const cBreakUnhandled As Long = 2 SetOption "Error Trapping", cBreakOnAll ' cBreakUnhandled
This feature is really more involved than can be easily described in one article, so I'll just give you the 30,000 foot overview. To look like a Web Browser, the app needs to stick to one main client form that is "loaded" with pages. There needs to be a device to remember navigation between pages and the pieces need to resize (in most cases) so that they take up the entire screen real estate.
Single Form with Subform
The code for loading a subform is embedded on frmMain, which is always open. For example, clicking on a customer in the list (see screen shot above) executes these lines of code. First, a function is called to "set" the CustomerID and the next line asks the main form to load the main subform with a form named "frmShowCustomer". That form performs a "get" of the CustomerID when it loads, effectively filtering the results to the selected customer.
SetCustomerID Nz(Me!CustomerID, "") g_lngResult = Forms!frmMain.LoadMainSubform("frmShowCustomer", True)
What web browser would be complete without a Home or Start Page button. While this could be handled through the navigation control, I've opted to manage that feature with a form called frmStartPage. This is the form that is always loaded first, and to reload it is a simple modification of the call above, substituting "frmStartPage" for "frmShowCustomer". By the way, the second option in that method, which is a Boolean True or False, determines whether or not the navigation control is asked to log the visit.
Strictly Web Features
Add a Web Browser
That's all there is to adding web surfing to your apps.
DoCmd.OutputTo acOutputReport, sReport, acFormatSNP, sFile, False Application.FollowHyperlink strFile
As it turns out, this is a really clean solution. Sure, files are created on the disk, but disk space is cheap these days, and it gives the added benefit of archiving snapshots of the reports over time. Something that itself could be managed from your app, providing links to previously created reports. It doesn't get cooler than this.
Well, I apologize if the above description lacks detail. There just isn't time or space to go into detail about all the features of this Something Not Entirely Unlike Access application. The download will be a great starting point for you, if any of these features sound like something you would want to add to your applications. Also, watch this space for future articles, where I'll describe in detail the inner workings of the more complex processes.