frmclient.frm

本程序用VB+SQL 2000实现,可以实现小区的住户资料,仪表资料,住户投宿,住户报修等数据的查询,修改,删除,统计和以报表形式打印输出.

Private Sub cmdTable_Click()
   frmTableClient.Show
End Sub

Private Sub Command1_Click()
DataReport1.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
   '设置显示记录条数的Text2不可写
   Text2.Enabled = False
   Combo1.ListIndex = 0
   '设置网格不可写
   DataGrid1.AllowAddNew = False
   DataGrid1.AllowDelete = False
   DataGrid1.AllowUpdate = False
   
   Select Case SSTab1.Tab
       Case 0
           '如果不是查询显示,则显示第一条记录
           If query = False Then
               '在from_laod()方法中打开住户信息表
               '如果rs_count rs_client 当前状态是打开的，则先关闭之
               If rs_count.State = adStateOpen Then
                  rs_count.Close
               End If
               If rs_client.State = adStateOpen Then
                  rs_client.Close
               End If
               '计算总共数据条数
               Dim sqlcount As String
               sqlcount = "select count(*) from Client"
               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_client As String
               sql_client = "select * from Client"
               rs_client.CursorLocation = adUseClient
               rs_client.Open sql_client, conn, adOpenStatic, adLockOptimistic
   
                '如果当前client表中有数据则显示第一条数据
               If Not rs_client.EOF And Not rs_client.BOF Then
                  For i = 0 To 14
                     Text1(i).Text = rs_client.Fields(i)
                  Next i
                  If rs_client.Fields(15) = "男" Then
                     Combo1.ListIndex = 0
                  Else
                     Combo1.ListIndex = 1
                  End If
                     
               End If
            '如果是查询显示则相应sql语句为查询语句
           ElseIf query = True Then
               If rs_client.State = adStateOpen Then
                   rs_client.Close
               End If
               If rs_count.State = adStateOpen Then
                   rs_count.Close
               End If
               '计算找到的条数
               Dim sqlquerycount As String
               sqlquerycount = "select count(*) from Client " & sqlqc
               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 Client " & sqlqc
               rs_client.CursorLocation = adUseClient
               rs_client.Open sqlquery, conn, adOpenStatic, adLockOptimistic
               '没有找到符合条件的记录
               If rs_client.EOF Then
                  MsgBox "没有找到符合条件的记录", vbOKOnly + vbInformation, "注意"
                  For i = 0 To 14
                     Text1(i).Text = ""
                  Next i
                  cmdEdit.Enabled = False
                  cmdDel.Enabled = False
                  cmdSave.Enabled = False
                  cmdCancel.Enabled = False
                  cmdFirst.Enabled = False
                  cmdPrev.Enabled = False
                  cmdNext.Enabled = False
                  cmdLast.Enabled = False
                  cmdTable.Enabled = False
               '找到符合条件的记录
               Else
                  cmdEdit.Enabled = True
                  cmdDel.Enabled = True
                  cmdSave.Enabled = True
                  cmdCancel.Enabled = True
                  cmdFirst.Enabled = True
                  cmdPrev.Enabled = True
                  cmdNext.Enabled = True
                  cmdLast.Enabled = True
                  cmdTable.Enabled = True
                  For i = 0 To 14
                     Text1(i).Text = rs_client.Fields(i)
                  Next i
                  If rs_client.Fields(15) = "男" Then
                     Combo1.ListIndex = 0
                  Else
                     Combo1.ListIndex = 1
                  End If
               End If
           End If
           '设置各个text框不可写
           For i = 0 To 14
               Text1(i).Enabled = False
           Next i
           '先设置ComboBox的默认值及不可改
           Combo1.Enabled = False
       Case 1
           '需要设置除关闭按钮外的所有按钮不可用
           cmdAdd.Enabled = False
           cmdEdit.Enabled = False
           cmdDel.Enabled = False
           cmdSave.Enabled = False
           cmdCancel.Enabled = False
           cmdFirst.Enabled = False
           cmdPrev.Enabled = False
           cmdNext.Enabled = False
           cmdLast.Enabled = False
           cmdQuery.Enabled = False
           cmdTable.Enabled = False
            '设置显示记录条数的Frame为不可见
           Frame1(0).Visible = False
           Dim sqlwy As String
           sqlwy = "select * from WuYeFee where 住户姓名 = '" & Text1(5).Text & " ' order by 年份,月份"
           rs_wuye.CursorLocation = adUseClient
           rs_wuye.Open sqlwy, conn, adOpenStatic, adLockOptimistic
           Set DataGrid1.DataSource = rs_wuye
           DataGrid1.Refresh
           
       Case 2
           '需要设置除关闭按钮外的所有按钮不可用
           cmdAdd.Enabled = False
           cmdEdit.Enabled = False
           cmdDel.Enabled = False
           cmdSave.Enabled = False
           cmdCancel.Enabled = False
           cmdFirst.Enabled = False
           cmdPrev.Enabled = False
           cmdNext.Enabled = False
           cmdLast.Enabled = False
           cmdQuery.Enabled = False
           cmdTable.Enabled = False
            '设置显示记录条数的Frame为不可见
           Frame1(0).Visible = False
           Dim sqlw As String
           Dim sqlp As String
           Dim sqlg As String
           '显示水表数据
           sqlw = "select 年份,月份,本月用量,本月费用,本月数据,本月抄表日期,本月交费日期" & _
                 " from Water where 住户姓名 = '" & Text1(5).Text & "'"
           rs_water.CursorLocation = adUseClient
           rs_water.Open sqlw, conn, adOpenStatic, adLockOptimistic
           Set DataGrid2.DataSource = rs_water
           DataGrid2.Refresh
           '显示电表数据
           sqlp = "select 年份,月份,本月用量,本月费用,本月数据,本月抄表日期,本月交费日期" & _
                 " from Power where 住户姓名 = '" & Text1(5).Text & "'"
           rs_power.CursorLocation = adUseClient
           rs_power.Open sqlp, conn, adOpenStatic, adLockOptimistic
           Set DataGrid3.DataSource = rs_power
           DataGrid3.Refresh
           '显示气表数据
           sqlg = "select 年份,月份,本月用量,本月费用,本月数据,本月抄表日期,本月交费日期" & _
                 " from Gas where 住户姓名 = '" & Text1(5).Text & "'"
           rs_gas.CursorLocation = adUseClient
           rs_gas.Open sqlg, conn, adOpenStatic, adLockOptimistic
           Set DataGrid4.DataSource = rs_gas
           DataGrid4.Refresh
           
   End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
  
   If rs_wuye.State = adStateOpen Then
      rs_wuye.Close
   End If
   If rs_water.State = adStateOpen Then
      rs_water.Close
   End If
   If rs_power.State = adStateOpen Then
      rs_power.Close
   End If
   If rs_gas.State = adStateOpen Then
      rs_gas.Close
   End If
   '关闭窗口时还需设置query为false,
   query = False
End Sub

Private Sub SSTab1_Click(PreviousTab As Integer)
   Select Case SSTab1.Tab
       Case 0
           '需要设置所有按钮可用
           cmdAdd.Enabled = True
           cmdEdit.Enabled = True
           cmdDel.Enabled = True
           cmdSave.Enabled = True
           cmdCancel.Enabled = True
           cmdFirst.Enabled = True
           cmdPrev.Enabled = True
           cmdNext.Enabled = True
           cmdLast.Enabled = True
           cmdQuery.Enabled = True
           cmdTable.Enabled = True
           '打开显示总记录条数的显示
           Frame1(0).Visible = True
           '如果不是查询显示,则显示第一条记录
           If query = False Then
               '在from_laod()方法中打开住户信息表
               '如果rs_count rs_client 当前状态是打开的，则先关闭之
               If rs_count.State = adStateOpen Then
                  rs_count.Close
               End If
               If rs_client.State = adStateOpen Then
                  rs_client.Close
               End If
               '计算总共数据条数
               Dim sqlcount As String
               sqlcount = "select count(*) from Client"
               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_client As String
               sql_client = "select * from Client"
               rs_client.CursorLocation = adUseClient
               rs_client.Open sql_client, conn, adOpenStatic, adLockOptimistic
   
                '如果当前client表中有数据则显示第一条数据
               If Not rs_client.EOF And Not rs_client.BOF Then
                  For i = 0 To 14
                     Text1(i).Text = rs_client.Fields(i)
                  Next i
                  If rs_client.Fields(15) = "男" Then
                     Combo1.ListIndex = 0
                  Else
                     Combo1.ListIndex = 1
                  End If
                     
               End If
            '如果是查询显示则相应sql语句为查询语句
           ElseIf query = True Then
               If rs_client.State = adStateOpen Then
                   rs_client.Close
               End If
               If rs_count.State = adStateOpen Then
                   rs_count.Close
               End If
               '计算找到的条数
               Dim sqlquerycount As String
               sqlquerycount = "select count(*) from Client " & sqlqc
               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 Client " & sqlqc
               rs_client.CursorLocation = adUseClient
               rs_client.Open sqlquery, conn, adOpenStatic, adLockOptimistic
               '没有找到符合条件的记录
               If rs_client.EOF Then
                  MsgBox "没有找到符合条件的记录", vbOKOnly + vbInformation, "注意"
                  For i = 0 To 14
                     Text1(i).Text = ""
                  Next i
                  '如果是0条记录，需要设置前翻后翻的按钮为不可用
                  cmdEdit.Enabled = False
                  cmdDel.Enabled = False
                  cmdSave.Enabled = False
                  cmdCancel.Enabled = False
                  cmdFirst.Enabled = False
                  cmdPrev.Enabled = False
                  cmdNext.Enabled = False
                  cmdLast.Enabled = False
                  cmdTable.Enabled = False
               '找到符合条件的记录
               Else
                  cmdEdit.Enabled = True
                  cmdDel.Enabled = True
                  cmdSave.Enabled = True
                  cmdCancel.Enabled = True
                  cmdFirst.Enabled = True
                  cmdPrev.Enabled = True
                  cmdNext.Enabled = True
                  cmdLast.Enabled = True
                  cmdTable.Enabled = True
                  For i = 0 To 14
                     Text1(i).Text = rs_client.Fields(i)
                  Next i
                  If rs_client.Fields(15) = "男" Then
                     Combo1.ListIndex = 0
                  Else
                     Combo1.ListIndex = 1
                  End If
               End If
           End If
           '设置各个text框不可写
           For i = 0 To 14
               Text1(i).Enabled = False
           Next i
           '先设置ComboBox的默认值及不可改
           Combo1.Enabled = False
       Case 1
           '需要设置除关闭按钮外的所有按钮不可用
           cmdAdd.Enabled = False
           cmdEdit.Enabled = False
           cmdDel.Enabled = False
           cmdSave.Enabled = False
           cmdCancel.Enabled = False
           cmdFirst.Enabled = False
           cmdPrev.Enabled = False
           cmdNext.Enabled = False
           cmdLast.Enabled = False
           cmdQuery.Enabled = False
           cmdTable.Enabled = False
            '设置显示记录条数的Frame为不可见
           Frame1(0).Visible = False
           If rs_wuye.State = adStateOpen Then
              rs_wuye.Close
           End If
           Dim sqlwy As String
           sqlwy = "select * from WuYeFee where 住户姓名 = '" & Text1(5).Text & "'order by 年份,月份"
           rs_wuye.CursorLocation = adUseClient
           rs_wuye.Open sqlwy, conn, adOpenStatic, adLockOptimistic
           Set DataGrid1.DataSource = rs_wuye
           DataGrid1.Refresh
           
       Case 2
           '需要设置除关闭按钮外的所有按钮不可用
           cmdAdd.Enabled = False
           cmdEdit.Enabled = False
           cmdDel.Enabled = False
           cmdSave.Enabled = False
           cmdCancel.Enabled = False
           cmdFirst.Enabled = False
           cmdPrev.Enabled = False
           cmdNext.Enabled = False
           cmdLast.Enabled = False
           cmdQuery.Enabled = False
           cmdTable.Enabled = False
           '设置显示记录条数的Frame为不可见
           Frame1(0).Visible = False
           Dim sqlw As String
           Dim sqlp As String
           Dim sqlg As String
           '显示水表数据
           sqlw = "select 年份,月份,本月用量,本月费用,本月数据,本月抄表日期,本月交费日期" & _
                 " from Water where 住户姓名 = '" & Text1(5).Text & "'"
           If rs_water.State = adStateOpen Then
              rs_water.Close
           End If
           rs_water.CursorLocation = adUseClient
           rs_water.Open sqlw, conn, adOpenStatic, adLockOptimistic
           Set DataGrid2.DataSource = rs_water
           DataGrid2.Refresh
           '显示电表数据
           sqlp = "select 年份,月份,本月用量,本月费用,本月数据,本月抄表日期,本月交费日期" & _
                 " from Power where 住户姓名 = '" & Text1(5).Text & "'"
           If rs_power.State = adStateOpen Then
              rs_power.Close
           End If
           rs_power.CursorLocation = adUseClient
           rs_power.Open sqlp, conn, adOpenStatic, adLockOptimistic
           Set DataGrid3.DataSource = rs_power
           DataGrid3.Refresh
           '显示气表数据
           sqlg = "select 年份,月份,本月用量,本月费用,本月数据,本月抄表日期,本月交费日期" & _
                 " from Gas where 住户姓名 = '" & Text1(5).Text & "'"
           If rs_gas.State = adStateOpen Then
              rs_gas.Close
           End If
           rs_gas.CursorLocation = adUseClient
           rs_gas.Open sqlg, conn, adOpenStatic, adLockOptimistic
           Set DataGrid4.DataSource = rs_gas
           DataGrid4.Refresh
           
   End Select
End Sub