By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Is there VB code or a Macro for duplicating a record then changing one field's value

P: 13

I'm working on a database and I though I tinker with Macros and VB, I am not very versed in them. So ANY help would be HUGELY appreciated!!!
(I'm using Access 2003)

What I need to do is, from a form I've created, I need to add a button so the end user can duplicate the record they are viewing, then update either the original or the duplicate record's field ["Status"] from its current value of "Active" to "Inactive".

So, the end result are two identical records, one with a status of active, the other inactive.

Can anyone help me with the code or a Macro??

Thanks in advance for any help you can provide!!!! :)
Mar 5 '08 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 3,532
You'll need to copy those fields you want to carry forward to variables, go to a new record then plug the variables value back into the fields on the new record, then fill in the other fields:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyPartialRecord2NewRecordButton_Click()
  3. ‘Assign field values to be carried forward to variables
  4. MyFirstField = Me.FirstField
  5. MySecondField = Me.SecondField
  6. MyThirdField = Me.ThirdField
  8. 'Go to a new record
  9. DoCmd.GoToRecord , , acNewRec
  11. 'Plug in old values from variables to new record
  12. Me.FirstField = MyFirstField
  13. Me.SecondField  = MySecondField 
  14. Me.ThirdField = MyThirdField 
  16. End Sub
Welcome to TheScripts!

Linq ;0)>
Mar 5 '08 #2

P: 13
I'll give it a whirl!
I can't thank you enough!!
Mar 5 '08 #3

P: 13
Ok. I don't know where I went wrong.

I'm getting the following error
Method or data member not found.
*(The error message launched my code and highlighted the"Me.FirstField" section of the code)*

Some info that may help to understand what I'm trying (unsuccessfully) to do
  • There are 49 fields in the table that need copied.
  • Only 10 show on the form.
  • Only 1 needs updated.
  • The QTSeq field is an auto numbering PK

Here is a sample of my code

Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyRecord2New_Button_Click()
  3. 'Assign field values to be carried forward to variables
  5. QTSeq = Me.FirstField   
  6. Rebate_File_Number = Me.SecondField
  7. Pharma_Name = Me.ThirdField
  8. Comm_Med = Me.FourthField
  9. Rebate_Period = Me.FifthField
  10. Record_Type = Me.SixthField
  11. [...  i went on to define the other 42 fields in the table, but won't bore you with it all here......]
  14. 'Go to a new record
  15. DoCmd.GoToRecord , , acNewRec
  17. 'Plug in old values from variables to new record
  19. Me.FirstField = QTSeq
  20. Me.SecondField = Rebate_File_Number
  21. Me.ThirdField = Pharma_Name
  22. Me.FourthField = Comm_Med
  23. Me.FifthField = Rebate_Period
  24. Me.SixthField = Record_Type
  25. [.... same here, relisted the other 42 fields the same way....]
  27. Set Record_Type = "I"
  29. End Sub
Mar 5 '08 #4

P: 13
Never mind.. I figured out what I did wrong...

Thank you SOOOOO much for your help!!!
Mar 5 '08 #5

Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Mar 6 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.