fmopenroom1.frm

<Visual Basic 数据库开发实例精粹（第二版）>一书首先介绍了Visual Basic(简称VB)开发的技巧和重点技术

         Caption         =   "房间类型："
         Height          =   495
         Left            =   480
         TabIndex        =   10
         Top             =   1000
         Width           =   975
      End
      Begin VB.Label Label10 
         Caption         =   "开房日期:"
         Height          =   285
         Left            =   480
         TabIndex        =   9
         Top             =   2670
         Width           =   975
      End
      Begin VB.Label Label11 
         Caption         =   "备   注："
         Height          =   375
         Left            =   480
         TabIndex        =   8
         Top             =   3960
         Width           =   855
      End
   End
   Begin VB.Label LabelMemberName 
      Height          =   375
      Left            =   5400
      TabIndex        =   34
      Top             =   240
      Width           =   1095
   End
   Begin VB.Label Label15 
      Caption         =   "会员姓名:"
      Height          =   375
      Left            =   4200
      TabIndex        =   33
      Top             =   240
      Width           =   975
   End
   Begin VB.Label Label14 
      Caption         =   "会员编号:"
      Height          =   375
      Left            =   1800
      TabIndex        =   31
      Top             =   240
      Width           =   975
   End
End
Attribute VB_Name = "FMOpenRoom1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim sRoomID As String

Function ShowRoomInfo()
Dim RoomQuery As New ADODB.Recordset
Dim Str As String

    sRoomID = Trim(Me.CombRID.Text)
    Str = "select RoomTypeName,RoomPrice from RoomInfo where RoomID='" & sRoomID & "'"
    RoomQuery.Open Str, DBCnn, adOpenStatic, adLockOptimistic
    
    Me.CombRType.Text = RoomQuery.Fields("RoomTypeName").Value
    Me.CombRPrice.Text = RoomQuery.Fields("RoomPrice").Value
    
    RoomQuery.Close
    'Adodc1.Refresh
End Function

Private Sub Check1_Click()
    Me.Label14.Visible = True
    Me.CombMemberID.Visible = True
    Me.Label15.Visible = True
    Me.LabelMemberName.Visible = True
End Sub

Private Sub CombMemberID_Click()
Dim ReadMemberN As New ADODB.Recordset
Dim Str As String
Dim sMemberID As String

    sMemberID = Trim(Me.CombMemberID.Text)
    Str = "select MemberName,Tel2 from MemberInfo where MemberID='" & sMemberID & "'"
    ReadMemberN.Open Str, DBCnn, adOpenStatic, adLockOptimistic
    Me.LabelMemberName.Caption = ReadMemberN.Fields("MemberName").Value
    Me.TextClientName.Text = ReadMemberN.Fields("MemberName").Value
    Me.TextClientTel.Text = ReadMemberN.Fields("Tel2").Value
    ReadMemberN.Close
    
End Sub
Private Sub CombRID_Click()
Dim ReadRoomID As New ADODB.Recordset
Dim Str As String
    Str = "select RoomTypeName,RoomPrice from RoomInfo where RoomID='" & Trim(Me.CombRID.Text) & "'"
    ReadRoomID.Open Str, DBCnn, adOpenStatic, adLockOptimistic
        Me.CombRType.Text = ReadRoomID.Fields("RoomTypeName").Value
        Me.CombRPrice.Text = ReadRoomID.Fields("RoomPrice").Value
    ReadRoomID.Close
    
End Sub

Private Sub ComdCancel_Click()
    Me.TextCount.Text = ""
    Me.TextClientName.Text = ""
    Me.TextClientTel.Text = ""
    Me.CombPayMode.Text = ""
    Me.TextPrepay.Text = ""
    Me.CombWaitor.Text = ""
    Me.TextRemake.Text = ""
    
End Sub

Private Sub ComdOK_Click()
Dim DelDestine As New ADODB.Recordset
Dim sDD As String
Dim Str As String
Dim SqlStr As String
Dim a As String
Dim b As String
Dim sMemberID As String
Dim sTime As String


    sRoomID = Trim(Me.CombRID.Text)
    If Trim(Me.TextCount.Text) = "" Then
        a = "0"
    Else
        a = Trim(Me.TextCount.Text)
    End If
    If Trim(Me.TextPrepay.Text) = "" Then
        b = "0"
    Else
        b = Trim(Me.TextPrepay.Text)
    End If
    If Trim(Me.CombMemberID.Text) = "" Then
        sMemberID = "000"
    Else
        sMemberID = Trim(Me.CombMemberID.Text)
    End If
    sTime = Left(Now, 10) & " " & Trim(Me.TextORTime.Text)
    SqlStr = "insert into RoomConsumed(RoomConsumedID,RoomID," & _
             "RoomType,RoomPrice,ClientCount,ClientName,OpenTime," & _
             "ClientTel,Prepay,PayMode,OpenDate,Agio,Remake,MemberID)" & _
             " values ('" & Trim(Me.TextORID.Text) & "'" & _
             ",'" & Trim(Me.CombRID.Text) & "'" & _
             ",'" & Trim(Me.CombRType.Text) & "'" & _
             "," & Me.CombRPrice.Text & "," & a & "" & _
             ",'" & Trim(Me.TextClientName.Text) & "'" & _
             ",'" & sTime & "','" & Trim(Me.TextClientTel.Text) & "'" & _
             "," & b & ",'" & Trim(Me.CombPayMode.Text) & "'" & _
             ",'" & Trim(Me.TextORDate.Text) & "'" & _
             ",'" & Trim(Me.CombAgio.Text) & "'" & _
             ",'" & Me.TextRemake.Text & "','" & sMemberID & "');"
    
    DBCnn.Execute SqlStr
    Str = "update RoomInfo set RoomState='占用' where RoomID='" & sRoomID & "'"
    DBCnn.Execute Str
    
    MsgBox "已经开房!"
    
    
    
    '判断是否预定,若预定从预定单中删除此记录
Dim ReadDestine As New ADODB.Recordset
Dim sDestine As String
    sDestine = "select * from DestineInfo where RoomID='" & Trim(Me.CombRID.Text) & "'"
    ReadDestine.Open sDestine, DBCnn, adOpenStatic, adLockOptimistic
    If ReadDestine.EOF Then
        ReadDestine.Close
        
        
    Else
    
        sDD = "select * from DestineInfo where RoomID='" & Trim(Me.CombRID.Text) & "'"
        DelDestine.Open sDD, DBCnn, adOpenStatic, adLockOptimistic
        DelDestine.Delete
        DelDestine.Close
        ReadDestine.Close
    End If
    Unload Me
End Sub

Private Sub ComdZhu_Click()
Unload Me
End Sub

Private Sub Form_Load()

Dim DBStr As String
Dim AddRCID As New ADODB.Recordset
Dim i As Integer
Dim sRoomConsumedID As String
'Dim sOpenRoomTime As String

    Me.Top = (Screen.Height - Me.Height) / 2    '垂直方向居中
    Me.Left = (Screen.Width - Me.Height) / 2    '水平方向居中
    
     '开房时间
    Me.TextORTime.Text = Right(Now, 8)
    'sOpenRoomTime = Now
    '开房日期
    Me.TextORDate.Text = Date
    '会员
    Me.Label14.Visible = False
    Me.CombMemberID.Visible = False
    Me.Label15.Visible = False
    Me.LabelMemberName.Visible = False
    
        
    '自动生成开房单编号
    DBStr = "select RoomConsumedID from RoomConsumed"
    AddRCID.CursorType = adOpenStatic
    AddRCID.CursorLocation = adUseClient
    AddRCID.Open DBStr, DBCnn, adOpenForwardOnly, adLockOptimistic
    
    If AddRCID.EOF Then
        sRoomConsumedID = "0001"
        Me.TextORID.Text = sRoomConsumedID
    Else
        AddRCID.MoveLast
        i = AddRCID.Fields("RoomConsumedID").Value + 1
        If i < 10 Then
            Me.TextORID.Text = "000" & i
        ElseIf i >= 10 & i < 100 Then
            Me.TextORID.Text = "00" & i
        End If
        
    End If
    
    AddRCID.Close
    'Me.TextORID.Enabled = False

    

    
'判断是否预定
Dim ReadDestine As New ADODB.Recordset
Dim sDestine As String
    sDestine = "select * from DestineInfo where RoomID='" & Trim(Me.CombRID.Text) & "'"
    ReadDestine.Open sDestine, DBCnn, adOpenStatic, adLockOptimistic
    If ReadDestine.EOF Then
    
'确定房间编号
Dim iRoomID As Integer
Dim strRoomID As String
Dim iRow, iCol As Integer

    iRow = Main.MSFlexGrid1.Row
    iCol = Main.MSFlexGrid1.Col
    iRoomID = 10 * iRow + iCol + 1
    If iRoomID < 10 Then
        strRoomID = "0" & iRoomID
    ElseIf iRoomID < 100 And iRoomID >= 10 Then
        strRoomID = iRoomID
    End If
    FMOpenRoom1.CombRID.Text = strRoomID
    
    
    '从数据库中读取数据写入房间编号下拉列表框
Dim adoRs As New ADODB.Recordset
    DBStr = "select RoomID from RoomInfo"
    adoRs.Open DBStr, DBCnn, adOpenStatic, adLockReadOnly
    If adoRs.RecordCount > 0 Then
        adoRs.MoveFirst
        Do While Not adoRs.EOF
                CombRID.AddItem adoRs.Fields(0).Value
                adoRs.MoveNext
        Loop
    End If
    adoRs.Close
    Set adoRs = Nothing
    
    
    '从数据库中读取数据写入会员编号下拉列表框
Dim adoMs As New ADODB.Recordset
    DBStr = "select MemberID from MemberInfo"
    adoMs.Open DBStr, DBCnn, adOpenStatic, adLockReadOnly
    If adoMs.RecordCount > 0 Then
        adoMs.MoveFirst
        adoMs.MoveNext
        Do While Not adoMs.EOF
                CombMemberID.AddItem adoMs.Fields(0).Value
                adoMs.MoveNext
        Loop
    End If
    adoMs.Close
    Set adoMs = Nothing
    
        'MsgBox "该预定单不存在!"
        ReadDestine.Close
        'Exit Sub
    Else

        Me.TextCount.Text = ReadDestine.Fields("ClientCount").Value
        Me.TextClientName.Text = ReadDestine.Fields("ClientName").Value
        Me.TextClientTel.Text = ReadDestine.Fields("ClientTel").Value
        Me.TextRemake.Text = ReadDestine.Fields("Remake").Value
        Me.CombMemberID.Text = ReadDestine.Fields("MemberID").Value
        
        ReadDestine.Close
    End If
    
    
End Sub