Best way to accomplish...

[Kelly Tyler]

I need to display the following in a form.
ID (PK)
Name
Address
HomePhone
CellPhone

Pretty simple. My problem is this. Whenever somebody
updates say their home phone number -- I would like to
archive the old one into a separate table where I could
query and see all of their previous information in a
readable format.

I probably made this way more difficult than I should but
I'm not real great with SQL. So I made an archive table
and placed a button on my original form labeled Archive
which runs an unmatched query to compare each individual
field address, homephone, and cellphone and then update
the archive table with the results. It works but I only
update one field, it also captures the rest of the fields
so the archive report looks like
ID 1
Address HomePhone CellPhone
1212 West 5th 111-111-1111 222-222-2222
1212 West 5th 111-111-1111 333-333-3333

I would like it to only show the part that is updated

ID 1
Address HomePhone CellPhone
1212 West 5th 111-111-1111 222-222-2222
222-222-2222

There may be a better way to do all of this -- so any
thoughts would be appreciated.

Thanks

 

[Steve Schapel]

Kelly,

Would it serve your purposes if you set up your archive table with
fields like this...

PersonID
ChangeDate
DataChanged
OldValue
.... so, in the case of the example you gave, the archive record would
look like...
1 6-Jun-04 CellPhone 222-222-2222

If so, you could put code on the After Update event of every data
control on your form, to append a record to the archive table for any
data change.

 

[Kelly Tyler]

Steve,

That's an excellent idea -- I'll go ahead and try to do
that a little later and let you know how it goes. Thanks
a lot!