Continued from Bookmarks and Data Editing
In the first part of this article, we used saved Bookmarks to revisit previously accessed records, enabling us to review and verify the accuracy of any edited information.
The myBookMarks() options can be enhanced with an additional option— the existing Options: 1 = Save Bookmark, 2 = Retrieve Bookmarks, 3 = Initialize Bookmark List—to display all edited records together in Datasheet View.
However, this approach has certain side effects you should be aware of before implementing new features. Recognizing these potential issues early will help you design effective workaround strategies when necessary. In this section, we’ll explore these challenges using the Order Details table as an example.
In the previous example, we used the Bookmark index number along with the OrderID value to verify that the correct records were being retrieved.
In many cases, a single purchase order may contain many products, meaning that all records associated with that order share the same OrderID. If we use OrderID alone as the criteria of a query, it will return all products associated with that order, regardless of which specific record we visited previously.
This limitation didn’t arise when we relied on Bookmarks, since each record has a unique Bookmark that precisely identifies it. In those earlier cases, the OrderID served as a reference point to verify that the retrieved record is the correct one.
However, our current approach uses the OrderID values saved in the ComboBox list as query criteria to retrieve all edited records at once. This can lead to unwanted duplicates or ambiguous results.
We can rectify this by ensuring that each item in the ComboBox list represents a unique record. If the table already has a unique field (such as a primary key), we can use that instead of the OrderID. Otherwise, we can combine multiple field values to create a unique identifier for each record, and store this composite value in the ComboBox list, along with the Bookmark index number.
This enhancement will form the basis of the fourth option in the myBookMarks() function.
Unique ID Value(s) as Key.
We will now combine the OrderID and ProductID values and store them together in the ComboBox list. Since the same product code will never appear more than once in a single purchase order, this combination guarantees that each ComboBox entry represents a unique record.
This new approach generates a Query based on the ComboBox Bookmark contents, allowing us to display all edited records from the Order Details table with a single click.
In the fourth option of the myBookMarks() function, we will build an SQL statement using the combined OrderID–ProductID values as query criteria. This SQL string will then replace the SQL of a pre-defined SELECT query, enabling it to retrieve the corresponding records.
To trigger this functionality, we’ll add another CommandButton beside the << Reset button. When the user clicks this button, all the edited records are in Datasheet View.
Before proceeding, let’s write the code segment that implements this specific option. We’ll begin by declaring the required objects and variables in the declaration section of the function.
Dim db as Database, QryDef as Querydef
Dim strSql as String, strSqltmp as String, strCriteria as a String
.
.
.
Select Case ActionCode
.
.
.
Case 1
.
Case 2
.
Case 3
.
Case 4
strSqltmp = "SELECT [Order Details].* "
strSqltmp = strSqltmp & "FROM [Order Details] "
strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34)
strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('"
strCriteria = ""
For j = 0 To ArrayIndex -1
If Len(strCriteria) = 0 Then
strCriteria = ctrlCombo.Column(1, j)
Else
strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j)
End If
Next
strCriteria = strCriteria & "')));"
Set db = CurrentDb
Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks")
strSql = strSqltmp & strCriteria
Qrydef.SQL = strSql
db.QueryDefs.Refresh
DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal
End Select We begin by defining the static portion of the SQL statement in the variable strSqltmp. Next, we loop through the items in the Combo Box, extracting the values from its second column, which contains the combined OrderID and ProductID separated by a hyphen (-). Inside the 'For… Next' loop, these values are used to build the criteria string in the variable strCriteria.
Once the criteria are fully constructed, we redefine the SQL statement of the OrderDetails_BookMarks query, inserting the newly built criteria before opening the query to display the extracted records.
Remember that Combo Box columns in Access use zero-based indexing. This means that the second column is referenced as Column(1, j), where j is the current row index. The statement
therefore retrieves the combined OrderID–ProductID string value from the second column and appends it to the criteria string used for record selection.
Modified VBA Code.
The modified Code of the myBookMarks() Function with the above Option is given below.
You may copy the Code and paste it into the Standard Module, replacing the earlier code, or rename the earlier Function and save this Code separately, with the original name.
Public Const ArrayRange As Integer = 25 Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer Public Function myBookMarks(ByVal ActionCode As Integer, ByVal cboBoxName As String, Optional ByVal RecordKeyValue) As String '----------------------------------------------------------------- 'Author : a.p.r. pillai 'Date : October-2009 'URL : www.msaccesstips.com 'Remarks: All Rights Reserved by www.msaccesstips.com '----------------------------------------------------------------- 'Action Code : 1 - Save Bookmark in Memory ' : 2 - Retrieve Bookmark and make the record current ' : 3 - Initialize Bookmark List and ComboBox contents ' : 4 - Filter Records and display in Datasheet View '----------------------------------------------------------------- Dim ctrlCombo As ComboBox, actvForm As Form, bkmk As String Dim j As Integer, msg As String, bkmkchk As Variant Dim strRowSource As String, strRStmp As String, matchflag As Integer Dim msgButton As Integer Dim db As Database, Qrydef As QueryDef Dim strSql As String, strSqltmp As String, strCriteria As String 'On Error GoTo myBookMarks_Err If ActionCode < 1 Or ActionCode > 4 Then msg = "Invalid Action Code : " & ActionCode & vbCr & vbCr msg = msg & "Valid Values : 1 to 4" MsgBox msg, , "myBookMarks" Exit Function End If Set actvForm = Screen.ActiveForm Set ctrlCombo = actvForm.Controls(cboBoxName) Select Case ActionCode Case 1 bkmk = actvForm.Bookmark 'check for existence of same bookmark in Array matchflag = -1 For j = 1 To ArrayIndex matchflag = StrComp(bkmk, bookmarklist(j), vbBinaryCompare) If matchflag = 0 Then Exit For End If Next If matchflag = 0 Then msg = "Bookmark of " & RecordKeyValue & vbCr & vbCr msg = msg & quot;Already Exists." MsgBox msg, , "myBookMarks()" Exit Function End If 'Save Bookmark in Array ArrayIndex = ArrayIndex + 1 If ArrayIndex > ArrayRange Then ArrayIndex = ArrayRange MsgBox "Boookmark List Full.", , "myBookMarks()" Exit Function End If bookmarklist(ArrayIndex) = bkmk GoSub FormatCombo ctrlCombo.RowSource = strRowSource ctrlCombo.Requery Case 2 'Retrieve saved Bookmark and make the record current j = ctrlCombo.Value actvForm.Bookmark = bookmarklist(j) Case 3 'Erase all Bookmarks from Array and 'Delete the Combobox contents msg = "Erase Current Bookmark List...?" msgButton = vbYesNo + vbDefaultButton2 + vbQuestion If MsgBox(msg, msgButton, "myBookMarks()") = vbNo Then Exit Function End If For j = 1 To ArrayRange bookmarklist(j) = "" Next ctrlCombo.Value = Null ctrlCombo.RowSource = "" ArrayIndex = 0 Case 4 strSqltmp = "SELECT [Order Details].* " strSqltmp = strSqltmp & "FROM [Order Details] " strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34) strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('" strCriteria = "" For j = 0 To ArrayIndex - 1 If Len(strCriteria) = 0 Then strCriteria = ctrlCombo.Column(1, j) Else strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j) End If Next strCriteria = strCriteria & "')));" Set db = CurrentDb Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks") strSql = strSqltmp & strCriteria Qrydef.SQL = strSql db.QueryDefs.Refresh DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal End Select myBookMarks_Exit: Exit Function FormatCombo: 'format current Bookmark serial number 'and OrderID to display in Combo Box strRStmp = Chr$(34) & Format(ArrayIndex, "00") & Chr$(34) & ";" strRStmp = strRStmp & Chr$(34) & RecordKeyValue & Chr$(34) 'get current combobox contents strRowSource = ctrlCombo.RowSource 'Add the current Bookmark serial number 'and OrderID to the List in Combo Box If Len(strRowSource) = 0 Then strRowSource = strRStmp Else strRowSource = strRowSource & ";" & strRStmp End If Return myBookMarks_Err: MsgBox Err.Description, , "myBookMarks()" Resume myBookMarks_Exit End FunctionClick on the newly added Command Button to select it, and open the Property Sheet by choosing View → Properties from the menu.
In the Property Sheet:
A Select Query and some Changes in the Form.
You can test this new option by making a few minor changes to the Form we created earlier (see the design view image below). To begin, create a CommandButton and a simple SELECT Query that we’ll use to display the bookmarked records.
First, create a new Query with the following SQL statement and save it under the name OrderDetails_BookMarks:
SELECT Order Details].* FROM Order Details];
Next, open the Order Details Form in Design View and add a new CommandButton next to the existing (<) Button, as shown in the sample Form design below. This new button runs the procedure that retrieves and displays the edited records from the Order Details table in Datasheet View.
-
Set the Name property to cmdShow.
-
Set the Caption property to View Records.
Next, locate the On Click property. From its drop-down list, select [Event Procedure], and then click the Build (…) button. This will open the Form’s Code Module, where Access automatically inserts the empty procedure skeleton, as shown below:
Private Sub cmdShow_Click() End Sub
Write the middle of the Sub-Routine as shown below:
Private Sub cmdShow_Click()
myBookMarks 4, "cboBMList"
End Sub
Perform a Trial Run
Save and close the Order Details Form and open it in Normal View.
Double-click on the Record Selector of a few records on the Form to add the Bookmark List to the ComboBox.
Click on the drop-down control of the ComboBox to ensure that the selected Item Codes are added to the ComboBox List.
Click on the View Records CommandButton to open the Query OrderDetails_Bookmarks in Datasheet View with the records that match the ComboBox Values.
Examine the sample image displaying the Query result overlapping the Form, with all records corresponding to the values stored in the Combo Box list.
Notice that the Product field displays the Product Description instead of the Product Code that appears in the Bookmark Combo Box on the main form. This is because the Display Width of the Combo Box’s first column (Product Code) is set to 0", effectively hiding it from view in Datasheet mode. However, when you select an item from this Combo Box, the Product Code—not the description—is stored in the Order Details table, since it is the bound column.
When you double-click a record selector, the stored ProductID value (rather than the displayed description) is retrieved and combined with the OrderID value to update the Combo Box list correctly.
Let us find out how to open a Form with the last record you were working on in an earlier session: Click here.
Want to find out how to use Combo Boxes and List Boxes in different ways? Visit the following Links:
Download SlideShow.zip
