I can't search tek-tips at the moment...
I'm looking for an EASY way to allow a user to cancel changes on a bound form that includes a subform - i.e., if the user click the 'cancel' button, any changes to both the main form and subform will rollback.
I want to use my App on Access 2000 and above and want the subform to display as a continuous form (and be able to update it).
Getting the main form to rollback is simple, but getting the subform to rollback is not so simple(?). I thought this would be a commonly wanted feature and thus there would be an easy way to implement it!
The only support i've noticed is that MS Access 2000 allows you to bind a form to a DAO recordset and Access XP allows you to bind to (and update) ADO recordsets.
There are only two methods that I know about that allow you to completely rollback changes on a main form and subform (subform displayed as a continuous form):
1. Use temporary or batch tables for the subform(s). This involves extracting data from the working tables into temp tables, binding the subform to the temp tables, then updating the working data when/if the user saves the changes.
2. Create a DAO Workspace and bind the subform to a DAO recordset made from the Workspace. Then begin a transaction on the workspace.
I've tried the second method but I can't get it to use optimistic locking....even though I tell it to and set rs.LockEdit = False.
[tt]
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set mwks = DBEngine.CreateWorkspace("mwks", "Admin", ""
Set db = mwks.OpenDatabase(CurrentDb.Name)
Set qdf = db.QueryDefs(cDATA_SOURCE)
qdf.Parameters(0).Value = SampleID
Set mrs = qdf.OpenRecordset()
'Set mrs = db.OpenRecordset("SELECT * FROM tblSampleMixing WHERE SampleID =" & SampleID, , dbOptimistic)
mrs.LockEdits = False
set mySubForm.recordset = mrs
[/tt]
How do the developers handle rollback?? Are temp or batch tables the best way?
Cheers,
Dan
I'm looking for an EASY way to allow a user to cancel changes on a bound form that includes a subform - i.e., if the user click the 'cancel' button, any changes to both the main form and subform will rollback.
I want to use my App on Access 2000 and above and want the subform to display as a continuous form (and be able to update it).
Getting the main form to rollback is simple, but getting the subform to rollback is not so simple(?). I thought this would be a commonly wanted feature and thus there would be an easy way to implement it!
The only support i've noticed is that MS Access 2000 allows you to bind a form to a DAO recordset and Access XP allows you to bind to (and update) ADO recordsets.
There are only two methods that I know about that allow you to completely rollback changes on a main form and subform (subform displayed as a continuous form):
1. Use temporary or batch tables for the subform(s). This involves extracting data from the working tables into temp tables, binding the subform to the temp tables, then updating the working data when/if the user saves the changes.
2. Create a DAO Workspace and bind the subform to a DAO recordset made from the Workspace. Then begin a transaction on the workspace.
I've tried the second method but I can't get it to use optimistic locking....even though I tell it to and set rs.LockEdit = False.
[tt]
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set mwks = DBEngine.CreateWorkspace("mwks", "Admin", ""
Set db = mwks.OpenDatabase(CurrentDb.Name)
Set qdf = db.QueryDefs(cDATA_SOURCE)
qdf.Parameters(0).Value = SampleID
Set mrs = qdf.OpenRecordset()
'Set mrs = db.OpenRecordset("SELECT * FROM tblSampleMixing WHERE SampleID =" & SampleID, , dbOptimistic)
mrs.LockEdits = False
set mySubForm.recordset = mrs
[/tt]
How do the developers handle rollback?? Are temp or batch tables the best way?
Cheers,
Dan
