Trying to get the RollBack Transaction to work!!

[legs00]

In my form, where the user could remove the authorization(checkbox) from a record. Before it can be removed, it must reach a specific criteria. The children of that record must not be authorized, and if they are the user recieves a message where they can remove all authorization of all children. Now if the children of that record have children whom are authorized then the whole process is aborted and everything stays the way it was. If the children of the record have no children whom are authorized then the authorization of the initial record can be removed.

The following is the code that I have wrote with the flags and begin/commit/rollback transactions. I am not sure if the transactions are right????? Or maybe my loop may be screwed up? Can anyone give me a hand. It would be very appreciated.


On Error GoTo rourine_error

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection

'retrieves all childs of the parent that u want to modify
Dim rsauth As ADODB.Recordset
Set rsauth = New Recordset

strProjectId = Form_ActivitiesSubform.Controls.Item("projectid&quot
strWBS = Form_ActivitiesSubform.Controls.Item("txtwbs&quot

strAuth = Form_frmModActivities.Controls.Item("chkAuth&quot
strLen = Len(strWBS) + 1

strwbs1 = Form_ActivitiesSubform.Controls.Item("txtwbs&quot & "%"

'query to get all children of the selected activity
strSqlAuth = "select Auth, wbs, wbschild from activities where wbs like '" & strWBS & "'and projectid = '" & strProjectId & "'"
rsauth.Open strSqlAuth, conn, adOpenKeyset, adLockOptimistic

Dim strFlag As Boolean 'if flag =true then you can remove authorization
strFlag = False 'if flag = false cannot remove authorization

Dim rsauth1 As ADODB.Recordset
Set rsauth1 = New Recordset

strSqlAuth1 = "select Auth, wbs, wbschild from activities where wbs like '" & strwbs1 & "'and projectid = '" & strProjectId & "'"
rsauth1.Open strSqlAuth1, conn, adOpenKeyset, adLockOptimistic

conn.BeginTrans

If strAuth = False Then
If rsauth("wbschild&quot = False Then
strAuth = False
Else
Do While rsauth1.EOF = False
'selects all the children that are 1 length longer
If Len(rsauth1("wbs&quot) = strLen Then
If rsauth1("auth&quot = True Then
If MsgBox("This activity CANNOT be unauthorized due to its childrens authorizations! In order to unauthorize this parent all children must be unauthorized." & vbCrLf & "Do you want to unauthorize all child activities?", vbCritical + vbYesNo, "Authorization!&quot = vbYes Then
rsauth1.MoveFirst
Do While Not rsauth1.EOF
Dim rsauth2 As ADODB.Recordset
Set rsauth2 = New Recordset
strAuth1 = rsauth1("auth&quot
strLen1 = Len(rsauth1("wbs&quot) + 1
strWbs2 = rsauth1("wbs&quot & "%"
strSqlAuth2 = "select auth ,wbs, wbschild from activities where wbs like '" & strWbs2 & "' and projectid = '" & strProjectId & "'"
rsauth2.Open strSqlAuth2, conn, adOpenKeyset, adLockOptimistic
Do While Not rsauth2.EOF
If strFlag = False Then
If rsauth2("auth&quot = False Then
strFlag = False
Else
strFlag = True
End If
rsauth2.MoveNext
Else
conn.RollbackTrans
Exit Sub
End If

Loop
Loop
conn.CommitTrans
Else
strAuth = 1
Exit Sub
End If
End If
End If
rsauth1.MoveNext
Loop
End If
End If

rourine_error:
If strFlag = True Then conn.RollbackTrans