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

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.

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.<Field Name>.setFocus

  Note Replace <Field Name> 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.

1. Start Access 2003 or an earlier version of Access.
2. 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....
3. In the File New Database dialog box, type Adp1.adp, and then click Create.
4. 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.
5. In the Enter information to log on to the server box, type the username and the password to connect to the computer that is running SQL Server.
6. In the Select the database on the server box, type pubs, and then click OK.
7. Create the following stored procedure, and then save the stored procedure. Use the default name StoredProcedure1.
   

   Create Procedure "StoredProcedure1"
   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
   

8. 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.
9. To add the form that is named frmauthors, follow these steps:
   1. In the Database window under the Objects section, click Forms.
   2. Double-click Create form by using wizard.
   3. In the Form Wizard dialog box under the Tables/Queries list, select Table: Authors.
   4. Click >> to select all fields, and then click Finish.
   5. On the View menu, click Design View.
   6. Add a command button with the following properties:
      
      Caption: Show List
      Name: cmdOpenAuthorsList
   7. On the File menu, click Save As.
   8. In the Save As dialog box in the Save Form <name> box, type frmAuthors, and then click OK.
   9. 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 "frmAuthorsList", 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 = "AuthorsHistory"
              .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 "Select * FROM Authors", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
          
          Set Me.Recordset = MyRS
      End Sub
      

   10. On the File menu, click Close and return to Microsoft Access.
   11. Close the frmauthors form.
10. To create another form that is named frmAuthorsList, follow these steps:
    1. In the Database window under Objects, click Forms.
    2. Double-click Create form by using wizard.
    3. In the Form Wizard dialog box under the Tables/Queries list, select Table: Authors.
    4. Select the following fields in the Authors table:
       
       au_id
       au_lname
       au_fname
       
       Click Finish.
    5. On the View menu, click Design View.
    6. On the View menu, click Properties, and then set the following properties:
       
       Caption: Authors List
       Default View: Datasheet
       
    7. Save the form as frmAuthorsList.
    8. 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
       

    9. On the File menu, click Close and return to Microsoft Access.
    10. Close the frmauthorslist form.
11. Add a module, and then declare MyRS as a global variable. To do this, follow these steps:
    1. In the Database window under Objects, click Module.
    2. On the Insert menu, click Module.
    3. Add the following code to the Visual Basic Editor:

       Option Explicit
       
       Public MyRS As ADODB.Recordset
       

    4. On the File menu, click Save Adp1.
    5. On the File menu, click Close and return to Microsoft Access.
12. Open the frmAuthors form. To do this, follow these steps:
    1. In the Database window under Objects, click Forms.
    2. In the right pane, double-click frmauthors.
13. Click Show List on the form to open frmAuthorsList in Datasheet view.
14. In the frmAuthors form, modify the last name in the first record.
15. Select the second record in the frmAuthorsList.
16. Open the authorshistory table. To do this, follow these steps:
    1. In the Database window under Objects, click Tables.
    2. 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.

For more information about the BeforeUpdate event procedure, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type BeforeUpdate event in the Search for box in the Assistance pane, and then click Start searching to view the topic.