asked on

Access 2010: Create revision records for subform data

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 & "));"

MsgBox (strTS)
Set rs = dbData.OpenRecordset(strTS)
doData.Execute strTS

Jim P.

The code looks good. So what is the question?

btgtech

ASKER

I do not have any new records showing up in the tblDocumentProceduresTS.

Can I use the same field as the criteria for the select statement and set it to a new value in the same select statement?

btgtech

ASKER

I noticed that the execute command above used DoData instead of dbdata. I made this change but now I get an error:

Number of query values and destination fields are not the same.

Jim P.

Try chaning the code to this:

    strTS = "INSERT INTO tblDocumentProceduresTS(DocumentID, ProcedureID, ProcedureName) " & _
            "SELECT me.NewRevision as tblDocumentProcedureTS.DocumentID, tblDocumentProceduresTS.ProcedureID, tblDocumentProceduresTS.ProcedureName " & _
            "FROM tblDocumentProceduresTS " & _
            "WHERE (((tblDocumentProceduresTS.DocumentID)=" & Me.DocumentID & "));"
   
    MsgBox (strTS)

    DoCmd.SetWarnings False
    DoCmd.RunSQL strTS 
    DoCmd.SetWarnings True

Open in new window

btgtech

ASKER

This still has the same issue show up.

I do not have any new records showing up in the tblDocumentProceduresTS.

Can I use the same field as the criteria for the select statement and set it to a new value in the same select statement?

ASKER CERTIFIED SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

btgtech

ASKER

No, If you look back at the code I posted, I want to select all of the records that are equal to the current DocumentID and set it equal to the NewRevision

So for example,

If Document ID = 1 and NewRevision = 301
then
1. Select all of the records with a DocumentID of 1
2. Insert the set of records into the same table with a DocumentID = NewRevision which is 301

Make sense?

Jim P.

Change the " & Me.DocumentID & " to the " & Me.NewRevision & ".