product departure form.frm

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

      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            =   1920
      TabIndex        =   11
      Top             =   360
      Width           =   4935
   End
End
Attribute VB_Name = "Form5"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public callingform As Object
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_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 46) Then
KeyAscii = 0
End If
End Sub

Private Sub bagw_KeyUp(KeyCode As Integer, Shift As Integer)
kgs.Text = Val(tbags.Text) * Val(bagw.Text)
a = Val(kgs.Text) / 37.324
tmn.Text = Str(Round(a, 3))
End Sub

Private Sub bilty_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub clear_Click()
 
 invoiceno.Text = ""
 invoicedate.Text = ""
 custcode.Text = ""
 custname.Text = ""
 prodname.Text = ""
 tbags.Text = ""
 bagw.Text = ""
 tmn.Text = ""
 bilty.Text = ""
 company.Text = ""
 drname.Text = ""
 kgs.Text = ""
 vehname.Text = ""
 vehno.Text = ""
 prodid.Text = ""
 qty.Text = ""
 '//////////////
 depdate.Text = Date
 deptime.Text = Time
 Command1.SetFocus
 If rs4.State = adStateOpen Then rs4.Close
rs4.Open "Departure_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs4.RecordCount = 0 Then
depno.Text = 1
Else
rs4.MoveLast
depno.Text = rs4.Fields(0) + 1
End If
End Sub

Private Sub cmdclose_Click()
Unload Me
End Sub

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

Private Sub cmdsave_Click()
If invoiceno.Text = "" Then

MsgBox "Please enter the Sale Invoice No"
invoiceno.SetFocus
Exit Sub
End If
If tbags.Text = "" Then
MsgBox "Please enter the No Of Bags"
tbags.SetFocus
Exit Sub
End If
If bagw.Text = "" Then
MsgBox "Please enter the Weight Per Bag"
bagw.SetFocus
Exit Sub
End If


If tmn.Text = "" Then
MsgBox "Please enter the Total Mounds"
tmn.SetFocus
Exit Sub
End If
If vehname.Text = "" Then
MsgBox "Please enter the Vehical Name"
vehname.SetFocus
Exit Sub
End If
If drname.Text = "" Then
MsgBox "Please enter the Driver Name"
drname.SetFocus
Exit Sub
End If
'///////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open " select Dep_Id from Departure_Table where Dep_Id='" & Val(depno.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox " This Departure record is already present"

Exit Sub
Else

'///////////////////////////////////////
'///////////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select Total_Mounds from Sale_Supply_Order_Detail_Table where S_Supply_Order='" & Val(invoiceno.Text) & "'", cnn, adOpenStatic, adLockReadOnly
c = 0
a = rs.Fields("Total_Mounds")
b = Val(tmn.Text)
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select Total_Mn from Departure_Table where S_Supply_Order='" & Val(invoiceno.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount > 0 Then
rs1.MoveFirst
While Not rs1.EOF
c = c + rs1.Fields("Total_Mn")
rs1.MoveNext
Wend
End If

If c + b > a Then
MsgBox "Saled only(Mn)" & a
MsgBox "Departured(Mn)" & c
Exit Sub

Else
'///////////////////////////////////////
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 "Departure_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rs.AddNew
rs.Fields("Dep_Id") = depno.Text
rs.Fields("S_Supply_Order") = invoiceno.Text
rs.Fields("Dep_Date") = depdate.Text
rs.Fields("Dep_Time") = deptime.Text
rs.Fields("TBags") = tbags.Text
rs.Fields("W/Bag") = bagw.Text
rs.Fields("Total_Mn") = tmn.Text
rs.Fields("Bilty_No") = Val(bilty.Text)
rs.Fields("Comp_Name") = Trim(company.Text)
rs.Fields("Driver_Name") = Trim(drname.Text)
rs.Fields("Veh_Name") = Trim(vehname.Text)
rs.Fields("Veh_No") = Val(vehno.Text)
'**************************************
'stock maintenance
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rs3.AddNew
rs3.Fields("Prod_Id") = prodid.Text
rs3.Fields("Tras_Date") = depdate.Text
rs3.Fields("Qty_In") = 0
rs3.Fields("Qty_Out") = tmn.Text
rs3.Update

'**************************************

clear_Click
depdate.Text = Date
deptime.Text = Time
rs.MoveLast
depno.Text = rs.Fields(0) + 1
rs.Update
rs.Close
depno.SetFocus
End If
End If
End If
'End If
End Sub

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

Private Sub company_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 13 Or KeyAscii = 44 Or KeyAscii = 95 Or KeyAscii >= 65 And KeyAscii <= 90 Or KeyAscii >= 97 And KeyAscii <= 122 Or KeyAscii = 32 Or KeyAscii = 46) Then
  KeyAscii = 0
 ' MsgBox "Please Enter integer Value"
End If
If KeyAscii = 13 Then
  supaddr.SetFocus
End If
End Sub

Private Sub delete_Click()

If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Departure_Table where Dep_Id='" & Val(depno.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 "Departure_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
depno.Text = 1
Else
rs.MoveLast
depno.Text = rs.Fields(0) + 1
End If
Exit Sub
End If
'***************
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Stock1 where Prod_Id='" & (prodid.Text) & "' and Tras_Date='" & depdate.Text & "' and Qty_Out='" & tmn.Text & "'", cnn, adOpenDynamic, adLockOptimistic

'****************
If MsgBox("Are you sure to delete this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
rs1.delete
rs.delete
clear_Click
depdate.Text = ""
depdate.Text = Date
deptime.Text = ""
deptime.Text = Time
If rs.State = adStateOpen Then rs.Close

rs.Open "Departure_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
depno.Text = 1
Else
rs.MoveLast
depno.Text = rs.Fields(0) + 1
End If
End If
clear_Click

End Sub

Private Sub drname_KeyPress(KeyAscii As Integer)
If Not (KeyAscii = 8 Or KeyAscii = 13 Or KeyAscii >= 65 And KeyAscii <= 90 Or KeyAscii >= 97 And KeyAscii <= 122 Or KeyAscii = 32 Or KeyAscii = 46) Then
KeyAscii = 0
End If
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 "Departure_Table", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
depno.Text = 1
Else
rs.MoveLast
depno.Text = rs.Fields(0) + 1

End If
depdate.Text = Date
deptime.Text = Time
End Sub

Private Sub tbags_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub Text1_Change()

End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
If Not (KeyAscii = 8 Or KeyAscii = 13 Or KeyAscii >= 65 And KeyAscii <= 90 Or KeyAscii >= 97 And KeyAscii <= 122 Or KeyAscii = 32) Then
KeyAscii = 0
End If

End Sub

Private Sub tbags_KeyUp(KeyCode As Integer, Shift As Integer)
kgs.Text = Val(tbags.Text) * Val(bagw.Text)
a = Val(kgs.Text) / 37.324
tmn.Text = Str(Round(a, 3))
End Sub

Private Sub tmn_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8 Or KeyAscii = 46) Then
KeyAscii = 0
End If
End Sub

Private Sub update_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Departure_Table where Dep_Id='" & Val(depno.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "First Select a Record From Search Button"
cmdopen.SetFocus
Exit Sub
End If
If MsgBox("Are you sure to Update this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
rs.Fields("Dep_Id") = depno.Text
rs.Fields("S_Supply_Order") = invoiceno.Text
rs.Fields("Dep_Date") = depdate.Text
rs.Fields("Dep_Time") = deptime.Text
rs.Fields("TBags") = tbags.Text
rs.Fields("W/Bag") = bagw.Text
rs.Fields("Total_Mn") = tmn.Text
rs.Fields("Bilty_No") = bilty.Text
rs.Fields("Comp_Name") = Trim(company.Text)
rs.Fields("Driver_Name") = Trim(drname.Text)
rs.Fields("Veh_Name") = Trim(vehname.Text)
rs.Fields("Veh_No") = Trim(vehno.Text)
rs.Update
'*********************
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Stock1 where Prod_Id='" & (prodid.Text) & "' and Tras_Date='" & depdate.Text & "' and Qty_Out='" & qty.Text & "'", cnn, adOpenDynamic, adLockOptimistic
rs1.Fields("Prod_Id") = prodid.Text
rs1.Fields("Tras_Date") = depdate.Text
rs1.Fields("Qty_In") = 0
rs1.Fields("Qty_Out") = tmn.Text
rs1.Update

'*********************
clear_Click
End If
clear_Click

End Sub

Private Sub vehname_KeyPress(KeyAscii As Integer)
If Not (KeyAscii = 8 Or KeyAscii = 13 Or KeyAscii >= 65 And KeyAscii <= 90 Or KeyAscii >= 97 And KeyAscii <= 122 Or KeyAscii = 32) Then
KeyAscii = 0
End If

End Sub

Private Sub vehno_KeyPress(KeyAscii As Integer)
If Not (KeyAscii = 8 Or KeyAscii = 13 Or KeyAscii >= 65 And KeyAscii <= 90 Or KeyAscii = 32 Or KeyAscii >= 48 And KeyAscii <= 57) Then
KeyAscii = 0
End If
End Sub