frmrooms.frm

hotel mnagement system

        
        If .Rows = 2 Then Grid.Rows = Grid.Rows + 1
        .RemoveItem (.RowSel)
    End With

    btnRemove.Visible = False
    
    Grid_Click
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Public Sub cmdSave_Click()
On Error GoTo err

    Dim rsRoomRates As New Recordset

    CN.BeginTrans
    
    rsRoomRates.CursorLocation = adUseClient
    rsRoomRates.Open "SELECT * FROM [Room Rates] WHERE RoomNumber=" & PK, CN, adOpenStatic, adLockOptimistic

    If State = adStateAddMode Then
        If txtRoomNumber.Text = "" Then txtRoomNumber.SetFocus: Exit Sub
        
        RS.AddNew
        
        RS.Fields("RoomNumber") = txtRoomNumber.Text
        RS.Fields("AddedByFK") = CurrUser.USER_PK
    Else
        RS.Fields("DateModified") = Now
        RS.Fields("LastUserFK") = CurrUser.USER_PK
    End If

    With RS
        .Fields("RoomNumber") = txtRoomNumber.Text
        .Fields("RoomTypeID") = dcRoomType.BoundText
        .Fields("RoomStatusID") = dcRoomStatus.BoundText
        
        .Update
    End With
    
    Dim c As Integer
    
    With Grid
        'Save the details of the records
        For c = 1 To cIRowCount
            .Row = c
            
            If State = adStateAddMode Then
                rsRoomRates.AddNew
                
                rsRoomRates![RoomNumber] = txtRoomNumber.Text
            Else
                rsRoomRates.Filter = "[RateTypeID]=" & .TextMatrix(c, 1)
            End If

            If rsRoomRates.RecordCount > 0 Then
                rsRoomRates![RateTypeID] = .TextMatrix(c, 1)
                rsRoomRates![RoomRate] = .TextMatrix(c, 3)
                rsRoomRates![NoofPerson] = .TextMatrix(c, 4)
                rsRoomRates![ExtraAdultRate] = .TextMatrix(c, 5)
                rsRoomRates![ExtraChildRate] = .TextMatrix(c, 6)

                rsRoomRates.Update
            End If
        Next c
    End With

    'Save last rate entry to rate templates
    If State = adStateAddMode Then
        CN.Execute "DELETE RoomTypeID " & _
                    "From [Rate Templates] " & _
                    "WHERE RoomTypeID=" & dcRoomType.BoundText
                    
        CN.Execute "INSERT INTO [Rate Templates] ( RoomTypeID, RateTypeID, RoomRate, NoofPerson, ExtraAdultRate ) " & _
                    "SELECT Rooms.RoomTypeID, [Room Rates].RateTypeID, [Room Rates].RoomRate, [Room Rates].NoofPerson, [Room Rates].ExtraAdultRate " & _
                    "FROM [Room Rates] INNER JOIN Rooms ON [Room Rates].RoomNumber = Rooms.RoomNumber " & _
                    "WHERE [Room Rates].RoomNumber=" & txtRoomNumber.Text
    End If

    'Clear variables
    c = 0
    Set rsRoomRates = Nothing
    
    CN.CommitTrans
    
    Unload frmRooms

    Exit Sub
    
err:
    CN.RollbackTrans
    prompt_err err, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

Private Sub cmdUpdate_Click()
    With Grid
        .TextMatrix(.RowSel, 1) = dcRateType.BoundText
        .TextMatrix(.RowSel, 2) = dcRateType.Text
        .TextMatrix(.RowSel, 3) = toMoney(txtRoomRate.Text)
        .TextMatrix(.RowSel, 4) = txtNoofPerson.Text
        .TextMatrix(.RowSel, 5) = toMoney(txtAdults.Text)
        .TextMatrix(.RowSel, 6) = toMoney(txtChildrens.Text)
    End With
End Sub

Private Sub AddRoomRates()
On Error GoTo err

    CN.BeginTrans
    
    If State = adStateAddMode Then
        CN.Execute "INSERT INTO [Room Rates] ( RoomNumber, RateTypeID ) " & _
                    "SELECT " & txtRoomNumber.Text & ", [Rate Type].RateTypeID " & _
                    "FROM [Rate Type]"
    Else
        Dim rsRateType As New Recordset
        
        With rsRateType
            .Open "SELECT RateTypeID FROM [Rate Type]", CN, adOpenStatic, adLockOptimistic
            
            Do While Not .EOF
                If .Fields("RateTypeID") <> getValueAt("SELECT RateTypeID FROM [Room Rates] WHERE RoomNumber = " & txtRoomNumber.Text & " AND RateTypeID = " & .Fields("RateTypeID"), "RateTypeID") Then
                    CN.Execute "INSERT INTO [Room Rates] ( RoomNumber, RateTypeID ) " & _
                                "SELECT " & txtRoomNumber.Text & ", " & .Fields("RateTypeID")
                    
                    .Update
                End If
                
                .MoveNext
            Loop
            .Close
        End With
    End If
    
    CN.CommitTrans
    
    Exit Sub
    
err:
    CN.RollbackTrans
    prompt_err err, Name, "AddRoomRates"
    Screen.MousePointer = vbDefault
End Sub

Private Sub dcRoomType_Click(Area As Integer)
    AddFromRateTemplates
'     Call AddRoomRates
'     Call DisplayForEditing
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        SendKeys "{TAB}"
    End If
End Sub

Private Sub Form_Load()
On Error GoTo err

    Call InitGrid
    
    bind_dc "SELECT * FROM [Room Type]", "RoomType", dcRoomType, "RoomTypeID", True
    bind_dc "SELECT * FROM [Rate Type]", "RateType", dcRateType, "RateTypeID", True
    bind_dc "SELECT * FROM [Room Status]", "Status", dcRoomStatus, "StatusID", True

    RS.Open "SELECT * FROM Rooms WHERE RoomNumber = " & PK, CN, adOpenStatic, adLockOptimistic
    
    'Check the form state
    If State = adStateAddMode Then
        AddFromRateTemplates

        cmdUsrHistory.Enabled = False
    Else
        With RS
            txtRoomNumber.Text = PK
            dcRoomType.BoundText = .Fields("RoomTypeID")
            dcRoomStatus.BoundText = .Fields("RoomStatusID")
        End With
        
        Call AddRoomRates
        
        DisplayForEditing
    End If
    
    Exit Sub
    
err:
    CN.RollbackTrans
    prompt_err err, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cIRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 7
        .ColSel = 6
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 0
        .ColWidth(2) = 1200
        .ColWidth(3) = 1200
        .ColWidth(4) = 1200
        .ColWidth(5) = 1200
        .ColWidth(6) = 1200

        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Rate Type ID"
        .TextMatrix(0, 2) = "Rate Type"
        .TextMatrix(0, 3) = "Room Rate"
        .TextMatrix(0, 4) = "No. of Person"
        .TextMatrix(0, 5) = "Extra Adult's Rate"
        .TextMatrix(0, 6) = "Extra Children's Rate"
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    frmRoomsList.RefreshRecords
    
    Set frmRooms = Nothing
End Sub

Private Sub Grid_Click()
    With Grid
        dcRateType.BoundText = .TextMatrix(.RowSel, 1)
        txtRoomRate.Text = .TextMatrix(.RowSel, 3)
        txtNoofPerson.Text = .TextMatrix(.RowSel, 4)
        txtAdults.Text = .TextMatrix(.RowSel, 5)
        txtChildrens.Text = .TextMatrix(.RowSel, 6)
    
        If Grid.Rows = 2 And Grid.TextMatrix(1, 2) = "" Then
            btnRemove.Visible = False
        Else
            btnRemove.Visible = True
            btnRemove.Top = (Grid.CellTop + Grid.Top) - 20
            btnRemove.Left = Grid.Left + 50
        End If
    End With
End Sub

Private Sub ResetFields()
    txtRoomNumber.Text = ""
    
    txtRoomNumber.SetFocus
End Sub

Private Sub DisplayForEditing()
On Error GoTo err
    
    'Display the details
    Dim rsRoomRates As New Recordset

    cIRowCount = 0
    
    rsRoomRates.CursorLocation = adUseClient
    rsRoomRates.Open "SELECT * FROM qry_Room_Rates WHERE RoomNumber=" & PK, CN, adOpenStatic, adLockOptimistic
    
    If rsRoomRates.RecordCount > 0 Then
        rsRoomRates.MoveFirst
        While Not rsRoomRates.EOF
          cIRowCount = cIRowCount + 1     'increment
            With Grid
                If .Rows = 2 And .TextMatrix(1, 1) = "" Then
                    .TextMatrix(1, 1) = rsRoomRates!RateTypeID
                    .TextMatrix(1, 2) = rsRoomRates!RateType
                    .TextMatrix(1, 3) = toMoney(rsRoomRates!RoomRate)
                    .TextMatrix(1, 4) = rsRoomRates!NoofPerson
                    .TextMatrix(1, 5) = toMoney(rsRoomRates!ExtraAdultRate)
                    .TextMatrix(1, 6) = toMoney(rsRoomRates!ExtraChildRate)
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = rsRoomRates!RateTypeID
                    .TextMatrix(.Rows - 1, 2) = rsRoomRates!RateType
                    .TextMatrix(.Rows - 1, 3) = toMoney(rsRoomRates!RoomRate)
                    .TextMatrix(.Rows - 1, 4) = rsRoomRates!NoofPerson
                    .TextMatrix(.Rows - 1, 5) = toMoney(rsRoomRates!ExtraAdultRate)
                    .TextMatrix(.Rows - 1, 6) = toMoney(rsRoomRates!ExtraChildRate)
                End If
            End With
           
            rsRoomRates.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 6
        'Set fixed cols
        If State = adStateEditMode Then
            Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionFree
            Grid.FixedCols = 1
        End If
    End If

    rsRoomRates.Close
    'Clear variables
    Set rsRoomRates = Nothing

    Exit Sub
err:
    If err.Number = 94 Then Resume Next
    
    prompt_err err, Name, "DisplayForEditing"
    Screen.MousePointer = vbDefault
End Sub

Private Sub AddFromRateTemplates()
    Dim rsRateType As New Recordset
    Dim rsRateTemplates As New Recordset

    Grid.Clear
    InitGrid
    
    cIRowCount = 0
    
    rsRateTemplates.CursorLocation = adUseClient
    rsRateTemplates.Open "SELECT [Rate Templates].RoomTypeID, [Rate Templates].RateTypeID, [Rate Templates].RoomRate, [Rate Templates].NoofPerson, [Rate Templates].ExtraAdultRate " & _
                    "From [Rate Templates] " & _
                    "WHERE RoomTypeID= " & dcRoomType.BoundText, CN, adOpenStatic, adLockOptimistic
    
    rsRateType.CursorLocation = adUseClient
    rsRateType.Open "SELECT RateTypeID, RateType FROM [Rate Type]", CN, adOpenStatic, adLockOptimistic
    
    If rsRateType.RecordCount > 0 Then
        rsRateType.MoveFirst
        While Not rsRateType.EOF
          cIRowCount = cIRowCount + 1     'increment
          rsRateTemplates.Filter = "[RateTypeID]=" & rsRateType!RateTypeID
            With Grid
                If .Rows = 2 And .TextMatrix(1, 1) = "" Then
                    .TextMatrix(1, 1) = rsRateType!RateTypeID
                    .TextMatrix(1, 2) = rsRateType!RateType
                    If rsRateTemplates.RecordCount > 0 Then
                        .TextMatrix(1, 3) = toMoney(rsRateTemplates!RoomRate)
                        .TextMatrix(1, 4) = rsRateTemplates!NoofPerson
                        .TextMatrix(1, 5) = toMoney(rsRateTemplates!ExtraAdultRate)
                    End If
                    .TextMatrix(1, 6) = 0
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = rsRateType!RateTypeID
                    .TextMatrix(.Rows - 1, 2) = rsRateType!RateType
                    If rsRateTemplates.RecordCount > 0 Then
                        .TextMatrix(.Rows - 1, 3) = toMoney(rsRateTemplates!RoomRate)
                        .TextMatrix(.Rows - 1, 4) = rsRateTemplates!NoofPerson
                        .TextMatrix(.Rows - 1, 5) = toMoney(rsRateTemplates!ExtraAdultRate)
                    End If
                    .TextMatrix(.Rows - 1, 6) = 0
                End If
            End With
           
            rsRateType.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 6
        'Set fixed cols
        If State = adStateEditMode Then
            Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionFree
            Grid.FixedCols = 1
        End If
    End If
    
    rsRateType.Close
    rsRateTemplates.Close
    
    Set rsRateType = Nothing
    Set rsRateTemplates = Nothing
End Sub

Private Sub txtAdults_GotFocus()
    HLText txtAdults
End Sub

Private Sub txtAdults_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

Private Sub txtAdults_Validate(Cancel As Boolean)
    txtAdults.Text = toMoney(txtAdults.Text)
End Sub

Private Sub txtChildrens_GotFocus()
    HLText txtChildrens
End Sub

Private Sub txtChildrens_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

Private Sub txtChildrens_Validate(Cancel As Boolean)
    txtChildrens.Text = toMoney(txtChildrens.Text)
End Sub

Private Sub txtNoofPerson_GotFocus()
    HLText txtNoofPerson
End Sub

Private Sub txtNoofPerson_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

Private Sub txtNoofPerson_Validate(Cancel As Boolean)
    txtNoofPerson.Text = toNumber(txtNoofPerson.Text)
End Sub

Private Sub txtRoomRate_GotFocus()
    HLText txtRoomRate
End Sub

Private Sub txtRoomRate_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

Private Sub txtRoomRate_Validate(Cancel As Boolean)
    txtRoomRate.Text = toMoney(txtRoomRate.Text)
End Sub