frmaddstocks.frm

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

VERSION 5.00
Begin VB.Form frmAddStocks 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Stocks"
   ClientHeight    =   2205
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   4905
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   2205
   ScaleWidth      =   4905
   StartUpPosition =   2  'CenterScreen
   Begin VB.TextBox txtQuantity 
      Height          =   375
      Left            =   4080
      TabIndex        =   9
      Top             =   960
      Width           =   735
   End
   Begin VB.CommandButton cmdlClose 
      Caption         =   "Cl&ose"
      Height          =   495
      Left            =   4080
      TabIndex        =   1
      Top             =   1560
      Width           =   735
   End
   Begin VB.CommandButton cmdSaved 
      Caption         =   "&Saved"
      Height          =   495
      Left            =   120
      TabIndex        =   0
      Top             =   1560
      Width           =   1095
   End
   Begin VB.Label Label5 
      Caption         =   "Additional book(s):"
      Height          =   255
      Left            =   2640
      TabIndex        =   8
      Top             =   1080
      Width           =   1335
   End
   Begin VB.Label Label6 
      Caption         =   "No. of Copies:"
      Height          =   255
      Left            =   240
      TabIndex        =   7
      Top             =   1080
      Width           =   1215
   End
   Begin VB.Label lblNoOfCopies 
      BorderStyle     =   1  'Fixed Single
      Height          =   375
      Left            =   1560
      TabIndex        =   6
      Top             =   960
      Width           =   975
   End
   Begin VB.Label lblTitle 
      BorderStyle     =   1  'Fixed Single
      Height          =   375
      Left            =   1560
      TabIndex        =   5
      Top             =   480
      Width           =   3255
   End
   Begin VB.Label lblBookID 
      BorderStyle     =   1  'Fixed Single
      Height          =   375
      Left            =   1560
      TabIndex        =   4
      Top             =   0
      Width           =   3255
   End
   Begin VB.Label Label2 
      Caption         =   "Title:"
      Height          =   255
      Left            =   240
      TabIndex        =   3
      Top             =   600
      Width           =   855
   End
   Begin VB.Label Label1 
      Caption         =   "Reference #:"
      Height          =   255
      Left            =   240
      TabIndex        =   2
      Top             =   120
      Width           =   975
   End
End
Attribute VB_Name = "frmAddStocks"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Function GetMaxQuantityLibraryItemByBookID(ByVal intBookID As Integer) 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(Quantity) as Quantity FROM LibraryItem WHERE BookID = " & intBookID
        
        If Not IsNull(rs!Quantity) Then
            GetMaxQuantityLibraryItemByBookID = rs!Quantity
        Else
            GetMaxQuantityLibraryItemByBookID = 0
        End If
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Function

Private Sub SaveLibraryItem()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim intCounter As Integer
    Dim intMaxBookQuantity As Integer
    cn.Open gblConnectionString
    
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "SELECT * FROM LibraryItem WHERE 1=0"
        
        intMaxBookQuantity = GetMaxQuantityLibraryItemByBookID(Me.lblBookID.Caption)
        
        For intCounter = 1 To Me.txtQuantity.Text
            .AddNew
            rs!BookID = Me.lblBookID.Caption
            rs!Quantity = intMaxBookQuantity + intCounter
            .Update
        Next intCounter
        
    End With

    Set cn = Nothing
    Set rs = Nothing
End Sub


Private Sub LinkAuthorToBook()
    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 * FROM BookAuthor WHERE 1=0"
        
        For intRow = 1 To Me.grdAuthor.Rows - 1
            If Me.grdAuthor.TextMatrix(intRow, colAuthorCheckBox) & "" <> "" Then
                If Me.grdAuthor.TextMatrix(intRow, colAuthorCheckBox) = -1 Then
                    .AddNew
                    rs!BookID = Me.lblBookID.Caption
                    rs!AuthorID = Me.grdAuthor.TextMatrix(intRow, colAuthorID)
                    .Update
                End If
            End If
        Next intRow
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Public Sub DisplayBookAuthorByBookID()
    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 " & _
            " LEFT JOIN BookAuthor ON Author.AuthorID = BookAuthor.AuthorID AND BookAuthor.BookID = " & Me.lblBookID.Caption & _
            " WHERE BookAuthor.AuthorID IS NULL"
    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 cmdAddAuthor_Click()
    If Me.grdAuthor.FixedRows = Me.grdAuthor.Rows Then
        MsgBox "Please select author to link.", vbInformation + vbOKOnly, App.ProductName
        Exit Sub
    End If
    
    LinkAuthorToBook
    MsgBox "Record saved.", vbInformation + vbOKOnly, App.ProductName
End Sub

Private Sub cmdlClose_Click()
    Unload Me
End Sub

Private Sub cmdSaved_Click()
    If Trim(Me.txtQuantity.Text) = "" Then
        MsgBox "Additional books required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtQuantity.SetFocus
        Exit Sub
    ElseIf IsNumeric(Me.txtQuantity.Text) = False Then
        MsgBox "Invalid additional book. Please enter whole number", vbInformation + vbOKOnly, App.ProductName
        Me.txtQuantity.SetFocus
        Exit Sub
    End If
    
    SaveLibraryItem
    MsgBox "Record saved.", vbInformation + vbOKOnly, App.ProductName
End Sub

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