Access 2010: Create revision records for subform data
Posted on 2014-04-10
We have an Access Database form with a Main form and 7 subforms.
We would like to allow the user to create a revision to the main document and the subforms while maintaining the the original version of the data.
The subforms are linked to the mainform using the DocumentID
We already have created code using the DAO method to create the revision in the main form
Now we need to query the data in the subforms and write the data to the subform tables with the new DocumentID number.
I was thinking of using the Insert Into and a select statement like this:
Set dbData = CurrentDb()
strTS = "INSERT INTO tblDocumentProceduresTS(DocumentID, ProcedureID, ProcedureName)" & _
"SELECT me.NewRevision as tblDocumentProcedureTS.DocumentID, tblDocumentProceduresTS.ProcedureID, tblDocumentProceduresTS.ProcedureName " & _
"FROM tblDocumentProceduresTS " & _
"WHERE (((tblDocumentProceduresTS.DocumentID)=" & Me.DocumentID & "));"
Set rs = dbData.OpenRecordset(strTS)