Solved

edit an existing data and save it as new record

Posted on 2014-01-20
1
429 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 36

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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