Update / CancelUpdate without AddNew / Edit Error

[MHadden]

I am using the following code, but keep getting the following error "Update or CancelUpdate without Addnew or Edit"

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MyDB As Database
Dim MySet As Recordset

Set MyDB = CurrentDb
Set MySet = MyDB.OpenRecordset("Updates&quot

MySet.AddNew
MySet!FirstName = Me!FirstName.OldValue
MySet!LastName = Me!LastName.OldValue
MySet!ServiceType = Me!ServiceType.OldValue
MySet!LastConcCheck = Me!LastConcCheck
MySet!DateChanged = Now()
MySet!User = CurrentUser()
MySet.Update
MySet.close
MyDB.close

End Sub

I have studied the help files, and this forum, but can't find an answer to why it isn't working. It appears to me to be accurate & have the right syntax, but who knows? Can anyone help?
TIA - Michael
MichaelHadden@AltaVista.Com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[ToeShot]

It looks like you are trying to update a form with a recordset that does not exist. Instead using that code on the Before Update Event Try Using this on the OnCurrent Event.

If Me.NewRecord Then
Dim MyDB As Database
Dim MySet As Recordset

Set MyDB = CurrentDb
Set MySet = MyDB.OpenRecordset("Updates&quot

MySet.MoveLast
MySet.AddNew

Me.FirstName = MySet![FirtsName]
Me.LastName = MySet![LastName]
Me.ServiceType = MySet![ServiceType]
Me.LastConcCheck = MySet![LastConcCheck]

'Not Sure if you are trying to put this on form or in recordset.

'For recordset use this code.
MySet![DateChanged] = Now()
MySet![User] = CurrentUser()

'For Form use this code. And Remove the MySet.AddNew and The MySet.Update from this code.
Me.DateChanged = Now()
Me.User = CurrentUser()

MySet.Update
MySet.close
MyDB.close
End If


HTH

 

[MHadden]

Thanks for the tip, I will give it a try. What I am trying to do is to use this like an audit trail. When a record is changed, it will copy the changes, date, and user to a table named "Updates". I also have this in the Delete event to copy the ones that are deleted to a table called "Deleted". The deleted files seem to copy over fine, but when I try to update any data on the form, I get this error message. I shluod have put this in my initial post, but I didn't want it to get too wordy. I tend to be long - winded.
Thanks Again,
Michael MichaelHadden@AltaVista.Com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[ToeShot]

You should Have put it in first What I wrote the Firstime Will not work. I assumed you were trying to put the last valuse entered on a form into a new record. What you had the first time should work try putting the brackets around the field names for example
MySet![FirstName] = Me!FirstName.OldValue

You need these brackets to Identify the Field names in the recordset
You also may want to check to see if there was a change for that particular field

 

[MHadden]

Will do! Thanks again, Sorry for the confusion.
Michael MichaelHadden@AltaVista.Com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!