frmbook.frm

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

    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 PublisherID, PublisherName FROM Publisher"
    End With
    
    With grdPublisher
        .Rows = rs.RecordCount + 1
        For intRow = 1 To rs.RecordCount
            .TextMatrix(intRow, colPublisherID) = Format(rs!PublisherID, "000")
            .TextMatrix(intRow, colPublisherName) = rs!PublisherName
            rs.MoveNext
        Next intRow
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Function CreateNewBookID() 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 MAX(BookID) as BookID FROM Book"
        
        If .RecordCount > 0 Then
            CreateNewBookID = rs!BookID + 1
        Else
            CreateNewBookID = 1
        End If
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Function

Private Sub LoadBook()
    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 BookID, Title, Edition, Volume FROM Book"
    End With
    
    With grdBook
        .Rows = rs.RecordCount + 1
        For intRow = 1 To rs.RecordCount
            .TextMatrix(intRow, colBookReferenceNumber) = Format(rs!BookID, "000000")
            .TextMatrix(intRow, colBookTitle) = rs!Title & ""
            .TextMatrix(intRow, colBookEdition) = rs!Edition & ""
            .TextMatrix(intRow, colBookVolume) = rs!Volume & ""
            rs.MoveNext
        Next intRow
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub SetupControls()
    If enumDBOperation = None Then
        Me.grdBook.Enabled = True
        
        Me.grdPublisher.Enabled = False
        Me.txtTitle.Enabled = False
        Me.cboLocation.Enabled = False
        Me.txtDuration.Enabled = False
        Me.dtpDateRevised.Enabled = False
        Me.txtEdition.Enabled = False
        Me.txtVolume.Enabled = False
        Me.txtYear.Enabled = False
        Me.txtPages.Enabled = False
        Me.txtRemarks.Enabled = False
        
        Me.cmdAddAuthor.Enabled = False
        Me.cmdRemoveAuthor.Enabled = False
       
        Me.cmdAddStock.Enabled = False
        
        Me.lblID.Caption = ""
        Me.txtTitle.Text = ""
        Me.cboLocation.ListIndex = -1
        Me.txtDuration.Text = ""
        
        Me.dtpDateRevised.Value = Now
        Me.txtEdition.Text = ""
        Me.txtVolume.Text = ""
        Me.txtYear.Text = ""
        Me.txtPages.Text = ""
        Me.txtRemarks.Text = ""
        
        Me.cmdAdd.Enabled = True
        Me.cmdEdit.Enabled = True
        Me.cmdDelete.Enabled = True
        Me.cmdSave.Enabled = False
        Me.cmdCancel.Enabled = False
    
    ElseIf enumDBOperation = Add Then
        Me.grdBook.Enabled = False
        
        Me.grdPublisher.Enabled = True
        Me.txtTitle.Enabled = True
        Me.cboLocation.Enabled = True
        Me.txtDuration.Enabled = True
        Me.dtpDateRevised.Enabled = True
        Me.txtEdition.Enabled = True
        Me.txtVolume.Enabled = True
        Me.txtYear.Enabled = True
        Me.txtPages.Enabled = True
        Me.txtRemarks.Enabled = True
        
        Me.cmdAddAuthor.Enabled = True
        Me.cmdRemoveAuthor.Enabled = True
        
        Me.cmdAddStock.Enabled = True
        
        Me.lblID.Caption = ""
        Me.txtTitle.Text = ""
        Me.cboLocation.ListIndex = -1
        Me.txtDuration.Text = ""
        
        Me.dtpDateRevised.Value = Now
        Me.txtEdition.Text = ""
        Me.txtVolume.Text = ""
        Me.txtYear.Text = ""
        Me.txtPages.Text = ""
        Me.txtRemarks.Text = ""
        
        Me.cmdAdd.Enabled = False
        Me.cmdEdit.Enabled = False
        Me.cmdDelete.Enabled = False
        Me.cmdSave.Enabled = True
        Me.cmdCancel.Enabled = True
        
    ElseIf enumDBOperation = Edit Then
        Me.grdBook.Enabled = False
        
        Me.grdPublisher.Enabled = True
        Me.txtTitle.Enabled = True
        Me.cboLocation.Enabled = True
        Me.txtDuration.Enabled = True
        Me.dtpDateRevised.Enabled = True
        Me.txtEdition.Enabled = True
        Me.txtVolume.Enabled = True
        Me.txtYear.Enabled = True
        Me.txtPages.Enabled = True
        Me.txtRemarks.Enabled = True
        
        Me.cmdAddAuthor.Enabled = True
        Me.cmdRemoveAuthor.Enabled = True
        
        Me.cmdAddStock.Enabled = True

        Me.cmdAdd.Enabled = False
        Me.cmdEdit.Enabled = False
        Me.cmdDelete.Enabled = False
        Me.cmdSave.Enabled = True
        Me.cmdCancel.Enabled = True
    End If
End Sub

Private Sub cmdAdd_Click()
    enumDBOperation = Add
    SetupControls
    Me.lblID.Caption = Format(CreateNewBookID, "000000")
End Sub

Private Sub cmdAddAuthor_Click()
    With frmLinkBookAuthor
        .lblBookID.Caption = Format(Me.lblID.Caption, "000")
        .lblTitle.Caption = StrConv(Me.txtTitle.Text, vbProperCase)
        .DisplayBookAuthorByBookID
        .Show vbModal
        DisplayBookAuthorByBookID Me.lblID.Caption
    End With

End Sub

Private Sub cmdAddStock_Click()
    With frmAddStocks
        .lblBookID.Caption = Format(Me.lblID.Caption, "000")
        .lblTitle.Caption = StrConv(Me.txtTitle.Text, vbProperCase)
        .lblNoOfCopies.Caption = Me.lblNoOfCopies.Caption
        .Show vbModal
        DisplayLibraryItemByBookID Me.lblID.Caption
    End With
End Sub

Private Sub cmdCancel_Click()
    Form_Load
End Sub

Private Sub cmdDelete_Click()
    If Me.lblID.Caption = "" Then
        MsgBox "Please select Book to delete.", vbInformation + vbOKOnly, App.ProductName
        Exit Sub
    End If
    
    DeleteBook
    MsgBox "Record Deleted.", vbInformation + vbOKOnly, App.ProductName
    
    Form_Load
End Sub

Private Sub cmdEdit_Click()
    If Me.lblID.Caption = "" Then
        MsgBox "Please select Book to edit.", vbInformation + vbOKOnly, App.ProductName
        Exit Sub
    End If
    
    enumDBOperation = Edit
    SetupControls
End Sub

Private Sub cmdlClose_Click()
    Unload Me
End Sub

Private Sub cmdRemoveAuthor_Click()
    If Me.grdAuthor.FixedRows = Me.grdAuthor.Rows Then
        MsgBox "Please select author to unlink.", vbInformation + vbOKOnly, App.ProductName
        Exit Sub
    End If
    
    UnLinkAuthorToBook
    MsgBox "Record saved.", vbInformation + vbOKOnly, App.ProductName
    DisplayBookAuthorByBookID Me.lblID.Caption
End Sub

Private Sub cmdSave_Click()
    If Trim(Me.txtTitle.Text) = "" Then
        MsgBox "Title required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtTitle.SetFocus
        Exit Sub
    ElseIf Me.cboLocation.ListIndex = -1 Then
        MsgBox "Location required.", vbInformation + vbOKOnly, App.ProductName
        Me.cboLocation.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtDuration.Text) = "" Then
        MsgBox "Duration required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtDuration.SetFocus
        Exit Sub
    ElseIf IsNumeric(Me.txtDuration.Text) = False Then
        MsgBox "Invalid Duration. Please enter whole number", vbInformation + vbOKOnly, App.ProductName
        Me.txtDuration.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtEdition.Text) = "" Then
        MsgBox "Edition required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtEdition.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtVolume.Text) = "" Then
        MsgBox "Volume required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtVolume.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtYear.Text) = "" Then
        MsgBox "Year required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtYear.SetFocus
        Exit Sub
    ElseIf IsNumeric(Me.txtYear.Text) = False Then
        MsgBox "Invalid Year. Please enter whole number", vbInformation + vbOKOnly, App.ProductName
        Me.txtYear.SetFocus
        Exit Sub
    ElseIf Len(Me.txtYear.Text) <> 4 Then
        MsgBox "Invalid Year. Please enter 4 digit number", vbInformation + vbOKOnly, App.ProductName
        Me.txtYear.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtPages.Text) = "" Then
        MsgBox "Number of page required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtPages.SetFocus
        Exit Sub
    ElseIf IsNumeric(Me.txtPages.Text) = False Then
        MsgBox "Invalid Number of page. Please enter whole number", vbInformation + vbOKOnly, App.ProductName
        Me.txtPages.SetFocus
        Exit Sub
    End If
    
    If enumDBOperation = Add Then
        SaveBook
        MsgBox "Record Saved.", vbInformation + vbOKOnly, App.ProductName
    Else
        UpdateBook
        MsgBox "Record Updated.", vbInformation + vbOKOnly, App.ProductName
    End If
    
    Form_Load
End Sub


Private Sub Form_Load()
    enumDBOperation = None
    SetupControls
    LoadBook
    LoadPublisher
    LoadLocation
    
    
End Sub

Private Sub grdBook_Click()
    With grdBook
        Me.lblID.Caption = .TextMatrix(.Row, colPublisherID)
        DisplayBookByID Me.lblID
        DisplayBookAuthorByBookID Me.lblID
        DisplayLibraryItemByBookID Me.lblID
    End With
End Sub

Private Sub grdBook_EnterCell()
    grdBook_Click
End Sub

Private Sub txtDuration_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 8, 48 To 57
        Case Else
            KeyAscii = vbNull
    End Select
    
End Sub

Private Sub txtPages_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 8, 48 To 57
        Case Else
            KeyAscii = vbNull
    End Select
End Sub

Private Sub txtYear_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 8, 48 To 57
        Case Else
            KeyAscii = vbNull
    End Select
End Sub