Save record before it is changed.

[hrprabhu]

Hi All,

I want to save a copy of the record in the main form. If some one changes the record in the main form, how do I save it?

Generally if a female person gets married her title changes from "Miss" to "Mrs" and her surname changes too.

So in this sample database I have tried to save the record on changing of two fields, title and / or surname. It is not working. What am I doing wrong? The record is being saved after the change and not before.

Thanks

Raghu

[Rabbit]

Most people aren't going to download attachments. It would be better if you posted the relevant code instead.

Also, I don't know what you mean by saving the record before a change. Are you keeping it in case they want to undo the change? Other than that, I don't really see a reason for it.

[ADezii]

I agree with Rabbit, but if you wish to keep a Record of all changes in a Related Table/Sub-Form for the [Title], [Givennames], and [Surname] Fields for the Main Record:

1. You must add the [ID] Field from Table1 to the Main Form because of the Relationship:
   

   Table1.[ID]{1} ==> Table2.[tableID]{MANY}

2. Create a Private Function in the Code Module of the Main Form:
   

   Private Sub CopyRecord()
   Dim sSql As String
    
   sSql = "INSERT INTO subTable2 ([table1ID], [Title], [GivenNames], [Surname], [DOBirth], [idNumber]) " & _
           "VALUES (" & Me![ID] & ",'" & Me![Title] & "','" & Me![GivenNames] & "','" & Me![Surname] & _
           "',#" & Me![DOBirth] & "#,'" & Me![idNumber] & "');"
    
   CurrentDb.Execute sSql, dbFailOnError
    
   Me.zfsubTable2.Requery
   End Sub

3. In The BeforeUpdate(0 Event of the three Fields, add the following Code:
   

   Private Sub GivenNames_BeforeUpdate(Cancel As Integer)
     If Me.NewRecord Then Exit Sub
       Call CopyRecord
   End Sub

   Private Sub Title_BeforeUpdate(Cancel As Integer)
     If Me.NewRecord Then Exit Sub
       Call CopyRecord
   End Sub

   Private Sub Surname_BeforeUpdate(Cancel As Integer)
     If Me.NewRecord Then Exit Sub
       Call CopyRecord
   End Sub

4. Download the Revised Attachment to really see what is going on.

[hrprabhu]

Thanks for the help. It is working like a charm.