Solved

edit an existing data and save it as new record

Posted on 2014-01-20
1
437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 38

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

617 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