ACC2002: Pending Records Are Displayed Incorrectly in a Form Bound to a Batch Cursor (282376)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To commit the changes made to cursor data, follow these steps after you perform the steps in the "Steps to Reproduce the Problem" section of this article:

1. Open Module1 in Design view.
2. Immediately following the comment

   ' (Commit the records here)
   						

   insert the following code:

   rst.UpdateBatch
   					

Steps to Reproduce the Problem

1. Start Access, and then create a new blank database named FormBatchUpdate.mdb.
2. Create a new form in Design view, and then save it as frmDisplayRecords.
3. Add an unbound text box to the form's Detail section, and then set the following properties for the text box and its label:

                    Label         Text Box
                   -------       ----------
       Name:      lblField1       txtField1
       Caption:   Field1 
   					

4. Add two more text boxes as in the preceding step, using Field2 and Field3 in their respective properties.
5. Create a new module, save it as Module1, and then insert the following code.
   
   NOTE: Be sure to use the correct path to the Northwind database on your computer.

   Sub ShowData()
   
       Dim cnnJet As ADODB.Connection
       Dim rst As ADODB.Recordset
       Dim strRecords As String
       
       ' Open a connection to the Northwind database.
       ' Be sure to set the correct path for your machine
       Set cnnJet = New ADODB.Connection
       With cnnJet
           .Provider = "Microsoft.Jet.OLEDB.4.0"
           .Open "C:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb"
       End With
     
       ' Open a client-side, batch-optimistic Recordset on the Customers table.
       Set rst = New ADODB.Recordset
       With rst
           .CursorLocation = adUseClient
           .Open "Categories", cnnJet, adOpenStatic, adLockBatchOptimistic, adCmdTableDirect
       End With
       
       ' Add new record to client-side Recordset.
       With rst
           .AddNew
           !CategoryID = 9
           !CategoryName = "Sweets"
           !Description = "Life-sustaining comestibles"
           .Update
       End With
       
       ' Update an existing record.
       With rst
           .MoveFirst
           .Find "CategoryName = 'Beverages'"
           !CategoryName = "Swill"
           .Update
       End With
       
       ' (Commit the records here)
   
       ' Filter Recordset to show only pending records.
       rst.Filter = "CategoryName Like 'S%'"     'adFilterPendingRecords
       
       ' Open frmDisplayRecords with filtered Recordset in Datasheet
       ' view. The form does not show the filtered Recordset, but
       ' instead shows all records, and doesn't show the updated
       ' and added records correctly. This also doesn't work
       ' correctly when displaying the form in Form view (acNormal).
       
       DoCmd.OpenForm "frmDisplayRecords", acFormDS
           
       Set Forms("frmDisplayRecords").Recordset = rst
           
       With Forms("frmDisplayRecords")
           .Controls("txtField1").ControlSource = "CategoryID"
           .Controls("lblField1").Caption = "CategoryID:"
           .Controls("txtField2").ControlSource = "CategoryName"
           .Controls("lblField2").Caption = "CategoryName:"
           .Controls("txtField3").ControlSource = "Description"
           .Controls("lblField3").Caption = "Description:"
           .Caption = "Pending records?"
       End With
       
       ' Switch to Access window at this point to see incorrect records
       ' and non-functioning scrolling behavior.
       
       ' Build string to display correctly filtered records in MsgBox.
       strRecords = BuildString(rst)
       
       ' Display MsgBox with filtered records. This shows the
       ' pending records correctly.
       MsgBox strRecords, , "Actual pending records"
       
       ' Close Recordset and Connection.
       rst.Close
       cnnJet.Close
       Set rst = Nothing
       Set cnnJet = Nothing
     
   End Sub
   
   Function BuildString(rstView As ADODB.Recordset) As String
       Dim intCount As Integer
       Dim strView As String
           
       intCount = 0
       Do Until intCount = rstView.RecordCount
           strView = strView & rstView(0) _
               & vbTab & rstView(1) & vbTab & _
               rstView(2) & vbCr
           rstView.MoveNext
           intCount = intCount + 1
       Loop
       
       BuildString = strView
   End Function
   					

6. Save the module, click anywhere in the ShowData procedure, and then press F5.
   
   Notice that the modified data is displayed correctly in the message box, but not in the form's datasheet view.