If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Best way to accomplish...
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 |
#3
|
|||
|
|||
Best way to accomplish...
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 . |
Thread Tools | |
Display Modes | |
|
|