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

create duplicate records on a form datasheet

P: 6
hi guys
i need some help to duplicate records on my form datasheet:
here's the example of my form results:
ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual
UJ 18-Apr-08 01-Mar-08 Fees: Asset 1 R 31,200.00 R 31,200.00
NMBM 22-Apr-08 23-Mar-08 P-MI (E) 07/2006 3 R 47,485 R 38,849
i have 200 records deplayed in the form i'm using access2007 and i have a command button which is the built in command button to duplicate records in the form, at the moment when i click the button on one record is duplicated but i want to duplicate atleast 5 records and set the DateCaptured to today's date.

the reason why i want to user to be able to duplicate the records is that 90% of the time when the user enters the entry in the form most of the fields will remain the same and only the DateCaptured, Forecast or Actual values would need to be changed so rather than the user enters the new records he/she should be able to duplicate few rows and edit the relevant fields from there.

another thing i only sent you 7 fields of 15 fields in my form.

hope this explains clearly of what i'm trying to do.
and thanks in advance
Aug 27 '08 #1
Share this Question
Share on Google+
2 Replies

P: 23
Why not just set the default value for the fields on your form?
Aug 27 '08 #2

Expert 2.5K+
P: 3,532
Aric's suggestion is a good one if the record you want to copy has just been entered, i.e. if you're entering a series of records in a row.

In your form, you can use the AfterUpdate event of the control holding your data to set the DefaultValue for the field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record. The syntax varies slightly, depending on the datatype of the data:

For Date fields

Private Sub YourDateControlName_AfterUpdate()
If Not IsNull(Me.YourDateControlName.Value) Then
YourDateControlName.DefaultValue ="#" & Me.YourDateControlName & "#"
End If
End Sub

For Text fields

Private Sub YourTextControlName_AfterUpdate()
If Not IsNull(Me.YourTextControlName.Value) Then
YourTextControlName.DefaultValue = """" & Me.YourTextControlName.Value & """"
End If
End Sub

For Numeric fields

Private Sub YourNumericControlName_AfterUpdate()
If Not IsNull(Me.YourNumericControlName.Value) Then
YourNumericControlName.DefaultValue = Me.YourNumericControlName.Value
End If
End Sub

If, on the other hand, you want to come back at a later date and make multiple copies of a record from an earlier sessions, this should do the job:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdMultiCopy_Click()
  2. For I = 1 To 5
  3.   If Me.Dirty Then Me.Dirty = False
  4.   DoCmd.RunCommand acCmdSelectRecord
  5.   DoCmd.RunCommand acCmdCopy
  6.   DoCmd.GoToRecord , , acNewRec
  7.   DoCmd.RunCommand acCmdPaste
  8.   Me.DateCaptured = Date
  9. Next I
  10. End Sub
You could also set the Forecast and Actual fields to blank if you wanted to, just like DateCaptured is set to the current date.

You could also modify this code to accept a variable, if you wanted to.

Place an unbound textbox, call it NumberCopies.

Replace the line

For I = 1 To 5

with these lines

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.NumberCopies) then
  2.   Copies = Me.NumberCopies
  3. Else
  4. Copies = 5
  5. End If
  7. For I = 1 To Cpies
and the user can decide how many copies to make by entering the number in the textbox. If no number is entered, a default of 5 copies will be made.

My question to you is, how do you have a command button on a form that is in Datasheet view? Unbound controls, such as command buttons, don't show on Datasheets!

Welcome to Bytes!

Linq ;0)>
Aug 27 '08 #3

Post your reply

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