Copy record in same table

[brampeirs]

Hello,

I would like to copy a record and past it in the same table.
I want to use a button on a form to do this, of course you could go into the table and manualy copy and paste the record, but I don't like my users to go into the table.

Can anyone help me please.

the table name is PROJECT and I want to copy one record with all of its contents. The primary key is Auto numbering.
The table containts more than 100 columns.

 

[jdraw]

This seems to be a strange request.
A table with 100+ fields seems to be not normalized. And, duplicating (copy and Paste) a record in the same table begs the question , WHY?

I think you would benefit by creating a data model of your "project" and get your tables and relationships well designed.

 

[brampeirs]

This seems to be a strange request.
A table with 100+ fields seems to be not normalized. And, duplicating (copy and Paste) a record in the same table begs the question , WHY?

I think you would benefit by creating a data model of your "project" and get your tables and relationships well designed.

Hello,

Thank you for your reply,
What you say is true, 100+ fields is a lot, that's because my boss wants to save A LOT of dates. With those dates we can then calculate the average time of a task in a project. And make improvements in our planning.

So why would I want to be able to copy a record. That's because a customer sometimes desires different quotes to compare. Then we copy the project and make some changes for the new quote. By doing so we can keep the old version of the project. Because for an other type of quote we only need to change a few things in the project. All the rest stays the same. And it would be silly to type everything over.

So I've already tried some vba with sql. But I can't fiend the right code to do it.

It think I would have to save the record in a variable,
create a new record and paste the variable in that new record.

 

[stopher]

Hello,

Thank you for your reply,
What you say is true, 100+ fields is a lot, that's because my boss wants to save A LOT of dates. With those dates we can then calculate the average time of a task in a project. And make improvements in our planning.

I agree with jdraw - you need to normalise this design. These dates should be in a separate table with a record for each date (not a column for each date). I would expect each record to be a task so that you can record start/end date and other info about a task such as who it is assigned to.

To answer your question, you can create an append query using your table as both the source and destination. Or you could write some vba to fill in the fields in a form. Perhaps you could give some more details about the table names and fields. Even better, just post your database.

Chris

 

[brampeirs]

I agree with jdraw - you need to normalise this design. These dates should be in a separate table with a record for each date (not a column for each date). I would expect each record to be a task so that you can record start/end date and other info about a task such as who it is assigned to.

To answer your question, you can create an append query using your table as both the source and destination. Or you could write some vba to fill in the fields in a form. Perhaps you could give some more details about the table names and fields. Even better, just post your database.

Chris

I will not upload the database, because it's written in dutch (I'm from Belgium).
My table name is "PROJECT"
I have a button in my project form called "Copy project" this button must copy the active project.
The primary key is called "Project_id" and is autonumber type.
When I manually copy the project and paste it, the number of the primary key automaticly increments.

I've seen some code like this:
[FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica]INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE id= (some condition)

[/FONT][/FONT]So I tried this:
Private Sub Knop149_Click()
Dim SQL As String
SQL = "INSERT INTO PROJECT"
SQL = SQL & "SELECT * from PROJECT"
SQL = SQL & "WHERE Project_id= 94"
DoCmd.RunSQL (SQL)
End Sub

But that gave an syntax error.

My knowledge about SQL and VBA is minimal.

 

[spikepl]

Scroll down to the section Debugging tips in this post http://www.access-programmers.co.uk/forums/showthread.php?t=149429

use debug.print to display your SQL string before it goes into the DoCmd.RunSQL command.

 

[stopher]

There are some spaces missing. Try this:

Private Sub Knop149_Click()
Dim SQL As String
SQL = "INSERT INTO PROJECT "
SQL = SQL & "SELECT * from PROJECT "
SQL = SQL & "WHERE Project_id= 94"
DoCmd.RunSQL (SQL)
End Sub

However, the above code will try to add a new record with project_id=94. If project_id is the primary key then Access won't let you add the record (because you can't have two records the same).

So instead of *, you need to list the fields that you want to repeat (except project_id). Then, assuming project_id is an autonumber, project_id will be given a new number automatically.

Chris

 

[Msmartine]

Hello,

I know this is an old thread but I can't quite find a solution to fit my specific needs:

I would like to copy a record and paste it into the same table. Essentially, I would like to add ".1" to the ID (primary key) that is being copied to distinguish the new record from the old. The ID is not an autonumber, so excluding the ID field in an INSERT INTO statement will not work.

I've been scouring the internet for days and have yet to find a specific solution. Any help will be much appreciated.

 

[Gasman]

Sounds like you will need to specify all the fields in the INSERT and SELECT clause.?
Whe you have you ID field you should be able to use

IDFField + 0.1

in the correct location.

As mentioned above

So instead of *, you need to list the fields that you want to repeat (except project_id). Then, assuming project_id is an autonumber, project_id will be given a new number automatically.
[/code]

https://docs.microsoft.com/en-us/of...ce/insert-into-statement-microsoft-access-sql

HTH

 

[Msmartine]

Hi Gasman,

Thanks for your reply! The ID field is NOT an autonumber. I was able to find solutions with the autonumber but the ID is a series of text and numbers that I would like to add “.1” to when making a new record.

 

[Gasman]

If it is text use & instead off +

 

[Msmartine]

Hi Gasman,

That worked perfectly. Thank you!! However, I've learned new information on the requirements needed making it even more complex.

Since there can be several updates to the original ID, the .1 added to the ID needs to be incremented every time there is an update or change made.

Scenario:

The ID is searched on a form by a combobox. If changes or updates are needed I have a button that will to trigger the following:

1. copy/paste the record in the same table based on the ID (primary key) in combobox
2. add .1 to the newly copied record

If the ID selected in the combobox already has a .1 it will be incremented each time the .1 or is selected - keeping the original ID as well.

 

[Gasman]

Ok.
I think you could do this by

1. Search for . with Instr() or InstrRev() in current control value
2. If not found append the ".1"
3. If found, save base part of control with Left() then use RIGHT() with the value returned by the previous STR function.
4. Inspect that and increment
5. Append back to control value

If needing to do this in several places, create a Public Function and call from where needed.

HTH

 

[Msmartine]

Definitely makes sense but I’m not an expert. You’ve been so help so far but I’ll need help with the code. Once the “.” is found do I use the insert into statement? Essentially, I’ll need help with each step please.

 

[Gasman]

You would use this function now instead of what you had.

NewID:IncrementID(ID)

and the function is

Public Function IncrementID(strID As String)
Dim iIncrement As Integer, iDot As Integer

iIncrement = 1
iDot = InStrRev(strID, ".")
'Debug.Print iDot
If iDot > 0 Then
    iIncrement = Val(Mid(strID, iDot + 1)) + 1
    strID = Left(strID, iDot - 1)
End If
IncrementID = strID & "." & iIncrement

End Function

I've only done some basic testing on it.

HTH

 

[jdraw]

Msmartine,

As was suggested to the original poster back in July 2011, you should build a data model based on your requirements. Adjusting and fixing a physical database each time a new feature/requirement is identified is a long, tortuous route. Much easier to make a data model,and using sample data and sample outputs, test that model. Adjust the model as necessary until it accurately supports your data requirements. You now have a blueprint to build your physical database.
You may also find that a separate field --eg VersionNumber -- may be a simpler approach that continually concatenating a .1.1.1 to your ID. Just a thought for consideration.

Good luck with your project.

 

[Msmartine]

when I call it from the button - I get the "Argument not optional" compile error. :banghead:

 

[Gasman]

That is a function that you would use in the query. You do not run it direct.?

Post your query code.

 

[Msmartine]

Sorry for the confusion, I am sharpening my skills. Below is what I had before the additional requirements were realized.
My code is as followed:

Private Sub Command378_Click()

DoCmd.RunSQL "INSERT INTO tblLogNPRFinalIDs ([NPR ID], [User ID], [Number of Parts], [NPR Name], [Director_Manager], [Oracle Prod], [Oracle UAT2], [Online Store], [ServiceMax], " & _
"[ECommerce], [Price Force], [Price Book], [Zuora UAT], [Zuora Prod], [Finance], [Contract], [Planning Analysis], [Order Management], [Postal Compliance], [Service], " & _
"[Legal], [Leasing], [Memphis], [PriceBook], [Operations], [Approval Required], [Finance Approval Rec Date], [Contract Approval Rec Date], [Planning Analysis Approval Rec Date], " & _
"[Order Mngmnt Approval Rec Date], [Postal Compliance Approval Rec Date], [Service Approval Rec Date], [Legal Approval Rec Date], [Leasing Approval Rec Date], [Memphis Approval Rec Date], " & _
"[PriceBook Approval Rec Date], [Operations Approval Rec Date]) " & _
"SELECT [NPR ID] & '.1', [User ID], [Number of Parts], [NPR Name], [Director_Manager], [Oracle Prod], [Oracle UAT2], [Online Store], [ServiceMax], [ECommerce], [Price Force], " & _
"[Price Book], [Zuora UAT], [Zuora Prod], [Finance], [Contract], [Planning Analysis], [Order Management], [Postal Compliance], [Service], [Legal], [Leasing], [Memphis], [PriceBook], [Operations], " & _
"[Approval Required], [Finance Approval Rec Date], [Contract Approval Rec Date], [Planning Analysis Approval Rec Date], [Order Mngmnt Approval Rec Date], [Postal Compliance Approval Rec Date], [Service Approval Rec Date], " & _
"[Legal Approval Rec Date], [Leasing Approval Rec Date], [Memphis Approval Rec Date], [PriceBook Approval Rec Date], [Operations Approval Rec Date] " & _
"FROM tblLogNPRFinalIDs " & _
"WHERE ([NPR ID] = '" & Me.Combo233 & "')"


Call SendRejectEmail


End Sub

 

[Gasman]

Firstly you need to start using code tags please.
The function goes in a module.

Then use it in the query as below (in red)

Private Sub Command378_Click()

DoCmd.RunSQL "INSERT INTO tblLogNPRFinalIDs ([NPR ID], [User ID], [Number of Parts], [NPR Name], [Director_Manager], [Oracle Prod], [Oracle UAT2], [Online Store], [ServiceMax], " & _
"[ECommerce], [Price Force], [Price Book], [Zuora UAT], [Zuora Prod], [Finance], [Contract], [Planning Analysis], [Order Management], [Postal Compliance], [Service], " & _
"[Legal], [Leasing], [Memphis], [PriceBook], [Operations], [Approval Required], [Finance Approval Rec Date], [Contract Approval Rec Date], [Planning Analysis Approval Rec Date], " & _
"[Order Mngmnt Approval Rec Date], [Postal Compliance Approval Rec Date], [Service Approval Rec Date], [Legal Approval Rec Date], [Leasing Approval Rec Date], [Memphis Approval Rec Date], " & _
"[PriceBook Approval Rec Date], [Operations Approval Rec Date]) " & _
"SELECT [COLOR="Red"]IncrementID([NPR ID]) AS NewID[/COLOR], [User ID], [Number of Parts], [NPR Name], [Director_Manager], [Oracle Prod], [Oracle UAT2], [Online Store], [ServiceMax], [ECommerce], [Price Force], " & _
"[Price Book], [Zuora UAT], [Zuora Prod], [Finance], [Contract], [Planning Analysis], [Order Management], [Postal Compliance], [Service], [Legal], [Leasing], [Memphis], [PriceBook], [Operations], " & _
"[Approval Required], [Finance Approval Rec Date], [Contract Approval Rec Date], [Planning Analysis Approval Rec Date], [Order Mngmnt Approval Rec Date], [Postal Compliance Approval Rec Date], [Service Approval Rec Date], " & _
"[Legal Approval Rec Date], [Leasing Approval Rec Date], [Memphis Approval Rec Date], [PriceBook Approval Rec Date], [Operations Approval Rec Date] " & _
"FROM tblLogNPRFinalIDs " & _
"WHERE ([NPR ID] = '" & Me.Combo233 & "')"


Call SendRejectEmail


End Sub

Also pay attention to what jdraw has mentioned. A separate field for version would probably be easier, and easier to amend in the future.?

This is a workaround for your issue at the moment.