"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:
- Absence of Access menus
- Obfuscation of Access icon and startup screen
- Single form (page) interface
- Automatic resizing of subforms and their controls
- Forward and Back buttons to navigate "pages"
- Start Page button to return users to their "home" page
- Built in WWW web browser page
- Hyperlinks to load new forms/subforms
- Reports display as Snapshots outside of application
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
To turn off the menus, execute the DoCmd.ShowToolbar command with the name of the target toolbar or menu, and the parameter acToolbarNo. (acToolbarYes, as you might imagine, shows the menu.) The help file says that ShowToolbar works only on toolbars, but that isn't my experience. To prove this to yourself, double-click on the Welcome label. I added code that executed the acToolbarYes option to show the "Menu Bar" and it indeed toggles the main menu bar visible.
Icon and Title
The next Access Stuff to remove is the application icon, application title and title bar text. It's been so long that I've been using this code, I can't say for sure who to give credit to, but more likely than not, it was from the Access Web developer's reference site. If not, I apologize to whomever contributed this fun and useful piece of code.
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
The last piece of visual housekeeping to take care of is to hide the Status Bar ... that horizontal information warehouse at the bottom of the page. Now, you could keep this option turned on, if you like, because even Internet Explorer has a status bar that you can opt to see, but I found that removing it helped to obfuscate the Accessian features and to enhance the appearance that the application was not a Microsoft Access program. Here's how you set the Status Bar option in code:
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 first of those objectives is realized by creating only one form that the users ever really "open" as a form. I've called mine frmMain, and it's the startup form. All other forms are named sfrmXXX or sfrmYYY to identify them as subforms. (Except for a popup form like frmAbout or frmHelp.) This main form contains a single subform named objSubform. Each time a request for data is made, whether that be for products, employees or orders, the appropriate form is loaded into this subform and a command is sent to the subform to resize itself according to the available space.
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)
The code to maintain the list of forms visited and to reload them is not trivial, but it's encapsulated in a special class named clsNavigation. This will most definitely be the source of a future article, but in brief it does the following:
- The Load method creates an in-memory ADO recordset
- AddNavPage method inserts a new nav record into the recordset
- GetNavPage returns a specific "page"
- NavPrevPage and NavNextPage step you through the pages
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
The last set of enhancements have to do with adding features that are strictly webish. There exists a custom control in Access called the Microsoft Web Browser control. The code to load a web page is so simple you're going to laugh. After locating it in the toolbox by clicking on the More Tools icon and navigating to and clicking it, rename the control to objWebBrowser and add this line of code to any event that suits your needs:
That's all there is to adding web surfing to your apps.
The Hyperlinks are created by setting the IsHyperlink property of the control to Yes. (It's in the list of properties under the Format tab for any bound control.) Next, you lock the control for editing add code to the Click method to cause the app to do something, like "jump" to the record that was clicked. Access takes care of formatting the text to look like a hyperlink and even gives you the pointing finger icon that you get on a web page. Pretty cool, and very simple to do.
If it's not Access, then it can't display an Access report, right? Right. So, we use an alternate process for displaying reports. Assuming the report is in the variable sReport and it's being output to a file named sFile, the following lines of code will create and open a Microsoft Snapshot Report in a separate window:
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.