Continued from Bookmarks and Data Editing
In the first part of this article, we used saved Bookmarks to revisit previously accessed records, allowing us to review and verify the accuracy of any edited information.
The function myBookMarks(), which we created for that purpose, can be enhanced with an additional option—alongside the existing ones (1 = Save Bookmark, 2 = Retrieve Bookmarks, 3 = Initialize Bookmark List)—to display all the edited records together in Datasheet View.
However, this approach comes with certain side effects that must be understood before implementation. 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 include multiple products, and consequently, all the records related to that order will share the same OrderID. If we use only the OrderID as a criterion in a query, it will return several Products associated with that order, regardless of which specific record we had previously visited.
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 merely as a reference point to verify that the retrieved record was the correct one.
However, our current approach involves using the OrderID values saved in the Combo Box list as query criteria to retrieve all edited records at once. This can lead to unwanted duplicates or ambiguous results.
We can overcome this issue by ensuring that each item in the Combo Box list represents a unique record. If the table already contains 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 Combo Box 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 Combo Box list. Since the same product code will never appear twice under a single purchase order, this combination guarantees that each entry in the Combo Box represents a unique record.
The purpose of this new approach is to dynamically generate a query using the values stored in the Combo Box list, 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 Command Button beside the << Reset button. When the user clicks this button, all the edited records are displayed 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 Function
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 Command Button 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:
Next, open the Order Details Form in Design View and add a new Command Button next to the existing (
<)Button, as shown in the sample Form design below. This new button will be used to run the procedure that retrieves and displays all the edited records from theOrder Detailstable in Datasheet View. Click 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:
-
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 SubPerform 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 Combo Box.
Click on the drop-down control of the Combo Box to ensure that the selected Item Codes are added to the Combo Box List.
Click on the View Records Command Button to open the Query OrderDetails_Bookmarks in Datasheet View with the records that match the Combo Box Values.
Examine the sample image displaying the Query result overlapping the Form, 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 on 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:
No comments:
Post a Comment
Comments subject to moderation before publishing.