Undo Changes made by Query.

[Jackpotgee]

Hi, i am stumped.

I have two tables Tbl_Referrals and Tbl_CMSData. Both of these tables have an identifier in called ConflictID. I have created a query which selects the records from both tables where the conflict ID is the same.

I have a form running this query. Down one side of the form i have all the details from Tbl_CMSData and on the otherside of the Form i have the corresponding details from Tbl_Referrals. I need the user to be able to update field by field from Tbl_CMSData to TblReferrals. This is done by means of a simple update query for each field. This all works fine.

Problem - I need an undo button for each field. Me.Undo does not work becuase the changes have been made by a query and not on the form.

Is there a way of undoing what the query has updated? Possibly by setting the focus to a Tbl_Referrals and then undoing the changes?

Thanks in advance

 

[Guus2005]

No, there is not an easy way to do this.

You can track your changes in another database and from there undo your changes.

or duplicate your record and use a timestamp to track the last one.
Deleting the last one will "undo" your changes.

HTH

 

[The_Doc_Man]

Access is a small-business tool with small-business features.

You are asking for a rollback feature that exists in ORACLE and some of the other big-machine databases. Doesn't happen in Access.

Anything you do by query commits immediately. There is NO rollback ability. Worse, you can't implement an audit system that would support a rollback because there are no executable events in a query to drive any event code that could track such things.

 

[Jackpotgee]

Thanks you two, i was beginning to fear as much!

 

[Guus2005]

Access is a small-business tool with small-business features.

You are asking for a rollback feature that exists in ORACLE and some of the other big-machine databases. Doesn't happen in Access.

Anything you do by query commits immediately. There is NO rollback ability. Worse, you can't implement an audit system that would support a rollback because there are no executable events in a query to drive any event code that could track such things.

Sorry, don't agree with you. Don't know the functionality within Oracle but
within a function (never tried it globally) you can define a workspace. within this workspace you can begin a transaction and then commit or rollback.

sub Aap()

    Dim ws as workspace
    dim dbs as database

    on error goto Err_Aap
    Set ws = DBEngine(0)
    set dbs = ws(0)
    
    ws.BeginTrans
    dbs.Execute "Update Reports Set Sequence = 10"
    dbs.Execute "Update Clients Set Sequence = 11"
    ws.CommitTrans
        
Exit_Aap:
    Exit Sub
Err_Aap:
    ws.Rollback
    resume Exit_Aap
End Sub

Once out of the procedure, your changes are final.

HTH

 

[The_Doc_Man]

From his description, a begin/commit block was not implied, hence my answer.