Duplicate record

[HappyApple]

HI there,
I have a form which is made up from a query linking together about 3 tables.

I would like the user to be able to click a button on the form that enables them to duplicate the entire record without having to retype it in all the fields. The new record should have a new id and the ability to make minor changes to any fields afterwards.

I know this should be simple but I'm not using vba yet and the 'Paste Append' is not availble from the form.

any help would be greatly appreciated

 

[missinglinq]

I'm guessing that if you've found Paste Append to not be available you've tried using the Command Button Wizard to create a copy button and it's failed on this. This, plus the fact that your form is based on a query made up of 3 tables, makes me wonder if you can, in fact, add records to the form, or is it Read-Only?

 

[HappyApple]

yes, records can be added to the form ... this is what the form was designed for. Its just a shame as when adding a number of new records via the form here is a lot of detailed and lengthy info and it can become tedious having to re-type it so many times for each new record

 

[gemma-the-husky]

one way

make a button to save the data from any selected row into a temporary table (or a typed variable structure)
have another button to paste this data into the current (new) row

you will need a bit of code to do this with recordset fields or to manage a typed variable, if there isnt a paste all command.


a second way
store the data in a control in its tag property, and on a new record restore the last tag property. Access cookbook demos this to make individual controls on a form behave in this way, and users can select which fields remember their data. I haven't got the code to hand


a third way
create the new data in a spreadsheet, and load the data from a spreadsheet - you have to write the interface once, if you use it a lot its well worth it.

a fourth way (possibly)
display the data in datasheet view - i am not sure, but you may be able to cut and paste whole rows

 

[missinglinq]

How about, in Design View, select all controls you want to "carry forward."
Goto Properties - Other
In the Tag Property enter CarryForward (without quotes)
Now use this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
  If ctl.Tag = "CarryForward" Then
    ctl.DefaultValue = """" & ctl.Value & """"
  End If
Next ctl
End Sub

Each of the "tagged" controls' values will be carried forward into a new record until you either

1. Edit the data
2. Close the form

 

[mrgunner]

To me it sound using the Command Button Wizard would do it, i guess.

 

[missinglinq]

I suspect that's what the OP tried to begin with, given his comment about 'Paste Append' is not available from the form. That's part of the standard code generated by the Wizard for this kind of button. Not sure why this wouldn't work. It would probably help if Happy could Zip up and attach the field for us to look at.

 

[birsey]

Hi, I've not been using Access for long, but I found a very easy way to do this (note though; I use Access 2007, so I'm not sure if this will work on previous versions, and I have described how to do this in the 2007 version).

1. Open your form in design view.
2. Click the "design" tab at the top of the page (2nd-from the right).
3. Click the "Button" button (!!!).
4. Position your new "cross-hairs" cursor where you want the command button to be positioned on your form, and drag to the size of the button you want. This launches the "command-button" wizard.
5. Select "record operations", "duplicate record".
6. Hit the Next button, edit the text you want to appear on your button (the default is "Duplicate Record", which ought to work well), give the button a name (which you will probably never need to refer to again), and the wizard does the rest.

My (crude) understanding of how this works is that Access 2007 creates an invisible "embedded" VBA code within the form. It couldn't be easier to implement, and does exactly what you want it to. Hooray!

 

[chefboyrz]

I suspect that's what the OP tried to begin with, given his comment about 'Paste Append' is not available from the form. That's part of the standard code generated by the Wizard for this kind of button. Not sure why this wouldn't work. It would probably help if Happy could Zip up and attach the field for us to look at.

I had the same issue... I just added

Me.AllowAdditions = True
​

before the code that is generated by the wizard and it took care of the paste append error. Note that this was in Access 2003, but sounds like exactly the same deal.

 

[HappyApple]

Thank you for all your responses. I will be working on this again this week so will let you know how I get on.
ps (yes you are right ... the command wizard button doesnt work)

 

[snair]

Hi there,

I am a new Acess user & a new user to this forum & I have a related question I am using Access 2007... I have a combo-box that I use to first find the record & then need to duplicate that record... I have created a button that I titled "Duplicate Record" ... I want this button, when clicked, to duplicate records.. I used using the "command-button" wizard & then "record operations", & "duplicate record" . The problem is that after it duplicates 1 -3 records (after using the combobox to search records & then duplicating the record using the button I created to duplicate the record), it gives me a type mismatch error (13)... I have no idea about coding whatsoever, but I tried to look at the crude code it automatically created, & apparently there is some issue with the combobox .. that where it was highlighted .. Any suggestions to overcome this issue?? Thanks so very much ..

Hi, I've not been using Access for long, but I found a very easy way to do this (note though; I use Access 2007, so I'm not sure if this will work on previous versions, and I have described how to do this in the 2007 version).

1. Open your form in design view.
2. Click the "design" tab at the top of the page (2nd-from the right).
3. Click the "Button" button (!!!).
4. Position your new "cross-hairs" cursor where you want the command button to be positioned on your form, and drag to the size of the button you want. This launches the "command-button" wizard.
5. Select "record operations", "duplicate record".
6. Hit the Next button, edit the text you want to appear on your button (the default is "Duplicate Record", which ought to work well), give the button a name (which you will probably never need to refer to again), and the wizard does the rest.

My (crude) understanding of how this works is that Access 2007 creates an invisible "embedded" VBA code within the form. It couldn't be easier to implement, and does exactly what you want it to. Hooray!