仪表数据管理.frm

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

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

Private Sub cmdQuery_Click()
   frmQueryYiBiao.Show
End Sub

Private Sub cmdSave_Click()
      '使用 k 变量简化代码
      If SSTab1.Tab = 0 Then
         k = 0
      ElseIf SSTab1.Tab = 1 Then
         k = 14
      ElseIf SSTab1.Tab = 2 Then
         k = 28
      End If
      
      '设置本月抄表日期和本月交费日期为当前日期
      Text1(11 + k).Text = Date
      Text1(12 + k).Text = Date
      '检测数据是否完整
      If Text1(0 + k).Text = "" Then
         MsgBox "仪表编号不可为空！", vbOKOnly + vbInformation, "注意"
         Text1(0 + k).SetFocus
         Exit Sub
      ElseIf Text1(1 + k).Text = "" Then
         MsgBox "住户姓名不可为空！", vbOKOnly + vbInformation, "注意"
         Text1(1 + k).SetFocus
         Exit Sub
      ElseIf Not (Val(Text1(3 + k).Text) < 3000 And Val(Text1(3 + k).Text) > 1900) Then
         MsgBox "年份输入不正确！", vbOKOnly + vbInformation, "注意"
         Text1(3 + k).SetFocus
         Exit Sub
      ElseIf Not (Val(Text1(4 + k).Text) >= 1 And Val(Text1(4 + k).Text) <= 12) Then
         MsgBox "月份书写不对，应为1－12的数字！", vbOKOnly + vbInformation, "注意"
         Text1(4 + k).SetFocus
         Exit Sub
      ElseIf IsNumeric(Text1(5 + k).Text) = False Then
         MsgBox "上月数据必须为数字！", vbOKOnly + vbInformation, "注意"
         Text1(5 + k).SetFocus
         Exit Sub
      ElseIf IsNumeric(Text1(6 + k).Text) = False Then
         MsgBox "本月数据必须为数字！", vbOKOnly + vbInformation, "注意"
         Text1(6 + k).SetFocus
         Exit Sub
      ElseIf IsNumeric(Text1(8 + k).Text) = False Then
         MsgBox "本月费用必须为数字！", vbOKOnly + vbInformation, "注意"
         Text1(8 + k).SetFocus
         Exit Sub
      ElseIf IsDate(Text1(10 + k).Text) = False Then
         MsgBox "上月抄表日期必须为这样的日期格式2003-7-25！", vbOKOnly + vbInformation, "注意"
         Text1(10 + k).SetFocus
         Exit Sub
      ElseIf IsDate(Text1(11 + k).Text) = False Then
         MsgBox "本月抄表日期必须为这样的日期格式2003-7-25！", vbOKOnly + vbInformation, "注意"
         Text1(11 + k).SetFocus
         Exit Sub
      ElseIf IsDate(Text1(12 + k).Text) = False Then
         MsgBox "本月交费日期必须为这样的日期格式2003-7-25！", vbOKOnly + vbInformation, "注意"
         Text1(12 + k).SetFocus
         Exit Sub
      End If
   '本月用量和本月费用通过计算得到
   Text1(7 + k).Text = Val(Text1(6 + k).Text) - Val(Text1(5 + k).Text)
   Text1(9 + k).Text = Val(Text1(7 + k).Text) * Val(Text1(8 + k).Text)
   '3个选项卡的保存代码
   If SSTab1.Tab = 0 Then
      '添加数据后保存
      If addw = 1 Then
         '检测房间编号这个主键是否已经在表中存在
         Dim rs_wcheck As New ADODB.Recordset
         Dim sqlwCheck As String
         sqlwCheck = "select * from Water where 仪表编号= '" & (Text1(0 + k).Text) & "'"
         rs_wcheck.Open sqlwCheck, conn, adOpenStatic, adLockOptimistic
         If Not rs_wcheck.EOF And Not rs_wcheck.BOF Then
            MsgBox "该仪表编号已经存在，请重填一个！", vbOKOnly + vbInformation, "注意"
            rs_wcheck.Close
            Text1(0 + k).SetFocus
            Text1(0 + k).Text = ""
            Exit Sub
         End If
         rs_wcheck.Close
         '主键不重复，可以加入表中
         rs_water.AddNew
         For i = 0 To 13
            rs_water.Fields(i) = Text1(i + k).Text
         Next i
         rs_water.Update
         '添加之后显示总共条数信息加 1
         Text2.Text = Val(Text2.Text) + 1
      '修改数据后的保存
      Else
         rs_water.Update
      End If
   ElseIf SSTab1.Tab = 1 Then
      '添加数据后保存
      If addp = 1 Then
         '检测房间编号这个主键是否已经在表中存在
         Dim rs_pcheck As New ADODB.Recordset
         Dim sqlpcheck As String
         sqlpcheck = "select * from Power where 仪表编号= '" & (Text1(0 + k).Text) & "'"
         rs_pcheck.Open sqlpcheck, conn, adOpenStatic, adLockOptimistic
         If Not rs_pcheck.EOF And Not rs_pcheck.BOF Then
            MsgBox "该仪表编号已经存在，请重填一个！", vbOKOnly + vbInformation, "注意"
            rs_pcheck.Close
            Text1(0 + k).SetFocus
            Text1(0 + k).Text = ""
            Exit Sub
         End If
         rs_pcheck.Close
         '主键不重复，可以加入表中
         rs_power.AddNew
         For i = 0 To 13
            rs_power.Fields(i) = Text1(i + k).Text
         Next i
         rs_power.Update
         '添加之后显示总共条数信息加 1
         Text2.Text = Val(Text2.Text) + 1
      '修改数据后的保存
      Else
         rs_power.Update
      End If
   ElseIf SSTab1.Tab = 2 Then
      '添加数据后保存
      If addg = 1 Then
         '检测房间编号这个主键是否已经在表中存在
         Dim rs_gcheck As New ADODB.Recordset
         Dim sqlgcheck As String
         sqlgcheck = "select * from Gas where 仪表编号= '" & (Text1(0 + k).Text) & "'"
         rs_gcheck.Open sqlgcheck, conn, adOpenStatic, adLockOptimistic
         If Not rs_gcheck.EOF And Not rs_gcheck.BOF Then
            MsgBox "该仪表编号已经存在，请重填一个！", vbOKOnly + vbInformation, "注意"
            rs_gcheck.Close
            Text1(0 + k).SetFocus
            Text1(0 + k).Text = ""
            Exit Sub
         End If
         rs_gcheck.Close
         '主键不重复，可以加入表中
         rs_gas.AddNew
         For i = 0 To 13
            rs_gas.Fields(i) = Text1(i + k).Text
         Next i
         rs_gas.Update
         '添加之后显示总共条数信息加 1
         Text2.Text = Val(Text2.Text) + 1
      '修改数据后的保存
      Else
         rs_gas.Update
      End If
   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 13
      Text1(i + k).Enabled = False
   Next i
   
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
   '设置显示记录条数的Text2不可写
   Text2.Enabled = False
   '显示水表选项卡
   If SSTab1.Tab = 0 Then
      '如果不是查询显示,则显示第一条记录
      '如果rs_wcount rs_water 当前状态是打开的，则先关闭之
      If rs_wcount.State = adStateOpen Then
         rs_wcount.Close
      End If
      If rs_water.State = adStateOpen Then
         rs_water.Close
      End If
      '计算总共数据条数
      '显示全表与查询表只是sql查询语句不同，因此如下设置
      If querywater = False Then
         sqlwcount = "select count(*) from Water"
      Else
         sqlwcount = "select count(*) from Water " & sqlYiBiao
      End If
      rs_wcount.Open sqlwcount, conn, adOpenStatic, adLockOptimistic
      If rs_wcount.EOF Then
         Text2.Text = 0
      Else
         Text2.Text = rs_wcount.Fields(0)
      End If
      If querywater = False Then
         sqlw = "select * from Water"
      Else
         sqlw = "select * from Water " & sqlYiBiao
      End If
      rs_water.CursorLocation = adUseClient
      rs_water.Open sqlw, conn, adOpenStatic, adLockOptimistic
      '如果当前水表中有数据则显示第一条数据
      If Not rs_water.EOF And Not rs_water.BOF Then
         For i = 0 To 13
            Text1(i).Text = rs_water.Fields(i)
         Next i
         '如果有记录，需要设置前翻后翻的按钮为可用
         cmdFirst.Enabled = True
         cmdPrev.Enabled = True
         cmdNext.Enabled = True
         cmdLast.Enabled = True
         cmdEdit.Enabled = True
         cmdDel.Enabled = True
      Else
         '如果是0条记录，需要设置前翻后翻的按钮为不可用
         cmdFirst.Enabled = False
         cmdPrev.Enabled = False
         cmdNext.Enabled = False
         cmdLast.Enabled = False
         cmdEdit.Enabled = False
         cmdDel.Enabled = False
         For i = 0 To 13
            Text1(i).Text = ""
         Next i
         If querywater = False Then
             MsgBox "该表中没有记录！", vbOKOnly + vbInformation, "注意"
         Else
             MsgBox "没有找到符合条件的记录", vbOKOnly + vbInformation, "注意"
         End If
      End If
      '设置各个text框不可写
      For i = 0 To 13
         Text1(i).Enabled = False
      Next i
   '点击电表选项卡
   ElseIf SSTab1.Tab = 1 Then
      '如果不是查询显示,则显示第一条记录
      '如果rs_pcount rs_power 当前状态是打开的，则先关闭之
      If rs_pcount.State = adStateOpen Then
         rs_pcount.Close
      End If
      If rs_power.State = adStateOpen Then
         rs_power.Close
      End If
      '计算总共数据条数
      If querypower = False Then
         sqlpcount = "select count(*) from Power"
      Else
         sqlpcount = "select count(*) from Power " & sqlYiBiao
      End If
      rs_pcount.Open sqlpcount, conn, adOpenStatic, adLockOptimistic
      If rs_pcount.EOF Then
         Text2.Text = 0
      Else
         Text2.Text = rs_pcount.Fields(0)
      End If
      If querypower = False Then
         sqlp = "select * from Power"
      Else
         sqlp = "select * from Power " & sqlYiBiao
      End If
      rs_power.CursorLocation = adUseClient
      rs_power.Open sqlp, conn, adOpenStatic, adLockOptimistic
      '如果当前水表中有数据则显示第一条数据
      If Not rs_power.EOF And Not rs_power.BOF Then
         For i = 14 To 27
            Text1(i).Text = rs_power.Fields(i - 14)
         Next i
         '如果有记录，需要设置前翻后翻的按钮为可用
         cmdFirst.Enabled = True
         cmdPrev.Enabled = True
         cmdNext.Enabled = True
         cmdLast.Enabled = True
         cmdEdit.Enabled = True
         cmdDel.Enabled = True
      Else
         '如果是0条记录，需要设置前翻后翻的按钮为不可用
         cmdFirst.Enabled = False
         cmdPrev.Enabled = False
         cmdNext.Enabled = False
         cmdLast.Enabled = False
         cmdEdit.Enabled = False
         cmdDel.Enabled = False
         For i = 14 To 27
            Text1(i).Text = ""
         Next i
         If querypower = False Then
             MsgBox "该表中没有记录！", vbOKOnly + vbInformation, "注意"
         Else
             MsgBox "没有找到符合条件的记录", vbOKOnly + vbInformation, "注意"
         End If
      End If
      '设置各个text框不可写
      For i = 14 To 27
         Text1(i).Enabled = False
      Next i
   '点击气表选项卡
   ElseIf SSTab1.Tab = 2 Then
      '如果不是查询显示,则显示第一条记录
      '如果rs_gcount rs_gas 当前状态是打开的，则先关闭之
      If rs_gcou