Copy Datasheet Subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a form with a subform that is in the datasheet view. I would like to
be able to copy the entire record, subform and all with a command button.
How on earth would I code this?
Thanks!
Melinda
 
OK, the table that my master form (frmTimeCard) comes from is tblDateName.
The table my sub form (frmSub) comes from is tblTimeJob. The forms are
linked by DateID which is an AutoNumber in tblDateName and a Number in
tblTimeJob.
Thanks for the help! I've never even heard of Insert Queries! If you need
any more info let me know.
 
Thanks Ofer,
Your suggestion didn't do what I was looking for. I don't want to copy just
the subform. I want to copy the master and the sub and paste the whole thing
into a new record. Any more ideas?

As to what it's for:
You're going to love this. We are testing out having our time cards in
Access. Now, if we work more than 40 hours, we have to put our overtime onto
a second time card so they know who to bill the OT to. So, one of my
coworkers requested that he be able to copy his time card, so all he'd have
to do is change the hours. You see, the jobs and hours are displayed in the
subform and linked to the DateID of the master (which displays the employee
and the week in question).

Thanks,
Melinda
 
Try this

Me.SubFormName.SetFocus
SendKeys "^{a}^{a}"
SendKeys "^{c}^{c}"

Out of interest, why?
 
' Try this
Private Sub ButtonName_Click()
Dim MaxDateID As Double
' To add the records to the main table, add all fields except of the key, it
will be assign automaticaly
DoCmd.RunSQL "INSERT INTO tblDateName ( [Field1 Name], [Field2 Name]
,[Field3 Name] ,[Field4 Name] ) SELECT tblDateName.[Field Name] ,
tblDateName.[Field2 Name] ,tblDateName.[Field3 Name] ,tblDateName.[Field4
Name] FROM tblDateName WHERE [DateID] = " & Me.DateID
' Look for the new key
MaxDateID = DMax("DateID", "tblDateName")
' Insert the new records in the second table based on the old key, but with
the new key above
DoCmd.RunSQL "INSERT INTO tblTimeJob ( [Field1 Name], [Field2 Name] ,[Field3
Name] ,[Field4 Name],[DateID] ) SELECT tblTimeJob.[Field Name] ,
tblTimeJob.[Field2 Name] ,tblTimeJob.[Field3 Name] ,tblTimeJob.[Field4 Name]
, " & MaxDateID & " FROM tblTimeJob WHERE [DateID] = " & Me.DateID
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[DateID] = " & MaxDateID
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub
 
In that case the easiest way will be, to create to insert queries, that will
insert the records from the current main form and the subform.
Requey the Form
And go to the new record

I can help you with the code, if you provide me with the names of the
tables, and the key (name and type) that join both tables.
 
That did it! Thanks for all of your help. I never would have figured out
that one!

Ofer said:
' Try this
Private Sub ButtonName_Click()
Dim MaxDateID As Double
' To add the records to the main table, add all fields except of the key, it
will be assign automaticaly
DoCmd.RunSQL "INSERT INTO tblDateName ( [Field1 Name], [Field2 Name]
,[Field3 Name] ,[Field4 Name] ) SELECT tblDateName.[Field Name] ,
tblDateName.[Field2 Name] ,tblDateName.[Field3 Name] ,tblDateName.[Field4
Name] FROM tblDateName WHERE [DateID] = " & Me.DateID
' Look for the new key
MaxDateID = DMax("DateID", "tblDateName")
' Insert the new records in the second table based on the old key, but with
the new key above
DoCmd.RunSQL "INSERT INTO tblTimeJob ( [Field1 Name], [Field2 Name] ,[Field3
Name] ,[Field4 Name],[DateID] ) SELECT tblTimeJob.[Field Name] ,
tblTimeJob.[Field2 Name] ,tblTimeJob.[Field3 Name] ,tblTimeJob.[Field4 Name]
, " & MaxDateID & " FROM tblTimeJob WHERE [DateID] = " & Me.DateID
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[DateID] = " & MaxDateID
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
I hope that helped
Good luck


Melinda said:
OK, the table that my master form (frmTimeCard) comes from is tblDateName.
The table my sub form (frmSub) comes from is tblTimeJob. The forms are
linked by DateID which is an AutoNumber in tblDateName and a Number in
tblTimeJob.
Thanks for the help! I've never even heard of Insert Queries! If you need
any more info let me know.
Click to expand...
Click to expand...
 
Back
Top