form10.frm

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

End If
 'Grid1.ColWidth(0) = 1000
  Grid1.ColWidth(1) = 1200
 Grid1.ColWidth(2) = 1300
 Grid1.ColWidth(3) = 1200
 Grid1.ColWidth(4) = 1200
 Grid1.ColWidth(5) = 1200
 Grid1.ColWidth(7) = 1200
 'code.Enabled = False
 'nm.Enabled = False
 'Grid2.Visible = False

End Sub

Private Sub cmdsearch_Click()

End Sub

Private Sub Command8_Click(Index As Integer)
Set Form26.callingform = Me
Form26.Show vbModal
End Sub

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

rs.Open "Purchase_Receive_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
pno(2).Text = 1
Else
rs.MoveLast
pno(2).Text = rs.Fields(0) + 1
End If
Exit Sub
End If


If MsgBox("Are you sure to delete this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'//////////////////////////
'****************
If rs7.State = adStateOpen Then rs7.Close
rs7.Open "select * from Arrival_Table where P_Receive_Order= " & pno(2).Text, cnn, adOpenStatic, adLockReadOnly
If rs7.RecordCount > 0 Then
If MsgBox("This Product has arrived. Are you want to delete this record?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'*****************
'Stock delete

If rs8.State = adStateOpen Then rs8.Close
rs8.Open "select * from Purchase_Receive_Order_Detail_Table where P_Receive_Order='" & pno(2).Text & "'", cnn, adOpenStatic, adLockReadOnly
'******************
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Arrival_Table where P_Receive_Order= " & pno(2).Text, cnn, adOpenStatic, adLockReadOnly
If rs9.RecordCount > 0 Then
rs9.MoveFirst
While Not rs9.EOF
'*********************
If rs10.State = adStateOpen Then rs10.Close
rs10.Open "delete from stock1 where Prod_Id='" & rs8.Fields("Product_Code") & "' and Tras_Date='" & rs9.Fields("Arr_Date") & "' and Qty_In='" & rs9.Fields("Total_Mn") & "'", cnn, adOpenDynamic, adLockOptimistic
rs9.MoveNext
Wend
End If

'//////////////////////
If rs7.State = adStateOpen Then rs7.Close
rs7.Open "select * from Arrival_Table where P_Receive_Order= " & pno(2).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 Arrival_Table where P_Receive_Order= '" & rs7.Fields("P_Receive_Order") & "'", cnn, adOpenDynamic, adLockOptimistic, adCmdText
rs7.MoveNext
Wend
End If

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

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

If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Purchase_Receive_Order_Detail_Table where P_Receive_Order='" & pno(2).Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount <> 0 Then
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "delete from Purchase_Receive_Order_Detail_Table where P_Receive_Order='" & pno(2).Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'/////////////////
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "select * from Purchase_Receive_Order_Table where P_Receive_Order='" & pno(2).Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount <> 0 Then
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "delete from Purchase_Receive_Order_Table where P_Receive_Order='" & pno(2).Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
End If
'*****************
clear_Click
If rs.State = adStateOpen Then rs.Close
rs.Open "Purchase_Receive_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
pno(2).Text = 1
Else
rs.MoveLast
pno(2).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 "Purchase_Receive_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
pno(2).Text = 1
Else
rs.MoveLast
pno(2).Text = rs.Fields(0) + 1
End If
'sdate(1).Text = date
'Text7(1).Text = Date

'Grid1.Visible = False
'ab.Visible = False

End Sub



Private Sub lb_KeyPress(Index As Integer, 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(Index As Integer, KeyCode As Integer, Shift As Integer)
lbexp(1).Text = Val(noofbags(1).Text) * Val(lb(2).Text)
a = Val(amount(1).Text) + Val(lbexp(1).Text) + Val(pexp(1).Text) + Val(oexp(2).Text) + Val(ttax(2).Text)
namount(1).Text = Str(Round(a, 0))
'Text8(2).Text = Val(namount(1).Text)
End Sub

Private Sub mrate_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
amount(1).Text = Val(totalm(1).Text) * Val(mrate(1).Text)
namount(1).Text = Val(amount(1).Text) + Val(lbexp(1).Text) + Val(pexp(1).Text) + Val(oexp(2).Text)
'Text8(2).Text = Val(namount(1).Text)

End Sub

Private Sub noofbags_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 noofbags_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
totalkg(1).Text = Val(noofbags(1).Text) * Val(bagw(1).Text)
'totalm(1).Text = (totalkg(1).Text) / 37.324
lbexp(1).Text = Val(noofbags(1).Text) * Val(lb(2).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(1).Text = Val(amount(1).Text) + Val(lbexp(1).Text) + Val(pexp(1).Text) + Val(oexp(2).Text) + Val(ttax(2).Text)
'Text8(2).Text = Val(namount(1).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(1).Text = Val(amount(1).Text) + Val(lbexp(1).Text) + Val(pexp(1).Text) + Val(oexp(2).Text) + Val(ttax(2).Text)
'Text8(2).Text = Val(namount(1).Text)
End Sub








Private Sub stock_Click()
If Grid1.Visible = False Then

'ab.Visible = True
'Grid1.clear
'Grid1.Rows = 5
'stock.Visible = False
'sale.Visible = False
'crude1.Visible = False
'purchase.Visible = True
Grid1.Visible = True
Grid1.ColWidth(0) = 1200
Grid1.ColWidth(1) = 1600
Grid1.ColWidth(2) = 1700
Grid1.ColWidth(3) = 1700
Grid1.ColWidth(4) = 1700
Grid1.TextMatrix(0, 0) = "Product"
Grid1.TextMatrix(0, 1) = "Total Purchase(Mn)"
Grid1.TextMatrix(0, 2) = "Used In Production(Mn)"
Grid1.TextMatrix(0, 3) = "Remaining Stock(Mn)"
Grid1.TextMatrix(0, 4) = "Remaining Stock(Kg)"

Grid1.TextMatrix(1, 0) = "Cotton Seed"
Grid1.TextMatrix(2, 0) = "Castic Soda"

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=1", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
ad
Exit Sub
Else
If rs.State = adStateOpen Then rs.Close
rs.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=1", cnn, adOpenStatic, adLockReadOnly


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

ad
End If
End If
Else
'ab.Visible = False
Grid1.Visible = False
End If

End Sub

Private Sub Text1_KeyPress(Index As Integer, KeyAscii As Integer)
'///check amount
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub Text5_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 Text6_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 Text6_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
Text4(1).Text = Val(Text8(2).Text) - Val(Text6(1).Text)
End Sub
'Private Sub ad()
'/////////////////////
'If rs.State = adStateOpen Then rs.Close
'rs.Open "select * from Stock1 where Prod_Id=2", 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=2", cnn, adOpenDynamic, adLockOptimistic
'qin = rs.Fields(0)
'qout = rs.Fields(1)
'rstock = rs.Fields(0) - rs.Fields(1)
'tkg = Val(rstock * 37.344)
'Grid1.TextMatrix(2, 1) = qin
'Grid1.TextMatrix(2, 2) = qout
'Grid1.TextMatrix(2, 3) = rstock
'Grid1.TextMatrix(2, 4) = tkg
'End Sub

Private Sub view_Click()

End Sub

Private Sub tax_KeyPress(Index As Integer, 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(Index As Integer, KeyCode As Integer, Shift As Integer)
a = Val(Val(amount(1).Text) * Val(tax(0).Text)) / 100
ttax(2).Text = Str(Round(a, 0))
namount(1).Text = Val(amount(1).Text) + Val(lbexp(1).Text) + Val(pexp(1).Text) + Val(oexp(2).Text) + Val(ttax(2).Text)
End Sub

Private Sub update_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Purchase_Receive_Order_Table where P_Receive_Order='" & Val(pno(2).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 Purchase_Receive_Order_Detail_Table where P_Receive_Order='" & rs.Fields("P_Receive_Order") & "'", cnn, adOpenDynamic, adLockOptimistic
'////////////////////////////////
If MsgBox("Are you sure to Update this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'///////////////////////////////////////
rs.Fields("P_Receive_Order") = pno(2).Text
rs.Fields("P_Receive_Date") = sdate.Value
rs.Fields("P_Given_Order") = csid(2).Text
rs.Fields("Invoice_No") = invoiceno.Text
rs.Fields("Sup_Code") = scode(1).Text
rs.Fields("Net_Amount") = namount(1).Text
'/////////////////////////////////////
rs1.Fields("P_Receive_Order") = pno(2).Text
rs1.Fields("Product_Code") = oexp(0).Text
rs1.Fields("Product_Name") = Trim(item(2).Text)
rs1.Fields("Total_Bags") = noofbags(1).Text
rs1.Fields("W/Bag") = bagw(1).Text
rs1.Fields("Total_Mounds") = totalm(1).Text
rs1.Fields("Rate/Mound") = mrate(1).Text
rs1.Fields("Labour_Exp") = Val(lbexp(1).Text)
rs1.Fields("Packing_Exp") = Val(pexp(1).Text)
rs1.Fields("Other_Exp") = Val(oexp(2).Text)
rs1.Fields("Sales_Tax") = Val(ttax(2).Text)

rs1.Update
rs.Update
clear_Click
End If
clear_Click
End Sub