Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Form Bookmarks And Data Editing-2

Continued from Bookmarks and Data Editing

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 one must be aware of it to implement some workaround methods in such situations. Here, we will try that with the Order Details Table.

In the last example, we have used 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 Order IDs 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 Order IDs were used only as a guide to cross-check the retrieved record's identity.

But here, we are trying to use the Order Id Values saved in the Combo Box List as Criteria in a Query to retrieve all the edited records in 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 on 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.

Unique ID Value(s) as Key.

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 a few Objects and Variable declarations 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 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's 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.

Modified VBA Code.

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

  1. 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.
    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 try out this Option with a few changes to the Form that we created earlier (the Form in the design view is given below) by creating another Command Button and 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 Event Procedure 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
    

    Perform a Trial Run

  8. Save and Close the Order Details Form and open it in Normal View.
  9. Double-Click on the Record Selector of a 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 to 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 match with the Combo Box Values.

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

The Product Field displays the Product Description rather than the Product Code that appears 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 OrderID Value and update 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 Dependent ComboBox Contents
Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code