sale invoice form.frm

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

      TabIndex        =   20
      Top             =   2310
      Width           =   1695
   End
   Begin VB.Label Label21 
      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
      ForeColor       =   &H000000C0&
      Height          =   255
      Left            =   4440
      TabIndex        =   19
      Top             =   0
      Visible         =   0   'False
      Width           =   1215
   End
End
Attribute VB_Name = "Form12"
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 rs6 As New ADODB.Recordset
Dim rs7 As New ADODB.Recordset
Dim rs8 As New ADODB.Recordset
Dim rs9 As New ADODB.Recordset
Dim rs10 As New ADODB.Recordset
Dim a As Double
Public callingform As Object

Private Sub bagw_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 46) Then
KeyAscii = 0
'sdate.value
End If
End Sub

Private Sub bagw_KeyUp(KeyCode As Integer, Shift As Integer)
totalkg.Text = Val(noofbags.Text) * Val(bagw.Text)
'a = Val((totalkg.Text) / 37.324)
'If Val(a) <> valtotalm Then
'MsgBox "Your Transaction does not match the Contract"
'bagw.Text = ""
'End If
'totalm.Text = Val(a)
'37.324
End Sub

Private Sub bagw_LostFocus()
'totalkg.Text = Val(noofbags.Text) * Val(bagw.Text)
'a = Val((totalkg.Text) / 37.324)
'If Val(a) <> valtotalm Then
'MsgBox "Your Transaction does not match the Contract"
'bagw.Text = ""
'End If
End Sub

Private Sub clear_Click()

cno.Text = ""
csid(0).Text = ""
amount.Text = ""
namount(6).Text = ""
noofbags.Text = ""
bagw.Text = ""
totalm.Text = ""
mrate.Text = ""
lbexp(3).Text = ""
pexp(5).Text = ""
oexp(0).Text = ""
totalkg.Text = ""
lb.Text = ""
nam.Text = ""
tax.Text = ""
ttax.Text = ""
pexp(0).Text = ""
item.Text = ""
Command2(0).SetFocus
'///////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "Sale_Supply_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
pno(0).Text = 1
Else
rs.MoveLast
pno(0).Text = rs.Fields(0) + 1
End If

sdate.Value = Date
'Text7.Text = Date
End Sub

Private Sub cmdclose_Click()
Unload Me
End Sub

Private Sub cmdopen_Click()
Set Form7.callingform = Me
Form7.Show vbModal
End Sub

Private Sub cmdsave_Click()
If cno.Text = "" Then
MsgBox "Please enter the Contract No."
Command2(0).SetFocus
Exit Sub
End If

If csid(0).Text = "" Then
MsgBox "Please Enter the Customer Code"
csid(0).SetFocus
Exit Sub
End If

If noofbags.Text = "" Then
If Label15.Visible = True Then
MsgBox "Please Enter the No of Bags"
noofbags.SetFocus
Else
MsgBox "Please Enter the No of Drums"
noofbags.SetFocus
End If
Exit Sub

End If

If bagw = "" Then
If Label15.Visible = True Then
MsgBox "Please Enter the Weight Per Bag"
bagw.SetFocus
Else
MsgBox "Please Enter the Weight Per Drum"
bagw.SetFocus
End If
Exit Sub

End If

If mrate.Text = "" Then
MsgBox "Please Enter the Rate Per Mound"
mrate.SetFocus
Exit Sub
End If
'////////////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open " select S_Receive_Order from Sale_Supply_Order_Table where S_Receive_Order='" & Val(cno.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox " This contract invoice is already issued"
clear_Click
Exit Sub
Else
'//////////////////////////
If MsgBox("Are you sure to save this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'////////////////////////////////////////////////////
'************************
If rs4.State = adStateOpen Then rs4.Close
rs4.Open " select Valid_Date from Sale_Receive_Order_Table where S_Receive_Order='" & Val(cno.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If sdate.Value > rs4.Fields("Valid_Date").Value Then
If MsgBox("Contract Date has been Expired. Are You Save this Record?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbNo Then
Exit Sub
End If
End If
'*************************

'Stock Maintenance of Oil Cake(4)(out)
If pexp(0).Text = 4 Then
Set rs3 = New ADODB.Recordset
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount = 0 Then
MsgBox "Oil Cake is not available for Sale"
'clear_Click
Exit Sub
Else
Set rs2 = New ADODB.Recordset
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenStatic, adLockReadOnly
If rs2.RecordCount = 0 Then
MsgBox "Oil Cake is not available for Sale"
'clear_Click
Exit Sub
Else
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
rs2.AddNew
rs2.Fields("Prod_Id") = Val(pexp(0).Text)
rs2.Fields("Tras_Date") = sdate.Value
rs2.Fields("Qty_In") = 0
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select sum(Qty_In),sum(Qty_Out) from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
qty = rs1.Fields(0) - rs1.Fields(1)
If qty < Val(totalm.Text) Then
MsgBox "Required QTY is not available,Remaining Oil Cake(Mn)= " & qty
Exit Sub
Else
'rs2.Fields("Stock") = qty - Val(totalm.Text)
rs2.Fields("Qty_Out") = Val(totalm.Text)
rs2.Update
End If
End If
End If
End If
''//////////////////////////////////////////////
'////////////////////////////////////////////////////'
'Stock Maintenance of Delinted cotton(3)(out)
If pexp(0).Text = 3 Then
Set rs3 = New ADODB.Recordset
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount = 0 Then
MsgBox "Delinted Cotton is not available for Sale"
'clear_Click
Exit Sub
Else
Set rs2 = New ADODB.Recordset
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenStatic, adLockReadOnly
If rs2.RecordCount = 0 Then
MsgBox "Delinted Cotton is not available for Sale"
'clear_Click
Exit Sub
Else
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
rs2.AddNew
rs2.Fields("Prod_Id") = Val(pexp(0).Text)
rs2.Fields("Tras_Date") = sdate.Value
rs2.Fields("Qty_In") = 0
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select sum(Qty_In),sum(Qty_Out) from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
qty = rs1.Fields(0) - rs1.Fields(1)
If qty < Val(totalm.Text) Then
MsgBox "Required QTY is not available,Remaining Delinted Cotton(Mn)= " & qty
'clear_Click
Exit Sub
Else
'rs2.Fields("Stock") = qty - Val(totalm.Text)
rs2.Fields("Qty_Out") = Val(totalm.Text)
rs2.Update
End If
End If
End If
End If
''//////////////////////////////////////////////
'////////////////////////////////////////////////////
'Stock Maintenance of Refine Oil(6)(out)
If pexp(0).Text = 6 Then
Set rs3 = New ADODB.Recordset
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount = 0 Then
MsgBox "Refine Oil is not available for Sale"
'clear_Click
Exit Sub
Else
Set rs2 = New ADODB.Recordset
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenStatic, adLockReadOnly
If rs2.RecordCount = 0 Then
MsgBox "Refine Oil is not available for Sale"
'clear_Click
Exit Sub
Else
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
rs2.AddNew
rs2.Fields("Prod_Id") = Val(pexp(0).Text)
rs2.Fields("Tras_Date") = sdate.Value
rs2.Fields("Qty_In") = 0
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select sum(Qty_In),sum(Qty_Out) from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
qty = rs1.Fields(0) - rs1.Fields(1)
If qty < Val(totalm.Text) Then
MsgBox "Required QTY is not available,Remaining Refine Oil(Mn)= " & qty
'clear_Click
Exit Sub
Else
'rs2.Fields("Stock") = qty - Val(totalm.Text)
rs2.Fields("Qty_Out") = Val(totalm.Text)
rs2.Update
End If
End If
End If
End If
''//////////////////////////////////////////////
''////////////////////////////////////////////////////
''Stock Maintenance of Dirt Oil(7)(out)
If pexp(0).Text = 7 Then
Set rs3 = New ADODB.Recordset
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenStatic, adLockReadOnly
If rs3.RecordCount = 0 Then
MsgBox "Dirt Oil is not available for Sale"
'clear_Click
Exit Sub
Else
Set rs2 = New ADODB.Recordset
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenStatic, adLockReadOnly
If rs2.RecordCount = 0 Then
MsgBox "Dirt Oil is not available for Sale"
'clear_Click
Exit Sub
Else
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
rs2.AddNew
rs2.Fields("Prod_Id") = Val(pexp(0).Text)
rs2.Fields("Tras_Date") = sdate.Value
rs2.Fields("Qty_In") = 0
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select sum(Qty_In),sum(Qty_Out) from Stock2 where Prod_Id=" & Val(pexp(0).Text), cnn, adOpenDynamic, adLockOptimistic
qty = rs1.Fields(0) - rs1.Fields(1)
If qty < Val(totalm.Text) Then
MsgBox "Required QTY is not available,Remaining Dirt Oil(Mn)= " & qty
'clear_Click
Exit Sub
Else
'rs2.Fields("Stock") = qty - Val(totalm.Text)
rs2.Fields("Qty_Out") = Val(totalm.Text)
rs2.Update
End If
End If
End If
End If
'//////////////////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "Sale_Supply_Order_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rs.AddNew
rs.Fields("S_Supply_Order") = pno(0).Text
rs.Fields("S_Supply_Date") = sdate
rs.Fields("Cust_Id") = csid(0).Text
rs.Fields("S_Receive_Order") = cno.Text
rs.Fields("Grand_Amount") = amount.Text
rs.Fields("Net_Amount") = namount(6).Text
If rs3.State = adStateOpen Then rs3.Close
rs.Update
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "Sale_Supply_Order_Detail_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rs1.AddNew
rs1.Fields("S_Supply_Order") = pno(0).Text
rs1.Fields("Product_Code") = pexp(0).Text
rs1.Fields("Sales_Tax") = Val(ttax.Text)
rs1.Fields("Product_Name") = Trim(item.Text)
rs1.Fields("Total_Bags") = noofbags.Text
rs1.Fields("W/Bag") = bagw.Text
rs1.Fields("Total_Mounds") = totalm.Text
rs1.Fields("Rate/Mound") = mrate.Text
rs1.Fields("Labour_Exp") = Val(lbexp(3).Text)
rs1.Fields("Packing_Exp") = Val(pexp(5).Text)
rs1.Fields("Other_Exp") = Val(oexp(0).Text)

rs1.Update

clear_Click
rs.MoveLast
pno(0).Text = rs.Fields(0) + 1
'End If
End If
End If
End Sub



Private Sub cmdsearch_Click()

End Sub



Private Sub Command2_Click(Index As Integer)
Set Form27.callingform = Me
Form27.Show vbModal
End Sub

Private Sub csid_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
'cusid(1).Text = csid(0).Text

End Sub

Private Sub DataGrid1_Click()

End Sub

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

rs.Open "Sale_Supply_Order_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
pno(0).Text = 1
Else
rs.MoveLast
pno(0).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 rs7.State = adStateOpen Then rs7.Close
rs7.Open "select * from Departure_Table where S_Supply_Order= " & pno(0).Text, cnn, adOpenStatic, adLockReadOnly
If rs7.RecordCount > 0 Then
If MsgBox("This Product has departured. Are you want to delete this record?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
'************************
'Stock delete

If rs8.State = adStateOpen Then rs8.Close
rs8.Open "select * from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & pno(0).Text & "'", cnn, adOpenStatic, adLockReadOnly
'******************
If rs9.State = adStateOpen Then rs9.Close
rs9.Open "select * from Departure_Table where S_Supply_Order= " & pno(0).Text, 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