frmbook.frm

library system for students..these is very useful system.....

      Enabled         =   -1  'True
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Tahoma"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      MousePointer    =   0
      BackColor       =   -2147483643
      ForeColor       =   -2147483640
      BackColorFixed  =   -2147483633
      ForeColorFixed  =   -2147483630
      BackColorSel    =   -2147483635
      ForeColorSel    =   -2147483634
      BackColorBkg    =   -2147483636
      BackColorAlternate=   -2147483643
      GridColor       =   -2147483633
      GridColorFixed  =   -2147483632
      TreeColor       =   -2147483632
      FloodColor      =   192
      SheetBorder     =   -2147483642
      FocusRect       =   2
      HighLight       =   1
      AllowSelection  =   -1  'True
      AllowBigSelection=   0   'False
      AllowUserResizing=   3
      SelectionMode   =   0
      GridLines       =   1
      GridLinesFixed  =   2
      GridLineWidth   =   1
      Rows            =   1
      Cols            =   4
      FixedRows       =   1
      FixedCols       =   0
      RowHeightMin    =   0
      RowHeightMax    =   0
      ColWidthMin     =   0
      ColWidthMax     =   0
      ExtendLastCol   =   -1  'True
      FormatString    =   $"frmBook.frx":0146
      ScrollTrack     =   0   'False
      ScrollBars      =   3
      ScrollTips      =   0   'False
      MergeCells      =   0
      MergeCompare    =   0
      AutoResize      =   -1  'True
      AutoSizeMode    =   0
      AutoSearch      =   1
      AutoSearchDelay =   2
      MultiTotals     =   -1  'True
      SubtotalPosition=   1
      OutlineBar      =   0
      OutlineCol      =   0
      Ellipsis        =   0
      ExplorerBar     =   0
      PicturesOver    =   0   'False
      FillStyle       =   0
      RightToLeft     =   0   'False
      PictureType     =   0
      TabBehavior     =   0
      OwnerDraw       =   0
      Editable        =   0
      ShowComboButton =   -1  'True
      WordWrap        =   0   'False
      TextStyle       =   0
      TextStyleFixed  =   0
      OleDragMode     =   0
      OleDropMode     =   0
      DataMode        =   0
      VirtualData     =   -1  'True
      DataMember      =   ""
      ComboSearch     =   3
      AutoSizeMouse   =   -1  'True
      FrozenRows      =   0
      FrozenCols      =   0
      AllowUserFreezing=   0
      BackColorFrozen =   0
      ForeColorFrozen =   0
      WallPaperAlignment=   9
   End
End
Attribute VB_Name = "frmBook"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Dim enumDBOperation As DatabaseOperation

Private Sub UnLinkAuthorToBook()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim intRow As Integer
    
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        
        
        For intRow = 1 To Me.grdAuthor.Rows - 1
            If Me.grdAuthor.TextMatrix(intRow, colAuthorCheckBox) & "" <> "" Then
                If Me.grdAuthor.TextMatrix(intRow, colAuthorCheckBox) = -1 Then
                    .Open "DELETE FROM BookAuthor WHERE BookID=" & Me.lblID.Caption & _
                            " AND AuthorID=" & Me.grdAuthor.TextMatrix(intRow, colAuthorID)
                End If
            End If
        Next intRow
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub DisplayLibraryItemByBookID(ByVal intBookID As Integer)
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim intRow As Integer
    Dim intAvailableCopies As Integer
    
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open " SELECT LibraryItem.Quantity, " & _
            "   CASE WHEN CheckOut.LoanID IS NULL THEN 'Check In' Else 'Check Out' END As Status" & _
            " From LibraryItem " & _
            " Left Join" & _
            " ( SELECT MAX(LoanID) as LoanID, LibraryItemID" & _
            " From Loan " & _
            " Where DateReturn Is Null" & _
            " GROUP BY LibraryItemID" & _
            " ) CheckOut on CheckOut.LibraryItemID = LibraryItem.LibraryItemID" & _
            " WHERE LibraryItem.BookID = " & Me.lblID.Caption & _
            " ORDER BY LibraryItem.Quantity "
            
    End With
              
    With grdLibraryItem
        intAvailableCopies = 0
        .Rows = rs.RecordCount + 1
        For intRow = 1 To rs.RecordCount
            .TextMatrix(intRow, colLibraryItemNo) = Format(rs!Quantity, "000")
            .TextMatrix(intRow, colLibraryItemStatus) = rs!Status
            If rs!Status = "Check In" Then
                intAvailableCopies = intAvailableCopies + 1
            End If
            rs.MoveNext
        Next intRow
    End With

    Me.lblNoOfCopies.Caption = rs.RecordCount
    Me.lblAvailableCopies.Caption = intAvailableCopies
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub DisplayBookAuthorByBookID(ByVal intBookID As Integer)
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim intRow As Integer
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open " SELECT Author.AuthorID, Author.FirstName + ' ' + Author.LastName as Author" & _
            " From Author " & _
            " INNER JOIN BookAuthor ON Author.AuthorID = BookAuthor.AuthorID " & _
           " WHERE BookAuthor.BookID = " & Me.lblID.Caption
    End With
              

    With grdAuthor
        .Rows = rs.RecordCount + 1
        For intRow = 1 To rs.RecordCount
            .TextMatrix(intRow, colAuthorID) = Format(rs!AuthorID, "000")
            .TextMatrix(intRow, colAuthorName) = rs!Author
            rs.MoveNext
        Next intRow
    End With

    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub DisplayBookByID(ByVal intBookID As Integer)
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT * FROM Book WHERE BookID=" & Me.lblID.Caption
        
        Me.lblID.Caption = Format(rs!BookID, "000")
        
        If IsNumeric(rs!PublisherID) = True Then
            Me.grdPublisher.Row = Me.grdPublisher.FindRow(rs!PublisherID, , colPublisherID)
        End If
        
        If IsNumeric(rs!LocationID) = True Then
            Me.cboLocation.ListIndex = ComboBoxBySelectedID(Me.cboLocation, rs!LocationID)
        Else
            Me.cboLocation.ListIndex = -1
       End If
        
        If Not IsNull(rs!DateRevised) Then
            Me.dtpDateRevised.Value = rs!DateRevised
        End If
        
        Me.txtTitle.Text = rs!Title & ""
        Me.txtEdition.Text = rs!Edition & ""
        Me.txtVolume.Text = rs!Volume & ""
        Me.txtPages.Text = rs!Pages & ""
        Me.txtYear.Text = rs!YearOfBook & ""
        Me.txtDuration.Text = rs!Duration & ""
              
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub DeleteBook()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT * FROM Book WHERE BookID=" & Me.lblID.Caption

        .Delete
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub UpdateBook()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT * FROM Book WHERE BookID=" & Me.lblID.Caption
        
        rs!BookID = Me.lblID.Caption
        If Me.grdPublisher.Row > 0 Then
            rs!PublisherID = Me.grdPublisher.TextMatrix(Me.grdPublisher.Row, colPublisherID)
        End If
        rs!LocationID = Me.cboLocation.ItemData(Me.cboLocation.ListIndex)
        rs!DateRevised = Format(Me.dtpDateRevised.Value, "mm/dd/yyyy")
        rs!Title = StrConv(Me.txtTitle.Text, vbProperCase)
        rs!Edition = Me.txtEdition.Text
        rs!Volume = Me.txtVolume.Text
        rs!Pages = Me.txtPages.Text
        rs!YearOfBook = Me.txtYear.Text
        rs!Duration = Me.txtDuration.Text
        .Update
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub SaveBook()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT * FROM Book WHERE 1=0"
        
        .AddNew
        rs!BookID = Me.lblID.Caption
        If Me.grdPublisher.Row > 0 Then
            rs!PublisherID = Me.grdPublisher.TextMatrix(Me.grdPublisher.Row, colPublisherID)
        End If
        rs!LocationID = Me.cboLocation.ItemData(Me.cboLocation.ListIndex)
        rs!DateRevised = Format(Me.dtpDateRevised.Value, "mm/dd/yyyy")
        rs!Title = StrConv(Me.txtTitle.Text, vbProperCase)
        rs!Edition = Me.txtEdition.Text
        rs!Volume = Me.txtVolume.Text
        rs!Pages = Me.txtPages.Text
        rs!YearOfBook = Me.txtYear.Text
        rs!Duration = Me.txtDuration.Text
        .Update
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub


Private Sub LoadLocation()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim intRow As Integer
    
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT LocationID, LocationName FROM Location"
    End With
    
    With cboLocation
        .Clear
        For intRow = 1 To rs.RecordCount
            .AddItem rs!LocationName
            .ItemData(.NewIndex) = rs!LocationID
            rs.MoveNext
        Next intRow
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub LoadPublisher()
    Dim cn As New ADODB.Connection