Solved

Make changes to data on Access form and keep original data in history table

Posted on 2013-01-30
3
270 Views
Last Modified: 2013-01-30
I have a form (myFormA) with about 30 fields where the end users enter product specs.

I want to create another form (myFormEdits) with the same 30 fields but it will allow the end users to edit any of the 30 fields, but I want to keep the original value in a history table (myTableHistory- it has 5 fields: ProductNumber, Spec, OriginalValue, Date, UserID ).

How can I do this so if the end user decides to change 6 out of the 30, it will know to only insert the 6 original values into  myTableHistory. I was thinking of possibly adding an "edit" button to all 30 and when the user clicks it, it will insert the original value into myTableHistory and update the main table with the new value, but it will be tedious to do this 30 times in code. Any better ideas?

Thank you in advance!
0
Comment
Question by:printmedia
  • 2
3 Comments
 
LVL 39

Expert Comment

by:als315
ID: 38836560
You can copy 30 records to temporary table, edit. Edited records can be compared with original data in query and only different records are saved to history table
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38836610
@Alexey, 30 fields, not records.

@printmedia, You could use the Form_BeforeUpdate event, and loop through the controls, comparing the controls Value to the OldValue, and where the OldValue has changed, save that value to your table.  Although if you want to record any field changes, you would also need to record the field name in your table.  Not sure you need to save the new value, as it will be in your main table.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38836616
You might also want to check out this page from Allen Browne's site
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now