Microsoft KB Archive/282376

= ACC2002: Pending Records Are Displayed Incorrectly in a Form Bound to a Batch Cursor =

Article ID: 282376

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q282376



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
Forms bound to a cursor that uses batch updates do not display the cursor's data correctly after the data has been modified.



CAUSE
This behavior occurs because when a recordset uses batch updates, changes to its data are not saved, and those changes are not reflected in forms bound to the cursor until the changes are committed.



RESOLUTION
to work around this issue, commit the cursor data before you refresh the form.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



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 &quot;Steps to Reproduce the Problem&quot; section of this article:  Open Module1 in Design view.  Immediately following the comment ' (Commit the records here) insert the following code: rst.UpdateBatch 

Steps to Reproduce the Problem
 Start Access, and then create a new blank database named FormBatchUpdate.mdb. Create a new form in Design view, and then save it as frmDisplayRecords.</li>  Add an unbound text box to the form's Detail section, and then set the following properties for the text box and its label: <pre class="fixed_text">                Label         Text Box ---      --    Name:      lblField1       txtField1 Caption:  Field1 </li> Add two more text boxes as in the preceding step, using Field2 and Field3 in their respective properties.</li>  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 = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Open &quot;C:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb&quot; End With ' Open a client-side, batch-optimistic Recordset on the Customers table. Set rst = New ADODB.Recordset With rst .CursorLocation = adUseClient .Open &quot;Categories&quot;, cnnJet, adOpenStatic, adLockBatchOptimistic, adCmdTableDirect End With ' Add new record to client-side Recordset. With rst .AddNew !CategoryID = 9 !CategoryName = &quot;Sweets&quot; !Description = &quot;Life-sustaining comestibles&quot; .Update End With ' Update an existing record. With rst .MoveFirst .Find &quot;CategoryName = 'Beverages'&quot; !CategoryName = &quot;Swill&quot; .Update End With ' (Commit the records here)

' Filter Recordset to show only pending records. rst.Filter = &quot;CategoryName Like 'S%'&quot;    '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 &quot;frmDisplayRecords&quot;, acFormDS Set Forms(&quot;frmDisplayRecords&quot;).Recordset = rst With Forms(&quot;frmDisplayRecords&quot;) .Controls(&quot;txtField1&quot;).ControlSource = &quot;CategoryID&quot; .Controls(&quot;lblField1&quot;).Caption = &quot;CategoryID:&quot; .Controls(&quot;txtField2&quot;).ControlSource = &quot;CategoryName&quot; .Controls(&quot;lblField2&quot;).Caption = &quot;CategoryName:&quot; .Controls(&quot;txtField3&quot;).ControlSource = &quot;Description&quot; .Controls(&quot;lblField3&quot;).Caption = &quot;Description:&quot; .Caption = &quot;Pending records?&quot; 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,, &quot;Actual pending records&quot; ' 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 </li> 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.</li></ol>

Additional query words: pra

Keywords: kbbug kbnofix KB282376

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.