Copying a Main Form and Sub Form

[jbento]

All,
I have figured out a temporary solution for copying a main form and sub form record.

Note: There are two forms involved here and they should be identical, but just named differently. Main Form's name is: frm_main, and the form that captures the results is called: frm_main_copy. You must have a Autonumber field (i.e. MainID) associated with the main table and the sub forms table must have the same name as the Autonumber field of the main table (i.e. MainID), but the MainID field of the subform tables must be a Number datatype.

If any subforms on the main form don't have data, you have to make sure a blank record is created before doing the copy from the main form.

I do this by doing the following:

I have a field called "title".

On the After Update property of that field, I create a [Event Procedure]. This event procedure would have code for every subform on the mainform as follows:

If IsNull(Forms!frm_test!frm_test_subform1.Form!txt_subField1) Then
[Forms]![frm_test]![frm_test_subform1]![txt_subField1].Value = " "
End If

If IsNull(Forms!frm_test!frm_test_subform2.Form!txt_subField2) Then
[Forms]![frm_test]![frm_test_subform2]![txt_subField2].Value = " "
End If

If IsNull(Forms!frm_test!frm_test_subform3.Form!txt_subField3) Then
[Forms]![frm_test]![frm_test_subform3]![txt_subField3].Value = " "
End If

The next steps are for the command button on the main form (frm_main):

Private Sub Command14_Click()

Dim db As DAO.Database, rst As DAO.Recordset

'Copy record
Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_Main&quot
rst.AddNew

rst!field1 = Me!field1
rst!field2 = Me!field2
rst!field3 = Me!field3

rst.Update
rst.Close
db.Close

DoCmd.OpenForm "frm_main_copy", acNormal, "", "",, acNormal
DoCmd.RunCommand acCmdRecordsGoToLast

End Sub

The next steps are for the command button on the form that captures the results (frm_main_copy). The MainID has to be present on this form, but it should be set to not visible:

Private Sub Command48_Click()

DoCmd.SetWarnings False
[Forms]![frm_test].SetFocus
[Forms]![frm_test]![frm_test_subform1].SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy

[Forms]![frm_main_copy].SetFocus
[Forms]![frm_main_copy]![frm_test_subform1].SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdPaste
[Forms]![frm_main_copy]![frm_test_subform1]![MainID] = MainID

You would continue putting more instances of the code just above this line for every subform you have.

This works very well!!!




Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!