Copy Records from Subform of one Record to Another Record

Copy Records from Subform of one Record to Another Record

Post by Cy » Thu, 08 May 2008 01:24:02

Using Access 2000

I'll try to make this a short post, but any help would be appreciated.

I have a form, bound to a table, that allows the users to lookup an
employee. Once they are on the correct employee, they then enter time/
payroll, into a subform, for that individual employee.

What I'd like to be able to do is then take all the records in a
subform for one employee and allow the user to copy that to another
employee. They can manually copy and paste, with no problems. I've
been searching for a way to do this under a command button or
something of that nature.

Any thoughts?

Copy Records from Subform of one Record to Another Record

Post by Jeanette C » Thu, 08 May 2008 08:03:42

it sounds as if you are trying to set defaults for some of the fields in the
You can do this in a couple of ways.

Private Sub controlname_AfterUpdate()
'text field
Me.TheTextControl.DefaultValue = """" & Me. TheTextControl & """"

'date field
Me.TheDateControl.DefaultValue = "#" & Me.TheDateControl & "#"

'number field
Me.TheNumberField.DefaultValue = Me.TheNumberField
End Sub

When the user enters XYZ it will set the DefaultValue property to "XYZ"
which will be used for the next entry. When you open the form you must enter
values in the controls to be able to see the default values when you move to
the next record.

If you want a subform to inherit a value from the mainform (and don't want
editable) you can make that field part of the Master/Child Field pair.

If you want the value available when the form first opens to enter data, you
can use the field's OnGotFocus event.
Copy, paste, and edit the following code:

Private Sub controlname_GotFocus()
'Fetching default value from previous record
Dim rs As DAO.Object
Set rs = Me.RecordsetClone

' Don't do anything if no previous record exist or not new Record
If rs.EOF Or Not Me.NewRecord Then
With rs
Me![YourFieldNameHere] = .Fields("YourFieldNameHere")
End With
End If

When you tab over to the field, it will default to the previous record's

Jeanette Cunningham -- Melbourne Victoria Australia


Copy Records from Subform of one Record to Another Record

Post by Cy » Thu, 08 May 2008 23:07:43


Thank you for your response. I am not trying to copy defaults. I
have that piece mastered. What is happening, is that we are sending
out crews of 2 to 4 guys at a time to the same job site. In the past,
we would send out just one guy, but with gas prices, etc, we are now
doubling up guys.

So, the payroll girl is entering time, in the subform, for the prior
day, for the one guy. Then she moves onto the next guy on the crew
and enters the same exact data, only for the second guy, then the
third, etc. Thus, she is doing a lot of redundant entry.

The form and subsequent subform is isolated for daily entry. Meaning,
that when she gets done entering the daily activity, she has a total
hour at the bottom for just that day. It's the entries in the
subform, that I want to be able to copy and paste to the next guy on
the crew. Right now, I have her highlighting the rows and doing a
copy, then going to the next person on the crew and pasting, but this
is a manual process and can cause some issues because the girl gets
lost in what she is doing.

So, I am trying to find a way to add code, that will do the copy and
paste for her, without her having to manually highlight and copy, then
highlight and paste.

Does that make sense?


On May 6, 7:03 pm, "Jeanette Cunningham"
< XXXX@XXXXX.COM > wrote:


Copy Records from Subform of one Record to Another Record

Post by Jeanette C » Fri, 09 May 2008 05:27:11

you could save the details to a hidden form.
Put a copy button on your form.
The copy button gets the values from the hidden form and puts them in the
appropriate controls for the next guy.
The hidden form is one that you open when the database opens, and closes
when the database closes.
You may already have a hidden form that you can use for this.

Jeanette Cunningham -- Melbourne Victoria Australia

"Cy" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...