Form Bookmarks And Data Editing-2

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 & "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 <<Reset 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

---

MS-Access Report and Page Total
Detail and Summary from Same Report
Hiding Report Lines Conditionally-3
Hiding Report Lines Conditionally-2
Hiding Report Lines Conditionally