In the first part of this Article, we were using the saved Bookmarks to revisit the earlier visited records one by one to take a second look, if it became necessary, to ascertain the accuracy of edited information.

The Function myBookMarks() that we have created for this purpose can be added with one more Option, (along with 1=Save Bookmark, 2=retrieve Bookmarks, 3=initialize Bookmark List) to display all the edited records together in Datasheet View.

But, this method has some side effects and must be aware of it to implement some work around methods in such situations. Here, we will try that with the Order Details Table.

In the last example we were using the Bookmark Index Number and OrderID number values as a guide to cross check with the retrieved record.

Several Products can be ordered under the same Purchase Order and all Products under the same Order will bear the same OrderIDs too. If OrderIDs are alone used in a Query Criteria to retrieve the records then all records with the same Order IDs will be displayed, irrespective of which record among them we have visited earlier.

There were no such issues when we were using Bookmarks of each record to find them again and OrderIDs were used only as a guide to cross check the retrieved record's identity.

But here, we are trying to use the OrderId Values saved in the Combo Box List as Criteria in a Query to retrieve all the edited records at one go.

This problem we can overcome if some other unique value, if available, is used in the Combo Box list. Or use one or more field values combined to form a unique value for each record and save it in the Combo Box List along with the Bookmark Index Number. This is what we are going to do now with the 4th Option of myBookMarks() Function.

We will use OrderID with ProductID combined Values and save them in the Combo Box List. The same Product Code will not appear twice under the same Purchase Order. This will ensure that the values saved in the Combo Box are unique.

The idea behind this new method is to create a Dynamic Query using the Values saved in the Combo Box list and open the Query with all the edited records from the Order Detail Table with one click.

In the fourth Option of the Function myBookMarks() we will build an SQL String using the Values saved in the Combo box as Criteria and modify the SQL string of a SELECT Query to retrieve the records. We have to create another Command Button near the << Reset Button to run this Option, so that the User can click on it to retrieve all the edited records and display them in Datasheet View at his will.

But, first let us write the Code Segment that implements this particular Option. We need few Objects and Variable declarations at the declaration section of the Function.

Dim db as Database, QryDef as Querydef
Dim strSql as String, strSqltmp as String, strCriteria as 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 are creating part of the SQL string that remains constant in the strSqltmp. Extracting the Combo Box 2nd Column Values (combined values of OrderID and ProductID separated with a hyphen character) and building the Criteria part of the Query in the String Variable strCriteria within the For. . .Next Loop. Finally we are redefining the SQL of the OrderDetails_BookMarks Query before opening it with the extracted Records.

The Combo Box Columns have Zero based Index Numbers and the second Column Index number is 1. So the statement strCriteria = strCriteria & "-,’" & ctrlCombo.Column(1, j) takes the second column value OrderID and PrductID combined String value for criteria.

The modified Code of the myBookMarks() Function with the above Option is given below.

  1. You may Copy the Code and Paste it in the Standard Module replacing the earlier Code or rename the earlier Function and save this Code separately.
    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

    You can try out this Option with few changes to the Form that we have created earlier (the Form in design view is given below) by creating another Command Button and with a simple SELECT Query.

  2. First, Create a SELECT Query with the following SQL String and save it with the name OrderDetails_Bookmarks:
    SELECT [Order Details].* FROM [Order Details];
    
  3. Open the Form Order Details and create a Command Button next to the < Command Button as shown on the Form Design image given below:
  4. Click on the Command Button to select it and display the Property Sheet (View – – > Properties)
  5. Change the Name Property Value to cmdShow and the Caption Property Value to View Records.
  6. Select the On Click Property, select EventProcedure from the drop down list and click on the Build (. . .) Button to open the Form's Code Module with the following empty skeleton of Sub-Routine:
    Private Sub cmdShow_Click()
    
             End Sub
  7. Write the following line in the middle of the Sub-Routine as shown below:
    Private Sub cmdShow_Click()
        myBookMarks 4, "cboBMList"
    End Sub
    
  8. Save and Close the Order Details Form and open it in Normal View.
  9. Double-Click on the Record Selector of few records on the Form to add the Bookmark List in the Combo Box.
  10. Click on the drop down control of the Combo Box to ensure that the selected Item Codes are added into the Combo Box List.
  11. Click on the View Records Command Button to open the Query OrderDetails_Bookmarks in Datasheet View with the records that matches with the Combo Box Values.

Check the sample image of Query result overlapping the Form, displaying all the records that belong to the Combo Box List Values.

The Product Field displays Product Description rather than the Product Code that is appearing in the Bookmark Combo Box on the Main Form. The Display Width of the Combo Box in the Product Field is set to 0" to hide the Product Code in the Data View. But when you select an item from this Combo Box the Product Code is stored in the Order Details Table, because that is the Bound Column to the Table. When you double-click on the Record Selector the stored value of ProductID is taken rather than the Product Description, to combine with OrderID Value and updates the Combo Box List.

Want to find out how to open a Form with the last record that you were working on in the earlier session?, Click here?

Want to find out how to use Combo Boxes and List Boxes in different ways? Visit the following Links:

  1. Selected ListBox Items and Dynamic Query
  2. Create List from another ListBox
  3. ListBox and Date : Part-1
  4. ListBox and Date : Part-2
  5. ComboBox Column Values
  6. External Files List in Hyperlinks
  7. Refresh Dependant ComboBox Contents

StumbleUpon Toolbar