Something Not Entirely Unlike Access

"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

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles