thiya.NET :: Renew / Return / View Form

Code for the Renew / Return / View Form.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim dtdiff As Integer

Private Sub cmdMoreDetails_Click()
  rs.Open "select * from book_master where b_no like '" & Text1.Text & "'", cn
  MsgBox "Book Name      : " & rs!b_name & vbCr & _
      "Book Author    : " & rs!b_author & vbCr & _
      "Book Publisher : " & rs!b_publisher & vbCr & _
      "Book Edition   : " & rs!b_edition & vbCr & _
      "Book Category  : " & rs!b_category & vbCr & _
      "Book ISBN      : " & rs!b_isbn & vbCr & _
      "Book Price     : " & rs!b_price & vbCr & _
      "Current STATUS >> " & rs!b_status
  rs.Close
  cmdMoreDetails.Enabled = False
  cmdRenewBook.Enabled = False
  cmdReturnBook.Enabled = False
End Sub

Private Sub cmdRenewBook_Click()
  rs.Open "select * from student_account where s_no like '" & s_no & "' AND b_no='" & Text1.Text & "'", cn, adOpenDynamic, adLockOptimistic
  dtdiff = DateDiff("d", rs!return_date, Date)
  If dtdiff > 0 Then
    fine = dtdiff * 1
    MsgBox "Please pay Rs. " & fine & " for " & dtdiff & " day(s) overdue."
  End If
  rs!issue_date = Date
  rs!return_date = DateAdd("d", 14, Date)
  rs.Update
  Text1.Text = ""
  cmdRenewBook.Enabled = False
  cmdReturnBook.Enabled = False
  cmdMoreDetails.Enabled = False
  MsgBox "Successfully Renewed!" & vbCr _
      & "New Return Date is " & rs!return_date
  rs.Close
End Sub

Private Sub cmdReturnBook_Click()
  rs.Open "select * from student_account where s_no like '" & s_no & "' AND b_no='" & Text1.Text & "'", cn, adOpenDynamic, adLockOptimistic
  dtdiff = DateDiff("d", rs!return_date, Date)
  If dtdiff > 0 Then
    fine = dtdiff * 1
    MsgBox "Please pay Rs. " & fine & " for " & dtdiff & " day(s) overdue."
  End If
  rs.Delete
  rs.Close
  rs.Open "Select * from book_master where b_no='" & Text1.Text & "'", cn, adOpenDynamic, adLockOptimistic
  rs!b_status = "Stack"
  rs.Update
  rs.Close
  rs.Open "select * from student_master where s_no='" & s_no & "'", cn, adOpenDynamic, adLockOptimistic
  rs!s_books_count = rs!s_books_count - 1
  rs.Update
  s_books_count.Text = rs!s_books_count
  rs.Close
  MsgBox "Returned Book No : " & Text1.Text & " successfully!"
  Text1.Text = ""
  List1.RemoveItem List1.ListIndex
  cmdRenewBook.Enabled = False
  cmdReturnBook.Enabled = False
  cmdMoreDetails.Enabled = False
End Sub

Private Sub Form_Load()
  cmdRenewBook.Enabled = False
  cmdReturnBook.Enabled = False
  cn.Open "dsn=ora;uid=scott;pwd=tiger", cn
  s = InputBox("Enter the Student Number of the form Sxxxx where 'xxxx' are numerals : ", "Enter number correctly", "S")
  rs.Open "select * from student_master where s_no='" & s & "'", cn
  s_no = rs!s_no
  s_name = rs!s_name
  s_dept = rs!s_dept
  s_books_max = rs!s_books_max
  s_books_count = rs!s_books_count
  rs.Close
  text_date.Text = Date
  Text1.MaxLength = 5
  Text1.Enabled = False
  cmdMoreDetails.Enabled = False
  cmdIssueBook.Enabled = False
  rs.Open "select * from student_account where s_no like '" & s_no & "'", cn
  Do Until rs.EOF
    List1.AddItem rs!b_no & " *** ISSUED ON " & rs!issue_date & " *** DUE ON " & rs!return_date
    rs.MoveNext
  Loop
  rs.Close
End Sub

Private Sub Form_LostFocus()
  cn.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)
  cn.Close
End Sub

Private Sub List1_Click()
  Text1.Text = List1.Text
  cmdMoreDetails.Enabled = True
  cmdIssueBook.Enabled = True
  cmdRenewBook.Enabled = True
  cmdReturnBook.Enabled = True
End Sub