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:

• 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

Menus
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

Status Bar
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

Managing "Pages"

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)

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

   
Me!objWebBrowser.Navigate "http://www.google.com"

That's all there is to adding web surfing to your apps.

Hyperlinks
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.

Reports
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.

Satisfied?

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.

» See All Articles by Columnist Danny J. Lesandrini