Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ans As Integer
Private Sub br_Click()
Form27.Show
End Sub
Private Sub bsm_Click()
Unload Me
Form3.Show
End Sub
Private Sub Command1_Click()
If Text1.Text = "" Or Text2.Text = "" Then
MsgBox ("please enter user id and password")
Text1.Text = ""
Text2.Text = ""
Text1.SetFocus
Else
If Text1 <> "" Or Text2 <> "" Then
SQL = "select count(*) from login where userid ='" & Text1.Text & "' and password ='" & Text2.Text & "'"
Set rs = conn.Execute(SQL)
If rs(0) <> 0 Then
Frame1.Visible = True
Text1.Enabled = False
Text2.Enabled = False
Command1.Enabled = False
Command2.Enabled = True
Text3.SetFocus
Else
SQL = "select count(*) from libralogin where userid ='" & Text1.Text & "' and password ='" & Text2.Text & "'"
Set rs = conn.Execute(SQL)
If rs(0) <> 0 Then
MsgBox "UserName and Password are correct"
Frame1.Visible = True
Text1.Enabled = False
Text2.Enabled = False
Command1.Enabled = False
Command2.Enabled = True
Text3.SetFocus
Else
SQL = "select count(*) from stulogin where userid ='" & Text1.Text & "' and password ='" & Text2.Text & "'"
Set rs = conn.Execute(SQL)
If rs(0) <> 0 Then
MsgBox "UserName and Password are correct"
Frame1.Visible = True
Text1.Enabled = False
Text2.Enabled = False
Command1.Enabled = False
Command2.Enabled = True
Text3.SetFocus
Else
MsgBox "UserName and Password are invalid"
Text1.Text = ""
Text2.Text = ""
Text1.SetFocus
End If
End If
End If
End If
End If
End Sub
Private Sub Command10_Click()
Unload Me
End Sub
Private Sub Command11_Click()
Text3.Text = ""
Text7.Text = ""
Text8.Text = ""
Text4.Text = ""
Text3.Enabled = True
Command7.Enabled = True
Command3.Enabled = True
Text3.SetFocus
End Sub
Private Sub Command12_Click()
Text5.Text = ""
End Sub
Private Sub Command2_Click()
Text1.Text = ""
Text2.Text = ""
Text1.Enabled = True
Text2.Enabled = True
Frame1.Visible = False
Command1.Enabled = True
Text1.SetFocus
End Sub
Private Sub Command3_Click()
If Text4.Text = "borrowed" Then
MsgBox ("select another book. that book is borrowed alredy")
Else
If Text3.Text = "" And Text7.Text = "" And Text8.Text = "" And Text4.Text = "" Then
MsgBox ("check the availability or if any field is empty ")
Text3.SetFocus
Else
ans = MsgBox("do you confirm to borrow the book", vbYesNo, Save)
If ans = vbYes Then
SQL = "insert into borrowandreturn(userid,booktitle,authorname,subjectname,status) values('" & Text1.Text & "','" & (Text3.Text) & "','" & (Text7.Text) & "','" & (Text8.Text) & "','borrowed')"
Set rs = conn.Execute(SQL)
SQL = "update bookinformation set Status= 'borrowed' where BookTitle = '" + Trim(Text3.Text) + "'"
Set rs = conn.Execute(SQL)
MsgBox ("book borrowed successfully")
Text3.Enabled = False
Text7.Enabled = False
Text8.Enabled = False
Command7.Enabled = False
Command3.Enabled = False
Command1.Enabled = False
Command2.Enabled = False
Command4.Enabled = False
Else
End If
End If
End If
End Sub
Private Sub Command4_Click()
If Text4.Text = "available" Then
MsgBox "Cant return Book Which is Available"
Else
If Text4 = "borrowed" Then
SQL = "select count(*) from borrowandreturn where userid ='" & Text1.Text & "' and booktitle ='" & Text3.Text & "'"
Set rs = conn.Execute(SQL)
If rs(0) <> 0 Then
ans = MsgBox("Do you wish to return the book?", vbYesNo)
If ans = vbYes Then
SQL = "delete * from borrowandreturn where userid = '" & Text1.Text & "' and BookTitle = '" + Trim(Text3.Text) + "'"
Set rs = conn.Execute(SQL)
SQL = "update bookinformation set Status= 'available' where BookTitle = '" + Trim(Text3.Text) + "' "
Set rs = conn.Execute(SQL)
MsgBox "Book returned"
End If
Text3.Text = ""
Text7.Text = ""
Text8.Text = ""
Text4.Text = ""
Text7.Enabled = False
Text8.Enabled = False
Command4.Enabled = False
Text3.SetFocus
Else
MsgBox "you are not the one borrowed the book"
End If
End If
End If
End Sub
Private Sub Command5_Click()
If Text1.Text <> "" And Text2.Text <> "" And Text5.Text <> "" And Text9.Text <> "" And Text10.Text <> "" And Text6.Text <> "borrowed" Then
ans = MsgBox("do you confirm to borrow the book", vbYesNo, Save)
If ans = vbYes Then
SQL = "insert into borrowandreturn(userid,booktitle,authername,subjectname,status) values('" & Text1.Text & "','" & LCase(Text2.Text) & "','" & LCase(Text5.Text) & "','" & LCase(Text9.Text) & "','" & LCase(Text10.Text) & "','" & LCase(Text6.Text) & "')"
Set rs = conn.Execute(SQL)
SQL = "update bookinformation set Status= '" & borrowed & "' where BookTitle = '" + Trim(Text3.Text) + "'"
Set rs = conn.Execute(SQL)
MsgBox ("book borrowed successfully")
Else
MsgBox ("check the availability or if any field is empty ")
End If
End If
End Sub
Private Sub Command6_Click()
ans = MsgBox("Do you wish to return the book?", vbYesNo)
If ans = vbYes Then
SQL = "delete * from borrowandreturn where userid = '" & Text1.Text & "%'"
Set rs = conn.Execute(SQL)
'conn.Execute ("delete * from bookinformation where BookCode=" & Text1.Text)
MsgBox "Book returned"
End If
Text1.Text = ""
Text2.Text = ""
Text5.Text = ""
Text9.Text = ""
Text10.Text = ""
Text6.Text = ""
End Sub
Private Sub Command7_Click()
If Text3.Text = "" Then
MsgBox ("please enter book title")
Text3.SetFocus
Else
SQL = " select * from bookinformation where BookTitle like '" + Trim(Text3.Text) + "'"
Set rs = conn.Execute(SQL)
If rs.EOF() Then
MsgBox ("Book Title not found")
Text3.Text = ""
Text3.SetFocus
Else
Text7.Text = rs(3)
Text8.Text = rs(7)
Text4.Text = rs(9)
Command4.Enabled = True
Command3.Enabled = True
End If
End If
End Sub
Private Sub Command8_Click()
If Text5.Text = "" Then
MsgBox ("please enter book title")
Else
SQL = " select * from bookinformation where BookTitle like '" + Trim(Text5.Text) + "'"
Set rs = conn.Execute(SQL)
If rs.EOF() Then
MsgBox ("Book Title not found")
Else
Text9.Text = rs(3)
Text10.Text = rs(7)
Text6.Text = rs(9)
Text7.Enabled = False
Text8.Enabled = False
End If
End If
End Sub
Private Sub Command9_Click()
Text3.Text = ""
Text7.Text = ""
Text8.Text = ""
Text4.Text = ""
Text3.Enabled = True
Command7.Enabled = True
Command3.Enabled = True
Text3.SetFocus
End Sub
Private Sub Form_Load()
Set conn = New ADODB.Connection
conn.Open "provider=MSDASQL;DSN=bookinformation;uid=;pwd=;"
sql1 = "Select * from bookinformation"
Set rs = conn.Execute(sql1)
Do While Not rs.EOF
rs.MoveNext
Loop
Set conn = New ADODB.Connection
conn.Open "provider=MSDASQL;DSN=borrowandreturn;uid=;pwd=;"
sql1 = "Select * from bookinformation"
Set rs = conn.Execute(sql1)
Do While Not rs.EOF
rs.MoveNext
Loop
Command4.Enabled = False
Command3.Enabled = False
Text1.Text = ""
Text2.Text = ""
Text7.Enabled = False
Text8.Enabled = False
Frame1.Visible = False
Text1.Text = ""
End Sub
Private Sub logout_Click()
Dim confirm
confirm = MsgBox("Are you sure you want to Exit and log_out??", vbYesNo, "BOOK BORROW AND RETURN MENU")
If confirm = vbYes Then
Unload Me
MDIForm1.Show
End If
End Sub
Private Sub refresh_Click()
Unload Me
Form26.Show
Text1.SetFocus
End Sub
