Microsoft KB Archive/823221

= BeforeUpdate event may refer an incorrect record when the forms are based on the same recordset =

Article ID: 823221

Article Last Modified on 3/29/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition

-





This article applies only to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
In a Microsoft Access Database Project, two forms may be based on the same recordset. If you edit a record in one form, and then you try to save the record by using the BeforeUpdate event procedure, an incorrect record may be referred to in the BeforeUpdate event procedure of the second form. This problem occurs when the following conditions are true:
 * Both the forms use the same recordset to access the data from the table.
 * Immediately after editing the record in the first form, a different record is selected in the second form.
 * You have not committed the changes that were made to the correct record.



WORKAROUND
To work around this problem, you can use one of the following methods:   Method 1

You can use the SetFocus method to return the focus to a field of the correct record just before you save the old record in the BeforeUpdate event procedure. To do this, add the following code in the BeforeUpdate event procedure:

Me..setFocus Note Replace  with the name of any field in the correct record.   Method 2

Instead of using the original recordset (Me.RecordSet) in the BeforeUpdate event procedure, you can use recordsetclone (Me.RecordSetClone), and then point to the same record as that of the original recordset.

For example, your code may contain code for the BeforeUpdate event procedure that is similar to the following code: Set frmRS = Me.Recordset You can then change the code as follows: Set frmRS = Me.RecordsetClone frmRS.Bookmark = Me.Bookmark 

Note You may avoid the problem by committing the change that you made to the record.



Steps to Reproduce the Problem
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. Note The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run correctly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later). To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.  Start Access 2003 or an earlier version of Access. Add a new Access project that uses existing data.

You can do this by selecting either Project using existing data... or Project using new data....</li> In the File New Database dialog box, type Adp1.adp, and then click Create.</li> In the Data Link Properties dialog box on the Connection tab, move to the Select or enter server name box. Type or select the name of the instance of SQL Server that you want to connect to.</li> In the Enter information to log on to the server box, type the  and the   to connect to the computer that is running SQL Server.</li> In the Select the database on the server box, type pubs, and then click OK.</li>  Create the following stored procedure, and then save the stored procedure. Use the default name StoredProcedure1.

Create Procedure &quot;StoredProcedure1&quot; As if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[authorshistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[authorshistory]

CREATE TABLE [dbo].[authorshistory] (   [ID] [integer] IDENTITY (1, 1) PRIMARY KEY NOT NULL,    [au_id] [id] NOT NULL ,    [au_lname] [varchar] (40) NOT NULL ,    [au_fname] [varchar] (20) NOT NULL ,    [phone] [char] (12)  NOT NULL ,    [address] [varchar] (40)  NULL ,    [city] [varchar] (20) NULL ,    [state] [char] (2)  NULL ,    [zip] [char] (5) NULL ,    [contract] [bit] NOT NULL ) ON [PRIMARY]

return </li> In the Database window, double-click the StoredProcedure1 stored procedure.

This adds a table to the database that is named authorshistory.

Note The authorshistory table stores all the old records that are modified by the frmauthors form.</li> To add the form that is named frmauthors, follow these steps: <ol style="list-style-type: lower-alpha;"> In the Database window under the Objects section, click Forms.</li> Double-click Create form by using wizard.</li> In the Form Wizard dialog box under the Tables/Queries list, select Table: Authors.</li> Click >> to select all fields, and then click Finish.</li> On the View menu, click Design View.</li> Add a command button with the following properties:

Caption: Show List

Name: cmdOpenAuthorsList</li> On the File menu, click Save As.</li> In the Save As dialog box in the Save Form  box, type frmAuthors, and then click OK.</li>  On the View menu, click Code, and then paste the following code in the Microsoft Visual Basic Editor: Option Compare Database Option Explicit

Private Sub cmdOpenAuthorsList_Click DoCmd.OpenForm &quot;frmAuthorsList&quot;, acFormDS End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer) Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim frmRS As ADODB.Recordset Dim FLD As Field Dim strFieldName As String If Me.Dirty = True Then Set CN = CurrentProject.Connection Set RS = New ADODB.Recordset Set frmRS = Me.Recordset With RS       .ActiveConnection = CN        .CursorLocation = adUseClient .CursorType = adOpenDynamic .LockType = adLockOptimistic .Source = &quot;AuthorsHistory&quot; .Open .AddNew For Each FLD In frmRS.Fields strFieldName = FLD.Name RS(strFieldName) = frmRS(strFieldName).OriginalValue Next FLD .Update End With RS.Close Set RS = Nothing Set frmRS = Nothing Set CN = Nothing End If End Sub

Private Sub Form_Load Set MyRS = New ADODB.Recordset MyRS.Open &quot;Select * FROM Authors&quot;, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText Set Me.Recordset = MyRS End Sub </li> <li>On the File menu, click Close and return to Microsoft Access.</li> <li>Close the frmauthors form.</li></ol> </li> <li>To create another form that is named frmAuthorsList, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the Database window under Objects, click Forms.</li> <li>Double-click Create form by using wizard.</li> <li>In the Form Wizard dialog box under the Tables/Queries list, select Table: Authors.</li> <li>Select the following fields in the Authors table:

au_id

au_lname

au_fname

Click Finish.</li> <li>On the View menu, click Design View.</li> <li>On the View menu, click Properties, and then set the following properties:

Caption: Authors List

Default View: Datasheet

</li> <li>Save the form as frmAuthorsList .</li> <li> On the View menu, click Code, and then paste the following code in the Visual Basic Editor: Option Compare Database Option Explicit

Private Sub Form_Load Set Me.Recordset = MyRS End Sub </li> <li>On the File menu, click Close and return to Microsoft Access.</li> <li>Close the frmauthorslist form.</li></ol> </li> <li>Add a module, and then declare MyRS as a global variable. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the Database window under Objects, click Module.</li> <li>On the Insert menu, click Module.</li> <li> Add the following code to the Visual Basic Editor: Option Explicit

Public MyRS As ADODB.Recordset </li> <li>On the File menu, click Save Adp1.</li> <li>On the File menu, click Close and return to Microsoft Access.</li></ol> </li> <li>Open the frmAuthors form. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the Database window under Objects, click Forms.</li> <li>In the right pane, double-click frmauthors.</li></ol> </li> <li>Click Show List on the form to open frmAuthorsList in Datasheet view.</li> <li>In the frmAuthors form, modify the last name in the first record.</li> <li>Select the second record in the frmAuthorsList.</li> <li>Open the authorshistory table. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the Database window under Objects, click Tables.</li> <li>In the right pane, double-click authorshistory.

Note Instead of saving the modified record in the authorshistory table as you expect, the code in the BeforeUpdate event procedure saves the other record that you selected in the second form.</li></ol> </li></ol>

<div class="references_section">