Solved

Copy a record and Update the record

Posted on 2015-01-05
6
159 Views
Last Modified: 2015-01-06
Hello,

Here is my problem  I want to be able to take a record..copy the record and update certain fields based off the form...

here is what I have ...

Name  Program and how much is funded to the program.  The user needs to change the "how Much"...but the key is there are several "How Much"...such as salary, communication, Equipment..etc.  So I have no way of knowing what is changed.  I want to be able to copy the record and have the user input the new number ...while I still have the orginal record.
0
Comment
Question by:Jass Saini
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40532554
0
 

Author Comment

by:Jass Saini
ID: 40532594
Hello Rey,

Sorry...maybe I was not clear.  My boss created a crosstab table and she wants me to use the as my storage table.  We eventaully want to upload to a different system....if that makes sense
0
 

Author Comment

by:Jass Saini
ID: 40532608
Also, it's there an easier way??
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 36

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40532830
The standard way to do this would be to create a "pending" table.  When the user initiates a change, you would copy all the child records to the pending table.  Your edit form is linked to the pending table.  When the user marks the update complete, you DELETE the rows from the permanent table and then append the rows from the pending table to the permanent table and delete the pending rows.

This process is awkward and should be encased in a transaction since you don't want to end up with the "update" only partially complete.  You also need to prevent multiple people from initiating an update.  It would be really poor for person A to start an update and not finish it immediately and have person B also start an update.  Then person A applies his changes and when person B applies his changes, he wipes out what person A just did.  That means you need a lot of infrastructure to manage the whole process including queries/reports that find pending changes that have been abandoned.

I've only created something like this ONCE.  It is not a common requirement and would most likely be found in a financial application where you are doing double entry accounting.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 40532832
My boss created a crosstab table
Lets be clear, there is no such thing as a crosstab *table*...
There are Crosstab queries, ...but they are not updateable, ...and as such, cannot be used for "storage" per se.

Another option might be to create a "Many" table for each "How Many" field..

So just like one customer can have many orders, you can have many salary entries
Ex:
Customer ID: 45 (Parent table)
Salary (Child Table)
SalaryDate, Salary
1/1/2009, 52000
1/1/2010, 55000
1/1/2011, 57000


But a lot is unclear here... A simple sample database would go a long way towards clearing things up...

JeffCoachman
0
 

Author Closing Comment

by:Jass Saini
ID: 40533789
I am just having a hard time with this DB as my boss is so unclear about what she wants and knows as much as I do about Access
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

680 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