cotton seed purchase.frm

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

            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H000000C0&
         Height          =   255
         Index           =   1
         Left            =   -71040
         TabIndex        =   77
         Top             =   1680
         Width           =   1455
      End
      Begin VB.Label Label9 
         BackColor       =   &H00FFFFC0&
         BackStyle       =   0  'Transparent
         Caption         =   "Weight Per Bag"
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H000000C0&
         Height          =   255
         Index           =   1
         Left            =   -71040
         TabIndex        =   76
         Top             =   2040
         Width           =   1455
      End
      Begin VB.Label Label8 
         BackColor       =   &H00FFFFC0&
         BackStyle       =   0  'Transparent
         Caption         =   "Total Mounds"
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H000000C0&
         Height          =   255
         Index           =   1
         Left            =   -71040
         TabIndex        =   75
         Top             =   2400
         Width           =   1455
      End
      Begin VB.Label Label7 
         BackColor       =   &H00FFFFC0&
         BackStyle       =   0  'Transparent
         Caption         =   "Mill Amount"
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H000000C0&
         Height          =   255
         Index           =   1
         Left            =   -71040
         TabIndex        =   74
         Top             =   2760
         Width           =   1455
      End
   End
End
Attribute VB_Name = "form4"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim rs4 As New ADODB.Recordset

Private Sub bagw_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
'////////////////////////////////////////////////
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 clear_Click()


noofbags(1).Text = ""
bagw(1).Text = ""
totalm(1).Text = ""
mrate(1).Text = ""
lbexp(1).Text = ""
pexp(1).Text = ""
oexp(2).Text = ""
'////////////////////////////
cusid(1).Text = ""
Text8(2).Text = ""
Text6(1).Text = ""
Text4(1).Text = ""
Text5(1).Text = ""
Text1(1).Text = ""
Text1(2).Text = ""
'/////////////////////////////////////
csid(2).Text = ""
scode(1).Text = ""
namount(1).Text = ""
totalkg(1).Text = ""
lb(2).Text = ""
amount(1).Text = ""

End Sub

Private Sub cmdclose_Click()
Unload Me
End Sub

Private Sub cmdopen_Click()
Form1.Show vbModal
End Sub

Private Sub cmdsave_Click()
If pno(2).Text = "" Then
MsgBox "Please Enter the Invoice No"
pno(0).SetFocus
Exit Sub
End If
If sdate(1).Text = "" Then
MsgBox "Please enter the Invoice Date"
sdate(1).SetFocus
Exit Sub
End If
If csid(2).Text = "" Then
MsgBox "Please Enter the Purchase Given Order No"
csid(2).SetFocus
Exit Sub
End If

If scode(1).Text = "" Then
MsgBox "Please Enter the Supplier Code"
scode(1).SetFocus
Exit Sub
End If

If noofbags(1).Text = "" Then
MsgBox "Please Enter the No Of Bags"
noofbags(1).SetFocus
Exit Sub
End If

If bagw(1).Text = "" Then
MsgBox "Please Enter the Weight/Bag"
bagw(1).SetFocus
Exit Sub
End If
If mrate(1).Text = "" Then
MsgBox "Please Enter the Rate/Mound"
mrate(1).SetFocus
Exit Sub
End If
If Text6(1).Text = "" Then
MsgBox "Please Enter the Pay amount"
Text6(1).SetFocus
Exit Sub
End If
If Val(Text6(1).Text) > Val(Text8(2).Text) Then
MsgBox "Pay Amount is greater than the Left Amount. Please Correct it"
Text6(1).Text = ""
Text4(1).Text = ""
Exit Sub
End If
'///////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open " select P_Given_Order from Purchase_Receive_Order_Table where P_Given_Order='" & Val(csid(2).Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox " This contract invoice is already issued"
clear_Click
Exit Sub
Else

'/////////////////////////////////////////
If rs.State = adStateOpen Then rs.Close

rs.Open "Purchase_Receive_Order_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable

rs.AddNew
rs.Fields("P_Receive_Order") = pno(2).Text
rs.Fields("P_Receive_Date") = sdate(1).Text
rs.Fields("P_Given_Order") = csid(2).Text

rs.Fields("Sup_Code") = scode(1).Text
'rs.Fields("Grand_Total") = amount.Text
rs.Fields("Net_Amount") = namount(1).Text
'/////////////////////////////////////
'If rs3.State = adStateOpen Then rs3.Close


rs.Update
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "Purchase_Receive_Order_Detail_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable


rs1.AddNew
rs1.Fields("P_Receive_Order") = pno(2).Text
rs1.Fields("Product_Code") = oexp(0).Text
rs1.Fields("Product_Name") = 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.Update





'If rs3.State = adStateOpen Then rs3.Close
'rs3.Open "select * from CustomerAmountDetail where Cust_Id='" & Val(cusid(1).Text) & "' and Ledger_Date='" & (Text7.Text) & "' and Total_Amount='" & Text8.Text & "'", cnn, adOpenStatic, adLockReadOnly
'If rs3.RecordCount <> 0 Then
'Exit Sub
'End If


If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Supplier_Amount_Detail", cnn, adOpenDynamic, adLockOptimistic, adCmdTable



rs3.AddNew
rs3.Fields("Sup_Code") = cusid(1).Text
rs3.Fields("P_Receive_Order") = pno(2).Text
rs3.Fields("Ledger_Date") = Text7(1).Text

rs3.Fields("Net_Amount") = Text8(2).Text

rs3.Fields("PAy_Amount") = Text6(1).Text
rs3.Fields("Left_Amount") = Val(Text4(1).Text)
rs3.Fields("Check_No") = Val(Text5(1).Text)
rs3.Fields("Check_Amount") = Val(Text1(1).Text)
rs3.Fields("Bank_Name") = Text1(2).Text

rs3.Update
'///////////////////////////////////////////
'stock maintenance
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount = 0 Then
If rs3.State = adStateOpen Then rs3.Close

rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = oexp(0).Text
rs3.Fields("Tras_Date") = sdate(1).Text
rs3.Fields("Qty_In") = totalm(1).Text
rs3.Fields("Stock") = totalm(1).Text

rs3.Fields("Qty_Out") = 0
rs3.Update
Else
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "select * from Stock1 where Prod_Id=" & Val(oexp(0).Text), cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount = 0 Then
If rs3.State = adStateOpen Then rs3.Close

rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = oexp(0).Text
rs3.Fields("Tras_Date") = sdate(1).Text
rs3.Fields("Qty_In") = totalm(1).Text
rs3.Fields("Stock") = totalm(1).Text
rs3.Fields("Qty_Out") = 0

rs3.Update
Else

If rs3.State = adStateOpen Then rs3.Close

rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = oexp(0).Text
rs3.Fields("Tras_Date") = sdate(1).Text
rs3.Fields("Qty_In") = totalm(1).Text
rs3.Fields("Qty_Out") = 0

If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select sum(Qty_In) from  Stock1 where Prod_Id=" & Val(oexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
'MsgBox " " & rs2.Fields(0)
rs3.Fields("Stock") = rs2.Fields(0) + Val(totalm(1).Text)
rs3.Update
End If
End If




MsgBox "Record has been saved successfully"
clear_Click
rs.MoveLast
pno(2).Text = rs.Fields(0) + 1

End If

End Sub

Private Sub Command1_Click()
Grid1.Visible = True
Grid1.Rows = 2
Grid1.TextMatrix(0, 0) = "Order No"
Grid1.TextMatrix(0, 1) = "Order Date"
Grid1.TextMatrix(0, 2) = "Supplier Code"
Grid1.TextMatrix(0, 3) = "Product Code"
Grid1.TextMatrix(0, 4) = "Product Name"
Grid1.TextMatrix(0, 5) = "Total Qty(Mn)"
Grid1.TextMatrix(0, 6) = "Rate/Mn"
Grid1.TextMatrix(0, 7) = "Total Amount"

'If cnn.State = adStateOpen Then cnn.Close

'cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=hussain"
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "Purchase_Given_Order_Table", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount > 0 Then
rs1.MoveFirst
While Not rs1.EOF
Grid1.TextMatrix(Grid1.Rows - 1, 0) = rs1.Fields("P_Given_Order")
Grid1.TextMatrix(Grid1.Rows - 1, 1) = rs1.Fields("Order_Date")
Grid1.TextMatrix(Grid1.Rows - 1, 2) = rs1.Fields("Sup_Code")

If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Purchase_Given_Order_Detail_Table where P_Given_Order= '" & rs1.Fields("P_Given_Order") & "'", cnn, adOpenStatic, adLockReadOnly

Grid1.TextMatrix(Grid1.Rows - 1, 3) = rs.Fields("Product_Code")
Grid1.TextMatrix(Grid1.Rows - 1, 4) = rs.Fields("Product_Name")
Grid1.TextMatrix(Grid1.Rows - 1, 5) = rs.Fields("Total_Mounds")
Grid1.TextMatrix(Grid1.Rows - 1, 6) = rs.Fields("Rate/Mn")
Grid1.TextMatrix(Grid1.Rows - 1, 7) = Val(rs.Fields("Rate/Mn")) * Val(rs.Fields("Total_Mounds"))

Grid1.Rows = Grid1.Rows + 1
rs1.MoveNext
Wend

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 Comma