Solved

Copy a record and Update the record

Posted on 2015-01-05
6
158 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 35

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2007 Switch To/Retry/Cancel Pop-Up 6 30
Dynamically Reorder List Box 4 37
Macro to import XML in Access 2013 2 34
is Microsoft Access going to Die? 9 44
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

786 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