Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2004
    Location
    chicago
    Posts
    26

    Arrow Copy a Record using a macro

    Is there a way I can copy a Record using a macro? I looked at all of the commands available within the macro section, and couldn't find one that will copy a record.

    After a record is copied i plan to reset the values of some of the fields using the SetValue command available within macros.

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    I don't understand what you mean by "copy a record". What do you do with the "copied" record? What is meant by "reset the values"? Sounds like you may be tampering with some dangerous methods of change-control... Could you post the business requirements behind your issue?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Location
    chicago
    Posts
    26

    update

    My user wants to create an almost identical record. On her form, she wants to be able to have a button which copies the current record into a new record, and then clears out the info in three of the fields. She will then update the info in those fields and save it as the new record.

    I hope this helps. thanks

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,925
    Personally, I think macro's are poor-man's programming (even though I did a lot of macros myself). You can do the same thing in code and/or via a query.
    You can:
    1. Create a query to append the current record to another table with the Primary RecordID criteria = Forms!MyForm!MyRecordID
    2. Execute this query (docmd.openquery "MyAppendQuery") in code somewhere with either a button (On_Click event of a button) or on a form event (i.e. On_Close) and then docmd.gotorecord,,acnewrec --- view the properties and look at the events you can do on certain items such as the form or a button, etc....(On_Click, On_DoubleClick, On_Close, After_Update, etc...) - you just write a little bit of code on these events (click the ... to the right of the event line) write your code, and you have your macro.
    or
    You can do the whole thing in vba code (step 1 above) by making it a Function (i.e. Function CopyRec() and using DAO or ADO code on opening/appending/closing a recordset - there should be some examples in lots of books and on-line on doing this).

    Macros are great for learning and doing some things quickly but I haven't had to make a macro for years now. Not sure what you mean though on reseting values on the form but the docmd.gotorecord,,acnewrec will go to a new record.
    Last edited by pkstormy; 09-12-06 at 15:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2004
    Location
    chicago
    Posts
    26

    Arrow adding it to the same table

    I know, I know...macros are for losers like me. But seriously, I am just a glorified user.

    Thanks for your insight, but I don't want to append the record to another table. I want to append it to the same table it came from.

    Also, by changing values, I mean that one may want to change a date field to the current date, or change the amount of quantities ordererd, not alter the field's properties.

    Thanks again!

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,536
    Something strange is going on here in the forum. I posted the following early this morning. Don't know where it went! Access doesn't let you copy a record in macros, I'm guessing, because the command button wizard will do the job in about 30 seconds when you place a new button on your form. The problem for you is that your user wants some of the fields to change. Below is a generic version of code that I use to accomplish this. As my signature insinuates, this is only one of several ways to do the job! Good luck!

    'Copy fields from original record to variables
    NewField1 = Me.YourField1
    NewField2 = Me.YourField2
    NewField3 = Me.YourField3

    'Go to a new record
    DoCmd.GoToRecord , , acNewRec

    'Plug in values from the original record into the new record
    Me.YourField1.Value = NewField1
    Me.YourField2.Value = NewField2
    Me.YourField3.Value = NewField3

    'Now set the fields your user wants to change to blanks
    Me.YourField4.Value = ""
    Me.YourField5.Value = ""
    Me.YourField6.Value = ""

    If any of these fields to be changed are marked as Required in their table's Design Grid, leaving them blank will throw an error, so you'll need to change these fields to something like this:

    Me.YourField4.Value = "Enter New Value for YourField4"
    Me.YourField5.Value = "Enter New Value for YourField5"
    Me.YourField6.Value = "Enter New Value for YourField6"
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Sep 2006
    Location
    Massachusetts north coast
    Posts
    8
    I have an old access database that I still use. It used to do exactly what you want but with time and updating, the command no longer works. Maybe someone can tell me why... and maybe you can learn from what it was programmed to do.

    Here is the command....

    Private Sub CopyPlanToNew_Click()
    On Error GoTo Err_CopyPlanToNew_Click

    Dim NewPlan As Integer, Title As String, MsgDialog As Integer
    Const MB_YESNO = 4
    Const MB_ICONEXCLAMATION = 48
    Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
    Const CLR_WHITE = 16777215
    Const NORMAL = 1

    Title = "New Plan"
    MsgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
    NewPlan = MsgBox("Action will Copy this information to a new Plan?", MsgDialog, Title)

    If NewPlan = IDYES Then


    DoCmd A_FORMBAR, A_EDITMENU, A_SELECTRECORD_V2, , A_MENU_VER20
    DoCmd A_FORMBAR, A_EDITMENU, A_COPY, , A_MENU_VER20
    DoCmd A_FORMBAR, A_EDITMENU, 6, , A_MENU_VER20 'Paste'


    Dim Y As String
    Y = "#######"
    Forms!frmPlanAddSub!PlanID = Y
    DoCmd.GoToControl "PlanID"

    It halts on the blue text above. As you can see, it was created with ver 2.0 (old!) If someone can tell me how to fix this I would be grateful. My programmer is Loooooong gone.

    PATA

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,536
    Don't know what version you're now using, but in AC2000 this code would be replaced with:

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


    This, by the way, is the code that is generated by the command button wizard if, on placing a button on a form, you select Record Operations and Duplicate Record. This code actually replicates the actions involved if you used the menubar to select the fields of the current record, copy them and then append theis "new record" to the end of your form.
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Sep 2006
    Location
    Massachusetts north coast
    Posts
    8
    That is Fabulous! Thank you for the help! I have tried to get simple help in the past (other forums) and for some reason noone would try or they would hit me with too much technical info that I was baffled. Thank you again!

    After reading your post once again... did you simply use a wizard to creat this? Maybe I should be a little more studious and try things. I have simply tried in the past and simply had given up. I apologize for being lazy

    PATA

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,536
    Yes, this is the code generated by the Comman Button Wizard. Just follow the steps in my previous post!
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Sep 2006
    Location
    Massachusetts north coast
    Posts
    8
    oh it worked... thanks!

    Now, I dont want to get someone to create my database for free here (at this forum)... just tell me... I have a menu in this database that will display my clients alphabetically. There already is a button... "add new client". But, I want the button to also create the new client number. My client numbers are calculated by taking the client number of the client before and after the new client (alphabetically) and figuring what the average is.

    Think that would be easy?

    Thanks!

    PATA

  12. #12
    Join Date
    Feb 2014
    Posts
    3

    copy a record using the duplicate record command button

    I know this is an old post but.....
    I have a problem in access 2010
    I use a form to update records in a table, I have the duplicate record command button in use. I would like to edit this button to allow me to copy most of the fields over to new record (within same table) but leave some fields empty so that they can be updated by user.
    I have a very limited knowledge although I have created my database, I have tried looking into the embedded macro but do not understand how to add the steps I require.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,251
    Quote Originally Posted by smirnoff1911 View Post
    I have a very limited knowledge although I have created my database, I have tried looking into the embedded macro but do not understand how to add the steps I require.
    You could perhaps post the code that's in use now, so we could have an idea on how it works and see what needs to be modified.
    Have a nice day!

  14. #14
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,536
    There's a couple of ways to copy a partial Record; here's one:

    Code:
    Private Sub CopyPartialRecordButton_Click()
    
      'Assign the fields to be ‘copied’ to variables
       MyFirstField = Me.FirstField
       MySecondField = Me.SecondField
       MyThirdField = Me.ThirdField
    
      'Go to a new record
       DoCmd.GoToRecord , , acNewRec
    
      'Reverse the process and plug old values into new record
       Me.FirstField = MyFirstField
       Me.SecondField = MySecondField
       Me.ThirdField = MyThirdField
    
    End Sub


    Linq ;0)>
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  15. #15
    Join Date
    Feb 2014
    Posts
    3

    copy a record using a macro

    Sinndho, thanks for responding i have typed out the code as requested.

    I have opened the properties behind the command button (duplicate record) it has an embedded macro in event tab, when i open this the following commands are listed:
    OnError
    Go to Next
    MacroName
    Run Menu Command
    Command SelectRecord
    if (MacroError)=() then
    Run Menu Command
    Command Copy
    End if (MacroError)=() then
    Run Menu Command
    Command Recordsgotonew
    End if (MacroError)=() then
    Run Menu Command
    Command SelectRecord
    End if (MacroError)=() then
    Run Menu Command
    Command Paste
    End if
    if (MacroError)=() then
    Messagebox
    Message=[MacroError],[Description]
    beep yes
    Type none
    Title

    I know the above is probably a very simple macro but I cannot figure out how to add the commands (if it is possible) that will paste some of the fields and "clear" or make blank some of the fields.
    Please feel free to tell me to get lost if this is annoying to you "superhero" programmers, but it is driving me insane, I also know that a little knowledge is a dangerous thing, but hey I work for a small charity and we have to make do with poor little me to try and make life easier for us.

    Missinglinq, thanks for responding, I cannot figure out where I would type these commands in. I am using the command button that comes with Access and I think you are quoting VBA code? I know I sound stupid, but i'm self taught and only venture back to Access when I need something new for the database.

Posting Permissions

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