My last couple of articles have featured a demo app
affectionately named Something Not Entirely Unlike Access. The
application employs a variety of methods to obfuscate the "Accessian"
features. Last
month, we discussed how to automatically resize subforms, the way some
browser frames work. This month, we’ll tackle navigation.
Once again, the download
is the same as last two month’s. The screen shot below displays the main form
in design view, and you’ll notice the Internet Exploreresque Forward and Back
buttons in the upper left corner. Programming the logic behind their navigation
is this month’s project.
The Easy Piece
Let’s get the easy piece out of the way. The form has at
least three buttons: one to navigate back, one to navigate forward, and a Home
or Start Page button. The images were easy to create using MSPaint and a screen
shot of Internet Explorer. Once you have an image you like, just assign it to
the [Picture] property of the button.
As shown in the screen shot, I’ve used the technique whereby I assign a public
function to the OnClick event. (A public sub should work in newer versions of
Access, but I believe even Access 2000 would not recognize the assignment
unless it was a Function, not a Sub.) So by clicking the back button, a
function on frmMain called MainNavBack() is executed. The code for these
functions, MainNavBack() and MainNavForward(), is shown below.
Each of these functions calls another function on frmMain named LoadMainSubform().
This process was described in my
June article, which introduced the Something Not Entirely Unlike
Access application. Accordingly, we won’t go into detail on what this
function does, but suffice it to say that, given the name of a form, it loads
and resizes that form into the single subform object on frmMain, effectively
changing pages.
This month’s trick will be logging page visits and then determining the correct
page to load when users click Back or Forward. To do this, we create and
implement our own navigation class, named clsNavigation. We’ll show the code
for the class a little later, but first let’s show how it’s used.
Implementation in the form requires these steps:
- Declare a module level object of type clsNavigation using
the NEW modifier. - Call the object’s Load method in the form’s Open event.
- For each new page, call the object’s AddNavPage method to
log the visit. - Create a function to call the object’s NavPrevPage method
to find the previous page. - Create a function to call the object’s NavNextPage method
to find the next page.
That’s all we have to do on the form. Our work is done here.
The real logic exists in the class, clsNavigation. So long as you have the
above five steps implemented correctly in the form, everything else depends on
the class module, which you may freely import into your application from the
download code. You’ll need to tweak it a little to get it to work, but it’s all
there, and more.
' First, create a module level navigation object of
' type clsNavigation. (this class does not yet exist
' ... we will be building it shortly.)
Private m_objNav As New clsNavigation
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
' Create class to manage navigation controls
m_objNav.Load
' do other Form Open stuff here ...
End Function
Public Function MainNavBack() As Boolean
On Error GoTo Err_Handler
Dim strPage As String
strPage = m_objNav.NavPrevPage
Call LoadMainSubform(strPage, False)
Exit_Here:
Exit Function
Err_Handler:
MsgBox Err.Description, vbCritical
Resume Next
End Function
Public Function MainNavForward() As Boolean
On Error GoTo Err_Handler
Dim strPage As String
strPage = m_objNav.NavNextPage
Call LoadMainSubform(strPage, False)
Exit_Here:
Exit Function
Err_Handler:
MsgBox Err.Description, vbCritical
Resume Next
End Function
Public Sub LoadMainSubform(ByVal sFormName As String, _
ByVal fLogNav As Boolean )
On Error GoTo Err_Handler
' Update the navigation object with the new page,
' unless the LogNav flag is set to False. This
' give you flexibility to skip logging for some pages.
If fLogNav = True Then m_objNav.AddNavPage sFormName
' continue with process of loading form ...
End Function
Creating and Loading The Navigation Class
Technically, the Navigation class is created above in the
declaration. When the module level variable, m_objNav, is declared with the New
modifier, the class is instantiated and ready to use. Because it is a module
level variable, it persists as long as frmMain is open.
To build the class, you need to select Class Module from the Insert
menu option. A Class module is different from a Standard module, so be sure
that all the code that follows goes into a Class Module. When the above-mentioned
variable, m_objNav, is declared, an instance of the class is created. (Notice
that the icon for a Class module is different from the icon for a Standard
module.)
While an instance of the class may exist in m_objNav, it can’t really do
anything until the Load method is called. Loading the class does little more
than create an in-memory ADO recordset, into which we will load our data. The
code looks like this …
Option Compare Database Option Explicit Private c_rstNav As ADODB.Recordset Private c_intCurrItem As Integer Private c_intMaxItem As Integer Private c_fMovedBack As Boolean Public Sub Load() On Error GoTo Err_Handler ' Instantiate the private level ADO recordset object, add ' as many fields as you'd like, and load the first record. Set c_rstNav = New ADODB.Recordset With c_rstNav .Fields.Append "ItemID", adInteger .Fields.Append "Value", adVarChar, 64 .Fields.Append "CustomerID", adVarChar, 5 .Fields.Append "EmployeeID", adInteger .Fields.Append "ProductID", adInteger .Fields.Append "OrderID", adInteger .Fields.Append "URL", adVarChar, 512 .Open ' Note that I have added various IDs to the recordset. ' These will be used to load the correct record. ' I always include functions to set and get these IDs. ' ' This could be recoded to be more flexible, but since ' the demo app uses this format, I'll let it be for now. .AddNew !ItemID = 0 !Value = "frmStartPage" !CustomerID = GetCustomerID() !EmployeeID = GetEmployeeID() !ProductID = GetProductID() !OrderID = GetOrderID() .Update End With Exit_Here: Exit Sub Err_Handler: MsgBox Err.Description, vbCritical Resume Next End Sub
The class is now loaded. The private ADO recordset,
c_rstNav, exists and contains a single, initial record. This recordset will
persist so long as the class persists. The class persists as long as the form
is open. So, we’ve created a little in-memory log book of the pages visited by
our user.
Adding a Page
The next action we need to code is the adding of a page. Of
course, we need to log the page’s name, but we’re also going to need some
additional information. For example, if we just navigated to frmCustomer, we’re
also going to need to know WHICH customer, that is, what was the CustomerID at
the moment the form was loaded?
Once again, having considered my previous articles would be of benefit, but in
a nutshell, here’s how I load forms: When the user double-clicks on, for
example, the row of the Customers List Subform, the current row’s CustomerID is
saved by means of the SetCustomerID() public function. When the form is loaded,
it uses the GetCustomerID() method to determine which record to load. So, at
the time the page is "navigated to," the CustomerID is known and can
be persisted in our navigation object. The same is true for EmployeeID,
ProductID, OrderID or URL, if the form is loading a web page.
Now, I should be ashamed of myself for this clumsy and non-extensible code. It
would have been much better to have only two columns: KeyFieldName and
KeyFieldValue. These could be reused more efficiently. When the frmCustomer is
loaded, the [KeyFieldName] would be set to the text "CustomerID" and
the [KeyFieldValue] to its value. When frmEmployee is loaded, [KeyFieldName]
would be "EmployeeID" and [KeyFieldValue] would contain the current
EmployeeID. This would have been smart, but as it turns out, that’s not how the
demo code works, so I won’t bother tweaking it now, but as you can see from the
code below, it’s pretty simple to modify this navigation recordset. Add and/or
remove fields as you wish. Play with it … it’s fun.
The AddNavPage() method has some tricks to it. First, the argument, sValue,
must exist. This part of the code could be smarter too, by checking to see that
a form actually exists by the name passed in sValue.
In order to behave as Internet Explorer does, the FlushForward method must be
called each time a page is added. It basically resets the recordset to make the
current page the last record. It’s like with IE (or any other browser) when you
navigate back three or four pages, and then go to a new page. The browser dumps
any knowledge of those pages and starts off on a new path, with the current page
becoming the last in the string of pages.
Next the c_rstNav recordset object is manipulated to locate our starting point,
the last ItemID and value in its set. The strLastValue variable is used to
avoid adding rows for the same page multiple times. (The Refresh or Requery
action of the form may trigger this method and we don’t need to log those
events.) I’ve also added a condition to allow for logging of multiple pages so
long as the form is frmIE, which is a browser control.
Once the record is successfully saved, we increment the class variables
c_intMaxItem and c_intCurrItem, which will be used later when locating the
requested navigation page. At this point, our page is logged in the recordset,
and the class properties are set.
Public Sub AddNavPage(ByVal sValue As String)
On Error GoTo Err_Handler
Dim intLastItem As Integer
Dim strLastValue As String
If Trim(sValue) = "" Then
Exit Sub
Else
Call FlushForward
With c_rstNav
If Not .EOF Then .MoveLast Else .MovePrevious
If Not .BOF Then
intLastItem = !ItemID
strLastValue = !Value
End If
' Always log IE browser pages.
If sValue <> strLastValue Or sValue = "frmIE" Then
.AddNew
!ItemID = intLastItem + 1
!CustomerID = GetCustomerID()
!Value = sValue
!EmployeeID = GetEmployeeID()
!ProductID = GetProductID()
!OrderID = GetOrderID()
!URL = GetURL()
On Error Resume Next
.Update
If Err.Number = 0 Then
c_intMaxItem = c_intMaxItem + 1
c_intCurrItem = c_intMaxItem
End If
End If
End With
End If
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical
Resume Next
End Sub
Retrieving A Page
The code for NavNextPage is shown below, but NavPrevPage is
nearly identical. All this code needs to do is locate the previous or next
record in the recordset and extract the form name and associated IDs. This is
where that class variable, c_intCurrItem, comes into play. The Find method is
used with the c_rstNav recordset to locate c_intCurrItem. Once found, the
values of the row are read and the form name is returned by the function.
Public Function NavNextPage() As String
On Error GoTo Err_Handler
Dim strOut As String
c_intCurrItem = c_intCurrItem + 1
If c_intCurrItem > c_intMaxItem Then
c_intCurrItem = c_intMaxItem
strOut = "-1"
End If
With c_rstNav
If Not .BOF Then .MoveFirst
.Find "[ItemID]=" & c_intCurrItem
If Not .EOF Then
strOut = !Value
SetCustomerID Nz(!CustomerID, "")
SetEmployeeID Nz(!EmployeeID, 0)
SetProductID Nz(!ProductID, 0)
SetOrderID Nz(!OrderID, 0)
SetURL Nz(!URL, "http://www.amazecreations.com/datafast/")
End If
End With
Exit_Here:
NavNextPage = strOut
Exit Function
Err_Handler:
MsgBox Err.Description, vbCritical
Resume Next
End Function
Final Thoughts
In reviewing the code, I can’t help but think that I’ve
over-complicated the process and over-simplified the explanation. That having
been said, I still think this code may be of benefit to readers. It can be
improved upon, it’s true and it will take some concentration to read and
understand all that is taking place, but that’s what programming is about. It’s
not perfect, but it’s a start.
