LOOKING AT THE FIRST SCENARIO
In many instances, clients want to keep track of multiple records during a session and be able to switch between them. Native Access provides the Bookmark property of recordsets to mark and return to records. This is quite simple to take advantage of, yet is somewhat limited.
A more advanced example of this is a student database. Administrators at a school might want to work on a few students at once and be able to switch between them. They would then want to keep track of their bookmarks rather than sequentially move through the recordset each time.
Another example for tracking multiple bookmarks is an employee database. If human resources personnel were assigned various employees to work with, or needed to look back and forth between multiple people, it would be convenient to have the list with the records in them, and be able to switch with a mouse click. This last example can be seen in Figure 16.1, where you can switch between multiple records by using a combo box.
Figure 16.1. This combo box lets you add, remove, and move between multiple bookmarks.
This last scenario will be the example covered in this chapter. Because every utility worth its salt has to have a name, let's call this one the Bookmark Tracker. The Bookmark Tracker and all the objects used for it can be found on the accompanying CD-ROM in ExamplesChap16Chap16.mdb.
Feature Set of the Bookmark Tracker
Before looking at the elements that make up the Bookmark Tracker, look at what features are included:
You can add bookmarks just by moving to the record you want to bookmark, and then selecting << Add a New Bookmark >> (refer to Figure 16.1).
You can delete bookmarks by selecting << Remove Bookmark(s) >> from the combo box in Figure 16.1. You're then given the dialog shown in Figure 16.2.
Figure 16.2. With this dialog, you can pick one or more bookmarks to remove, or all.
You can move to a stored bookmark by choosing one of the bookmarks in the list.
Now that you see the different tasks that can be performed, you should understand how useful the Bookmark Tracker can be.
Some Basic Objects of the Bookmark Tracker
The first object to look at is the tblEmployees table (see Figure 16.3). This table simply contains standard information for employees.
Figure 16.3. The tblEmployees table is the record source for the frmBookmarkTrackerExample form.
The second object to look at is the frmBookmarkTrackerExample form, displayed in Form view in Figure 16.1. This form is pretty standard except for a few lines of code and the combo box with the label Bookmarks. You will look at the lines of code in a moment, after you're introduced to the main players—the class modules. These class modules will be discussed in the next couple of sections, and then run through for each task performed.
Before getting to the class modules, collections, and bookmarks, there's a module named modFormUtilities with a function called ap_FormIsOpen(). Here is the code for that function:
Public Function apFormIsOpen(strFormName As String) As Boolean ApFormIsOpen = Application.CurrentProject.AllForms _ (strFormName).IsLoaded End Function
Note
The Application object is optional in this statement. Both the CurrentProject object and the AllForms collection are new to Access 2000. For more techniques on taking advantage of both of these, see Chapter 4, “Looking at the Access Collections.”
This function will be used within the clsBookmarkManagement class module, which will be discussed in the section “clsBookmarkManagement Performs the Hard Work.”
Let's Have a Little Class…Modules, That Is!
The Bookmark Tracker uses two class modules. Both class modules can be seen in the Database window in Figure 16.4.
Figure 16.4. Notice the difference in icons between standard modules (modFormUtilities) and class modules.
clsBookmarkItems Stores the Actual Bookmarks
The first class module, clsBookmarkItems, can be seen in its complete glory in Listing 16.1.
Listing 16.1. Chap16.mdb: The clsBookmarkItems Class Module Stores Information for Individual Bookmarks
Option Compare Database Option Explicit Private strBookmark As String Private strDescription As String Property Get ActualBM() As String ActualBM = strBookmark End Property Property Let ActualBM(strCurrBookmark As String) strBookmark = strCurrBookmark End Property Property Get BMDescription() As String BMDescription = strDescription End Property Property Let BMDescription(strCurrDescription As String) strDescription = strCurrDescription End Property |
Remember that class modules are custom objects you can create that consist of properties or methods that you specify. In this case, you have only two properties in the clsBookmarkItems class with no methods.
Tip
A great thing about creating your own class modules is they can expand to meet the needs of your programming. If you take the code presented here and use it for yourself, you can add properties and methods as needed in the future.
The clsBookmarkEntry class module is used to track individual entries of the bookmarks stored. Each bookmark stored will have this class object entered into a collection.
The first property, ActualBM, is used to store a bookmark value. The second property, BMDescription, stores a description specified for the bookmark entry, which will be displayed in the combo box. You will see more of these as you walk through the code for the other class module.
clsBookmarkManagement Performs the Hard Work
The second class module, clsBookmarkManagement, consists of two methods and one support subroutine. The two methods are explained here:
Note
You might be asking yourself why a separate initialization routine is needed when you have the Class_Initialize method, which can be used with the class modules you create. InitBookmarks has some arguments passed that it uses. You can't pass values to the built-in method.
Examining the Calls to the Class Module Methods
The best way to be introduced to the methods is to see them called from the form, shown in Listing 16.2.
Listing 16.2. Chap16.mdb: The Calls That Evoke the clsBookmarkManagement Methods
'-- Declare a clsBookmarkManagement variable.
Private bmtEmployeeForm As clsBookmarkManagement
Private Sub Form_Load()
'-- Create an instance of the clsBookmarkManagement class
Set bmtEmployeeForm = New clsBookmarkManagement
'-- Call the custom initialization routine, passing
'-- the necessary items.
bmtEmployeeForm.InitBookmarks Me!cboBookMark, "LastName", _
"FirstName"
End Sub
Private Sub cboBookMark_AfterUpdate()
'-- Call the BookmarkAction method, for all actions.
bmtEmployeeForm.BookmarkAction
End Sub
|
Look at what occurs in the frmBookmarkTrackerExample form module by the numbers:
Starting with the Declarations section, you can see the variable being declared as a clsBookmarkManagement class in the line that reads
Private bmtEmployeeForm As clsBookmarkManagement
The variable bmtEmployeeForm is instantiated with the following line in the Load event:
Set bmtEmployeeForm = New clsBookmarkManagement
Next comes the call to the initialization method created (InitBookmarks).
bmtEmployeeForm.InitBookmarks Me!cboBookMark, "LastName", "FirstName"
The InitBookmarks method takes up to three parameters:
The combo box that will be used on the form for the bookmarks
The first part of the description displayed in the combo box
The second part of the description displayed in the combo box (optional)
Lastly comes the call to the BookmarkAction method, which performs the actual work during the course of working with the form open.
Private Sub cboBookMark_AfterUpdate() '-- Call the BookmarkAction method, for all actions. bmtEmployeeForm.BookmarkAction End SubNotice that this method is called from the combo box's AfterUpdate event.
That's it for the code behind the form. When you want to use the Bookmark Tracker on another form, you just need to copy the combo box and the few lines of code just discussed.
Tip
To make it even easier, check out the next chapter, “Creating Your Own Wizards and Add-Ins,” which offers an add-in for adding the Bookmark Tracker to forms, without any additional coding.
Looking at the Declarations Section for the clsBookmarkManagement Class Module
Before looking at the InitBookmarks method, you need to examine the Declarations area of clsBookmarkManagement class module:
Option Compare Database Option Explicit Private frmBookmark As Form Private cboBookMark As ComboBox Private strBookMarkDesc1 As String Private strBookMarkDesc2 As String Private colBookMarks As Collection Const conQuotes = """" Const apAddNewBM = -1 Const apRemoveBM = -2
The variables and constants displayed here are pretty self-explanatory. They will be used throughout the class module.
Discussing the InitBookmarks Method
Look now at one of the two methods found in the clsBookmarkManagement module, InitBookmarks, in Listing 16.3.
Listing 16.3. Chap16.mdb: InitBookmarks Performs a Lot of Tasks in Setting Up the Bookmark Tracker
Public Sub InitBookmarks(cboCurrent As ComboBox, str1stDesc as String, _
Optional str2ndDesc As String = "")
'-- Store the form and combo box that is utilizing this class
Set frmBookmark = cboCurrent.Parent
Set cboBookMark = cboCurrent
'--Instantiate the collection that will store the bookmarks
Set colBookMarks = New Collection
'-- Set up the combo box to handle manipulating the bookmarks
cboBookMark.ColumnCount = 2
cboBookMark.ColumnWidths = "0;2"
cboBookMark.RowSourceType = "Value list"
cboBookMark.RowSource = "-1; '<< Add a New Bookmark >>'"
'-- Create the mask that will be used to display the description
strBookMarkDesc1 = str1stDesc
If len(str2ndDesc) > 0 Then
strBookMarkDesc2 = str2ndDesc
End If
End Sub
|
Actually, the steps from this method can be listed based on the comments in the code itself. Any comment that needs further explanation will have it.
1. |
Store the form and combo box that's using this class. Based on the combo box that was passed to the method, these control references will be used throughout the class. Set frmBookmark = cboCurrent.Parent Set cboBookMark = cboCurrent |
2. |
Instantiate the collection that will store the bookmarks. Set colBookMarks = New Collection |
3. |
Set up the combo box to handle manipulating the bookmarks. cboBookMark.ColumnCount = 2 cboBookMark.ColumnWidths = "0;2" cboBookMark.RowSourceType = "Value list" cboBookMark.RowSource = "-1; '<< Add a New Bookmark >>'" Notice that it also adds the first value, which consists of a –1 and the description << Add a New Bookmark >>. |
Note
If you're planning to re-create the combo box from scratch each time, leave this code the way it is. If you just want to copy the same combo from one form to another, place the values in these lines of code into the actual combo box.
After the load event executes and the form is open, if you click the combo box, you will see something similar to Figure 16.5.
Figure 16.5. This form has just opened with no bookmarks recorded yet.
As soon as the user clicks the combo box and selects << Add a New Bookmark >>, the combo box calls the BookmarkAction method, which stores the bookmark in the collection. Next, it rebuilds the combo box RowSource property, and then gives a message that the bookmark is recorded. If the bookmark is the first to be added to the list, another choice is added—<< Remove Bookmark(s) >>.
Working with the BookmarkAction Method
This method is actually the most code for this example (see Listing 16.4).
Listing 16.4. Chap16.mdb: Most of the Work Is Performed by This Function
Wow, a lot of code. Because it has been documented, it should be easy to follow. Now analyze this code one action at a time.
Adding a Bookmark
The code to adding a bookmark starts in the Select Case statement with the line that reads
Case apAddNewBM
If you read the comments in this section of code, they list out as follows, with further explanation:
1. |
Create the current description. This takes the control names specified in the InitBookmarks method and creates the actual description displayed in the combo box. |
2. |
Check to see whether the bookmark already is stored in the collection. This is performed by cycling through the collection storing the clsBookmarkItem objects, creating a clsBookmarkItem variable called clsCurrBM to assign each collection item to, and then comparing it to the description of the current item possibly being added to the list. This is done with the following lines: For intCurrItem = 1 To colBookMarks.Count
Set clsCurrBM = colBookMarks.Item(intCurrItem)
'-- Compare the current description to the current
' bookmark item description.
If clsCurrBM.BMDescription = strFullDesc Then
|
3. |
Add the current description and bookmark to the current bookmark items object. This not only shows how to deal with class module objects, but it also stores a bookmark into the ActualBM
property of the clsCurrBM object. Set clsCurrBM = New clsBookmarkItems clsCurrBM.BMDescription = strFullDesc clsCurrBM.ActualBM = frmBookmark.Bookmark |
4. |
Add the current bookmark items object to the collection, with the following line: colBookMarks.Add clsCurrBM |
5. |
Rebuild the combo box. This is performed with a call to the private subroutine RebuildBookmarkCombo. You can see this routine in Listing 16.5. Listing 16.5. Chap16.mdb: This Routine Re-creates the Combo Box from the colBookmarks Collection |
Again, this routine is described pretty well with the comments in the code. Basically, it adds the << Add a New Bookmark >> selection to the top of the list, goes through the collection to add the descriptions, and then, if at least one entry is in the collection, adds the << Remove Bookmarks(s) >> entry to the list. The last tasks that occur are to reset the RowSource for the combo box, clear the combo by setting it to Null, and then requery it.
Removing a Bookmark
As with adding a bookmark, let's walk through the code step by step, starting with the line of code that reads
Case apRemoveBM
1. |
Create the row source for the list box on the Remove Bookmark(s) dialog. This is very similar to creating the row source for the main combo box, shown in the RebuildBookmarkCombo subroutine. For intCurrItem = 1 To colBookMarks.Count
Set clsCurrBM = colBookMarks.Item(intCurrItem)
strRowSource = strRowSource & intCurrItem & _
";" & conQuotes & clsCurrBM.BMDescription & conQuotes
If intCurrItem < colBookMarks.Count Then
strRowSource = strRowSource & ";"
End If
Next
|
2. |
Open the Remove Bookmark(s) dialog, passing the string just created. As mentioned, the next step is to open the form used for removing bookmarks. The strRowSource string variable is passed by using the OpenArgs property. DoCmd.OpenForm "apRemoveBookmarks", WindowMode:=acDialog, _ OpenArgs:=strRowSource You can see the form apRemoveBookmarks in Figure 16.6. The VBA code for each command button on apRemoveBookmarks is in Listing 16.6. Figure 16.6. You can either delete individual entries or all by using the Remove Bookmark(s) dialog.
Listing 16.6. Chap16.mdb: Based on the Button Selected, Either Remove Selected Bookmarks or All |
3. |
Continuing with the BookmarkAction method, this step performs two actions: First, it checks to see whether the apRemoveBookmarks form is still open (meaning that no error occurred). If it is, it looks at any selected bookmarks to remove and removes them from the colBookMarks collection. Here is the segment of code: If apFormIsOpen("apRemoveBookmarks") Then
Dim intNumRemoved As Integer
intNumRemoved = 0
'-- For any of the selected bookmarks to remove, do it.
For Each varCurrItem In _
Forms!apRemoveBookmarks!lboBookmarks.ItemsSelected
colBookMarks.Remove varCurrItem + 1 - intNumRemoved
intNumRemoved = intNumRemoved + 1
Next varCurrItem
DoCmd.Close acForm, "apRemoveBookmarks"
End If
|
Tip
This segment of code demonstrates a very useful way of utilizing a dialog to get user input, and then reading values from the dialog after users complete their entries and “close” the form. In this case, the form is opened with the window mode being acDialog, so the code in BookmarkAction is halted until the form is either closed or hidden. The form is hidden by setting the Visible property to False in the two remove command buttons on the apRemoveBookmark form—cmdRemoveAllBookmarks and cmdRemoveBookmarks. When it resumes, the code examines the selected items and removes the appropriate bookmark items from the collection. Lastly, the form is closed for real.
4. |
Rebuild the combo box. This calls the routine RebuildBookmarkCombo described in the section “Adding a Bookmark.” |
That's all there is to removing an item. The last task performed by the BookmarkAction method is to move to a current bookmark.
Moving to a Bookmark
In addition to being the last task, moving to an existing bookmark is actually the shortest task found in the BookmarkAction method as well, consisting of the following two lines of code:
Set clsCurrBM = colBookMarks.Item(CInt(cboBookMark))
frmBookmark.Bookmark = clsCurrBM.ActualBM
This code creates a reference to the specified item in the colBookMarks collection, and then moves to the bookmark recorded there.
Wrapping Up with the Bookmark Tracker
That about does it for the Bookmark Tracker. As with many of the techniques given throughout this book, remember that although it requires a lot of code to create the Bookmark Tracker, using it for your own purposes simply requires you to copy the combo box and code from the frmBookmarkTrackerExample form. Also remember that even this is done away with by using the add-in created in the next chapter.