create duplicate records on a form datasheet

[nomvula]

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 ForecastQuantit y 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

[Aric Green]

Why not just set the default value for the fields on your form?

[missinglinq]

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 YourDateControl Name_AfterUpdat e()
If Not IsNull(Me.YourD ateControlName. Value) Then
YourDateControl Name.DefaultVal ue ="#" & Me.YourDateCont rolName & "#"
End If
End Sub

For Text fields

Private Sub YourTextControl Name_AfterUpdat e()
If Not IsNull(Me.YourT extControlName. Value) Then
YourTextControl Name.DefaultVal ue = """" & Me.YourTextCont rolName.Value & """"
End If
End Sub

For Numeric fields

Private Sub YourNumericCont rolName_AfterUp date()
If Not IsNull(Me.YourN umericControlNa me.Value) Then
YourNumericCont rolName.Default Value = Me.YourNumericC ontrolName.Valu e
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:

Private Sub cmdMultiCopy_Click()
For I = 1 To 5
  If Me.Dirty Then Me.Dirty = False
  DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdCopy
  DoCmd.GoToRecord , , acNewRec
  DoCmd.RunCommand acCmdPaste
  Me.DateCaptured = Date
Next I
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

If Not IsNull(Me.NumberCopies) then
  Copies = Me.NumberCopies
Else
Copies = 5
End If
 
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)>