If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Copy a Record using a macro

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Purveyor of Discontent
 
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? ***
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
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.
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 09-12-06 at 15:38.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #6 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,507
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,507
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #10 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,507
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #13 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,177
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!
Reply With Quote
  #14 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,507
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
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On