Commit and RollBack in a Form

[Guest]

Hi

I have a form with 3 subforms. Form has a Cancel button to undo all the
chances. The problem comes when my main and subform has some new data
entered. Steve Schapel gave a guidline that access save the data before
loosing focus, so when I move focus from main to a child form, it saves my
main form and on pressing button it again moves focus to the main form so
save subform :-( He suggested to delete the currently added record which
works for addition of a new record but what about editing the existing
record. It again saves but deleting is not an option here. I was trying to
use BeginTrans, CommitTrans, Rollback Methods of a workspace object. I have a
global variable of type workspace which is set in OnLoad event of form to
DBEngine.Workspaces(0). After this when user presses Add or Edit buttons I
use MyVariable.BeginTrans to start the transaction. But if I use
MyVariable.RollBack in my cancel button, it doen't work. I don't know if I am
doing all right or even if it is possible. My previous post is at
http://msdn.microsoft.com/newsgroup...2a-5996e38e811c&cat=&lang=&cr=&sloc=en-us&p=1
posted on 4th with heading "About Cancel Button and Undo". I have worked in
ORACLE and was used to make SafePoints in the same sence.
Any help would be appreciated.
Rashid

 

[Douglas J Steele]

I don't believe there's any way to change the default behaviour of Access.
If you don't want the records being written immediately, you may have to
switch to an unbound form, although I'm not sure I've ever tried a
parent-child relationship using an unbound form. Another option would be to
use temporary tables to hold the data until you're done with it, and then
write the data to the database from the temporary tables.

 

[Demi]

One thing i have tried is a delete function when I know the user is
cancelling. maybe something like this:

Private Sub cmdCancel_Click()
Dim db As Database, qrt As QueryDef, str As String

On Error GoTo Err_cmdCancel_Click

Set db = CurrentDb()
Set qrt = db.CreateQueryDef("")
str = "DELETE MyTableDetail.MyID " & _
"FROM MyTableDetail " & _
"WHERE (MyTableDetail.MyID) = " & Me.MyID

If MsgBox("This will cancel all your allocations!" & vbCr & _
"Are you sure?", vbQuestion + vbYesNo, "Cancel allocation") =
vbYes Then
With qrt
.SQL = str
.Execute
.Close
End With
Me.frmMyTableSubForm.Requery


End If

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub