seed crushing.frm

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

Exit Sub
End If
If dkg.Text = "" Then
MsgBox "Please enter the Kg of Delinted Cotton Produced"
dkg.SetFocus
Exit Sub
End If
If ckg.Text = "" Then
MsgBox "Please enter the  Kg of Crude Oil Produced"
ckg.SetFocus
Exit Sub
End If

If okg.Text = "" Then
MsgBox "Please enter the Kg Oil Cake Produced"
okg.SetFocus
Exit Sub
End If
'******************
If shkg.Text < 0 Then
MsgBox "Please enter the Correct Values"
Exit Sub
End If

'******************
'////////////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open " select SCrNo from SeedCrtshing where SCrNo='" & Val(crno.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox " This record already exists"
reset
Exit Sub
Else
'///////////////////////////////
If MsgBox("Are you sure to save this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'////////////////////////////////////////////////////

'Stock Maintenance of seed
Set rs3 = New ADODB.Recordset

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount = 0 Then
MsgBox "Seed is not available for crushing"
Exit Sub

Else
Set rs2 = New ADODB.Recordset
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock1 where Prod_Id=" & Val(id.Text), cnn, adOpenStatic, adLockReadOnly
If rs2.RecordCount = 0 Then
MsgBox "Seed is not available For Crushing"
Exit Sub

Else
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock1 where Prod_Id=" & Val(id.Text), cnn, adOpenDynamic, adLockOptimistic
rs2.AddNew
rs2.Fields("Prod_Id") = Val(id.Text)
rs2.Fields("Tras_Date") = Text1.Text
rs2.Fields("Qty_In") = 0
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=" & Val(id.Text), cnn, adOpenDynamic, adLockOptimistic
qty = rs1.Fields(0) - rs1.Fields(1)
If qty < Val(smnused.Text) Then
MsgBox "Required Seed QTY is not available,Remaining Seed is= " & qty
reset
Exit Sub
Else
rs2.Fields("Qty_Out") = Val(smnused.Text)
rs2.update
End If
End If
End If

'//////////////////////////////////////////////
'For Delinted Cotton Stock Maintenance

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = dc.Text
rs3.Fields("Tras_Date") = Text1.Text
rs3.Fields("Qty_In") = dcmnproduced.Text
rs3.Fields("Qty_Out") = 0
rs3.update

'//////////////////////////////////////////////
'Stock Maintanance of Oil cake produced

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = oc.Text
rs3.Fields("Tras_Date") = Text1.Text
rs3.Fields("Qty_In") = oilcakemnprod.Text
rs3.Fields("Qty_Out") = 0
rs3.update
'//////////////////////////////////////////////
'Stock Maintanance of crude oil produced

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = co.Text
rs3.Fields("Tras_Date") = Text1.Text
rs3.Fields("Qty_In") = crudeoilmnprod.Text
rs3.Fields("Qty_Out") = 0
rs3.update
'///////////////////////////////////////////////
'************************************
'Stock Maintenance in Stock2 Table

'For Delinted Cotton Stock Maintenance

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = dc.Text
rs3.Fields("Tras_Date") = Text1.Text
rs3.Fields("Qty_In") = dcmnproduced.Text
rs3.Fields("Qty_Out") = 0
rs3.update

'//////////////////////////////////////////////
'Stock Maintanance of Oil cake produced

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = oc.Text
rs3.Fields("Tras_Date") = Text1.Text
rs3.Fields("Qty_In") = oilcakemnprod.Text
rs3.Fields("Qty_Out") = 0
rs3.update

'///////////////////////////////////////////////
'************************************
If rs.State = adStateOpen Then rs.Close
rs.Open "SeedCrtshing", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rs.AddNew
rs.Fields("SCrNo") = crno.Text
rs.Fields("CrDate") = Text1.Text
rs.Fields("SMnUsed") = smnused.Text
rs.Fields("DCMnProduced") = dcmnproduced.Text
rs.Fields("CrudeOilMnProd") = crudeoilmnprod.Text
rs.Fields("OilCakeMnProd") = oilcakemnprod.Text
rs.Fields("shortageMn") = shortage.Text
reset
rs.MoveLast
crno.Text = rs.Fields(0) + 1
rs.update
skg.SetFocus
End If
End If
End Sub

Private Sub Command4_Click()
Set Form18.callingform = Me
Form18.Show
End Sub

Private Sub Command5_Click()
Unload Me
End Sub

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

rs.Open "SeedCrtshing", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
crno.Text = 1
Else
rs.MoveLast
crno.Text = rs.Fields(0) + 1
End If
Exit Sub
End If
'///////////////////////////
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Stock1 where Prod_Id='" & Val(id.Text) & "' and Tras_Date='" & Text1.Text & "' and Qty_Out='" & Val(smnused.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'///////////////////////////
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock1 where Prod_Id='" & Val(dc.Text) & "' and Tras_Date='" & Text1.Text & "' and Qty_In='" & Val(dcmnproduced.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'/////////////////////////////
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "select * from Stock1 where Prod_Id='" & Val(co.Text) & "' and Tras_Date='" & Text1.Text & "' and Qty_In='" & Val(crudeoilmnprod.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'////////////////////////////////
If rs4.State = adStateOpen Then rs4.Close
rs4.Open "select * from Stock1 where Prod_Id='" & Val(oc.Text) & "' and Tras_Date='" & Text1.Text & "' and Qty_In='" & Val(oilcakemnprod.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'////////////////////////////////
'*************************
'Delete in Stock2 Table
If rs5.State = adStateOpen Then rs5.Close
rs5.Open "select * from Stock2 where Prod_Id='" & Val(dc.Text) & "' and Tras_Date='" & Text1.Text & "' and Qty_In='" & Val(dcmnproduced.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "select * from Stock2 where Prod_Id='" & Val(oc.Text) & "' and Tras_Date='" & Text1.Text & "' and Qty_In='" & Val(oilcakemnprod.Text) & "'", cnn, adOpenDynamic, adLockOptimistic

'***************************
If MsgBox("Are you sure to delete this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
rs6.delete
rs5.delete
rs4.delete
rs3.delete
rs2.delete
rs1.delete
rs.delete
'/////////////////////////////////
reset
Text1.Text = ""
Text1.Text = date

If rs.State = adStateOpen Then rs.Close

rs.Open "SeedCrtshing", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
crno.Text = 1
Else
rs.MoveLast
crno.Text = rs.Fields(0) + 1
End If
End If
reset
End Sub

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

Private Sub dkg_KeyUp(KeyCode As Integer, Shift As Integer)
b = Val(dkg.Text) / 37.324
dcmnproduced.Text = CStr(Round(b, 3))
End Sub

Private Sub Form_Load()
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=hussain"
rs.Open "SeedCrtshing", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
crno.Text = 1
Else
rs.MoveLast
crno.Text = rs.Fields(0) + 1


End If
'Text1.Text = date
'Grid1.Visible = False
'cotton.Visible = False
'test = Format$(Now, "hh:mm:ss AM/PM mm-dd-yy")

End Sub
Public Sub reset()
smnused.Text = ""
dcmnproduced.Text = ""
crudeoilmnprod.Text = ""
oilcakemnprod.Text = ""
shortage.Text = ""

'obag(0).Text = ""
skg.Text = ""
dkg.Text = ""
ckg.Text = ""
okg.Text = ""
shkg.Text = ""
skg.SetFocus
'/////////////////////////
Text1.Text = date
If rs5.State = adStateOpen Then rs5.Close

rs5.Open "SeedCrtshing", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs5.RecordCount = 0 Then
crno.Text = 1
Else
rs5.MoveLast
crno.Text = rs5.Fields(0) + 1
End If
End Sub

Private Sub obag_KeyPress(Index As Integer, KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 13) Then
  KeyAscii = 0
  MsgBox "Please Enter integer Value"
End If
If KeyAscii = 13 Then
skg.SetFocus
End If
End Sub

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

Private Sub okg_KeyUp(KeyCode As Integer, Shift As Integer)
d = Val(okg.Text) / 37.324
oilcakemnprod.Text = CStr(Round(d, 3))

shkg.Text = Val(skg.Text) - Val(dkg.Text) - Val(ckg.Text) - Val(okg.Text)
shortage.Text = Val(smnused.Text) - Val(dcmnproduced.Text) - Val(crudeoilmnprod.Text) - Val(oilcakemnprod.Text)
End Sub

Private Sub sbag_KeyPress(Index As Integer, KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 13) Then
  KeyAscii = 0
  MsgBox "Please Enter integer Value"
End If
If KeyAscii = 13 Then
skg.SetFocus
End If
End Sub



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

Private Sub skg_KeyUp(KeyCode As Integer, Shift As Integer)
a = Val(skg.Text) / 37.324
smnused.Text = CStr(Round(a, 3))
End Sub

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

cotton.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) = 1600
Grid1.ColWidth(1) = 1600
Grid1.ColWidth(2) = 1600
Grid1.ColWidth(3) = 1600
Grid1.ColWidth(4) = 1600
Grid1.TextMatrix(0, 0) = "Product"
Grid1.TextMatrix(0, 1) = "Total Purchase(Mn)"
Grid1.TextMatrix(0, 2) = "Used In Production"
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

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=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


End If
Else
cotton.Visible = False
Grid1.Visible = False
End If
End Sub