[RESOLVED] How to code the ROLLBACK in this situation

Hello,

I have a program where I am inserting data in multiple tables. I want to be able to ROLLBACK anything I did if any of the INSERT fails. Please tell me how to do it. I have never did this before thats why I need help. Here is my code:

Code:

---

Private Sub Process_save()
  Dim Flag As Boolean, LV_chg As Boolean, CustNFlag As Boolean
 
  Flag = False
  LV_chg = False
  CustNFlag = False
       
  Set cn = New ADODB.Connection
      cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= " & MyAppPath & "\RBShop.mdb"
  cn.Open
       
  If Val(Lbl(2).Caption) < 0 Then Call Add_customer(Flag)
       
  If Not Flag Then
      Call Chk_if_chg(Data_chg, LV_chg, CustNFlag)
      If Data_chg Or LV_chg Or CustNFlag Then
        If CustNFlag And Mode = "EDIT" Then
            Call Update_customer(Flag)
            If Flag Then Exit Sub
        End If
             
        If Data_chg Then
            Call Chk_screen(Data_required)
            If Not Data_required Then
              If Mode = "ADD" Then
                  Call Add_data(Flag)
                  If Not Flag Then
                    Btn(12).Visible = True
                         
                    Call Update_counter(Flag)
                    If Flag Then Exit Sub
                  Else
                    Exit Sub
                  End If
              Else
                  Call Update_data(Flag)
                  If Flag Then Exit Sub
              End If
            Else
              Exit Sub
            End If
        End If
           
        If LV_chg Then
            Call Save_lv(Flag)
            If Flag Then Exit Sub
        End If
       
        If Not Flag Then
            If Caller = "RBSWOList" Then RBSWOList.ReRead = True
               
            If Val(Lbl(3).Caption) > 0 Then
              If Caller = "RBShop" Then Btn(13).Visible = True
             
              Btn(18).Visible = True
            End If
             
            Call Update_sys_note(Flag, "BOT")
                 
            Fill_h
            MsgBox ("Data saved successfully.")
        End If
      End If
  End If
 
  cn.Close
  Set cn = Nothing
End Sub

Private Sub Update_customer(ByRef ErrFlag As Boolean)
  Dim sSql As String, Strg As String
 
  On Error GoTo DispError
 
  Strg = Chr(34) & Trim$(ECIRTBx.Text) & Chr(34)
 
  sSql = "UPDATE Customer SET "
  sSql = sSql & "Notes = " & Strg & " "
  sSql = sSql & "WHERE Cust_id = " & Val(Lbl(2).Caption)
 
  cn.Execute sSql
     
  Exit Sub
 
DispError:
  Call ShowError(sSql, "RBSWO", "Update_customer", Err.Number, Err.Description, Err.source)
  cn.Close
  Set cn = Nothing
  ErrFlag = True
End Sub

'This is how all the other INSERT routine look

Private Sub Add_data(ByRef ErrFlag As Boolean)
  Dim sSql As String, rs As ADODB.Recordset, Tf(42) As String
  Dim CusNo As Long, WordNo As Integer, Smsg As String, EFlag As Boolean
   
  On Error GoTo DispError
   
  sSql = "SELECT MAX(WO_no) FROM WOrderMain "
  sSql = sSql & "WHERE Cust_no = " & Val(Lbl(2).Caption)
       
  Set rs = cn.Execute(sSql)
 
  If Not IsNull(rs.Fields(0)) Then
      Tf(0) = rs.Fields(0) + 1
  Else
      Tf(0) = 1
  End If
  Lbl(3).Caption = Val(Tf(0))
 
  rs.Close
  Set rs = Nothing
 
  Call Set_tf(Tf())
 
  sSql = "INSERT INTO WOrderMain ("
  sSql = sSql & "WO_no, "
  sSql = sSql & "Cust_no, "
  sSql = sSql & "Created_on, "
  sSql = sSql & "Created_at, "
  sSql = sSql & "Created_by, "
  sSql = sSql & "Status, "
  sSql = sSql & "V_year, "
  sSql = sSql & "V_make, "
  sSql = sSql & "V_model, "
  sSql = sSql & "V_color, "
  sSql = sSql & "V_plate, "
  sSql = sSql & "V_miles, "
  sSql = sSql & "V_vin, "
  sSql = sSql & "Chk_cust_informed, "
  sSql = sSql & "Date_cust_informed, "
  sSql = sSql & "By_cust_informed, "
  sSql = sSql & "Chk_v_delivered, "
  sSql = sSql & "Date_v_delivered, "
  sSql = sSql & "By_v_delivered, "
  sSql = sSql & "Notes, "
  sSql = sSql & "Taxable, "
  sSql = sSql & "TaxRate, "
  sSql = sSql & "Non_taxable, "
  sSql = sSql & "Discount, "
  sSql = sSql & "Received, "
  sSql = sSql & "Balance, "
 
  If Trim$(Combo(0).Text) = "In Shop" Then
      sSql = sSql & "Work_sdate, "
      sSql = sSql & "Work_stime, "
     
      Lbl(1).Caption = Date & "  " & Format(Time, "hh:mm AMPM")
  End If
     
  sSql = sSql & "State_tax, "
  sSql = sSql & "City_tax, "
  sSql = sSql & "Storage_fee, "
  sSql = sSql & "Com_on_parts_ind, "
  sSql = sSql & "Com_on_parts, "
  sSql = sSql & "Com_on_parts_min, "
  sSql = sSql & "Com_on_labor_ind, "
  sSql = sSql & "Com_on_labor, "
  sSql = sSql & "Com_on_labor_min, "
  sSql = sSql & "County_tax, "
  sSql = sSql & "AgComm, "
  sSql = sSql & "Markup_ind, "
  sSql = sSql & "Markup, "
  sSql = sSql & "Com_amt, "
 
  If Trim$(Combo(0).Text) = "Paid Off" Then sSql = sSql & "PaidOff_on, "
 
  sSql = sSql & "ComToWho) "
 
  sSql = sSql & "VALUES ("
  sSql = sSql & Tf(0) & ", "
  sSql = sSql & Tf(1) & ", "
  sSql = sSql & Tf(2) & ", "
  sSql = sSql & Tf(3) & ", "
  sSql = sSql & Tf(4) & ", "
  sSql = sSql & Tf(5) & ", "
  sSql = sSql & Tf(6) & ", "
  sSql = sSql & Tf(7) & ", "
  sSql = sSql & Tf(8) & ", "
  sSql = sSql & Tf(9) & ", "
  sSql = sSql & Tf(10) & ", "
  sSql = sSql & Tf(11) & ", "
  sSql = sSql & Tf(12) & ", "
  sSql = sSql & Tf(13) & ", "
 
  If Chk(0).Value = 1 Then
      sSql = sSql & Tf(14) & ", "
  Else
      sSql = sSql & "Null, "
  End If
 
  sSql = sSql & Tf(15) & ", "
  sSql = sSql & Tf(16) & ", "
 
  If Chk(1).Value = 1 Then
      sSql = sSql & Tf(17) & ", "
  Else
      sSql = sSql & "Null, "
  End If
 
  sSql = sSql & Tf(18) & ", "
  sSql = sSql & Tf(19) & ", "
  sSql = sSql & Tf(20) & ", "
  sSql = sSql & Tf(21) & ", "
  sSql = sSql & Tf(22) & ", "
  sSql = sSql & Tf(23) & ", "
  sSql = sSql & Tf(24) & ", "
  sSql = sSql & Tf(25) & ", "
 
  If Trim$(Combo(0).Text) = "In Shop" Then
      sSql = sSql & Tf(26) & ", "
      sSql = sSql & Tf(27) & ", "
  End If
 
  sSql = sSql & Tf(28) & ", "
  sSql = sSql & Tf(29) & ", "
  sSql = sSql & Tf(30) & ", "
  sSql = sSql & Tf(31) & ", "
  sSql = sSql & Tf(32) & ", "
  sSql = sSql & Tf(33) & ", "
  sSql = sSql & Tf(34) & ", "
  sSql = sSql & Tf(35) & ", "
  sSql = sSql & Tf(36) & ", "
  sSql = sSql & Tf(37) & ", "
  sSql = sSql & Tf(38) & ", "
  sSql = sSql & Tf(39) & ", "
  sSql = sSql & Tf(40) & ", "
  sSql = sSql & Tf(41) & ", "
 
  If Trim$(Combo(0).Text) = "Paid Off" Then sSql = sSql & "#" & Date & "#, "
 
  sSql = sSql & Tf(42) & ")"
 
  cn.Execute sSql
     
  HStatus = Trim$(Combo(0).Text)
  Mode = "EDIT"
 
  CusNo = Val(Lbl(2).Caption)
  WordNo = Val(Lbl(3).Caption)
  Smsg = "WO " & WordNo & " created by " & Agent & " (Status = " & HStatus & ")"
     
  Call SysNote(EFlag, Smsg, CusNo, WordNo, 0, " ")
 
  Exit Sub
 
DispError:
  Call ShowError(sSql, "RBSWO", "Add_data", Err.Number, Err.Description, Err.source)
  cn.Close
  Set cn = Nothing
  ErrFlag = True
End Sub

Private Sub Set_tf(ByRef Rf() As String)
  Rf(1) = Val(Lbl(2).Caption)
  Rf(2) = "#" & Left$(Trim$(Lbl(1).Caption), 10) & "#"
  Rf(3) = "'" & Right$(Trim$(Lbl(1).Caption), 8) & "'"
  Rf(4) = Chr(34) & Trim$(Lbl(0).Caption) & Chr(34)
  Rf(5) = Chr(34) & Trim$(Combo(0).Text) & Chr(34)
  Rf(6) = Val(TBx(0).Text)
  Rf(7) = "'" & Trim$(Left$(Combo(1).Text, 15)) & "'"
  Rf(8) = "'" & Trim$(Left$(Combo(2).Text, 20)) & "'"
  Rf(9) = "'" & Trim$(Left$(Combo(3).Text, 10)) & "'"
  Rf(10) = "'" & Trim$(TBx(1).Text) & "'"
  Rf(11) = Val(TBx(2).Text)
  Rf(12) = "'" & Trim$(TBx(3).Text) & "'"
  Rf(13) = Chk(0).Value
  Rf(14) = "#" & DTPick(0).Value & "#"
  Rf(15) = "'" & Trim$(Lbl(22).Caption) & "'"
  Rf(16) = Chk(1).Value
  Rf(17) = "#" & DTPick(1).Value & "#"
  Rf(18) = "'" & Trim$(Lbl(23).Caption) & "'"
  Rf(19) = Chr(34) & Trim$(RTBx(0).Text) & Chr(34)
  Rf(20) = Val(Lbl(14).Caption)
  Rf(21) = Tax_rate
  Rf(22) = Val(Lbl(16).Caption)
  Rf(23) = Val(Lbl(17).Caption)
  Rf(24) = Val(Lbl(19).Caption)
  Rf(25) = Val(Lbl(20).Caption)
  Rf(26) = "#" & Format(Date, "mm/dd/yyyy") & "#"
  Rf(27) = "'" & Format(Time, "hh:mm AMPM") & "'"
  Rf(28) = Val(WOAgCom(7).Caption)
  Rf(29) = Val(WOAgCom(8).Caption)
  Rf(30) = Val(WOAgCom(10).Caption)
  Rf(31) = Val(WOAgCom(1).Caption)
  Rf(32) = Val(WOAgCom(2).Caption)
  Rf(33) = Val(WOAgCom(3).Caption)
  Rf(34) = Val(WOAgCom(4).Caption)
  Rf(35) = Val(WOAgCom(5).Caption)
  Rf(36) = Val(WOAgCom(6).Caption)
  Rf(37) = Val(WOAgCom(9).Caption)
  Rf(38) = Val(WOAgCom(0).Caption)
  Rf(39) = Val(WOAgCom(11).Caption)
  Rf(40) = Val(WOAgCom(12).Caption)
  Rf(41) = Val(WOAgCom(14).Caption)
  Rf(42) = Trim$(WOAgCom(15).Caption)
End Sub

---