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

[ladybug76]

Hi.

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!!!! :)

[missinglinq]

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:

Private Sub CopyPartialRecord2NewRecordButton_Click()
 
‘Assign field values to be carried forward to variables
MyFirstField = Me.FirstField
MySecondField = Me.SecondField
MyThirdField = Me.ThirdField
 
'Go to a new record
DoCmd.GoToRecord , , acNewRec
 
'Plug in old values from variables to new record
Me.FirstField = MyFirstField
Me.SecondField  = MySecondField 
Me.ThirdField = MyThirdField 
 
End Sub

Welcome to TheScripts!

Linq ;0)>

[ladybug76]

I'll give it a whirl!
I can't thank you enough!!

[ladybug76]

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

Private Sub CopyRecord2New_Button_Click()
 
'Assign field values to be carried forward to variables
 
QTSeq = Me.FirstField   
Rebate_File_Number = Me.SecondField
Pharma_Name = Me.ThirdField
Comm_Med = Me.FourthField
Rebate_Period = Me.FifthField
Record_Type = Me.SixthField
[...  i went on to define the other 42 fields in the table, but won't bore you with it all here......]
 
 
'Go to a new record
DoCmd.GoToRecord , , acNewRec
 
'Plug in old values from variables to new record
 
Me.FirstField = QTSeq
Me.SecondField = Rebate_File_Number
Me.ThirdField = Pharma_Name
Me.FourthField = Comm_Med
Me.FifthField = Rebate_Period
Me.SixthField = Record_Type
[.... same here, relisted the other 42 fields the same way....]
 
Set Record_Type = "I"
 
End Sub

[ladybug76]

Never mind.. I figured out what I did wrong...

Thank you SOOOOO much for your help!!!

[missinglinq]

Glad we could help!

Linq ;0)>