duplicating most of the field values from the previously-entered record

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

Sometimes my users enter multiple records in a form where most of the fields
are identical, so I would like to create a VBA procedure that will duplicate
most of the values from the last record entered.

I know how to clear a field, so if there is a way to duplicate all of the
field values from the last record entered, I could easily clear the fields
that need to be changed.

Is there a VBA method that will accomplish this?

Thanks in advance.

Paul
 
Don't know if VBA has a specific method to do the repeats. A suggested
method is to declare a set of public variables, one for each of the fields
you want to repeat. Next, when you save the form items to the fields in the
DB record also save the field values to the public variables. Place a small
button next to each text box on the form. In the button click event have
the text box equal to the value in the appropriate public variable.
Provides the user the ability to choose which fields should be repeated.
User fills in the rest of the form, clicks SAVE and the process repeats
itself.

Works for me.

Cheers,
Henry
 
Paul said:
Sometimes my users enter multiple records in a form where most of the fields
are identical, so I would like to create a VBA procedure that will duplicate
most of the values from the last record entered.

I know how to clear a field, so if there is a way to duplicate all of the
field values from the last record entered, I could easily clear the fields
that need to be changed.

Is there a VBA method that will accomplish this?
Click to expand...


If you just want each new record to supply the same values
that were entered in the previously entered record, then you
can use the AfterUpdate event of each control to set it's
own DefaultValue property to the value just entered. For
example:

Sub somecontrol_AfterUpdate()
Me.somecontrol.DefaultValue = """" & Me.somecontrol & """"
End Sub

For dates, you should format the date to an unambiguous
format with the # signs around it.

If you want to have a command button that can duplicate the
current record regardless of when it was entered, the here's
the way I prefer to do it:

If Me.Dirty Then Me.Dirty = False
With Me.RecordsetClone
.AddNew
For Each ctl In Me.Controls
If ctl.Tag = "COPY" then
.Fields(ctl.ControlSource).Value = ctl.Value
End If
Next ctl
.Update
Me.Bookmark = .LastModified
End With
 
Back
Top