frmadminzujin.frm

本章示例使用的是Windows2000 Professional版的操作系统

   If rs_zujin.EOF Then
      rs_zujin.MovePrevious
    '如果没有到记录首则显示该记录
    If Not rs_zujin.BOF Then
      For i = 0 To 8
         If IsNull(rs_zujin.Fields(i)) Then
            Text1(i).Text = ""
         Else
            Text1(i).Text = rs_zujin.Fields(i)
         End If
      Next i
     '如果到记录首，则表格已经为空，置所有text框显示为空,设置前后翻滚按钮为不可用
    ElseIf rs_zujin.BOF Then
      MsgBox "当前表格已为空！", vbOKOnly + vbInformation, "注意"
      For i = 0 To 8
         Text1(i).Text = ""
      Next i
      cmdFirst.Enabled = False
      cmdPrev.Enabled = False
      cmdNext.Enabled = False
      cmdLast.Enabled = False
      cmdDel.Enabled = False
      cmdEdit.Enabled = False
      cmdSave.Enabled = False
      cmdQuery.Enabled = False
    End If
   '如果删除的不是首尾记录，则显示当前记录即可
   Else
      For i = 0 To 8
         If IsNull(rs_zujin.Fields(i)) Then
            Text1(i).Text = ""
         Else
            Text1(i).Text = rs_zujin.Fields(i)
         End If
      Next i
      
   End If
End Sub

Private Sub cmdEdit_Click()
   
   '设置除保存和取消按钮外的其他按钮不可用
   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 8
      Text1(i).Enabled = True
   Next i
End Sub

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

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

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

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

Private Sub cmdQuery_Click()
   frmQueryZuJin.Show
End Sub

Private Sub cmdSave_Click()
   '先检测数据完整性
   If Text1(0).Text = "" Then
      MsgBox "收费编号不可为空！", vbOKOnly + vbInformation, "注意"
      Text1(0).SetFocus
      Exit Sub
   End If
   If Text1(1).Text = "" Or IsNumeric(Text1(1).Text) = False Then
      MsgBox "租金年份应为数字！", vbOKOnly + vbInformation, "注意"
      Text1(1).SetFocus
      Exit Sub
   End If
   If Text1(2).Text = "" Or IsNumeric(Text1(2).Text) = False Or Val(Text1(2).Text) < 1 Or Val(Text1(2).Text) > 12 Then
      MsgBox "租金月份应为1－12的数字！", vbOKOnly + vbInformation, "注意"
      Text1(2).SetFocus
      Exit Sub
   End If
   If Text1(3).Text = "" Or IsNumeric(Text1(3).Text) = False Then
      MsgBox "应交租金应为数字！", vbOKOnly + vbInformation, "注意"
      Text1(3).SetFocus
      Exit Sub
   End If
   If Text1(4).Text = "" Or IsNumeric(Text1(4).Text) = False Then
      MsgBox "已交租金应为数字！", vbOKOnly + vbInformation, "注意"
      Text1(4).SetFocus
      Exit Sub
   End If
   If Text1(6).Text = "" Then
      MsgBox "客户姓名不可为空！", vbOKOnly + vbInformation, "注意"
      Text1(6).SetFocus
      Exit Sub
   End If
   If Text1(7).Text = "" Or IsDate(Text1(7).Text) = False Then
      MsgBox "收费日期应为这样的日期格式：2003-8-3！", vbOKOnly + vbInformation, "注意"
      Text1(7).SetFocus
      Exit Sub
   End If
   '还需要检测客户姓名是否存在
   sqlclient = "select * from Contract where 客户姓名 = '" & Text1(6).Text & "'"
   rs_client.Open sqlclient, conn, adOpenStatic, adLockOptimistic
   If rs_client.EOF = True Then
      MsgBox "该客户姓名在合同表中不存在！", vbOKOnly + vbInformation, "注意"
      rs_client.Close
      Text1(6).SetFocus
      Exit Sub
   End If
   rs_client.Close
   
   '计算欠费金额
   Text1(5).Text = Val(Text1(3).Text) - Val(Text1(4).Text)
   
   '修改租金收费表
   If rs_zujin.State = adStateOpen Then
      rs_zujin.Close
   End If
   sqlzujin = "select * from ZuJin "
   rs_zujin.Open sqlzujin, conn, adOpenStatic, adLockOptimistic
   
   For i = 1 To 8
      rs_zujin(i) = Text1(i).Text
   Next i
   rs_zujin.Update
   rs_zujin.Close
   MsgBox "修改数据保存成功！", vbOKOnly + vbInformation, "注意"
   
   '保存后需要设置其他按钮可用，以及各个text框不可写
   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 8
      Text1(i).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
   '设置各个text框不可写
   For i = 0 To 8
      Text1(i).Enabled = False
   Next i
 '如果不是查询显示,则显示第一条记录
 If queryZuJin = False Then
   '在from_laod()方法中打开住户信息表
   '如果rs_count rs_zujin 当前状态是打开的，则先关闭之
   If rs_count.State = adStateOpen Then
      rs_count.Close
   End If
   If rs_zujin.State = adStateOpen Then
      rs_zujin.Close
   End If
   sqlzujin = "select * from ZuJin"
   rs_zujin.CursorLocation = adUseClient
   rs_zujin.Open sqlzujin, conn, adOpenStatic, adLockOptimistic
   If rs_zujin.EOF Then
      Text2.Text = 0
      '没有记录，则提示用户，退出本过程
      MsgBox "当前表中没有记录！", vbOKOnly + vbInformation, "注意"
      '如果是0条记录，需要设置前翻后翻的按钮为不可用
      cmdFirst.Enabled = False
      cmdPrev.Enabled = False
      cmdNext.Enabled = False
      cmdLast.Enabled = False
      cmdDel.Enabled = False
      cmdEdit.Enabled = False
      Exit Sub
   '找到符合条件的记录
   Else
      '如果有记录，需要设置前翻后翻的按钮为可用
      cmdFirst.Enabled = True
      cmdPrev.Enabled = True
      cmdNext.Enabled = True
      cmdLast.Enabled = True
      cmdDel.Enabled = True
      cmdEdit.Enabled = True
      '计算总共数据条数
      sqlcount = "select count(*) from ZuJin"
      rs_count.Open sqlcount, conn, adOpenStatic, adLockOptimistic
      '有记录则，显示第一条，并且显示记录条数
      Text2.Text = rs_count.Fields(0)
      
      If Not rs_zujin.EOF And Not rs_zujin.BOF Then
         For i = 0 To 8
            If IsNull(rs_zujin.Fields(i)) Then
               Text1(i).Text = ""
            Else
               Text1(i).Text = rs_zujin.Fields(i)
            End If
         Next i
      End If
   End If
       
 '如果是查询显示则相应sql语句为查询语句
 ElseIf queryZuJin = True Then
   '因为开始显示时必定打开了rs_zujin,rs_count,所以应该先关闭它们
   rs_zujin.Close
   If rs_count.State = adStateOpen Then
      rs_count.Close
   End If
   sqlzujin = "select * from ZuJin " & sqlqzj
   rs_zujin.CursorLocation = adUseClient
   rs_zujin.Open sqlzujin, conn, adOpenStatic, adLockOptimistic
   If rs_zujin.EOF Then
      Text2.Text = 0
      '如果没有找到记录，则提示用户，置空所有text控件，并且退出本子过程
      MsgBox "没有找到符合条件的记录", vbOKOnly + vbInformation, "注意"
      For i = 0 To 8
         Text1(i).Text = ""
      Next i
      cmdFirst.Enabled = False
      cmdPrev.Enabled = False
      cmdNext.Enabled = False
      cmdLast.Enabled = False
      cmdDel.Enabled = False
      cmdEdit.Enabled = False
      
      Exit Sub
   '找到符合条件的记录
   Else
      '如果有记录，需要设置前翻后翻的按钮为可用
      cmdFirst.Enabled = True
      cmdPrev.Enabled = True
      cmdNext.Enabled = True
      cmdLast.Enabled = True
      cmdDel.Enabled = True
      cmdEdit.Enabled = True
      
      '计算找到的条数
      sqlcount = "select count(*) from ZuJin " & sqlqzj
      rs_count.Open sqlcount, conn, adOpenStatic, adLockOptimistic
      Text2.Text = rs_count.Fields(0)
      '找到符合条件的记录，显示之
      
      For i = 0 To 8
         If IsNull(rs_zujin.Fields(i)) Then
            Text1(i).Text = ""
         Else
            Text1(i).Text = rs_zujin.Fields(i)
         End If
      Next i
   End If
         
 End If
   
End Sub

Private Sub Form_Unload(Cancel As Integer)
   If rs_zujin.State = adStateOpen Then
      rs_zujin.Close
   End If
   If rs_count.State = adStateOpen Then
      rs_count.Close
   End If
   '关闭时还需要设置queryZuJin为false
   queryZuJin = False
End Sub