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
[RESOLVED] How to code the ROLLBACK in this situation
Reply With Quote