Solved

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

Posted on 2013-01-30
3
269 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…

707 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

15 Experts available now in Live!

Get 1:1 Help Now