Using "On Change" to Append A Record

[Bluezman]

Trying to make a "change of address" where, if a members current address is altered, it will put what was their address into another table called "tblPrevAddress".

I've thought that using "On Change" for the StreetAddress would be appropriate for triggering this code, but haven't found a way to have Access append multiple fields (StreetAddress, City, ST, Zip) into the other table automatically.

Any ideas?

Bluez

 

[Paul_Bricker]

You can try this in the afterupdate event for your field(s). If any information is changed, it writes the four values to the table tblPrevAddress.

Private Sub StreetAddress_AfterUpdate()
Dim frm As Form
Set frm = Forms!YourFormName
Dim strSQL As String
strSQL = "INSERT INTO tblPrevAddress ( StreetAddress, City, ST, Zip ) Values ('" & frm!StreetAddress.OldValue & "', '" & frm!City.OldValue & "', '" & frm!ST.OldValue & "', '" & frm!Zip.OldValue & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

Paul

 

[Bluezman]

Hi Paul, I've made the substitutions for my database, but I'm getting a Runtime Error 438 "Object doesn't support this property or method" when I run this. Debug comes back to the SQL statement itself.

Am I missing an addin or something?

Bluez

 

[Paul_Bricker]

Bluez,
It shouldn't need any special library or anything. It's just an SQL statement. Can you post your complete code so I can look at it. Also, what version of Access are you running?

Paul