purchase order form.frm

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

         Left            =   840
         TabIndex        =   9
         Top             =   795
         Width           =   1935
      End
      Begin VB.Label Label1 
         BackColor       =   &H80000013&
         Caption         =   "Contract No"
         BeginProperty Font 
            Name            =   "Times New Roman"
            Size            =   11.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   840
         TabIndex        =   8
         Top             =   360
         Width           =   1815
      End
   End
   Begin VB.Image Image3 
      Height          =   645
      Left            =   7300
      Picture         =   "Purchase Order Form.frx":0028
      Top             =   6140
      Width           =   555
   End
   Begin VB.Label Label3 
      BackColor       =   &H80000013&
      Height          =   6255
      Left            =   7400
      TabIndex        =   30
      Top             =   0
      Width           =   375
   End
   Begin VB.Image Image2 
      Height          =   855
      Left            =   -1200
      Picture         =   "Purchase Order Form.frx":133A
      Top             =   6720
      Width           =   8775
   End
   Begin VB.Image Image1 
      Height          =   7560
      Left            =   7500
      Picture         =   "Purchase Order Form.frx":19A78
      Top             =   0
      Width           =   1440
   End
   Begin VB.Label Label5 
      Caption         =   "Product Code"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   1320
      TabIndex        =   21
      Top             =   0
      Visible         =   0   'False
      Width           =   1335
   End
   Begin VB.Label Label9 
      BackColor       =   &H80000013&
      Caption         =   "Purchase Product Contract "
      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            =   960
      TabIndex        =   14
      Top             =   240
      Width           =   5655
   End
End
Attribute VB_Name = "Form25"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim cnn As 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
Dim rs5 As New ADODB.Recordset
Dim rs7 As New ADODB.Recordset
Dim rs6 As New ADODB.Recordset
Dim rs8 As New ADODB.Recordset
Dim rs9 As New ADODB.Recordset
Dim rs10 As New ADODB.Recordset
Dim rs11 As New ADODB.Recordset
Dim rs12 As New ADODB.Recordset

Private Sub clear_Click()

 Text2.Text = ""
 code.Text = ""
 nam.Text = ""
 Text3.Text = ""
 Text4.Text = ""
 Text8.Text = ""
 Text5.Text = ""
 dat.Value = Date
 date1.Value = Date
 Command3.SetFocus
 If rs.State = adStateOpen Then rs.Close

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



Private Sub Command1_Click()
Set Form26.callingform = Me
Form26.Show vbModal
End Sub

Private Sub Command2_Click()
If Text2.Text = "" Then
MsgBox "Please Enter the Supplier Code"
Command3.SetFocus
Exit Sub
End If

If nam.Text = "" Then
MsgBox "Please Enter the Product Name"
nam.SetFocus
Exit Sub
End If

If Text3.Text = "" Then
MsgBox "Please Enter the Total Mounds"
Text3.SetFocus
Exit Sub
End If

If Text4.Text = "" Then
MsgBox "Please Enter the Rate Per Mound"
Text4.SetFocus
Exit Sub
End If

If date1.Enabled = False Then
MsgBox "Please Enter the valid date"
date1.SetFocus
Exit Sub
End If
If dat.Value > date1.Value Then
MsgBox "Valid date must be greater than or equal to contract date"
Exit Sub
End If
'////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open " select P_Given_Order from Purchase_Given_Order_Table where P_Given_Order='" & Val(Text1.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox " This contract is already exist"
clear_Click
Exit Sub
End If
'////////////////////////////

If MsgBox("Are you sure to save this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
If rs.State = adStateOpen Then rs.Close

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

rs.AddNew
rs.Fields("P_Given_Order") = Text1.Text
rs.Fields("Order_Date") = dat.Value
rs.Fields("Valid_Date") = date1.Value
rs.Fields("Sup_Code") = Text2.Text

rs.Update
'//////////////////////////////////////////////////////////

If rs1.State = adStateOpen Then rs1.Close
rs1.Open "Purchase_Given_Order_Detail_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable


rs1.AddNew
rs1.Fields("P_Given_Order") = Text1.Text
rs1.Fields("Product_Code") = code.Text
rs1.Fields("Product_Name") = nam.Text

rs1.Fields("Total_Mounds") = Text3.Text
rs1.Fields("Rate/Mn") = Text4.Text


rs1.Update


clear_Click
rs.MoveLast
Text1.Text = rs.Fields(0) + 1
Command3.SetFocus
End If
End Sub

Private Sub Command3_Click()
Set Form2.callingform = Me
Form2.Show vbModal
End Sub

Private Sub Command4_Click()
Unload Me
End Sub

Private Sub delete_Click()

If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Purchase_Given_Order_Table where P_Given_Order='" & Val(Text1.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "First Select a Record From Search Button"
Command1.SetFocus

 If rs.State = adStateOpen Then rs.Close

rs.Open "Purchase_Given_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
Text1.Text = 1
Else
rs.MoveLast
Text1.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 rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Purchase_Receive_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount > 0 Then
'///////////////////
'/////////////////////////
If MsgBox("This Contract Invoice has been issued. Do you want to delete this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'/////////////////
'stock delete
If rs11.State = adStateOpen Then rs11.Close
rs11.Open "select * from Purchase_Receive_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly

If rs8.State = adStateOpen Then rs8.Close
rs8.Open "select * from Purchase_Receive_Order_Detail_Table where P_Receive_Order='" & rs11.Fields("P_Receive_Order") & "'", cnn, adOpenStatic, adLockReadOnly
'******************
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Arrival_Table where P_Receive_Order= '" & rs11.Fields("P_Receive_Order") & "'", 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= " & rs1.Fields("P_Receive_Order"), 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 rs4.State = adStateOpen Then rs4.Close
rs4.Open "select * from Purchase_Receive_Order_Detail_Table where P_Receive_Order='" & rs1.Fields("P_Receive_Order") & "'", cnn, adOpenStatic, adLockReadOnly
If rs4.RecordCount <> 0 Then
If rs4.State = adStateOpen Then rs4.Close
rs4.Open "delete from Purchase_Receive_Order_Detail_Table where P_Receive_Order='" & rs1.Fields("P_Receive_Order") & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'/////////////////
If rs5.State = adStateOpen Then rs5.Close
rs5.Open "select * from Purchase_Receive_Order_Table where P_Receive_Order='" & rs1.Fields("P_Receive_Order") & "'", cnn, adOpenStatic, adLockReadOnly
If rs5.RecordCount <> 0 Then
If rs5.State = adStateOpen Then rs5.Close
rs5.Open "delete from Purchase_Receive_Order_Table where P_Receive_Order='" & rs1.Fields("P_Receive_Order") & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "select * from Purchase_Given_Order_Detail_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs6.RecordCount <> 0 Then
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "delete from Purchase_Given_Order_Detail_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Purchase_Given_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs9.RecordCount <> 0 Then
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "delete from Purchase_Given_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
End If
'////////////////////
Else
'/////////
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "select * from Purchase_Given_Order_Detail_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs6.RecordCount <> 0 Then
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "delete from Purchase_Given_Order_Detail_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Purchase_Given_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs9.RecordCount <> 0 Then
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "delete from Purchase_Given_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
End If

'//////////////////////////////////////////////////////
clear_Click
'dat.Value = ""
dat.Value = Date

If rs.State = adStateOpen Then rs.Close

rs.Open "Purchase_Given_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
Text1.Text = 1
Else
rs.MoveLast
Text1.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_Given_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
Text1.Text = 1
Else
rs.MoveLast
Text1.Text = rs.Fields(0) + 1
End If
'dat.Value = date
'date1.Value = date

End Sub

Private Sub nam_Click()
If Trim(nam.Text) = "Cotton Seed" Then
code.Text = 1
Else
code.Text = 2
End If
End Sub

Private Sub nam_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

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

Private Sub Text3_KeyUp(KeyCode As Integer, Shift As Integer)
a = Val(Text3.Text) * Val(Text4.Text)
Text8.Text = Str(Round(a, 0))
End Sub

Private Sub Text4_KeyPress(KeyAscii As Integer)
If Not (KeyAscii = 8 Or KeyAscii = 13 Or KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 32 Or KeyAscii = 46) Then
  KeyAscii = 0
 ' MsgBox "Please Enter integer Value"
End If
End Sub

Private Sub Text4_KeyUp(KeyCode As Integer, Shift As Integer)
a = Val(Text3.Text) * Val(Text4.Text)
Text8.Text = Str(Round(a, 0))
End Sub

Private Sub Text5_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub update_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Purchase_Given_Order_Table where P_Given_Order='" & Val(Text1.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "First Select a Record From Search Button"
Command1.SetFocus
Exit Sub
End If
'*******************************************
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Purchase_Receive_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs2.RecordCount > 0 Then
If MsgBox("This Contract Invoice has been issued.You can only update the valid date ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "select * from Purchase_Given_Order_Table where P_Given_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
rs3.Fields("Valid_Date") = date1.Value
rs3.Update
End If
'////////////////
Else
'********************************************
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Purchase_Given_Order_Detail_Table where P_Given_Order='" & rs.Fields("P_Given_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_Given_Order") = Text1.Text
rs.Fields("Order_Date") = dat.Value
rs.Fields("Valid_Date") = date1.Value
rs.Fields("Sup_Code") = Text2.Text
rs1.Fields("P_Given_Order") = Text1.Text
rs1.Fields("Product_Code") = code.Text
rs1.Fields("Product_Name") = nam.Text

rs1.Fields("Total_Mounds") = Text3.Text
rs1.Fields("Rate/Mn") = Text4.Text
rs1.Update
rs.Update
clear_Click
End If
End If
clear_Click
End Sub