Results 1 to 11 of 11

Thread: Copy record

  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy record

    What does this error indicate (Access 2000 SR1)?

    Error 2046
    The command or action "Duplicate" isn't available now.

    I'm attempting to do a
    Application.RunCommand (acCmdDuplicate)

    Similar error sometimes occurs for Refresh. Thanks in advance,


    - James -

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy record

    You haven't provided enough information to answer the question. What are the exact circumstances leading up to the error message and where are you in the application when the error occurs? For example, if you're in the code window, you will get such an error because the action isn't available in the code window, so the error means exactly what it says.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy record

    OK, I'll try again. As mentioned, Access 2000 SR1.

    I have a form in single form view. On the form is a button called Copy. The click event of the button runs this code:

    Private Sub cmdCopy_Click()
    &nbsp Application.RunCommand (acCmdDuplicate)
    &nbsp Application.RunCommand (acCmdRecordsGoToLast)
    End Sub

    The code produces error 2046 at this line:
    &nbsp Application.RunCommand (acCmdDuplicate)

    The Record Source of the form is a table. All of the table's fields are bound and present on the form. In case it matters:
    - Several of the fields are fed from combo boxes.
    - One is a Listbox.
    - In addition, one textbox is an unbound calculated field. Its value comes from column(1) of one of the combo boxes.

    I'm also wondering about the difference between acCmdCopy & acCmdDuplicate. Thanks,


    - James -

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy record

    May I ask why you put parens around the arguments? There isn't any reason to, and it's visually confusing because parens are normally used to control operation order or to force an evaluation.

    I'm still not clear on what you're trying to copy. Are you trying to copy the current record to the same recordset? Are you trying to copy an object like a control (that's what acCmdDuplicate is for)?

    If you want to copy a record, here's the method I use:

    <pre>Private Sub cmdDupe_Click()
    On Error GoTo Err_cmdDupe_Click

    <font color=448800>'first set the focus back to a control in the form detail
    'so Access knows what you want to do</font color=448800>
    Me!MyControl.SetFocus

    <font color=448800>'Select the current record</font color=448800>
    DoCmd.RunCommand acCmdSelectRecord

    <font color=448800>'copy the current record</font color=448800>
    DoCmd.RunCommand acCmdCopy

    <font color=448800>'append the copy to the recordset</font color=448800>
    DoCmd.RunCommand acCmdPasteAppend

    <font color=blue>'here you have to add code to change any fields
    'that might cause a duplicate key or index error. Don't
    'worry about autonumber fields, they take care of themselves.</font color=blue>

    End Sub <font color=448800>'cmdDupe_Click()</font color=448800></pre>

    Charlotte

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy record

    Thanks. Your technique worked, of course. I suspect a similar method would work for record Refresh.

    I used the parens because I am used to programming in languages where they are required.


    - James -

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy record

    Not a problem.

    I'm not sure what you mean about record refresh. You don't refresh an individual record, you refresh the recordset or a table, etc.
    Charlotte

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Copy record

    Thanks for this Charlotte. May I ask what you would recommend when there is also information in subforms to be brought forward with the new record?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy record

    You can't bring subform records forward until you have saved the parent record, assuming you have referential integrity in place. I don't understand why you would want to duplicate subform records as well, so I don't really know what to suggest. If I wanted to do something like that for some reason, I would use an append query behind the scenes to generate copies of the subform record with the new parent record's key in them after saving the new parent record.
    Charlotte

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Copy record

    I have moved the repeating data into separate tables. Let me back up a step and give you an overview. The DB is essentially a document database, recording the details of various programs. The main table collects "pieces" of this document. There are separate tables for things like: Participants. I wanted those doing the data entry to be able to pull from a list of participants, as many of the staff that are participating in the program. Each program may have different and different numbers of participants.
    However, new programs are frequently based on older programs (this is where the record copying comes in). So I want to copy previous programs including their list of participants so they can be edited.

    Long explanation for a simple scenario...

    "I would use an append query behind the scenes " - I've been looking at this, but am currently stumped on how to keep the append query "behind the scene" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Any advice would be appreciated.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Copy record

    More questions - (always a strategy used by a database type when they are trying to figure out how to approach the problem)
    Are all programs going into a single database, or is there a separate database for each program? And how often do new programs get created? A manual process may indeed work the best if it's a relatively infrequent task and can be done by the database administrator (in other words you in all liklihood). In that case, you create an append query which selects the participant records for the program that you want to copy and append them to the participant table, presumably with the new program ID which you have already added (a single database approach) so you know the ID. If it happens more frequently, then you would want to make the process of adding a new program also prompt for the user for the ID for the program to copy, and then have some VBA code that runs the append query using the parameter specified on the form. I hope this makes sense - it feels like it rambles! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Wendell

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Copy record

    I feel so silly as I write this reply, because I did the foolish thing and asked Charlotte an additional question without trying out her reply first.
    So, for anyone else following this thread. Using an append query to update the record in the main table and subtables worked just fine. I just didn't think I could do it because, well, because I'd never actually tried it out before (go figure). <img src=/S/woops.gif border=0 alt=woops width=58 height=36>

    But don't worry Wendell, I've got a printing question that I'm going post in a new thread <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>. ....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •