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

I need to duplicate records

P: 58
I want to offer the option to duplicate a record in order to minimize input time if much or all of the record's information is the same, and allow the user to then go directly to the new record to make any slight modifications if there are any.

How do I do this?

Example:

Record 1:
Employee: 900
Date: 11/20/06
WO: 123456789
Scope: Automation
Start: 7:00 am
End: 6:30 pm
TtlTime: 11.5
RT: 11.5
OT: 0
ECO: 0
HOL: 0

Then the record opens and the employee changes the employee number to 901.

Thanks for any help offered!
Dec 10 '06 #1
Share this Question
Share on Google+
4 Replies


missinglinq
Expert 2.5K+
P: 3,532
The Command Button Wizard offers the command "Duplicate a Record" but the problem is if any field is a Primary Key or otherwise doesn't allow Duplicates this throws an error. Below is a generic version of code that I use to accomplish this task. Behind a "Copy" command button, you basically copy the data from the current record to variables, go to a new record, then plug those variables into the new record.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub CopyYourRecord_Click()
  2.    'Copy fields from original record to variables
  3.        NewField1 = Me.YourField1
  4.        NewField2 = Me.YourField2
  5.        NewField3 = Me.YourField3
  6.  
  7.        'Go to a new record
  8.        DoCmd.GoToRecord , , acNewRec
  9.  
  10.    'Plug in old values into new record
  11.    Me.YourField1.Value = NewField1
  12.       Me.YourField2.Value = NewField2
  13.       Me.YourField3.Value = NewField3
  14.  
  15.       'Now set the field your user wants to change to blanks
  16.       '(If any of these values would be numbers, use 0 [zero] instead of  "")  
  17.       Me.YourField4.Value = ""
  18.       Me.YourField5.Value = ""
  19.       Me.YourField6.Value = ""
  20.  
  21.      'If any of these fields to be changed is marked as Required in their    
  22.      'table's   Design Grid, leaving them blank will throw an error, so you'll
  23.      'need to change these to something like this: 
  24.      '(Same here, use 0 [zero] instead of  "" for numbers) 
  25.    Me.YourField4.Value = "Enter New Value for YourField4 "
  26.      Me.YourField5.Value = "Enter New Value for YourField5 "
  27.      Me.YourField6.Value = "Enter New Value for YourField6 "
  28. End Sub
  29.  
Now just follow the example and plug in your own control names.
Dec 10 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
NewField1; NewField2; & NewField3 (your temporary variables) will need to be 'Dim'ed first though.
After the first line do :
Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyYourRecord_Click()
  2.     Dim NewField1 As Variant, NewField2 As Variant, NewField3 As Variant
  3. ...
Dec 10 '06 #3

missinglinq
Expert 2.5K+
P: 3,532
Only if you use Option Explicit, NeoPa. This code came from a very small form where I hadn't done that, although I ususally do. Runs and compiles just fine.
Dec 10 '06 #4

NeoPa
Expert Mod 15k+
P: 31,661
Only if you use Option Explicit, NeoPa. This code came from a very small form where I hadn't done that, although I ususally do. Runs and compiles just fine.
I wouldn't argue with that, and I'm certainly not trying to find fault or criticize your contribution MissingLinq. It would be very bad manners for me to do that to anyone contributing answers to these forums. In your case it would also be very stupid (I hope I'm never that).
That said, I would always recommend (I'm sure you would too from what you say) that Option Explicit is used and that, even where it is absent, code should be written consistently with that option.
Dec 10 '06 #5

Post your reply

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