frmborrower.frm

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

        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Function

'Private Function IsDuplicateBorrower() As Boolean
'    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
'        If enumDBOperation = Add Then
'            .Open "SELECT BorrowerID, BorrowerName FROM Borrower WHERE UPPER(BorrowerName)='" & UCase(Me.txtFirstName.Text) & "'"
'        Else
'            .Open "SELECT BorrowerID, BorrowerName FROM Borrower WHERE UPPER(BorrowerName)='" & UCase(Me.txtFirstName.Text) & "' AND BorrowerID <> " & Me.lblID.Caption
'        End If
'
'        IsDuplicateBorrower = False
'        If .RecordCount > 0 Then
'            IsDuplicateBorrower = True
'        End If
'
'    End With
'
'    Set cn = Nothing
'    Set rs = Nothing
'End Function

Private Sub DisplayBorrowerByID(ByVal intBorrowerID 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 Borrower WHERE BorrowerID=" & Me.lblID.Caption
        
        Me.lblID.Caption = Format(rs!BorrowerID, "000")
        Me.cboType.Text = rs!BorrowerType
        txtFirstName.Text = rs!FirstName
        txtMiddleName.Text = rs!MiddleName & ""
        txtLastName.Text = rs!LastName
        Me.txtSection.Text = rs!Section & ""
       
        If IsNumeric(rs!Year) = True Then
            Me.cboYear.ListIndex = rs!Year - 1
        Else
            Me.cboYear.ListIndex = -1
        End If
        
        Me.txtAddress.Text = rs!Address
        Me.txtContactNumbers.Text = rs!ContactNumber
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub DeleteBorrower()
    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 Borrower WHERE BorrowerID=" & Me.lblID.Caption

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

Private Sub UpdateBorrower()
    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 Borrower WHERE BorrowerID=" & Me.lblID.Caption
        
        rs!BorrowerType = Me.cboType.Text
        rs!FirstName = StrConv(txtFirstName.Text, vbProperCase)
        rs!MiddleName = StrConv(txtMiddleName.Text, vbProperCase)
        rs!LastName = StrConv(txtLastName.Text, vbProperCase)
        rs!Section = UCase(Me.txtSection.Text)
        
        If Me.cboYear.ListIndex > -1 Then
            rs!Year = Left(Me.cboYear.Text, 1)
        End If
        
        rs!Address = UCase(Me.txtAddress.Text)
        rs!ContactNumber = UCase(Me.txtContactNumbers.Text)
        .Update
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub SaveBorrower()
    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 Borrower WHERE 1=0"
        
        .AddNew
        rs!BorrowerID = Me.lblID.Caption
        rs!BorrowerType = Me.cboType.Text
        rs!FirstName = StrConv(txtFirstName.Text, vbProperCase)
        rs!MiddleName = StrConv(txtMiddleName.Text, vbProperCase)
        rs!LastName = StrConv(txtLastName.Text, vbProperCase)
        rs!Section = UCase(Me.txtSection.Text)
        
        If Me.cboYear.ListIndex > -1 Then
            rs!Year = Left(Me.cboYear.Text, 1)
        End If
        
        rs!Address = UCase(Me.txtAddress.Text)
        rs!ContactNumber = UCase(Me.txtContactNumbers.Text)
        .Update
        
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub

Private Sub SetupControls()
    If enumDBOperation = None Then
        Me.grdBorrower.Enabled = True
        Me.cboType.Enabled = False
        Me.txtFirstName.Enabled = False
        Me.txtMiddleName.Enabled = False
        Me.txtLastName.Enabled = False
        Me.txtAddress.Enabled = False
        Me.txtContactNumbers.Enabled = False
        Me.txtSection.Enabled = False
        Me.cboYear.Enabled = False
        
        Me.lblID.Caption = ""
        Me.cboType.ListIndex = -1
        Me.txtFirstName.Text = ""
        Me.txtMiddleName.Text = ""
        Me.txtLastName.Text = ""
        Me.txtAddress.Text = ""
        Me.txtContactNumbers.Text = ""
        Me.txtSection.Text = ""
        Me.cboYear.ListIndex = -1
        
        Me.txtSection.Visible = False
        Me.lblSection.Visible = False
        Me.cboYear.Visible = False
        Me.lblYear.Visible = False
        
            
        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.grdBorrower.Enabled = False
        Me.cboType.Enabled = True
        Me.txtFirstName.Enabled = True
        Me.txtMiddleName.Enabled = True
        Me.txtLastName.Enabled = True
        Me.txtAddress.Enabled = True
        Me.txtContactNumbers.Enabled = True
        Me.txtSection.Enabled = True
        Me.cboYear.Enabled = True
        
        Me.lblID.Caption = ""
        Me.cboType.ListIndex = -1
        Me.txtFirstName.Text = ""
        Me.txtMiddleName.Text = ""
        Me.txtLastName.Text = ""
        Me.txtAddress.Text = ""
        Me.txtContactNumbers.Text = ""
        Me.txtSection.Text = ""
        Me.cboYear.ListIndex = -1
        
        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.grdBorrower.Enabled = False
        Me.cboType.Enabled = True
        Me.txtFirstName.Enabled = True
        Me.txtMiddleName.Enabled = True
        Me.txtLastName.Enabled = True
        Me.txtAddress.Enabled = True
        Me.txtContactNumbers.Enabled = True
        Me.txtSection.Enabled = True
        Me.cboYear.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 LoadBorrower()
    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 BorrowerID, FirstName, LastName FROM Borrower"
    End With
    
    With grdBorrower
        .Rows = rs.RecordCount + 1
        For intRow = 1 To rs.RecordCount
            .TextMatrix(intRow, colID) = Format(rs!BorrowerID, "000")
            .TextMatrix(intRow, colFirstName) = rs!FirstName
            .TextMatrix(intRow, colLastName) = rs!LastName
            rs.MoveNext
        Next intRow
    End With
    
    Set cn = Nothing
    Set rs = Nothing
End Sub


Private Sub cboType_Click()
    If Me.cboType.Text = "Student" Then
        Me.txtSection.Visible = True
        Me.lblSection.Visible = True
        Me.cboYear.Visible = True
        Me.lblYear.Visible = True
    Else
        Me.txtSection.Visible = False
        Me.lblSection.Visible = False
        Me.cboYear.Visible = False
        Me.lblYear.Visible = False
    End If

End Sub

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

Private Sub cmdCancel_Click()
    Form_Load
End Sub

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

Private Sub cmdEdit_Click()
    If Me.lblID.Caption = "" Then
        MsgBox "Please select Borrower 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 cmdSave_Click()
    If Me.cboType.ListIndex = -1 Then
        MsgBox "Type required.", vbInformation + vbOKOnly, App.ProductName
        Me.cboType.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtFirstName.Text) = "" Then
        MsgBox "First Name required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtFirstName.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtMiddleName.Text) = "" Then
        MsgBox "Middle Name required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtMiddleName.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtLastName.Text) = "" Then
        MsgBox "Last Name required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtLastName.SetFocus
        Exit Sub
    ElseIf Trim(Me.txtAddress.Text) = "" Then
        MsgBox "Address required.", vbInformation + vbOKOnly, App.ProductName
        Me.txtAddress.SetFocus
        Exit Sub
    ElseIf cboType.Text = "Student" Then
        If Trim(Me.txtSection.Text) = "" Then
            MsgBox "Section required.", vbInformation + vbOKOnly, App.ProductName
            Me.txtSection.SetFocus
            Exit Sub
        ElseIf Me.cboYear.ListIndex = -1 Then
            MsgBox "Year required.", vbInformation + vbOKOnly, App.ProductName
            Me.cboYear.SetFocus
            Exit Sub
        End If
    End If
    
    If enumDBOperation = Add Then
        SaveBorrower
        MsgBox "Record Saved.", vbInformation + vbOKOnly, App.ProductName
    Else
        UpdateBorrower
        MsgBox "Record Updated.", vbInformation + vbOKOnly, App.ProductName
    End If
    
    Form_Load
End Sub

Private Sub Form_Load()
    enumDBOperation = None
    SetupControls
    LoadBorrower
End Sub

Private Sub grdBorrower_Click()
    With grdBorrower
        Me.lblID.Caption = .TextMatrix(.Row, colID)
        DisplayBorrowerByID Me.lblID
    End With
End Sub

Private Sub grdBorrower_EnterCell()
    grdBorrower_Click
End Sub