thiya.NET :: Form issue

Code for the Issue Form.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim check_s_no As Boolean
Dim issue_date As Date
Dim return_date As Date

Private Sub cmdIssueBook_Click()
  rs.Open "select * from student_master where s_no='" & s_no & "'", cn, adOpenDynamic, adLockOptimistic
  If rs!s_books_count >= rs!s_books_max Then
    Frame1.Enabled = False
    MsgBox "You have already taken your allocation!"
    cmdIssueBook.Enabled = False
    rs.Close
  Else
    rs.Close
    rs.Open "select * from student_account", cn, adOpenDynamic, adLockOptimistic
    rs.AddNew
    rs!s_no = s_no
    rs!b_no = Text1
    issue_date = Date
    rs!issue_date = issue_date
    return_date = DateAdd("d", 14, Date)
    rs!return_date = return_date
    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
    rs.Open "select * from book_master where b_no='" & Text1.Text & "'", cn, adOpenDynamic, adLockOptimistic
    rs!b_status = "Circulation"
    rs.Update
    rs.Close
    List1.RemoveItem List1.ListIndex
    cmdIssueBook.Enabled = False
    cmdMoreDetails.Enabled = False
    MsgBox "Book No : " & Text1.Text & " issued!" & vbCr _
        & "Return Date is : " & return_date
    Text1.Text = ""
  End If
End Sub

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
  cmdIssueBook.Enabled = False
End Sub
Private Sub Form_Load()
  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
  If rs!s_books_count >= rs!s_books_max Then
    Frame1.Enabled = False
    MsgBox "You have already taken your allocation!"
  End If
  rs.Close
  text_date.Text = Date
  Text1.MaxLength = 5
  Text1.Enabled = False
  cmdMoreDetails.Enabled = False
  cmdIssueBook.Enabled = False
  rs.Open "select * from book_master where b_status like 'Stack'", cn
  Do Until rs.EOF
    List1.AddItem rs!b_no & " - " & rs!b_name & " *** " & rs!b_author
    rs.MoveNext
  Loop
  rs.Close
End Sub

Private Sub Form_LostFocus()
  cn.Close
End Sub

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

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