Best way to accomplish...

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

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.

--
Steve Schapel, Microsoft Access MVP


Kelly Tyler wrote:
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,

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!


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

--
Steve Schapel, Microsoft Access MVP


Kelly Tyler wrote:
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
.