房产资料.frm

物业管理系统,VB做的,小试牛刀,不吝赐教

 '如果是从添加后取消
 If add = 1 Then
   '如果当前House表中有数据则显示第一条数据
   If Not rs_house.EOF And Not rs_house.BOF Then
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
   '如果没有数据，则显示空
   Else
      For i = 0 To 10
         Text1(i).Text = ""
      Next i
   End If
   
 '如果是修改后取消，则恢复到修改前的数据
 ElseIf add = 0 Then
   If rs_house.BOF = True And rs_house.EOF = True Then
      MsgBox "当前表中无记录！", vbOKOnly + vbInformation, "注意"
      For i = 0 To 10
         Text1(i).Text = ""
      Next i
      Exit Sub
   End If
   If rs_house.BOF = True Then
      rs_house.MoveFirst
   ElseIf rs_house.EOF = True Then
      rs_house.MoveLast
   End If
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
 End If
   '开始时设置各个text框不可写
   For i = 0 To 10
      Text1(i).Enabled = False
   Next i
   
   '先设置ComboBox的默认值及不可改
   Combo1.Enabled = False
End Sub

Private Sub cmdClose_Click()
   Unload Me
End Sub

Private Sub cmdDel_Click()
   '当单击删除记录时，需要弹出一个提示框，警告用户
   Dim answer As String
   answer = MsgBox("确定要删除吗？", vbYesNo, "")
   '确实删除
   If answer = vbYes Then
      rs_house.Delete  '删除当前记录
      rs_house.Update  '更新删除
      MsgBox "成功删除！", vbOKOnly + vbExclamation, ""
   Else
       Exit Sub
   End If
   '删除之后，显示总信息条数需要减 1
   Text2.Text = Val(Text2.Text) - 1
   '删除当前记录后，需要显示下一条记录，如果删除的是最后一条记录，则显示上一条记录
   '先移动rs_house记录到后一条
   rs_house.MoveNext
   If rs_house.EOF Then
      rs_house.MovePrevious
    '如果没有到记录首则显示该记录
    If Not rs_house.BOF Then
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
     '如果到记录首，则表格已经为空，置所有text框显示为空
    ElseIf rs_house.BOF Then
      For i = 0 To 10
         Text1(i).Text = ""
      Next i
    End If
   '如果删除的不是首尾记录，则显示当前记录即可
   Else
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
   End If
End Sub

Private Sub cmdEdit_Click()
   
   '设置除保存和取消按钮外的其他按钮不可用
   cmdAdd.Enabled = False
   cmdEdit.Enabled = False
   cmdDel.Enabled = False
   cmdSave.Enabled = True
   cmdCancel.Enabled = True
   cmdFirst.Enabled = False
   cmdPrev.Enabled = False
   cmdNext.Enabled = False
   cmdLast.Enabled = False
   cmdQuery.Enabled = False
   '需要设置除主键之外的text框可写
   For i = 1 To 10
      Text1(i).Enabled = True
   Next i
   '总价text框应设为不可写，自动计算总价
   Text1(7).Enabled = False
   Combo1.Enabled = True
   add = 0
End Sub

Private Sub cmdFirst_Click()
   '先移动rs_house记录到第一条
   rs_house.MoveFirst
   '同时需要设置相应按钮为不可用和不可用
   cmdPrev.Enabled = False
   cmdFirst.Enabled = False
   cmdNext.Enabled = True
   cmdLast.Enabled = True
   '如果已经是第一条记录，则提示用户
   If rs_house.BOF = True Then
      MsgBox "对不起，已经是第一条记录了！", vbOKOnly + vbInformation, "注意"
      Exit Sub
   '如果不是，则个数据表的记录位置移到第一条记录，并且显示之
   Else
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
   End If
End Sub

Private Sub cmdLast_Click()
   '移动rs_house记录到最后一条
   rs_house.MoveLast
   cmdFirst.Enabled = True
   cmdPrev.Enabled = True
   cmdNext.Enabled = False
   cmdLast.Enabled = False
    '如果已经是最后一条记录，则提示用户
   If rs_house.EOF = True Then
      MsgBox "对不起，已经是最后一条记录了！", vbOKOnly + vbInformation, "注意"
      Exit Sub
   '如果不是最后一条，则个数据表的记录位置移到后一条记录，并且显示之
   Else
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
   End If
End Sub

Private Sub cmdNext_Click()
   '先移动rs_house记录到后一条
   rs_house.MoveNext
   '设置前一条和第一条按钮可用
   cmdPrev.Enabled = True
   cmdFirst.Enabled = True
   '如果已经是最后一条记录，则提示用户
   If rs_house.EOF = True Then
      MsgBox "对不起，已经是最后一条记录了！", vbOKOnly + vbInformation, "注意"
      '并且设置“后一条”和最后一条按钮不可用
      cmdNext.Enabled = False
      cmdLast.Enabled = False
      Exit Sub
   '如果不是，则个数据表的记录位置移到后一条记录，并且显示之
   Else
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
   End If
End Sub

Private Sub cmdPrev_Click()
   '先移动rs_house记录到前一条
   rs_house.MovePrevious
   '设置后一条和最后一条按钮可用
   cmdNext.Enabled = True
   cmdLast.Enabled = True
   '如果已经是第一条记录，则提示用户
   If rs_house.BOF = True Then
      MsgBox "对不起，已经是第一条记录了！", vbOKOnly + vbInformation, "注意"
      '并且设置“前一条”和第一条按钮不可用
      cmdPrev.Enabled = False
      cmdFirst.Enabled = False
      Exit Sub
   '如果不是，则个数据表的记录位置移到前一条记录，并且显示之
   Else
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
   End If
End Sub

Private Sub cmdQuery_Click()
   frmQueryHouse.Show
End Sub

Private Sub cmdSave_Click()
   '检测数据是否完整
      If Text1(0).Text = "" Then
         MsgBox "房间编号不可为空！", vbOKOnly + vbInformation, "注意"
         Text1(0).SetFocus
         Exit Sub
      ElseIf Text1(1).Text = "" Then
         MsgBox "物业地址不可为空！", vbOKOnly + vbInformation, "注意"
         Text1(1).SetFocus
         Exit Sub
      ElseIf Text1(2).Text = "" Or IsNumeric(Text1(2).Text) = False Then
         MsgBox "建筑面积应为数字！", vbOKOnly + vbInformation, "注意"
         Text1(2).SetFocus
         Exit Sub
      ElseIf Text1(3).Text = "" Or IsNumeric(Text1(3).Text) = False Then
         MsgBox "使用面积应为数字！", vbOKOnly + vbInformation, "注意"
         Text1(3).SetFocus
         Exit Sub
      ElseIf Val(Text1(2).Text) < Val(Text1(3).Text) Then
         MsgBox "使用面积不可大于建筑面积！", vbOKOnly + vbInformation, "注意"
         Text1(3).SetFocus
         Exit Sub
      ElseIf Text1(6).Text = "" Or IsNumeric(Text1(6).Text) = False Then
         MsgBox "单价应为数字！", vbOKOnly + vbInformation, "注意"
         Text1(6).SetFocus
         Exit Sub
      ElseIf Combo1.ListIndex = 0 And (Text1(8).Text = "" Or Text1(9).Text = "") Then
         MsgBox "已售房子的买主编号和姓名不能为空！", vbOKOnly + vbInformation, "注意"
         Text1(8).SetFocus
         Exit Sub
      End If
   '总价等于单价乘以面积
   Text1(7).Text = Val(Text1(3)) * Val(Text1(6))
   '添加数据后保存
   If add = 1 Then
      '检测房间编号这个主键是否已经在表中存在
      Dim rs_check As New ADODB.Recordset
      Dim sqlCheck As String
      sqlCheck = "select * from House where 房间编号= '" & (Text1(0).Text) & "'"
      rs_check.Open sqlCheck, conn, adOpenStatic, adLockOptimistic
      If Not rs_check.EOF And Not rs_check.BOF Then
         MsgBox "该房间编号已经存在，请重填一个！", vbOKOnly + vbInformation, "注意"
         rs_check.Close
         Text1(0).SetFocus
         Text1(0).Text = ""
         Exit Sub
      End If
      rs_check.Close
      '主键不重复，可以加入表中
      rs_house.AddNew
      For i = 0 To 10
         rs_house.Fields(i) = Text1(i).Text
      Next i
      rs_house.Fields(11) = Combo1.Text
      rs_house.Update
      '添加之后显示总共条数信息加 1
      Text2.Text = Val(Text2.Text) + 1
   '修改数据后的保存
   Else
      rs_house.Update
   End If
   MsgBox "保存数据成功！", vbOKOnly + vbInformation, "祝贺"
   '保存后需要设置其他按钮可用，以及各个text框不可写
   cmdAdd.Enabled = True
   cmdEdit.Enabled = True
   cmdDel.Enabled = True
   cmdSave.Enabled = False
   cmdCancel.Enabled = False
   cmdFirst.Enabled = True
   cmdPrev.Enabled = True
   cmdNext.Enabled = True
   cmdLast.Enabled = True
   cmdQuery.Enabled = True
   For i = 0 To 10
      Text1(i).Enabled = False
   Next i
   Combo1.Enabled = False
End Sub

Private Sub cmdTable_Click()
   frmTableHouse.Show
End Sub

Private Sub Form_activate()
   Dim X0 As Long
   Dim Y0 As Long
   '让窗体居中
   X0 = Screen.Width
   Y0 = Screen.Height
   X0 = (X0 - Me.Width) / 2
   Y0 = (Y0 - Me.Height) / 2
   Me.Move X0, Y0
   cmdSave.Enabled = False
   cmdCancel.Enabled = False
   '总价text框应设为不可写，自动计算总价
   Text1(7).Enabled = False
   '设置显示记录条数的Text2不可写
   Text2.Enabled = False
   Combo1.ListIndex = 0
 '如果不是查询显示,则显示第一条记录
 If queryhouse = False Then
   '在from_laod()方法中打开住户信息表
   '如果rs_count rs_house 当前状态是打开的，则先关闭之
   If rs_count.State = adStateOpen Then
      rs_count.Close
   End If
   If rs_house.State = adStateOpen Then
      rs_house.Close
   End If
   '计算总共数据条数
   Dim sqlcount As String
   sqlcount = "select count(*) from House"
   rs_count.Open sqlcount, conn, adOpenStatic, adLockOptimistic
   If rs_count.EOF Then
      Text2.Text = 0
   Else
      Text2.Text = rs_count.Fields(0)
   End If
   Dim sql_House As String
   sql_House = "select * from House"
   rs_house.CursorLocation = adUseClient
   rs_house.Open sql_House, conn, adOpenStatic, adLockOptimistic
   
    '如果当前House表中有数据则显示第一条数据
   If Not rs_house.EOF And Not rs_house.BOF Then
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
         
   End If
 '如果是查询显示则相应sql语句为查询语句
 ElseIf queryhouse = True Then
   rs_house.Close
   rs_count.Close
   '计算找到的条数
   Dim sqlquerycount As String
   sqlquerycount = "select count(*) from House " & sqlqh
   rs_count.Open sqlquerycount, conn, adOpenStatic, adLockOptimistic
   If rs_count.EOF Then
      Text2.Text = 0
   Else
      Text2.Text = rs_count.Fields(0)
   End If
   Dim sqlquery As String
   sqlquery = "select * from House " & sqlqh
   rs_house.CursorLocation = adUseClient
   rs_house.Open sqlquery, conn, adOpenStatic, adLockOptimistic
   '没有找到符合条件的记录
   If rs_house.EOF Then
      MsgBox "没有找到符合条件的记录", vbOKOnly + vbInformation, "注意"
      For i = 0 To 10
         Text1(i).Text = ""
      Next i
      '如果是0条记录，需要设置前翻后翻的按钮为不可用
      cmdFirst.Enabled = False
      cmdPrev.Enabled = False
      cmdNext.Enabled = False
      cmdLast.Enabled = False
      cmdEdit.Enabled = False
      cmdDel.Enabled = False
   '找到符合条件的记录
   Else
      '如果有记录，需要设置前翻后翻的按钮为可用
      cmdFirst.Enabled = True
      cmdPrev.Enabled = True
      cmdNext.Enabled = True
      cmdLast.Enabled = True
      cmdEdit.Enabled = True
      cmdDel.Enabled = True
      For i = 0 To 10
         Text1(i).Text = rs_house.Fields(i)
      Next i
      If rs_house.Fields(11) = "是" Then
         Combo1.ListIndex = 0
      Else
         Combo1.ListIndex = 1
      End If
   End If
 End If
   '设置各个text框不可写
   For i = 0 To 10
      Text1(i).Enabled = False
   Next i
   '先设置ComboBox的默认值及不可改
   Combo1.Enabled = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
   rs_house.Close
   rs_count.Close
   '关闭时还需要设置queryhouse为false
   queryhouse = False
End Sub