frmlinkbookauthor.frm

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

VERSION 5.00
Object = "{D76D7128-4A96-11D3-BD95-D296DC2DD072}#1.0#0"; "Vsflex7.ocx"
Begin VB.Form frmLinkBookAuthor 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Link Author to Book"
   ClientHeight    =   5880
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   4980
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   5880
   ScaleWidth      =   4980
   StartUpPosition =   2  'CenterScreen
   Begin VB.CommandButton cmdlClose 
      Caption         =   "Cl&ose"
      Height          =   495
      Left            =   4200
      TabIndex        =   1
      Top             =   5280
      Width           =   735
   End
   Begin VB.CommandButton cmdAddAuthor 
      Caption         =   "&Link Author"
      Height          =   495
      Left            =   0
      TabIndex        =   0
      Top             =   5280
      Width           =   1095
   End
   Begin VSFlex7Ctl.VSFlexGrid grdAuthor 
      Height          =   4095
      Left            =   0
      TabIndex        =   6
      Top             =   1080
      Width           =   4935
      _cx             =   8705
      _cy             =   7223
      _ConvInfo       =   1
      Appearance      =   1
      BorderStyle     =   1
      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=   0
      SelectionMode   =   1
      GridLines       =   1
      GridLinesFixed  =   2
      GridLineWidth   =   1
      Rows            =   1
      Cols            =   3
      FixedRows       =   1
      FixedCols       =   0
      RowHeightMin    =   0
      RowHeightMax    =   0
      ColWidthMin     =   0
      ColWidthMax     =   0
      ExtendLastCol   =   -1  'True
      FormatString    =   $"frmLinkBookAuthor.frx":0000
      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        =   2
      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
   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 = "frmLinkBookAuthor"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

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