Answers for "ms access copy form and subform to table"

ms access copy form and subform to table

Dim lngOldID as Long	'change from long if text
Dim lngNewID as Long
Dim rsS as recordset	'source recordset
Dim rsT as recordset	'target recordset
Dim fld as field

DoCmd.RunCommand acCmdSaveRecord		'Save the record, to make sure all data is saved in table

lngOldID = Me!quote_ID

'Copy the main table

Set rsS = Currentdb.openrecordset("Select * From MyTable where Quote_ID=" & lngOldID, dbopensnapshot)
set rsT = currentdb.openrecordset("MyTable", dbOpenDynaset, dbAppendonly)

rst.AddNew
For each fld in rss.fields
	rst.Fields(fld.name) = fld
Next
lngNewID = rst!Quote_ID
rst.Update

'Copy a subtable

Set rsS = Currentdb.openrecordset("Select * From Subtable1 where Quote_ID=" & lngOldID, dbopensnapshot)
set rsT = currentdb.openrecordset("Subtable1", dbOpenDynaset, dbAppendonly)

rst.AddNew
rst!Quote_ID = lngNewID
For each fld in rss.fields
	if fld.name <> "Quote_ID" then
		rst.Fields(fld.name) = fld
	End if
Next
rst.Update