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.
- 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.
First, Create a SELECT Query with the following SQL String and save it with the name OrderDetails_Bookmarks:
SELECT [Order Details].* FROM [Order Details];
Open the Form Order Details and create a Command Button next to the < Command Button as shown on the Form Design image given below:
Click on the Command Button to select it and display the Property Sheet (View - - > Properties)
- Change the Name Property Value to cmdShow and the Caption Property Value to View Records.
- 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 - 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
- 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 in 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 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:
No comments:
Post a Comment
Comments subject to moderation before publishing.