Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

edit an existing data and save it as new record

Posted on 2014-01-20
1
Medium Priority
?
445 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 40

Accepted Solution

by:
PatHartman earned 1500 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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