Solved

edit an existing data and save it as new record

Posted on 2014-01-20
1
420 Views
Last Modified: 2014-01-27
Is it possible to save an existing record as a new one with new id - while editing it  from a form based up on a user choice? If the user option is "no" update the record, otherwise if it is "yes" add the record as a new one with new id. So that will help to get the saved histories of a particular record.
lease help me with the best way to give such an option for the user(admin) for this type of  selection. If we can apply this while updating the record (on button submit) will be great.
Thanks
0
Comment
Question by:gtmathewDallas
1 Comment
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39795019
The better solution is to make them decide up front if they want to create a new record.  You can then either give them the option or automatically copy the current record.

The question I would ask though is - if you are copying data from another record, why?  Perhaps you need to change your schema so that you add in a new "parent" table where the common data is stored.

There are several ways to copy existing data.
1. You can use an append query that selects a record based on an ID and then appends a copy of it.  The problem with this is figuring out what the ID of the new record is.
2. You can use DAO/ADO and the .AddNew method to copy the current record as a new one.  This is more coding but easier to control since you can easily determine the ID of the new record and then using the Recordset.Clone move to it.
3. You can copy the value property of each control to the control's tag property.  Then move to a new record and copy the data from the tag property into the control's value property.  This is actually probably the easiest since you can do it with code that loops through the fields collection of the form.  That way you won't have to remember to modify the code if you add/delete controls.

I don't have specific code samples for any of these but you can probably find some once you decide on what you need.  I have included a code loop that reads each control on a form and sets its locked property.  This is the heart of what you need to do.  In your case, you'll need one sub that copies from the .value to the .tag and another that copies from the .tag to the .value
Dim bLocked As Boolean
Dim ctl As Control

For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            If ctl.Tag = "NoLock" Then
                ctl.Locked = False
            Else
                ctl.Locked = bLocked
            End If
    End Select
Next ctl
Set ctl = Nothing

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

757 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

22 Experts available now in Live!

Get 1:1 Help Now