sale order form.frm

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

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 dat.Value > valid.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 S_Receive_Order from Sale_Receive_Order_Table where S_Receive_Order='" & Val(Text1.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox " This contract is already exist"
reset
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 "Sale_Receive_Order_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable

rs.AddNew
rs.Fields("S_Receive_Order") = Text1.Text
rs.Fields("Order_Date") = dat.Value
rs.Fields("Valid_Date") = valid.Value
rs.Fields("Cust_Id") = Text2.Text


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

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


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

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


rs1.Update


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

Private Sub Command3_Click()
Set Form21.callingform = Me
Form21.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 Sale_Receive_Order_Table where S_Receive_Order='" & Val(Text1.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "First Select a Record From Search Button"
search(0).SetFocus
If rs.State = adStateOpen Then rs.Close

rs.Open "Sale_Receive_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 Sale_Supply_Order_Table where S_Receive_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 Sale_Supply_Order_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly

If rs8.State = adStateOpen Then rs8.Close
rs8.Open "select * from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & rs11.Fields("S_Supply_Order") & "'", cnn, adOpenStatic, adLockReadOnly
'******************
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Departure_Table where S_Supply_Order= '" & rs11.Fields("S_Supply_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("Dep_Date") & "' and Qty_Out='" & rs9.Fields("Total_Mn") & "'", cnn, adOpenDynamic, adLockOptimistic
rs9.MoveNext
Wend
End If

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

If rs7.State = adStateOpen Then rs7.Close
rs7.Open "select * from Departure_Table where S_Supply_Order= " & rs1.Fields("S_Supply_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 Departure_Table where S_Supply_Order= '" & rs7.Fields("S_Supply_Order") & "'", cnn, adOpenDynamic, adLockOptimistic, adCmdText
rs7.MoveNext
Wend
End If
'///////////////////////////
If rs4.State = adStateOpen Then rs4.Close
rs4.Open "select * from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & rs1.Fields("S_Supply_Order") & "'", cnn, adOpenStatic, adLockReadOnly
If rs4.RecordCount <> 0 Then
If rs4.State = adStateOpen Then rs4.Close
rs4.Open "delete from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & rs1.Fields("S_Supply_Order") & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'/////////////////
If rs5.State = adStateOpen Then rs5.Close
rs5.Open "select * from Sale_Supply_Order_Table where S_Supply_Order='" & rs1.Fields("S_Supply_Order") & "'", cnn, adOpenStatic, adLockReadOnly
If rs5.RecordCount <> 0 Then
If rs5.State = adStateOpen Then rs5.Close
rs5.Open "delete from Sale_Supply_Order_Table where S_Supply_Order='" & rs1.Fields("S_Supply_Order") & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "select * from Sale_Receive_Order_Detail_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs6.RecordCount <> 0 Then
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "delete from Sale_Receive_Order_Detail_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Sale_Receive_Order_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs9.RecordCount <> 0 Then
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "delete from Sale_Receive_Order_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
End If
'////////////////////
Else
'/////////
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "select * from Sale_Receive_Order_Detail_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs6.RecordCount <> 0 Then
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "delete from Sale_Receive_Order_Detail_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
'//////////////////////
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Sale_Receive_Order_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly
If rs9.RecordCount <> 0 Then
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "delete from Sale_Receive_Order_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
End If
End If

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

reset
'dat.Value = ""
dat.Value = Date

If rs.State = adStateOpen Then rs.Close

rs.Open "Sale_Receive_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
reset
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
If rs.State = adStateOpen Then rs.Close
rs.Open "Sale_Receive_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
'valid.Value = date
End Sub

Private Sub nam_Click()
If nam.Text = "Oil Cake" Then
code.Text = 4
Else
If nam.Text = "Refine Oil" Then
code.Text = 6
Else
If nam.Text = "Del Cotton" Then
code.Text = 3
Else
code.Text = 7
End If
End If
End If

End Sub

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

Private Sub search_Click(Index As Integer)
Set Form27.callingform = Me
Form27.Show vbModal
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)
Text5.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)
Text5.Text = Str(Round(a, 0))
End Sub

Private Sub update_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Sale_Receive_Order_Table where S_Receive_Order='" & Val(Text1.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "First Select a Record From Search Button"
search(0).SetFocus
Exit Sub
End If
'*****************************
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Sale_Supply_Order_Table where S_Receive_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 Sale_Receive_Order_Table where S_Receive_Order='" & Text1.Text & "'", cnn, adOpenDynamic, adLockOptimistic
rs3.Fields("Valid_Date") = valid.Value
rs3.Update
End If
'////////////////
Else

'****************************
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Sale_Receive_Order_Detail_Table where S_Receive_Order='" & rs.Fields("S_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("S_Receive_Order") = Text1.Text
rs.Fields("Order_Date") = dat.Value
rs.Fields("Valid_Date") = valid.Value
rs.Fields("Cust_Id") = Text2.Text

rs1.Fields("S_Receive_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
reset
End If
End If
reset
End Sub