• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

edit an existing data and save it as new record

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
gtmathewDallas
Asked:
gtmathewDallas
1 Solution
 
PatHartmanCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now