frmreturn.frm

vb与access数据库的操作实例

VERSION 5.00
Object = "{67397AA1-7FB1-11D0-B148-00A0C922E820}#6.0#0"; "msadodc.ocx"
Object = "{CDE57A40-8B86-11D0-B3C6-00A0C90AEA82}#1.0#0"; "MSDatGrd.ocx"
Begin VB.Form FrmReturn 
   Caption         =   "汽车租赁归还管理"
   ClientHeight    =   4905
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   11445
   LinkTopic       =   "Form1"
   ScaleHeight     =   4905
   ScaleWidth      =   11445
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton Cmd_Modi 
      Caption         =   "修 改"
      Height          =   375
      Left            =   4440
      TabIndex        =   6
      Top             =   4320
      Width           =   975
   End
   Begin VB.CommandButton Cmd_Return 
      Caption         =   "归 还"
      Height          =   375
      Left            =   2160
      TabIndex        =   5
      Top             =   4320
      Width           =   975
   End
   Begin VB.CommandButton Cmd_RntCheck 
      Caption         =   "结算审核"
      Height          =   375
      Left            =   6720
      TabIndex        =   4
      Top             =   4320
      Width           =   975
   End
   Begin VB.ComboBox cob_Status 
      Height          =   300
      Left            =   1080
      Style           =   2  'Dropdown List
      TabIndex        =   3
      Top             =   120
      Width           =   1215
   End
   Begin VB.CommandButton Cmd_Exit 
      Caption         =   "退 出"
      Height          =   375
      Left            =   9000
      TabIndex        =   1
      Top             =   4320
      Width           =   1095
   End
   Begin MSAdodcLib.Adodc Adodc1 
      Height          =   375
      Left            =   6480
      Top             =   120
      Visible         =   0   'False
      Width           =   2055
      _ExtentX        =   3625
      _ExtentY        =   661
      ConnectMode     =   0
      CursorLocation  =   3
      IsolationLevel  =   -1
      ConnectionTimeout=   15
      CommandTimeout  =   30
      CursorType      =   3
      LockType        =   3
      CommandType     =   8
      CursorOptions   =   0
      CacheSize       =   50
      MaxRecords      =   0
      BOFAction       =   0
      EOFAction       =   0
      ConnectStringType=   1
      Appearance      =   1
      BackColor       =   -2147483643
      ForeColor       =   -2147483640
      Orientation     =   0
      Enabled         =   -1
      Connect         =   ""
      OLEDBString     =   ""
      OLEDBFile       =   ""
      DataSourceName  =   ""
      OtherAttributes =   ""
      UserName        =   ""
      Password        =   ""
      RecordSource    =   ""
      Caption         =   "Adodc1"
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      _Version        =   393216
   End
   Begin MSDataGridLib.DataGrid DataGrid1 
      Height          =   3615
      Left            =   120
      TabIndex        =   0
      Top             =   600
      Width           =   11175
      _ExtentX        =   19711
      _ExtentY        =   6376
      _Version        =   393216
      AllowUpdate     =   0   'False
      HeadLines       =   1
      RowHeight       =   15
      BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ColumnCount     =   2
      BeginProperty Column00 
         DataField       =   ""
         Caption         =   ""
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column01 
         DataField       =   ""
         Caption         =   ""
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      SplitCount      =   1
      BeginProperty Split0 
         MarqueeStyle    =   3
         AllowRowSizing  =   -1  'True
         AllowSizing     =   -1  'True
         BeginProperty Column00 
         EndProperty
         BeginProperty Column01 
         EndProperty
      EndProperty
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      Caption         =   "租赁状态"
      Height          =   180
      Left            =   240
      TabIndex        =   2
      Top             =   180
      Width           =   720
   End
End
Attribute VB_Name = "FrmReturn"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private SchCnd As String
Private Sub Cmd_Exit_Click()
  Unload Me
End Sub

Private Sub Cmd_Modi_Click()
  If Adodc1.Recordset.EOF = True Then
    MsgBox "请选择记录"
    Exit Sub
  End If

  '归还结算，没有审核前可以修改相关数据
  FrmReturnEdit.Modify = True
  FrmReturnEdit.sContractNo = Trim(Adodc1.Recordset.Fields(0))
  FrmReturnEdit.txtContractNo = Trim(Adodc1.Recordset.Fields(0))
  '根据合同编号提取所有租赁信息
  If MyLease.GetInfo(Trim(Adodc1.Recordset.Fields(0))) = True Then
    FrmReturnEdit.sCarNo = Trim(MyLease.CarNo)
    FrmReturnEdit.sCustId = Trim(MyLease.CustId)
    FrmReturnEdit.txtDeposit = MyLease.Deposit
    FrmReturnEdit.txtDayPrice = MyLease.Price1
    FrmReturnEdit.txtWeekEndPrice = MyLease.Price2
    FrmReturnEdit.txtOKMPrice = MyLease.OPrice1
    FrmReturnEdit.txtOTPrice = MyLease.OPrice2
    FrmReturnEdit.txtDayKM = MyLease.DayKM
    FrmReturnEdit.txtLeaseMode = Trim(MyLease.LeaseMode)
    FrmReturnEdit.txtLeaseTime = Trim(MyLease.LeaseTime)
    FrmReturnEdit.txtReturnTime = Trim(MyLease.ReturnTime)
    FrmReturnEdit.txtRealRTime = Trim(MyLease.RealRTime)
    '计算超时小时个数得到超时费用
    nDiff = DateDiff("h", CDate(Trim(MyLease.ReturnTime)), CDate(Trim(MyLease.RealRTime)))
    '如果没有超时，则超时费用为0
    If nDiff <= 0 Then
      FrmReturnEdit.txtOTCost = 0
    Else
      FrmReturnEdit.txtOTCost = Val(MyLease.OPrice2) * nDiff
    End If
    If Trim(MyLease.LeaseMode) = "日" Then
      FrmReturnEdit.txtWorkDays = MyLease.WorkDays
      FrmReturnEdit.txtWeekEndCount = MyLease.WeekEndCount
      FrmReturnEdit.Label13.Caption = "工作日"
      FrmReturnEdit.txtCost = MyLease.WorkDays * MyLease.Price1 + MyLease.WorkDays * MyLease.Price2
    ElseIf Trim(MyLease.LeaseMode) = "周" Then
      FrmReturnEdit.txtWorkDays = MyLease.WorkDays
      FrmReturnEdit.txtWeekEndCount = 0
      FrmReturnEdit.Label13.Caption = "周数"
      FrmReturnEdit.Label14.Caption = "个"
      FrmReturnEdit.Label19.Caption = "超时数"
      FrmReturnEdit.txtCost = MyLease.WorkDays * MyLease.Price1
    ElseIf Trim(MyLease.LeaseMode) = "月" Then
      FrmReturnEdit.txtWorkDays = MyLease.WorkDays
      FrmReturnEdit.txtWeekEndCount = 0
      FrmReturnEdit.Label13.Caption = "月份数"
      FrmReturnEdit.Label14.Caption = "个"
      FrmReturnEdit.Label19.Caption = "超时数"
      FrmReturnEdit.txtCost = MyLease.WorkDays * MyLease.Price1
    End If
    FrmReturnEdit.txtOutKM = MyLease.OutKM
    FrmReturnEdit.txtReturnKM = MyLease.ReturnKM
    FrmReturnEdit.txtRate = MyLease.Rate * 10
    '超公里费用=（日限公里数*实际租赁天数-（归还时公里数-出车时公里数））*超公里收费价格
    nKDiff = DateDiff("h", CDate(Trim(MyLease.LeaseTime)), CDate(Trim(MyLease.RealRTime))) '实际租赁天数
    nKM = (MyLease.ReturnKM - MyLease.OutKM) - MyLease.DayKM * nKDiff '超公里数
    If nKM <= 0 Then
      FrmReturnEdit.txtOKMCost = 0
    Else
      FrmReturnEdit.txtOKMCost = nKM * MyLease.OPrice1
    End If
    FrmReturnEdit.txtOtherCost = MyLease.OtherCost
    FrmReturnEdit.txtTotal = MyLease.Total
    FrmReturnEdit.txtRealPay = MyLease.Payment
    FrmReturnEdit.txtUserName = Trim(MyLease.UserName)
  End If
  '根据车牌号码提取车辆信息
  FrmReturnEdit.CarRefresh
  '根据客户号提取客户信息
  FrmReturnEdit.CustomerRefresh
  '修改时不能更改合同编号
  FrmReturnEdit.txtContractNo.Enabled = False
  FrmReturnEdit.Cmd_SchContract.Enabled = False
  FrmReturnEdit.Show 1
  GridRefresh
End Sub

Private Sub Cmd_RntCheck_Click()
  '归还审核后不能修改和删除
  If Adodc1.Recordset.EOF = True Then
    MsgBox "请选择记录"
    Exit Sub
  End If
  If MsgBox("是否审核确认了租赁归还信息", vbYesNo, "请确认") = vbNo Then
    Exit Sub
  End If
  '更新合同状态为归还审核
  MyLease.Status = "结算审核"
  MyLease.UpdateStatus (Adodc1.Recordset.Fields(0))
  GridRefresh
End Sub

Private Sub Cmd_Return_Click()
  '归还结算，没有审核前可以修改相关数据
  FrmReturnEdit.Modify = False
  FrmReturnEdit.Show 1
  GridRefresh
End Sub

Private Sub cob_Status_Click()
  '更改状态后更改记录
  GridRefresh
End Sub

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
  If Trim(Adodc1.Recordset.Fields(7)) = "归还" Then
    Cmd_Return.Enabled = True       '归还按钮可用
    Cmd_Modi.Enabled = True        '修改按钮可用
    Cmd_RntCheck.Enabled = True    '归还审核按钮可用
  ElseIf Trim(Adodc1.Recordset.Fields(7)) = "结算审核" Then
    Cmd_Return.Enabled = True       '归还按钮可用
    Cmd_Modi.Enabled = False       '修改按钮不可用
    Cmd_RntCheck.Enabled = False   '归还审核按钮不可用
  End If
End Sub

Private Sub Form_Load()
  GridRefresh
  '加载状态名称
  cob_Status.AddItem "归还", 0
  cob_Status.AddItem "归还审核", 1
  '设置审核按钮不可用
  Cmd_RntCheck.Enabled = False
End Sub
Private Sub GridRefresh()
  If Trim(cob_Status.Text) = "全部" Or Trim(cob_Status) = "" Then
    SchCnd = ""
  Else
    SchCnd = " AND Status='" + Trim(cob_Status.Text) + "'"
  End If
  Adodc1.ConnectionString = Conn
  Adodc1.RecordSource = "SELECT ContractNo AS 合同编号,CarNo AS 车牌号," _
    + "CustId AS 客户号,LeaseTime AS 租赁时间,LeaseMode AS 租赁模式, " _
    + "ReturnTime AS 应归还时间,RealRTime AS 实际归还时间,Status AS 状态,Price1 AS 租赁价格," _
    + "IIF([Status]='日',Price1*WorkDays+Price2*WeekEndCount,Price1*WorkDays) AS 租赁费用," _
    + "OtherCost AS 其他费用,Payment AS 实际支付,Deposit AS 押金,Total AS 总额 " _
    + " FROM Lease WHERE (Status='归还' OR Status='结算审核')" + SchCnd _
    + " ORDER BY ReturnTime DESC,LeaseTime DESC"
  Adodc1.Refresh
  Set DataGrid1.DataSource = Adodc1
  DataGrid1.Columns(0).Width = 1200
  DataGrid1.Columns(1).Width = 1000
  DataGrid1.Columns(2).Width = 1000
  DataGrid1.Columns(3).Width = 1800
  DataGrid1.Columns(4).Width = 900
  DataGrid1.Columns(5).Width = 1800
  DataGrid1.Columns(6).Width = 1800
  DataGrid1.Columns(7).Width = 1200
  DataGrid1.Columns(8).Width = 1200
  DataGrid1.Columns(9).Width = 1200
  DataGrid1.Columns(10).Width = 1200
  DataGrid1.Columns(11).Width = 1200
  DataGrid1.Columns(12).Width = 1200
End Sub