purchase trnsfer form.frm

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

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 "Arrival_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 Form30.callingform = Me
Form30.Show vbModal
End Sub

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

MsgBox "Please enter the Purchase Key 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 Arr_Id from Arrival_Table where Arr_Id='" & Val(depno.Text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox " This Product Transfer Record is already present"

Exit Sub
Else


'///////////////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select Total_Mounds from Purchase_Receive_Order_Detail_Table where P_Receive_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 Arrival_Table where P_Receive_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 "Order was given only(Mn)" & a
MsgBox "Received(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 "Arrival_Table", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rs.AddNew
rs.Fields("Arr_Id") = depno.Text
rs.Fields("P_Receive_Order") = invoiceno.Text
rs.Fields("Arr_Date") = depdate.Text
rs.Fields("Arr_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") = vehno.Text
rs.Update
'**************************************
'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") = tmn.Text
rs3.Fields("Qty_Out") = 0
rs3.Update

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

'///////////////////////////////////////////
'stock maintenance
'If rs3.State = adStateOpen Then rs3.Close
'rs3.Open "Stock1", cnn, adOpenStatic, adLockReadOnly
'If rs3.RecordCount = 0 Then
'If rs3.State = adStateOpen Then rs3.Close

'rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
'rs3.addnew
'rs3.Fields("Prod_Id") = prodid.Text
'rs3.Fields("Tras_Date") = depdate.Text
'rs3.Fields("Qty_In") = tmn.Text
'rs3.Fields("Stock") = tmn.Text

'rs3.Fields("Qty_Out") = 0
'rs3.update
'Else
'If rs3.State = adStateOpen Then rs3.Close
'rs3.Open "select * from Stock1 where Prod_Id=" & Val(prodid.Text), cnn, adOpenStatic, adLockReadOnly
'If rs3.RecordCount = 0 Then
'If rs3.State = adStateOpen Then rs3.Close

'rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
'rs3.addnew
'rs3.Fields("Prod_Id") = prodid.Text
'rs3.Fields("Tras_Date") = depdate.Text
'rs3.Fields("Qty_In") = tmn.Text
'rs3.Fields("Stock") = tmn.Text
'rs3.Fields("Qty_Out") = 0'

'rs3.update
'Else

'If rs3.State = adStateOpen Then rs3.Close

'rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
'rs3.addnew
'rs3.Fields("Prod_Id") = prodid.Text
'rs3.Fields("Tras_Date") = depdate.Text
'rs3.Fields("Qty_In") = tmn.Text
'rs3.Fields("Qty_Out") = 0

'If rs2.State = adStateOpen Then rs2.Close
'rs2.Open "select sum(Qty_In) from  Stock1 where Prod_Id=" & Val(prodid.Text), cnn, adOpenDynamic, adLockOptimistic
'rs3.Fields("Stock") = rs2.Fields(0) + Val(tmn.Text)
'rs3.update
'End If
'End If

'//////////////////////
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 Sub

Private Sub Command1_Click()
Set Form1.callingform = Me
Form1.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 Arrival_Table where Arr_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 "Arrival_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_In='" & tmn.Text & "'", cnn, adOpenDynamic, adLockOptimistic

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

rs.Open "Arrival_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 "Arrival_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 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 Arrival_Table where Arr_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("Arr_Id") = depno.Text
rs.Fields("P_Receive_Order") = invoiceno.Text
rs.Fields("Arr_Date") = depdate.Text
rs.Fields("Arr_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_In='" & qty.Text & "'", cnn, adOpenDynamic, adLockOptimistic
rs1.Fields("Prod_Id") = prodid.Text
rs1.Fields("Tras_Date") = depdate.Text
rs1.Fields("Qty_In") = tmn.Text
rs1.Fields("Qty_Out") = 0
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