Hi,
I've been having problems trying to write vba code that will add
records to tables in my database. I only want the tables to be added
to no errors occur while the update is taking place, otherwise nothing
should be saved until the erorors have been corrected by the user. I
have tried to use the begintrans , commitTrans and rollback functions
but have failed to make them work. It appeared to work at one point,
i.e it went through all my code without any errors, however the record
was not saved to the tables. Even more strangely if i then tried to
enter a record with the same primary keys it wouldn't let me, saying
that i would be creating duplicate keys even though it hadn't appeared
to save the previous record into the database. I fiddled about with
the code and tested it a few times and now however I change the code
about I get the following error: "Could not start transaction; too
many transactions already nested".
Aside from this latest problem of the error above, I am not sure how
to use the begintrans, committrans ad rollback properly ( If i did I
wouldn't be in this mees. lol!) I have followed the examples in the
help but by following what they did with my own code doesn't work.
Should I be writing it in a module then calling it where i want it on
the form or just write it in the form?
This is a sample of my code, would anyone be able to give me some help
please!!


Private Sub Save_Click()
Dim db As DAO.Database
Dim rstIE As DAO.Recordset
Dim rstE As DAO.Recordset
Dim wrkCurrent As Workspace
Dim count As Integer

count = 0

On Error GoTo Error_Exit_Procedure

Set wrkCurrent = DBEngine.Workspaces(0)
Set db = CurrentDb
Set rstIE = db.OpenRecordset("IE")
Set rstS = db.OpenRecordset("S")


wrkCurrent.BeginTrans

rstIE.AddNew
Invest.SetFocus
rstIE!Invest = Invest.Text

Ex.SetFocus
rstIE!Ex = Ex.Text

rstE.AddNew
Ex.SetFocus
rstE!Ex = Ex.Text

Method.SetFocus
If Method_Recorded <> "" Then
rstE!Method = Method.Text
End If

Summary.SetFocus
If Summary <> "" Then
rstE!Summary = Summary.Text
End If


rstE.Update
rstE.Close
rstIE.Update
rstIE.Close

MsgBox ("Your Record has been successfully saved to the database")

If count = 0 Then
wrkCurrent.CommitTrans
MsgBox ("committed")
Else
wrkCurrent.Rollback
MsgBox ("rolledback")
End If

db.Close
wrkCurrent.Close


DoCmd.Close


Error_Exit_Procedure:
count = count + 1
MsgBox ("Error: " & Err.Description & count)
Resume Next


End Sub


I also get another error message with no description after the form
has closed.
Please would anyone be able to help me out!
Caroline

Okay, maybe I'm stupid, but is there a reason why you can't use the
BeforeInsert event of the form to do all the validations and then just
reject the insert if all the tests don't succeed?