Solved

Copy a record and Update the record

Posted on 2015-01-05
6
157 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 119

Expert Comment

by:Rey Obrero
Comment Utility
0
 

Author Comment

by:Jass Saini
Comment Utility
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
Comment Utility
Also, it's there an easier way??
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

728 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

9 Experts available now in Live!

Get 1:1 Help Now