Cancel Queries' Changes Before Operation Ends

[INFOS]

WHAT IS WRONG WITH THIS CODE:

Dim wsp As Workspace
Dim fInTrans As Boolean

On Error GoTo C4Err

fInTrans = False
Set wsp = DBEngine.Workspaces(0)

wsp.BeginTrans
fInTrans = True

DoCmd.OpenQuery "query1"
DoCmd.OpenQuery "query2"
DoCmd.OpenQuery "query3"
DoCmd.OpenQuery "query4"

wsp.CommitTrans

C4Exit:
Set wsp = Nothing
Exit Sub

C4Err:
MsgBox Err.Description
If fInTrans Then
wsp.Rollback
End If
Resume C4Exit

I WANT TO RUN 4 BIG APPEND QUERYS BUT IF USER PRESS ESCAPE BUTTON BEFORE ALL QUERYS ARE FINISHED - THEN ALL RECORD CHANGES MADE BY THOSE QUERYS MUST BE CANCELED.
SO I MADE THIS CODE. BUT IT DOES NOT WORK.

 

[supercharge]

Have you tried putting "fInTrans = False" before your On Error GoTo statement?

 

[INFOS]

It's not that

Now, code is correct but this still does not work:

Dim wsp As Workspace
Dim fInTrans As Boolean

On Error GoTo C4Err

fInTrans = False
Set wsp = DBEngine.Workspaces(0)

wsp.BeginTrans
fInTrans = True

DoCmd.OpenQuery "query1"
DoCmd.OpenQuery "query2"
DoCmd.OpenQuery "query3"
DoCmd.OpenQuery "query4"

wsp.CommitTrans

C4Exit:
Set wsp = Nothing
Exit Sub

C4Err:
MsgBox Err.Description
If fInTrans Then
wsp.Rollback
End If
Resume C4Exit

WHY?

 

[Bat17]

does pressing escape trigger the error?

Peter

 

[INFOS]

yes

Yes it is. And don't tell me to use autokeys. First, there is no autokeys symbol
for "escape" button ("Esc" does not work) and even if it works that causes me another problems...

 

[Bat17]

I was not going to suggest trapping the keys I was just trying to get a better understanding of the problem.
As it stands then the code will run, Esc is pressed the query is cancelled, your error message box pops up but the queries are not rolled back.
Is that's what is happening?

Peter

 

[INFOS]

reply

That's exactly what happens. Is there a way to block "escape" button until this code ends or to use "rollback" somehow?

 

[Bat17]

only thing that comes to mind at the moment is to run the queries as sql code rather than saved queries. I don't think that Esc affects them.

Peter

 

[INFOS]

Same thing

Escape button stops sql code same as saved query.

 

[RuralGuy]

Trap the keycodes and turn vbKeyEscape into 0 during your query run.

 

[INFOS]

How?

There is no way to capture escape key - at all!
Autokeys doesn't support escape key (and I need escape key on other places in my application). And you can not stop "escape key break" neather with keydown event nor with keypress event!

 

[RuralGuy]

Did you set KeyPreview to Yes and use the OnKeyPress event?

 

[INFOS]

It's not that

Yes I did - it's not that.

 

[RuralGuy]

I'm not sure what is going on in your system. I tried it and it works just fine in my system. Sorry. Maybe someone else has a suggestion.

 

[INFOS]

Code

Send me please what exactly did you wrote in "on key press" event - the code.
Thanks!

 

[RuralGuy]

Private Sub Form_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeyEscape Then
   KeyAscii = 0
End If
End Sub