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


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
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.<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.

MORE INFORMATION

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.
  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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:6/30/2005
Keywords:kbEvent kbForms kbprb KB823221 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.