Copy a record and append to the same table (1 Viewer)

[John Sh]

Is it possible to enter code between "DoCmd.RunCommand acCmdCopy" and "DoCmd.RunCommand acCmdPaste" other than another "docmd" statement
When I do this I get an error message "run command action was cancelled"
My understanding is that the copy command writes to the clipboard so the data should still be there after other code is processed
I am trying to copy the data in an existing record to a new record where the data in each is very similar.
Is it better to use a recordset and loop through the fields, copying the original field data and writing to the new field.

 

[Minty]

I'd just use an Insert query and amend the fields you need to not include.
Something like

strSQL = " INSERT INTO YourTable Field1, field3, field 5  SELECT Field1, field3, field 5  FROM YourTable Where MyID = " & Me.MyID field   
Debug.print strSQL
Currentdb.Execute strSQL

Replace the ... with data fields you want from the existing record.

INSERT INTO statement (Microsoft Access SQL)

Office developer client VBA reference documentation

docs.microsoft.com

 

[The_Doc_Man]

If there is a primary key in the table, what you are doing with DoCmd (copy/paste) will ALWAYS incur a "duplicate key" error so should NEVER work. If there is no PK, I'm not sure what is happening there, but to answer the question in your first sentence, ... Yes you can put something between the copy and paste steps. I might consider a command to go to a new record, since that paste (without any intervening actions between the copy and the paste just overwrites the current record.

Minty's suggestion would also work if you copied every field EXCEPT the one that was a prime key.

 

[theDBguy]

other than another "docmd" statement

Just curious, what other code did you have in mind? If you're trying to move to a new record, there's a DoCmd code for that.

 

[John Sh]

Thanks all. I think I answered my own question.
There is no primary key, as such, but there is a field that does not allow duplicates, hence the error message.
The insert query is an option that I have looked at but the table has some 90+ fields, making the query a bit unwieldy.
I am now looking to using a recordset and looping through the fields. This involves much simpler code and I can skip the "no duplicates" field.
The code I was looking to insert is to create a unique number for that field but the paste statement would overwrite that any way so it would never work.
As always, I appreciate the time you take to respond.
Thank you.

 

[mike60smart]

Would recommend that you add an Autonumber field to all tables.

 

[John Sh]

Would recommend that you add an Autonumber field to all tables.

I have only one table so a PK is really unnecessary and there is a generated unique field in the table.

 

[Uncle Gizmo]

If you display the records as a subform in datasheet view, on a main form, then you might be able to use my Subform Record Duplicator code here:-

Subform Record Duplicator - Nifty Access

Subform Record Duplicator Nifty Access - "Subform Record Replicator" allows you to provide your user with the facility to quickly add sets of duplicate records in a subform. All they need do is specify the number of Records they want to add, select a record to copy, and hey presto you've got a...

www.niftyaccess.com

But it does need to have a primary key to work because that's how it identifies the record you select.

There's a free version available just read the instructions on my post...

Subform Record Duplicator - Nifty Access​

 

[John Sh]

There seems to be a fixation on having an "autonumber" field as a primary key. I would suggest that any unique field can be used as a primary key if. in fact, a primary key is actually required.
The database I am using has one table. It also has a field that contains a unique, non duplicatable, value that is generated by the system whenever a new record is added. Any searches or references to a record use this unique value. Why then would I need to further encumber my table with an autonumber field that would serve no other purpose than to be there.
My subforms also use this unique field and, to date, have functioned exactly as required. In fact any searches on subforms are controlled by select queries and said unique field is not part of the search criteria, here it is used purely for indexing purposes.

The only time I can see a "primary key", autonumber or internally generated, being of any use is if there are relationships established between various tables. Then, and only then, would a primary key of any origin be required.

Convince me that I MUST have an autonumber field or any primary key on a single, unrelated, table.

With respect.
John

 

[Uncle Gizmo]

The Northwind database has an ID field which is text, a bit like Sage. I would say as long as you know what you're doing carry on. On the down side, you could be making a rod for your own back, you've got something extra to maintain, to keep an eye on. An auto number field is just that, an auto number, you don't have to think about, or do anything with.

 

[John Sh]

you don't have to think about, or do anything with.

And that's exactly my point. I have nothing to do with it so why have it. It 's a bit like having an ashtray built in to a motorbike. Many bikers smoke but the ashtray would be totally useless so they don't put them on motorbikes.

 

[oleronesoftwares]

Is it possible to enter code between "DoCmd.RunCommand acCmdCopy" and "DoCmd.RunCommand acCmdPaste" other than another "docmd" statemen

A.Are you copying from one field to another in the same record?

OR
B. Are you copying from one field to another in another record?

If A, then an update statement can be used.

If B, then an insert statement can be used.

 

[John Sh]

A.Are you copying from one field to another in the same record?

OR
B. Are you copying from one field to another in another record?

If A, then an update statement can be used.

If B, then an insert statement can be used.

I have two scenarios. In one I will be copying one record to the same table as a new record.
Here my unique field will not be copied, rather generated once the new record is established.
I am currently doing this with a whole lot of public variables. This is messy but not that much messier than sql with a similar list of field names.
I haven't done much on it so far but I think running two recordsets and looping through the fields is the cleanest of the options.

In the other scenarioI will be copying one record to another table.
In this case I will be copying the complete record as the unique field value will not exist in the receiving table.
I should be able to do this with the docmd...accmdcopy and pasteappend with a bit of code to open/close the second table

 

[oleronesoftwares]

Here my unique field will not be copied, rather generated once the new record is established.

At what intervals will you be copying it?
Any criteria to determine which records will be copied?

 

[John Sh]

At what intervals will you be copying it?
Any criteria to determine which records will be copied?

To elaborate.
The database is a list of collected flora samples. Each sample has an accession number, the unique field.
From time to time it is more efficient to copy a record into the same table where most of the data is the same and only minor changes need to be made. There are some 90 fields in the table so if only a few are different then we would copy and paste.

The second scenario is where there are errors in the data or missing information. These records have been separated from the main table and will be reinserted when the data is correct / complete. These records already have a unique accession number so there is no clash when reinserted into the main table.

A record copy to the same table will be rare. Maybe once in six months or less.
The copy from one table to another could be more frequent.
In both cases it will depend entirely on the workload and what jobs take precedence.

 

[oleronesoftwares]

The second scenario is where there are errors in the data or missing information. These records have been separated from the main table and will be reinserted when the data is correct / complete. These records already have a unique accession number so there is no clash when reinserted into the main table.

when you write "will be re-inserted", is it that the records existed in the main table , was deleted and now needs to be re-inserted?

You can use the duplicate record command, this will create a copy of the active record on a form, and insert a new primary key.

To do this

1. Click on the command button.
2. Follow the wizard prompt.
3. Choose record operations
4. Choose Duplicate record, then press Next
5. Check Text, and enter a meaningful name for the caption of the command button
6. You can enter a meaningful name for the command button(this is different from 5 above, then click finish


Now once you click on this button, the active record, i.e current record(a single record) on the form, consisting of over 90 fields(except the primary key) will be copied once into the same table via the form, and appended as the last record on the form.

 

[John Sh]

!) when you write "will be re-inserted", is it that the records existed in the main table , was deleted and now needs to be re-inserted?

2) You can use the duplicate record command, this will create a copy of the active record on a form, and insert a new primary key.
(except the primary key)

1) These records have been cut from the main table and copied to a separate table where they will be worked on then re-inserted into the main table when all data is correct.

2) I keep coming up against this primary key. I do not have an autonumber primary key and do not need one. It would be nothing more than useless data. The Accession Number is the unique field but is not, and cannot be, autonumber simply because it has to be a type double.. The duplicate record command will fail when it tries to duplicate the accession number so when copying to the same table I must copy all fields except the accession number.

 

[Uncle Gizmo]

The duplicate record command will fail when it tries to duplicate the accession number so when copying to the same table I must copy all fields except the accession number.

This is what I meant when I said my code requires an auto number primary key. There can only be one in any table, and it is easily identifiable with VBA code. Hence, it can be excluded from the copying code automatically without knowing its name.

 

[oleronesoftwares]

I keep coming up against this primary key. I do not have an autonumber primary key and do not need one

The design is a bit flawed, you need an autonumber pkey.

 

[mike60smart]

If you have 1 table and no relationship with any other table(s), then why use a Relational Database ?