I have a serious problem with BeginTrans - Rollback
Rollback isn't working

Below is the code for a command button on a test form on a test table
in a big complicated FromtEnd access 97 application
after the code is run the table has a new record in it!

The form + code work as expexted if dragged into a new database

Have tried:- Repair,Compact - no go!


ANY IDEAS PLEASE?

you might try an Access VBA newgroup

########## CODE ########
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim cdb As Database, testset As Recordset

BeginTrans
Set cdb = CurrentDb
Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
testset.AddNew
testset!egno = 1
testset.Update
Rollback
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

###### END CODE #########

--
Jim Bunton



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.837 / Virus Database: 570 - Release Date: 17/01/2005

In your code, the BeginTrans is not applied to anything. Apply it to the
default workspace.

There's an example and discussion of the pitfalls for transactions in this
article:
Archive: Move records to another table
at:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim Bunton" <(E-Mail Removed)> wrote in message
news:rawHd.178533$(E-Mail Removed)...
> I have a serious problem with BeginTrans - Rollback
> Rollback isn't working
>
> Below is the code for a command button on a test form on a test table
> in a big complicated FromtEnd access 97 application
> after the code is run the table has a new record in it!
>
> The form + code work as expexted if dragged into a new database
>
> Have tried:- Repair,Compact - no go!
>
>
> ANY IDEAS PLEASE?
>
> you might try an Access VBA newgroup
>
> ########## CODE ########
> Private Sub Command0_Click()
> On Error GoTo Err_Command0_Click
> Dim cdb As Database, testset As Recordset
>
> BeginTrans
> Set cdb = CurrentDb
> Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
> testset.AddNew
> testset!egno = 1
> testset.Update
> Rollback
> Exit_Command0_Click:
> Exit Sub
>
> Err_Command0_Click:
> MsgBox Err.Description
> Resume Exit_Command0_Click
>
> End Sub
>
> ###### END CODE #########

Thanks for the reply Allen - no go I'm afraid

***** New code ****
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim cdb As Database, testset As Recordset
MsgBox "Number of workspaces - " & Workspaces.Count
'workspaces.count returns 1
Workspaces(0).BeginTrans
Set cdb = CurrentDb
Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
testset.AddNew
testset!egno = 1
testset.Update
Workspaces(0).Rollback
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
**** End Code ******
"Allen Browne" <(E-Mail Removed)> wrote in message
news:eDlaAnk$(E-Mail Removed)...
> In your code, the BeginTrans is not applied to anything. Apply it to the
> default workspace.
>
> There's an example and discussion of the pitfalls for transactions in this
> article:
> Archive: Move records to another table
> at:
> http://members.iinet.net.au/~allenbrowne/ser-37.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jim Bunton" <(E-Mail Removed)> wrote in message
> news:rawHd.178533$(E-Mail Removed)...
> > I have a serious problem with BeginTrans - Rollback
> > Rollback isn't working
> >
> > Below is the code for a command button on a test form on a test table
> > in a big complicated FromtEnd access 97 application
> > after the code is run the table has a new record in it!
> >
> > The form + code work as expexted if dragged into a new database
> >
> > Have tried:- Repair,Compact - no go!
> >
> >
> > ANY IDEAS PLEASE?
> >
> > you might try an Access VBA newgroup
> >
> > ########## CODE ########
> > Private Sub Command0_Click()
> > On Error GoTo Err_Command0_Click
> > Dim cdb As Database, testset As Recordset
> >
> > BeginTrans
> > Set cdb = CurrentDb
> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
> > testset.AddNew
> > testset!egno = 1
> > testset.Update
> > Rollback
> > Exit_Command0_Click:
> > Exit Sub
> >
> > Err_Command0_Click:
> > MsgBox Err.Description
> > Resume Exit_Command0_Click
> >
> > End Sub
> >
> > ###### END CODE #########
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.837 / Virus Database: 570 - Release Date: 18/01/2005

You are not being consistent here.
Explicitly set a variable to the default workspace.
Begin the transaction.
Set your database variable to the databaes in that workspace.
OpenRecordset on that database variable.
Perform your changes.
You can now rollback.

Dim ws As DAO.Workspace
Set ws = DBEngine(0)
ws.BeginTrans
Set cdb = ws(0)
'Open your recordset here.

ws.Rollback

Go back and re-read the article. It explains all that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim Bunton" <(E-Mail Removed)> wrote in message
news:QhPHd.182530$(E-Mail Removed)...
> Thanks for the reply Allen - no go I'm afraid
>
> ***** New code ****
> Private Sub Command0_Click()
> On Error GoTo Err_Command0_Click
>
> Dim cdb As Database, testset As Recordset
> MsgBox "Number of workspaces - " & Workspaces.Count
> 'workspaces.count returns 1
> Workspaces(0).BeginTrans
> Set cdb = CurrentDb
> Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
> testset.AddNew
> testset!egno = 1
> testset.Update
> Workspaces(0).Rollback
> Exit_Command0_Click:
> Exit Sub
>
> Err_Command0_Click:
> MsgBox Err.Description
> Resume Exit_Command0_Click
> End Sub
> **** End Code ******
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:eDlaAnk$(E-Mail Removed)...
>> In your code, the BeginTrans is not applied to anything. Apply it to the
>> default workspace.
>>
>> There's an example and discussion of the pitfalls for transactions in
>> this
>> article:
>> Archive: Move records to another table
>> at:
>> http://members.iinet.net.au/~allenbrowne/ser-37.html
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Jim Bunton" <(E-Mail Removed)> wrote in message
>> news:rawHd.178533$(E-Mail Removed)...
>> > I have a serious problem with BeginTrans - Rollback
>> > Rollback isn't working
>> >
>> > Below is the code for a command button on a test form on a test table
>> > in a big complicated FromtEnd access 97 application
>> > after the code is run the table has a new record in it!
>> >
>> > The form + code work as expexted if dragged into a new database
>> >
>> > Have tried:- Repair,Compact - no go!
>> >
>> >
>> > ANY IDEAS PLEASE?
>> >
>> > you might try an Access VBA newgroup
>> >
>> > ########## CODE ########
>> > Private Sub Command0_Click()
>> > On Error GoTo Err_Command0_Click
>> > Dim cdb As Database, testset As Recordset
>> >
>> > BeginTrans
>> > Set cdb = CurrentDb
>> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
>> > testset.AddNew
>> > testset!egno = 1
>> > testset.Update
>> > Rollback
>> > Exit_Command0_Click:
>> > Exit Sub
>> >
>> > Err_Command0_Click:
>> > MsgBox Err.Description
>> > Resume Exit_Command0_Click
>> >
>> > End Sub
>> >
>> > ###### END CODE #########

Aha - seem to have found a possible answer:-

Dragged the old database bit by bit into a new empty database.

Compile began to throw up errors not found in thew original:
I had some code that was refering to columns in forms' RecordSource
columns which were NOT actually displayed as
me.columnName

altering these to Me!Column name

got rid of the compile errors and BeginTrans > RollBack is still working

SO - it's an fault in the compiler not picking these errors up in the
original database - PERHAPS!



"Allen Browne" <(E-Mail Removed)> wrote in message
news:%23eVGr1w$(E-Mail Removed)...
> You are not being consistent here.
> Explicitly set a variable to the default workspace.
> Begin the transaction.
> Set your database variable to the databaes in that workspace.
> OpenRecordset on that database variable.
> Perform your changes.
> You can now rollback.
>
> Dim ws As DAO.Workspace
> Set ws = DBEngine(0)
> ws.BeginTrans
> Set cdb = ws(0)
> 'Open your recordset here.
>
> ws.Rollback
>
> Go back and re-read the article. It explains all that.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jim Bunton" <(E-Mail Removed)> wrote in message
> news:QhPHd.182530$(E-Mail Removed)...
> > Thanks for the reply Allen - no go I'm afraid
> >
> > ***** New code ****
> > Private Sub Command0_Click()
> > On Error GoTo Err_Command0_Click
> >
> > Dim cdb As Database, testset As Recordset
> > MsgBox "Number of workspaces - " & Workspaces.Count
> > 'workspaces.count returns 1
> > Workspaces(0).BeginTrans
> > Set cdb = CurrentDb
> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
> > testset.AddNew
> > testset!egno = 1
> > testset.Update
> > Workspaces(0).Rollback
> > Exit_Command0_Click:
> > Exit Sub
> >
> > Err_Command0_Click:
> > MsgBox Err.Description
> > Resume Exit_Command0_Click
> > End Sub
> > **** End Code ******
> > "Allen Browne" <(E-Mail Removed)> wrote in message
> > news:eDlaAnk$(E-Mail Removed)...
> >> In your code, the BeginTrans is not applied to anything. Apply it to
the
> >> default workspace.
> >>
> >> There's an example and discussion of the pitfalls for transactions in
> >> this
> >> article:
> >> Archive: Move records to another table
> >> at:
> >> http://members.iinet.net.au/~allenbrowne/ser-37.html
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Jim Bunton" <(E-Mail Removed)> wrote in message
> >> news:rawHd.178533$(E-Mail Removed)...
> >> > I have a serious problem with BeginTrans - Rollback
> >> > Rollback isn't working
> >> >
> >> > Below is the code for a command button on a test form on a test table
> >> > in a big complicated FromtEnd access 97 application
> >> > after the code is run the table has a new record in it!
> >> >
> >> > The form + code work as expexted if dragged into a new database
> >> >
> >> > Have tried:- Repair,Compact - no go!
> >> >
> >> >
> >> > ANY IDEAS PLEASE?
> >> >
> >> > you might try an Access VBA newgroup
> >> >
> >> > ########## CODE ########
> >> > Private Sub Command0_Click()
> >> > On Error GoTo Err_Command0_Click
> >> > Dim cdb As Database, testset As Recordset
> >> >
> >> > BeginTrans
> >> > Set cdb = CurrentDb
> >> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
> >> > testset.AddNew
> >> > testset!egno = 1
> >> > testset.Update
> >> > Rollback
> >> > Exit_Command0_Click:
> >> > Exit Sub
> >> >
> >> > Err_Command0_Click:
> >> > MsgBox Err.Description
> >> > Resume Exit_Command0_Click
> >> >
> >> > End Sub
> >> >
> >> > ###### END CODE #########
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.840 / Virus Database: 571 - Release Date: 19/01/2005

That sounds like a familiar inconsistency.

If you refer to a field in the form's recordset that is not represented by a
control on the form with the same name, the reference is an object of type
AccessField. This type has some inconsistencies (bugs?) associated with it.
Of of those is that it Me.MyField can suddenly stop compiling one day when
you make some other seemingly unrelated change. The workaround is (as you
found), to use Me!MyField, which is not checked at compile time (i.e. you
get no compiler error if you misspell it, like you do with Me.MyField). This
happens infrequently, but often enough that I now use the bang whenver I
refer to an AccessField.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim Bunton" <(E-Mail Removed)> wrote in message
news:G5aId.25158$(E-Mail Removed)...
> Aha - seem to have found a possible answer:-
>
> Dragged the old database bit by bit into a new empty database.
>
> Compile began to throw up errors not found in thew original:
> I had some code that was refering to columns in forms' RecordSource
> columns which were NOT actually displayed as
> me.columnName
>
> altering these to Me!Column name
>
> got rid of the compile errors and BeginTrans > RollBack is still working
>
> SO - it's an fault in the compiler not picking these errors up in the
> original database - PERHAPS!
>
>
>
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:%23eVGr1w$(E-Mail Removed)...
>> You are not being consistent here.
>> Explicitly set a variable to the default workspace.
>> Begin the transaction.
>> Set your database variable to the databaes in that workspace.
>> OpenRecordset on that database variable.
>> Perform your changes.
>> You can now rollback.
>>
>> Dim ws As DAO.Workspace
>> Set ws = DBEngine(0)
>> ws.BeginTrans
>> Set cdb = ws(0)
>> 'Open your recordset here.
>>
>> ws.Rollback
>>
>> Go back and re-read the article. It explains all that.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Jim Bunton" <(E-Mail Removed)> wrote in message
>> news:QhPHd.182530$(E-Mail Removed)...
>> > Thanks for the reply Allen - no go I'm afraid
>> >
>> > ***** New code ****
>> > Private Sub Command0_Click()
>> > On Error GoTo Err_Command0_Click
>> >
>> > Dim cdb As Database, testset As Recordset
>> > MsgBox "Number of workspaces - " & Workspaces.Count
>> > 'workspaces.count returns 1
>> > Workspaces(0).BeginTrans
>> > Set cdb = CurrentDb
>> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
>> > testset.AddNew
>> > testset!egno = 1
>> > testset.Update
>> > Workspaces(0).Rollback
>> > Exit_Command0_Click:
>> > Exit Sub
>> >
>> > Err_Command0_Click:
>> > MsgBox Err.Description
>> > Resume Exit_Command0_Click
>> > End Sub
>> > **** End Code ******
>> > "Allen Browne" <(E-Mail Removed)> wrote in message
>> > news:eDlaAnk$(E-Mail Removed)...
>> >> In your code, the BeginTrans is not applied to anything. Apply it to
> the
>> >> default workspace.
>> >>
>> >> There's an example and discussion of the pitfalls for transactions in
>> >> this
>> >> article:
>> >> Archive: Move records to another table
>> >> at:
>> >> http://members.iinet.net.au/~allenbrowne/ser-37.html
>> >>
>> >> --
>> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >> "Jim Bunton" <(E-Mail Removed)> wrote in message
>> >> news:rawHd.178533$(E-Mail Removed)...
>> >> > I have a serious problem with BeginTrans - Rollback
>> >> > Rollback isn't working
>> >> >
>> >> > Below is the code for a command button on a test form on a test
>> >> > table
>> >> > in a big complicated FromtEnd access 97 application
>> >> > after the code is run the table has a new record in it!
>> >> >
>> >> > The form + code work as expexted if dragged into a new database
>> >> >
>> >> > Have tried:- Repair,Compact - no go!
>> >> >
>> >> >
>> >> > ANY IDEAS PLEASE?
>> >> >
>> >> > you might try an Access VBA newgroup
>> >> >
>> >> > ########## CODE ########
>> >> > Private Sub Command0_Click()
>> >> > On Error GoTo Err_Command0_Click
>> >> > Dim cdb As Database, testset As Recordset
>> >> >
>> >> > BeginTrans
>> >> > Set cdb = CurrentDb
>> >> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
>> >> > testset.AddNew
>> >> > testset!egno = 1
>> >> > testset.Update
>> >> > Rollback
>> >> > Exit_Command0_Click:
>> >> > Exit Sub
>> >> >
>> >> > Err_Command0_Click:
>> >> > MsgBox Err.Description
>> >> > Resume Exit_Command0_Click
>> >> >
>> >> > End Sub
>> >> >
>> >> > ###### END CODE #########

Thanks for the help Allen -
I shall follow your example in future and make sure that I use the bangs!



"Allen Browne" <(E-Mail Removed)> wrote in message
news:u$rkol%23$(E-Mail Removed)...
> That sounds like a familiar inconsistency.
>
> If you refer to a field in the form's recordset that is not represented by
a
> control on the form with the same name, the reference is an object of type
> AccessField. This type has some inconsistencies (bugs?) associated with
it.
> Of of those is that it Me.MyField can suddenly stop compiling one day when
> you make some other seemingly unrelated change. The workaround is (as you
> found), to use Me!MyField, which is not checked at compile time (i.e. you
> get no compiler error if you misspell it, like you do with Me.MyField).
This
> happens infrequently, but often enough that I now use the bang whenver I
> refer to an AccessField.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jim Bunton" <(E-Mail Removed)> wrote in message
> news:G5aId.25158$(E-Mail Removed)...
> > Aha - seem to have found a possible answer:-
> >
> > Dragged the old database bit by bit into a new empty database.
> >
> > Compile began to throw up errors not found in thew original:
> > I had some code that was refering to columns in forms' RecordSource
> > columns which were NOT actually displayed as
> > me.columnName
> >
> > altering these to Me!Column name
> >
> > got rid of the compile errors and BeginTrans > RollBack is still working
> >
> > SO - it's an fault in the compiler not picking these errors up in the
> > original database - PERHAPS!
> >
> >
> >
> > "Allen Browne" <(E-Mail Removed)> wrote in message
> > news:%23eVGr1w$(E-Mail Removed)...
> >> You are not being consistent here.
> >> Explicitly set a variable to the default workspace.
> >> Begin the transaction.
> >> Set your database variable to the databaes in that workspace.
> >> OpenRecordset on that database variable.
> >> Perform your changes.
> >> You can now rollback.
> >>
> >> Dim ws As DAO.Workspace
> >> Set ws = DBEngine(0)
> >> ws.BeginTrans
> >> Set cdb = ws(0)
> >> 'Open your recordset here.
> >>
> >> ws.Rollback
> >>
> >> Go back and re-read the article. It explains all that.
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Jim Bunton" <(E-Mail Removed)> wrote in message
> >> news:QhPHd.182530$(E-Mail Removed)...
> >> > Thanks for the reply Allen - no go I'm afraid
> >> >
> >> > ***** New code ****
> >> > Private Sub Command0_Click()
> >> > On Error GoTo Err_Command0_Click
> >> >
> >> > Dim cdb As Database, testset As Recordset
> >> > MsgBox "Number of workspaces - " & Workspaces.Count
> >> > 'workspaces.count returns 1
> >> > Workspaces(0).BeginTrans
> >> > Set cdb = CurrentDb
> >> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
> >> > testset.AddNew
> >> > testset!egno = 1
> >> > testset.Update
> >> > Workspaces(0).Rollback
> >> > Exit_Command0_Click:
> >> > Exit Sub
> >> >
> >> > Err_Command0_Click:
> >> > MsgBox Err.Description
> >> > Resume Exit_Command0_Click
> >> > End Sub
> >> > **** End Code ******
> >> > "Allen Browne" <(E-Mail Removed)> wrote in message
> >> > news:eDlaAnk$(E-Mail Removed)...
> >> >> In your code, the BeginTrans is not applied to anything. Apply it to
> > the
> >> >> default workspace.
> >> >>
> >> >> There's an example and discussion of the pitfalls for transactions
in
> >> >> this
> >> >> article:
> >> >> Archive: Move records to another table
> >> >> at:
> >> >> http://members.iinet.net.au/~allenbrowne/ser-37.html
> >> >>
> >> >> --
> >> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> >>
> >> >> "Jim Bunton" <(E-Mail Removed)> wrote in message
> >> >> news:rawHd.178533$(E-Mail Removed)...
> >> >> > I have a serious problem with BeginTrans - Rollback
> >> >> > Rollback isn't working
> >> >> >
> >> >> > Below is the code for a command button on a test form on a test
> >> >> > table
> >> >> > in a big complicated FromtEnd access 97 application
> >> >> > after the code is run the table has a new record in it!
> >> >> >
> >> >> > The form + code work as expexted if dragged into a new database
> >> >> >
> >> >> > Have tried:- Repair,Compact - no go!
> >> >> >
> >> >> >
> >> >> > ANY IDEAS PLEASE?
> >> >> >
> >> >> > you might try an Access VBA newgroup
> >> >> >
> >> >> > ########## CODE ########
> >> >> > Private Sub Command0_Click()
> >> >> > On Error GoTo Err_Command0_Click
> >> >> > Dim cdb As Database, testset As Recordset
> >> >> >
> >> >> > BeginTrans
> >> >> > Set cdb = CurrentDb
> >> >> > Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
> >> >> > testset.AddNew
> >> >> > testset!egno = 1
> >> >> > testset.Update
> >> >> > Rollback
> >> >> > Exit_Command0_Click:
> >> >> > Exit Sub
> >> >> >
> >> >> > Err_Command0_Click:
> >> >> > MsgBox Err.Description
> >> >> > Resume Exit_Command0_Click
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> > ###### END CODE #########
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.840 / Virus Database: 571 - Release Date: 19/01/2005