sale invoice form.frm

This project is to design the automated system for the inventory of Al-Ahmad Cotton and Oil Mills si

rs7.Open "select * from Departure_Table where S_Supply_Order= " & pno(0).Text, cnn, adOpenStatic, adLockReadOnly
If rs7.RecordCount > 0 Then
rs7.MoveFirst
While Not rs7.EOF
If rs8.State = adStateOpen Then rs8.Close
 rs8.Open "delete from Departure_Table where S_Supply_Order= '" & rs7.Fields("S_Supply_Order") & "'", cnn, adOpenDynamic, adLockOptimistic, adCmdText
rs7.MoveNext
Wend
End If

'///////////////////////////

If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount <> 0 Then
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "delete from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'/////////////////
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "select * from Sale_Supply_Order_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount <> 0 Then
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "delete from Sale_Supply_Order_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
End If
'//////////////////////
'*********************
Else
'///////////////////////////

If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount <> 0 Then
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "delete from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'/////////////////
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "select * from Sale_Supply_Order_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount <> 0 Then
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "delete from Sale_Supply_Order_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
End If

'*********************
clear_Click
If rs.State = adStateOpen Then rs.Close
rs.Open "Sale_Supply_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
pno(0).Text = 1
Else
rs.MoveLast
pno(0).Text = rs.Fields(0) + 1
End If
End If
clear_Click

End Sub

Private Sub Form_Load()
Set cnn = New ADODB.Connection


cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=hussain"
Set rs = New ADODB.Recordset
rs.Open "Sale_Supply_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
pno(0).Text = 1
Else
rs.MoveLast
pno(0).Text = rs.Fields(0) + 1
End If
'sdate.value = date
'Text7.Text = Date
'Grid1.Visible = False
'cake.Visible = False

End Sub

Private Sub lb_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 46) Then
KeyAscii = 0
End If
End Sub

Private Sub lb_KeyUp(KeyCode As Integer, Shift As Integer)
lbexp(3).Text = Val(noofbags.Text) * Val(lb.Text)
a = Val(amount.Text) + Val(lbexp(3).Text) + Val(pexp(5).Text) + Val(oexp(0).Text)
namount(6).Text = Str(Round(a, 0))
'net(0).Text = Val(namount(6).Text)
End Sub

Private Sub mrate_KeyUp(KeyCode As Integer, Shift As Integer)
amount.Text = Val(totalm.Text) * Val(mrate.Text)
namount(6).Text = Val(amount.Text) + Val(lbexp(3).Text) + Val(pexp(5).Text) + Val(oexp(0).Text)

'net(0).Text = Val(namount(6).Text)
End Sub



Private Sub noofbags_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub noofbags_KeyUp(KeyCode As Integer, Shift As Integer)
totalkg.Text = Val(noofbags.Text) * Val(bagw.Text)
'totalm.Text = (totalkg.Text) / 37.324
lbexp(3).Text = Val(noofbags.Text) * Val(lb.Text)
'amount.Text = Val(totalm.Text) * Val(mrate.Text)
namount(6).Text = Val(amount.Text) + Val(lbexp(3).Text) + Val(pexp(5).Text) + Val(oexp(0).Text)

'net(0).Text = Val(namount(6).Text)
End Sub

Private Sub oexp_KeyPress(Index As Integer, KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub oexp_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
namount(6).Text = Val(amount.Text) + Val(lbexp(3).Text) + Val(pexp(5).Text) + Val(oexp(0).Text)
'net(0).Text = Val(namount(6).Text)
End Sub

Private Sub pexp_KeyPress(Index As Integer, KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub pexp_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
namount(6).Text = Val(amount.Text) + Val(lbexp(3).Text) + Val(pexp(5).Text) + Val(oexp(0).Text)
'net(0).Text = Val(namount(6).Text)
End Sub

Private Sub stock_Click()
If Grid1.Visible = False And cake.Visible = False Then
cake.Visible = True
Grid1.Visible = True
Grid1.clear
Grid1.Rows = 5
Grid1.ColWidth(0) = 1900
Grid1.ColWidth(1) = 1900
Grid1.ColWidth(2) = 1900
Grid1.ColWidth(3) = 1900
Grid1.ColWidth(4) = 1900
Grid1.TextMatrix(0, 0) = "Product"
Grid1.TextMatrix(0, 1) = "Total Production(Mn)"
Grid1.TextMatrix(0, 2) = "Sale"
Grid1.TextMatrix(0, 3) = "Remaining Stock(Mn)"
Grid1.TextMatrix(0, 4) = "Remaining Stock(Kg)"

Grid1.TextMatrix(1, 0) = "Oil Cake"
Grid1.TextMatrix(2, 0) = "Refine Oil"
Grid1.TextMatrix(3, 0) = "Del Cotton"
Grid1.TextMatrix(4, 0) = "Dirt Oil"
If rs.State = adStateOpen Then rs.Close
rs.Open "Stock1", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
Exit Sub
Else
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Stock1 where Prod_Id=4", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
ad1
'Exit Sub
Else
If rs.State = adStateOpen Then rs.Close
rs.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=4", cnn, adOpenDynamic, adLockOptimistic
qin = rs.Fields(0)
qout = rs.Fields(1)
rstock = rs.Fields(0) - rs.Fields(1)
tkg = Val(rstock * 37.324)
Grid1.TextMatrix(1, 1) = qin
Grid1.TextMatrix(1, 2) = qout
Grid1.TextMatrix(1, 3) = rstock
Grid1.TextMatrix(1, 4) = tkg
ad1
ad2
ad3
End If
End If
Else
Grid1.Visible = False
cake.Visible = False
End If
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub Text3_KeyPress(KeyAscii As Integer)
If Not (KeyAscii = 8 Or KeyAscii = 13 Or KeyAscii >= 65 And KeyAscii <= 90 Or KeyAscii >= 97 And KeyAscii <= 122 Or KeyAscii = 32 Or KeyAscii = 46 Or KeyAscii >= 48 And KeyAscii <= 57) Then
  KeyAscii = 0
 ' MsgBox "Please Enter integer Value"
End If
'If KeyAscii = 13 Then
  'supphno.SetFocus
'End If
End Sub

Private Sub Text5_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub Text6_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If

End Sub

Private Sub Text6_KeyUp(KeyCode As Integer, Shift As Integer)
Text4.Text = net(0).Text - Val(Text6.Text)
End Sub

Public Sub ad()
If rs.State = adStateOpen Then rs.Close
rs.Open "select Sale_Id from Sale_Detail_Table where Product_Name= '" & "Cotton" & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select Total_Amount,Pay_amount,Net_Amount,Left_Amount from CustomerAmountDetail where Sale_Id= '" & rs.Fields("Sale_Id") & "'", cnn, adOpenStatic, adLockReadOnly
Sum = Sum + rs1.Fields("Total_Amount").Value
pamount = pamount + rs1.Fields("Pay_Amount")
lamount = lamount + rs1.Fields("Left_Amount")
namt = namt + rs1.Fields("Net_Amount")


rs.MoveNext
Wend
Grid1.TextMatrix(2, 1) = Sum
Grid1.TextMatrix(2, 2) = pamount
Grid1.TextMatrix(2, 3) = lamount
Grid1.TextMatrix(2, 4) = namt

End If

End Sub
Public Sub ahmad()
If rs.State = adStateOpen Then rs.Close
rs.Open "select Sale_Id from Sale_Detail_Table where Product_Name= '" & "Refine Oil" & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select Total_Amount,Pay_amount,Net_Amount,Left_Amount from CustomerAmountDetail where Sale_Id= '" & rs.Fields("Sale_Id") & "'", cnn, adOpenStatic, adLockReadOnly


pamount = pamount + rs1.Fields("Pay_Amount")
lamount = lamount + rs1.Fields("Left_Amount")
namt = namt + rs1.Fields("Net_Amount")
Add = Add + rs1.Fields("Total_Amount")

rs.MoveNext
Wend
Grid1.TextMatrix(3, 1) = Add
Grid1.TextMatrix(3, 2) = pamount
Grid1.TextMatrix(3, 3) = lamount
Grid1.TextMatrix(3, 4) = namt

End If
End Sub
Public Sub ahmad1()
If rs.State = adStateOpen Then rs.Close
rs.Open "select Sale_Id from Sale_Detail_Table where Product_Name= '" & "Dirt Oil" & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select Total_Amount,Pay_amount,Net_Amount,Left_Amount from CustomerAmountDetail where Sale_Id= '" & rs.Fields("Sale_Id") & "'", cnn, adOpenStatic, adLockReadOnly
Sum = Sum + rs1.Fields("Total_Amount").Value
pamount = pamount + rs1.Fields("Pay_Amount")
lamount = lamount + rs1.Fields("Left_Amount")
namt = namt + rs1.Fields("Net_Amount")


rs.MoveNext
Wend
Grid1.TextMatrix(4, 1) = Sum
Grid1.TextMatrix(4, 2) = pamount
Grid1.TextMatrix(4, 3) = lamount
Grid1.TextMatrix(4, 4) = namt
End If
End Sub

Private Sub viewsale_Click()

End Sub

Private Sub view_Click()
conlist.Visible = False
DataGrid1.Visible = False
End Sub
Public Sub ad1()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Stock1 where Prod_Id=6", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
ad2
'Exit Sub
Else
If rs.State = adStateOpen Then rs.Close
rs.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=6", cnn, adOpenDynamic, adLockOptimistic
qin = rs.Fields(0)
qout = rs.Fields(1)
rstock = rs.Fields(0) - rs.Fields(1)
tkg = Val(rstock * 37.324)
Grid1.TextMatrix(2, 1) = qin
Grid1.TextMatrix(2, 2) = qout
Grid1.TextMatrix(2, 3) = rstock
Grid1.TextMatrix(2, 4) = tkg
End If
End Sub
Public Sub ad2()
'////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Stock1 where Prod_Id=3", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
ad3
'Exit Sub
Else
'//////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=3", cnn, adOpenDynamic, adLockOptimistic
qin = rs.Fields(0)
qout = rs.Fields(1)
rstock = rs.Fields(0) - rs.Fields(1)
tkg = Val(rstock * 37.324)
Grid1.TextMatrix(3, 1) = qin
Grid1.TextMatrix(3, 2) = qout
Grid1.TextMatrix(3, 3) = rstock
Grid1.TextMatrix(3, 4) = tkg
End If
End Sub
Public Sub ad3()
'/////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Stock1 where Prod_Id=7", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then

Exit Sub
End If

'/////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=7", cnn, adOpenDynamic, adLockOptimistic
qin = rs.Fields(0)
qout = rs.Fields(1)
rstock = rs.Fields(0) - rs.Fields(1)
tkg = Val(rstock * 37.324)
Grid1.TextMatrix(4, 1) = qin
Grid1.TextMatrix(4, 2) = qout
Grid1.TextMatrix(4, 3) = rstock
Grid1.TextMatrix(4, 4) = tkg
End Sub


Private Sub tax_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 46) Then
KeyAscii = 0
End If
End Sub

Private Sub tax_KeyUp(KeyCode As Integer, Shift As Integer)
a = Val(Val(amount.Text) * Val(tax.Text)) / 100
ttax.Text = Str(Round(a, 0))
'namount(6).Text = Val(amount.Text) + Val(lbexp(3).Text) + Val(pexp(5).Text) + Val(oexp(0).Text) + Val(ttax.Text)
End Sub

Private Sub update_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Sale_Supply_Order_Table where S_Supply_Order='" & Val(pno(0).Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "First Select a Record From Search Button"
cmdopen.SetFocus
Exit Sub
End If

If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & rs.Fields("S_Supply_Order") & "'", cnn, adOpenDynamic, adLockOptimistic
'////////////////////////////////
'If rs2.State = adStateOpen Then rs2.Close
'rs2.Open "select * from Customer_Amount_Detail where S_Supply_Order='" & rs.Fields("S_Supply_Order") & "'", cnn, adOpenDynamic, adLockOptimistic


'////////////////////////////////
If MsgBox("Are you sure to Update this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'///////////////////////////////////////
rs.Fields("S_Supply_Order") = pno(0).Text
rs.Fields("S_Supply_Date") = sdate
rs.Fields("Cust_Id") = csid(0).Text

rs.Fields("S_Receive_Order") = cno.Text
rs.Fields("Grand_Amount") = amount.Text
rs.Fields("Net_Amount") = namount(6).Text
'/////////////////////////////////////
rs1.Fields("S_Supply_Order") = pno(0).Text
rs1.Fields("Product_Code") = pexp(0).Text

rs1.Fields("Product_Name") = item.Text
rs1.Fields("Total_Bags") = noofbags.Text
rs1.Fields("W/Bag") = bagw.Text
rs1.Fields("Total_Mounds") = totalm.Text
rs1.Fields("Rate/Mound") = mrate.Text
rs1.Fields("Labour_Exp") = Val(lbexp(3).Text)
rs1.Fields("Sales_Tax") = Val(ttax.Text)
rs1.Fields("Packing_Exp") = Val(pexp(5).Text)
rs1.Fields("Other_Exp") = Val(oexp(0).Text)

'////////////////////////////////////
'rs2.Fields("Cust_Id") = cusid(1).Text
'rs2.Fields("Ledger_Date") = Text7.Text

'rs2.Fields("S_Supply_Order") = pno(0).Text
'rs2.Fields("PAy_Amount") = Text6.Text
'rs2.Fields("Left_Amount") = Val(Text4.Text)
'rs2.Fields("Check_No") = Val(Text5.Text)
'rs2.Fields("Check_Amount") = Val(Text1.Text)
'rs2.Fields("Bank_Name") = Text3.Text
'///////////////////////////////////
'rs2.update
rs1.Update
rs.Update
clear_Click
End If
clear_Click

End Sub