Duplicate Record Button

[SP76]

Can someone please help me, I am not very clued up on Access. I want to create a duplicate record button on my main page, see attached.

I have tried to use the wizard but when I click on the button nothing happens apart from the error "The command or action "Copy" is not available now"

What am I doing wrong?

Alls I am trying to do is copy one form to another.

 

[doco]

Alls I am trying to do is copy one form to another.

Can you show the code behind the button that is instanced from the _Click event?

 

[SP76]

I have On Click [Event Procedure] and this code:

Private Sub Command462_Click()
On Error GoTo Err_Command462_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command462_Click:
Exit Sub

Err_Command462_Click:
MsgBox Err.Description
Resume Exit_Command462_Click

End Sub

 

[doco]

Paste Append is greyed out - in other words not available. While I have never used this approach, it seems to me after a minor search on Google, that Paste Append is used to paste record(s) into an active table.

When I run your code, of course in a completely foreign db from yours, I get "paste append not available." Telling me that menu option is not active. Obviously, because I do not have an active table available to paste to.

Could this not also be your problem?

Need more information.

 

[SP76]

doco, the paste apend is in green, what other information should I give you?

 

[doco]

.

Yes it is in green cause it is a comment. But what drives the menu item choice 'Paste Append' is 'acEditMenu, 5' param in your DoMenuItem argument list. If Paste Append is inactive at the time the command is given then you will get the error 'not available, etc'

Please see attached image.

 

[SP76]

ok thanks anyway I'm giving up as I don't know what I'm doing properly. maybe at some stage I will figure it out.

 

[doco]

Is the db small enough to attach and let me work with it. I am having a bit of trouble seeing what it is you're trying to do?

 

[SP76]

The size of the DB is 3.33 MB too big.

I have 4 forms in total, one which I use as a switchboard or menu page, the other three are forms one to add, one to edit and one to view. All forms are identical in layout. They all have unique id numbers. What I want to do is for someone to be able to locate a document using the Edit form and copy the information on it to a new record then they may only need to edit certain areas.

I would have thought that the duplicate record wizard would be easy to use....

 

[doco]

How about posting some screen shots?

 

[SP76]

doco, I have added some screen shots, top and bottom of the form.

I want the user to copy/duplicate the record which will be in Edit Mode and alter the parts they want to change.

 

[doco]

Some questions:

Where is the record-to-be-copied coming from?
Where is the record intended to be pasted to after edit?
Where is your code instanced from? The [ Close Form ] button? or Where is the Command462 command button on your form? I don't see it.

I created a test form with a test table of data with a command button and pasted your code un-edited into the click event of that button and it works marvelously.
So, I would ask the additional question: While you have this form open in 'Edit Mode' under the menu item Edit is Paste Append active or inactive?

Also, if the [ ID ] field is a primary key and you attempt to make a copy back to the table you will have a validation problem.

 

[SP76]

Some questions:

Where is the record-to-be-copied coming from? - Coming from a table called "Table1 Main"
Where is the record intended to be pasted to after edit? - dont know? what would I need to do for this?
Where is your code instanced from? The [ Close Form ] button? or Where is the Command462 command button on your form? I don't see it. - sorry its at the top of the form, see attached. It is the Duplicate Record button.

So, I would ask the additional question: While you have this form open in 'Edit Mode' under the menu item Edit is Paste Append active or inactive? - This is showing as inactive.

Also, if the [ ID ] field is a primary key and you attempt to make a copy back to the table you will have a validation problem. - This is the currently the primary key.

 

[doco]

My guess is then, you are bringing a single record by virtue of the [Find Record] button. This action then will not allow Paste Append becasue there is no physical table to append to; which is why the Paste Append menu item is inactivated.

 

[doco]

Perhaps in your [Close Form] button you need to create and update query to update the original table with the edited data instead of the copy/paste append.

 

[SP76]

"Perhaps in your [Close Form] button you need to create and update query to update the original table with the edited data instead of the copy/paste append."

Sorry to sound to thick but how would I do that??

 

[doco]

You would need to work through each control in the active form and then populate the appropriate fields in the appropriate table. It appears all of your controls on the form are text boxes. And like almost everything, there are a couple ways to perform this task all require knowledge of the data.

(I am wondering though why the original table is not being edited instead of appending an edited record?)

Is the data on the form an exact and complete copy of a single record in the table being queried? And appearing on the form in the same order they appear in the table?

Anyway I will give an example that may help you get the idea

Dim db As DAO.Database
Dim rs As DAO.Recordset
'    will leave error trapping up to you, etc

    Set db = CurrentDb
    Set rs = db.OpenRecordset( Table2BUpdated, dbOpenDynaset )

    With rs
        '    you will need to populate the records with the text box entries
        '    and as stated there are several ways to do that - I will leave that to 
        '    you and I am assuming you are appending data not editing existing
         .AddNew  '    do not use the [ ID ] from your form it will cause error
         .Fields( "FirstField" ) = Me.FirstTextBox
         .Fields( "NextField" ) = Me.NextTextBox
         .
         .
         .
         .Fields( "LastField" ) = Me.LastTextBox
         .Update
    End With

    Set rs = Nothing
    Set db = Nothing