form7.frm

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

   End
   Begin VB.Image Image2 
      Height          =   1020
      Left            =   -110
      Picture         =   "Form7.frx":1EE2
      Top             =   7200
      Width           =   10245
   End
   Begin VB.Label Label3 
      BackColor       =   &H80000013&
      Height          =   6735
      Left            =   10080
      TabIndex        =   18
      Top             =   0
      Width           =   375
   End
   Begin VB.Image Image1 
      Height          =   8100
      Left            =   10120
      Picture         =   "Form7.frx":24034
      Top             =   0
      Width           =   1800
   End
   Begin VB.Label Label1 
      BackColor       =   &H80000013&
      Caption         =   " Search Sale Product"
      BeginProperty Font 
         Name            =   "Times New Roman"
         Size            =   24
         Charset         =   0
         Weight          =   700
         Underline       =   -1  'True
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   615
      Left            =   2640
      TabIndex        =   4
      Top             =   240
      Width           =   4455
   End
End
Attribute VB_Name = "Form7"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Public callingform As Object
Private Sub Command1_Click()
Unload Me
End Sub

Private Sub customer_Click()
If DataGrid1.Visible = False And Label3.Visible = False Then
DataGrid1.Visible = True
Label3.Visible = True
Else
DataGrid1.Visible = False
Label3.Visible = False
End If
End Sub

Private Sub Form_Load()
Grid1.Rows = 2
Grid1.TextMatrix(0, 0) = "Invoice No"
Grid1.TextMatrix(0, 1) = "Invoice Date"
Grid1.TextMatrix(0, 2) = "Contract No"

Grid1.TextMatrix(0, 3) = "Customer Code"
Grid1.TextMatrix(0, 4) = "Customer Name"
Grid1.TextMatrix(0, 5) = "Product Code"
Grid1.TextMatrix(0, 6) = "Product Name"


Grid1.TextMatrix(0, 7) = "Total Bags"
Grid1.TextMatrix(0, 8) = "Weight/Bags"
Grid1.TextMatrix(0, 9) = "Total Qty(Mn)"
Grid1.TextMatrix(0, 10) = "Total Qty(Kgs)"

Grid1.TextMatrix(0, 11) = "Rate/Mound"
Grid1.TextMatrix(0, 12) = "Labour Exp"
Grid1.TextMatrix(0, 13) = "Packing Exp"
Grid1.TextMatrix(0, 14) = "Other Exp"
Grid1.TextMatrix(0, 15) = "Sales Tax"
Grid1.TextMatrix(0, 16) = "Grand Total"
Grid1.TextMatrix(0, 17) = "Net Amount"
'///////////////////////////////////
'Grid1.TextMatrix(0, 16) = "Pay Amount"
'Grid1.TextMatrix(0, 17) = "Left Amount"
'Grid1.TextMatrix(0, 18) = "Check No"
'Grid1.TextMatrix(0, 19) = "Check Amount"
'Grid1.TextMatrix(0, 20) = "Bank Name"

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

cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=hussain"
'////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Sale_Supply_Order_Table", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
Grid1.TextMatrix(Grid1.Rows - 1, 0) = rs.Fields("S_Supply_Order")
Grid1.TextMatrix(Grid1.Rows - 1, 1) = rs.Fields("S_Supply_Date")
Grid1.TextMatrix(Grid1.Rows - 1, 2) = rs.Fields("S_Receive_Order")
Grid1.TextMatrix(Grid1.Rows - 1, 3) = rs.Fields("Cust_Id")
Grid1.TextMatrix(Grid1.Rows - 1, 16) = rs.Fields("Grand_Amount")
Grid1.TextMatrix(Grid1.Rows - 1, 17) = rs.Fields("Net_Amount")
'////////////////////////////////
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").Value & "'", cnn, adOpenStatic, adLockReadOnly

Grid1.TextMatrix(Grid1.Rows - 1, 5) = rs1.Fields("Product_Code")
Grid1.TextMatrix(Grid1.Rows - 1, 6) = rs1.Fields("Product_Name")
Grid1.TextMatrix(Grid1.Rows - 1, 7) = rs1.Fields("Total_Bags")
Grid1.TextMatrix(Grid1.Rows - 1, 8) = rs1.Fields("W/Bag")
Grid1.TextMatrix(Grid1.Rows - 1, 9) = rs1.Fields("Total_Mounds")
Grid1.TextMatrix(Grid1.Rows - 1, 10) = Val(rs1.Fields("Total_Mounds")) * 37.324
Grid1.TextMatrix(Grid1.Rows - 1, 15) = rs1.Fields("Sales_Tax")

Grid1.TextMatrix(Grid1.Rows - 1, 11) = rs1.Fields("Rate/Mound")
Grid1.TextMatrix(Grid1.Rows - 1, 12) = rs1.Fields("Labour_Exp")
Grid1.TextMatrix(Grid1.Rows - 1, 13) = rs1.Fields("Packing_Exp")
Grid1.TextMatrix(Grid1.Rows - 1, 14) = rs1.Fields("Other_Exp")


'//////////////////////////////////////////
'If rs2.State = adStateOpen Then rs2.Close
'rs2.Open "select * from Customer_Amount_Detail where S_Supply_Order='" & rs.Fields("S_Supply_Order").Value & "'", cnn, adOpenStatic, adLockReadOnly
'Grid1.TextMatrix(Grid1.Rows - 1, 16) = rs2.Fields("Pay_Amount")
'Grid1.TextMatrix(Grid1.Rows - 1, 17) = rs2.Fields("Left_Amount")
'Grid1.TextMatrix(Grid1.Rows - 1, 18) = rs2.Fields("Check_No")
'Grid1.TextMatrix(Grid1.Rows - 1, 19) = rs2.Fields("Check_Amount")
'Grid1.TextMatrix(Grid1.Rows - 1, 20) = rs2.Fields("Bank_Name")

'//////////////////////
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Customer_Table where Cust_Id=" & rs.Fields("Cust_Id"), cnn, adOpenStatic, adLockReadOnly
Grid1.TextMatrix(Grid1.Rows - 1, 4) = rs2.Fields("Cust_Name")
'//////////////////////////

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

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

End If
Grid1.ColWidth(0) = 1000
Grid1.ColWidth(1) = 1200
Grid1.ColWidth(3) = 1300
Grid1.ColWidth(4) = 2300
Grid1.ColWidth(6) = 1200
Grid1.ColWidth(7) = 1200
Grid1.ColWidth(8) = 1200
Grid1.ColWidth(5) = 1300
Grid1.ColWidth(9) = 1300
Grid1.ColWidth(10) = 1200
'Grid1.ColWidth(18) = 1200
Grid1.ColWidth(12) = 1200
'nm.Value = Now
'date2.Value = Now
End Sub





Private Sub Op1_Click()
code.Text = ""
order.Text = ""
'pname.Text = ""
invoice.Text = ""
invoice.Enabled = False
'pname.Enabled = False
code.Enabled = True
nm.Enabled = False
date2.Enabled = False
order.Enabled = False
code.SetFocus
End Sub

Private Sub Option1_Click()
code.Text = ""
order.Text = ""
'pname.Text = ""
invoice.Text = ""
invoice.Enabled = False
'pname.Enabled = False
code.Enabled = False
date2.Enabled = True
nm.Enabled = True
order.Enabled = False
nm.SetFocus
End Sub

Private Sub Option2_Click()
code.Text = ""
order.Text = ""
'pname.Text = ""
invoice.Text = ""
invoice.Enabled = False
date2.Enabled = False
'pname.Enabled = False
code.Enabled = False
nm.Enabled = False
order.Enabled = True
order.SetFocus
End Sub

Private Sub Option3_Click()
code.Text = ""
order.Text = ""
pname.Text = ""
invoice.Text = ""
invoice.Enabled = False
date2.Enabled = False
pname.Enabled = True
code.Enabled = False
nm.Enabled = False
order.Enabled = False
pname.SetFocus
End Sub

Private Sub Option4_Click()
code.Text = ""
order.Text = ""
'pname.Text = ""
invoice.Text = ""
'pname.Enabled = False
code.Enabled = False
nm.Enabled = False
date2.Enabled = False
order.Enabled = False
invoice.Enabled = True
invoice.SetFocus
End Sub

Private Sub search_Click()
Grid1.clear
Grid1.Rows = 2
Grid1.ColWidth(0) = 1000
Grid1.ColWidth(1) = 1200
Grid1.ColWidth(3) = 1300
Grid1.ColWidth(4) = 1200
Grid1.ColWidth(6) = 1200
Grid1.ColWidth(7) = 1200
Grid1.ColWidth(8) = 1200
Grid1.ColWidth(5) = 1300
Grid1.ColWidth(9) = 1300
Grid1.ColWidth(10) = 1200
'Grid1.ColWidth(19) = 1200
Grid1.ColWidth(12) = 1200
Grid1.TextMatrix(0, 0) = "Invoice No"
Grid1.TextMatrix(0, 1) = "Invoice Date"
Grid1.TextMatrix(0, 2) = "Contract No"

Grid1.TextMatrix(0, 3) = "Customer Code"
Grid1.TextMatrix(0, 4) = "Customer Name"
Grid1.TextMatrix(0, 5) = "Product Code"
Grid1.TextMatrix(0, 6) = "Product Name"


Grid1.TextMatrix(0, 7) = "Total Bags"
Grid1.TextMatrix(0, 8) = "Weight/Bags"
Grid1.TextMatrix(0, 9) = "Total Qty(Mn)"
Grid1.TextMatrix(0, 10) = "Total Qty(Kgs)"

Grid1.TextMatrix(0, 11) = "Rate/Mound"
Grid1.TextMatrix(0, 12) = "Labour Exp"
Grid1.TextMatrix(0, 13) = "Packing Exp"
Grid1.TextMatrix(0, 14) = "Other Exp"
Grid1.TextMatrix(0, 15) = "Sales Tax"
Grid1.TextMatrix(0, 16) = "Grand Total"
Grid1.TextMatrix(0, 17) = "Net Amount"
'///////////////////////////////////
'Grid1.TextMatrix(0, 16) = "Pay Amount"
'Grid1.TextMatrix(0, 17) = "Left Amount"
'Grid1.TextMatrix(0, 18) = "Check No"
'Grid1.TextMatrix(0, 19) = "Check Amount"
'Grid1.TextMatrix(0, 20) = "Bank Name"

'//////////////////////////////////
If Op1.Value = False And Option1.Value = False And Option2.Value = False And Option4.Value = False Then
MsgBox "First Select an Option"
Exit Sub
End If
'//////////////
If rs1.State = adStateOpen Then rs1.Close

rs1.Open " Purchase_Receive_Order_Table ", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount = 0 Then
MsgBox " No Record Exists"

Exit Sub
End If
'///////////////////

If Op1.Value = True Then
If code.Text = "" Then
MsgBox "Please Give Customer Code"
code.SetFocus
Else
'*********************************
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Sale_Supply_Order_Table where Cust_Id='" & Val(code.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
MsgBox "Required Record is not Present"
Exit Sub
Else
rs.MoveFirst
While Not rs.EOF
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "Select * from Sale_Supply_Order_Detail_Table where S_Supply_Order=" & rs.Fields("S_Supply_Order"), cnn, adOpenStatic, adLockReadOnly
'If Trim(rs2.Fields("Product_Code")) = 4 Then

Grid1.TextMatrix(Grid1.Rows - 1, 5) = rs2.Fields("Product_Code")
Grid1.TextMatrix(Grid1.Rows - 1, 6) = rs2.Fields("Product_Name")
Grid1.TextMatrix(Grid1.Rows - 1, 7) = rs2.Fields("Total_Bags")
Grid1.TextMatrix(Grid1.Rows - 1, 8) = rs2.Fields("W/Bag")
Grid1.TextMatrix(Grid1.Rows - 1, 9) = rs2.Fields("Total_Mounds")
Grid1.TextMatrix(Grid1.Rows - 1, 10) = Val(rs2.Fields("Total_Mounds")) * 37.324

Grid1.TextMatrix(Grid1.Rows - 1, 11) = rs2.Fields("Rate/Mound")
Grid1.TextMatrix(Grid1.Rows - 1, 12) = rs2.Fields("Labour_Exp")
Grid1.TextMatrix(Grid1.Rows - 1, 13) = rs2.Fields("Packing_Exp")