Home - Tables required - Setting up database driver and references -  Code with comments

Code with comments

I'll just get down to the basics. See the form, you'll get an idea.
Please first setup database driver and references before proceeding!
Some syntaxes
Connection :
cn.Open "dsn=your_dsn_name; uid=username;pwd=password"
Ex: cn.Open "dsn=ora;uid=scott;pwd=tiger"

Recordset :
rs.Open "SQL STATEMENT HERE" , connection_variable
Ex : rs.Open "select * from student", cn

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

The following happens when the form loads.

Private Sub Form_Load()
'Opening connection to database using the dsn created earlier
cn.Open "dsn=ora;uid=scott;pwd=tiger"
'Getting input for number on load
s = InputBox("Enter the Student Number of the form Sxxxx where 'xxxx' are numerals : ", "Enter number correctly", "S")
'Open a recordset using the connection cn, the syntax given below followed by the comma and "cn"
' to show that the recordset opens from the connection "cn".
' The recordset "rs" will have all the values returned by the query below
' The letter "s" below gets the s_no as given above, that is appended to the query using the '&' character
rs.Open "select * from student_master where s_no='" & s & "'", cn
' s_no, s_name and so on are text boxes, assigning the values from "rs" to them
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
' Checking if account full, if full, disable the frame which has all the controls in it.
' See form to understand easier.
If rs!s_books_count >= rs!s_books_max Then
Frame1.Enabled = False
MsgBox "You have already taken your allocation!"
End If
' Closing connections, Close any connection after using immediately to prevent errors later.
rs.Close
' text_date is the name of the text box that displays the date at the bottom
' The keyword Date returns the current date
text_date.Text = Date
' Setting max length of the text box beside "Selected book is" label. The reason later
Text1.MaxLength = 5
Text1.Enabled = False
cmdMoreDetails.Enabled = False
cmdIssueBook.Enabled = False
' Adding items to the listbox from book_master where books are in the stack.
rs.Open "select * from book_master where b_status like 'Stack'", cn
Do Until rs.EOF ' Loop the sequence until the end of file
' Adding item to the listbox, the contents being BookNo, Book Name and Book Author
List1.AddItem rs!b_no & " - " & rs!b_name & " *** " & rs!b_author
rs.MoveNext
Loop
rs.Close
End Sub

The following happens when the listbox is clicked

Private Sub List1_Click()
' Assigning the text of selected item to the text1.text
' List1.text will return for example "B0001 - C Programming - Balagurusamy"
' But Text1.text will contain only B0001 because its max length is 5 specified during form load.
' Now you have the Book number in the textbox and you have a record of what book he clicked on.
' Using that you can manipulate the databases.. See below
Text1.Text = List1.Text
cmdMoreDetails.Enabled = True
cmdIssueBook.Enabled = True
End Sub

The following happens when the Issue button is clicked

Private Sub cmdIssueBook_Click()
' Open the record from student master to check for his books taken, the adOpenDynamic and the next
' is available as drop down when you type, so dont mug! ;-)
rs.Open "select * from student_master where s_no='" & s_no & "'", cn, adOpenDynamic, adLockOptimistic
' Check for books taken and alert! Read, you'll understand.
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
' If he can take books, insert into student account first
rs.Open "select * from student_account", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
' Serial Number for new record got from s_no textbox
rs!s_no = s_no
' Book_no got from text1 which gets filled when you click on the listbox
rs!b_no = Text1
' Date returns current date
issue_date = Date
rs!issue_date = issue_date
' Return date calculated using DateAdd function, the "d" specifies that days are being added, 14 specifies number to add,
' and Date specifies to what you have to add, in the below example, you are adding 14 days to current date and storing
' it as Return Date.
return_date = DateAdd("d", 14, Date)
rs!return_date = return_date
' Updating or storing the Newly added values
rs.Update
' Closing current recordset
rs.Close
' Opening student_master to increment book count
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
' Updating textbox on the form to display new count
s_books_count.Text = rs!s_books_count
' Closing current recordset
rs.Close
' Opening book_master to change the status of issued book to Circulation
rs.Open "select * from book_master where b_no='" & Text1.Text & "'", cn, adOpenDynamic, adLockOptimistic
rs!b_status = "Circulation"
' Updating and Closing recordset
rs.Update
rs.Close
' Removing issued book from listbox
List1.RemoveItem List1.ListIndex
cmdIssueBook.Enabled = False
cmdMoreDetails.Enabled = False
' Displaying a message box, Appending vbCr is similar to the /n - newline character of C. The underscore "_" lets you
' to continue on the next line.
MsgBox "Book No : " & Text1.Text & " issued!" & vbCr _
& "Return Date is : " & return_date
' Clear the "Selected Book is" textbox
Text1.Text = ""
End If
End Sub

This is the msgbox that shows when "More Details" button is clicked.

Private Sub cmdMoreDetails_Click()
' Opening record for book_no using the book_no taken from text1.text
' Please remember that text1.text will get the value of the selected book.
rs.Open "select * from book_master where b_no like '" & Text1.Text & "'", cn
' In message boxes, vbCr takes the output to the next line.. You can append it by using '&' character
' The underscore "_" allows the current statement to be continued in the next line.
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            ' The code for message box ends here only!!!
rs.Close
cmdMoreDetails.Enabled = False
cmdIssueBook.Enabled = False
End Sub

Similarly, please read thru and understand the renewal / view / return form. Start from form_load, then see what happens when you click on the list box, namely list1_click() and then see what happens when you see each and every button.
Enjoy!
Call me if any....

~ Thiyagaraj K.