frmreturnedit.frm

vb与access数据库的操作实例

         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         =   "AdoCar"
         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          =   735
         Left            =   120
         TabIndex        =   20
         Top             =   240
         Width           =   8175
         _ExtentX        =   14420
         _ExtentY        =   1296
         _Version        =   393216
         AllowUpdate     =   0   'False
         Enabled         =   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 
            BeginProperty Column00 
            EndProperty
            BeginProperty Column01 
            EndProperty
         EndProperty
      End
   End
   Begin VB.Label Label4 
      AutoSize        =   -1  'True
      Caption         =   "合同编号"
      Height          =   180
      Left            =   240
      TabIndex        =   10
      Top             =   150
      Width           =   720
   End
End
Attribute VB_Name = "FrmReturnEdit"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public Modify As Boolean      'False:插入；true:修改
Public sContractNo As String  '合同编号
Public sCarNo As String       '车牌号
Public sCustId As String      '客户编号
Dim nDiff As Integer      '超时小时数
Dim nKMDiff As Integer    '超公里数
Dim lCost As Long         '租车费用

Private Sub Cmd_Cancel_Click()
  Unload Me
End Sub

Private Sub Cmd_OK_Click()
  '域校验
  If Len(Trim(txtContractNo)) = 0 Then
    MsgBox ("请输入合同编号")
    txtContractNo.SetFocus
    Exit Sub
  End If
  If Len(Trim(txtRealRTime)) = 0 Then
    MsgBox ("请输入实际归还时间")
    txtReturnTime.SetFocus
    Exit Sub
  End If
  If Len(Trim(txtReturnKM)) = 0 Or Val(txtReturnKM) = 0 Then
    MsgBox ("请输入归还时公里数")
    txtReturnKM.SetFocus
    Exit Sub
  End If
  With MyLease
    .ContractNo = MakeStr(txtContractNo)
    .ReturnKM = Val(txtReturnKM)
    .RealRTime = MakeStr(txtRealRTime)
    .OtherCost = Val(txtOtherCost)
    .Payment = Val(txtRealPay)
    .Total = Val(txtTotal)
    .UserName = MakeStr(txtUserName)
    .Status = "归还"
    .UpdateSettleAccount (Trim(txtContractNo))
  End With
  Unload Me
End Sub

Private Sub Cmd_SchContract_Click()
  If Len(Trim(txtContractNo)) = 0 Then
    MsgBox ("请输入合同编号")
    txtContractNo.SetFocus
    Exit Sub
  End If
  '根据合同编号提取所有租赁信息
  If MyLease.GetInfo(Trim(txtContractNo)) = True Then
    '如果合同的状态为出租审核或者续租审核，才可以归还
    If InStr(Trim(MyLease.Status), "审核") <= 0 Then
      MsgBox ("合同必须审核了才可以办理归还结算手续")
      Exit Sub
    End If
    sCarNo = Trim(MyLease.CarNo)
    sCustId = Trim(MyLease.CustId)
    txtDeposit = MyLease.Deposit
    txtDayPrice = MyLease.Price1
    txtWeekEndPrice = MyLease.Price2
    txtOKMPrice = MyLease.OPrice1
    txtOTPrice = MyLease.OPrice2
    txtDayKM = MyLease.DayKM
    txtLeaseMode = Trim(MyLease.LeaseMode)
    txtLeaseTime = Trim(MyLease.LeaseTime)
    txtReturnTime = Trim(MyLease.ReturnTime)
    txtRealRTime = Trim(Str(Now))
    '计算超时小时个数得到超时费用
    nDiff = DateDiff("h", CDate(Trim(txtReturnTime)), CDate(Trim(txtRealRTime)))
    '如果没有超时，则超时费用为0
    If nDiff <= 0 Then
      txtOTCost = 0
    Else
      txtOTCost = Val(txtOTPrice) * nDiff   '超时费=超时价格*超时小时数
    End If
    If Trim(MyLease.LeaseMode) = "日" Then
      txtWorkDays = MyLease.WorkDays
      txtWeekEndCount = MyLease.WeekEndCount
      Label13.Caption = "工作日"
      txtCost = MyLease.WorkDays * MyLease.Price1 + MyLease.WorkDays * MyLease.Price2
    ElseIf Trim(MyLease.LeaseMode) = "周" Then
      txtWorkDays = MyLease.WorkDays
      txtWeekEndCount = 0
      Label13.Caption = "周数"
      Label19.Caption = "超时数"
      txtCost = MyLease.WorkDays * MyLease.Price1
    ElseIf Trim(MyLease.LeaseMode) = "月" Then
      txtWorkDays = MyLease.WorkDays
      txtWeekEndCount = 0
      Label13.Caption = "月份数"
      Label19.Caption = "超时数"
      txtCost = MyLease.WorkDays * MyLease.Price1
    End If
    txtOutKM = MyLease.OutKM
    txtTotal = (Val(txtCost) + Val(txtOTCost) + Val(txtOKMCost)) * Val(txtRate) + Val(txtOtherCost)
    txtRealPay = Val(txtTotal) - Val(txtDeposit)
    txtRate = MyLease.Rate * 10
  End If
  '根据车牌号码提取车辆信息
  CarRefresh
  '根据客户号提取客户信息
  CustomerRefresh
End Sub

Private Sub Form_Load()
  sCarNo = ""
  sCustId = ""
  CarRefresh
  CustomerRefresh
End Sub

Public Sub CarRefresh()
  AdoCar.ConnectionString = Conn
  AdoCar.RecordSource = "SELECT c.CarNo AS 车牌号,c.CarName AS 车辆名称,t.TypeName AS 车辆类型," _
     + "c.Color AS 颜色,c.OilNo AS 汽油编号,c.BuyDate AS 购买日期 " _
     + " FROM Cars c,Types t" _
     + " WHERE c.TypeId=t.Id AND c.CarNO='" + Trim(sCarNo) + "'"
  AdoCar.Refresh
  Set DataGrid1.DataSource = AdoCar
  DataGrid1.Columns(0).Width = 1400
  DataGrid1.Columns(1).Width = 1400
  DataGrid1.Columns(2).Width = 1200
  DataGrid1.Columns(3).Width = 1000
  DataGrid1.Columns(4).Width = 1000
  DataGrid1.Columns(5).Width = 1600
End Sub
Public Sub CustomerRefresh()
  AdoCustomer.ConnectionString = Conn
  AdoCustomer.RecordSource = "SELECT Id AS 客户号,Name AS 姓名,Sex AS 性别,Age AS 年龄," _
     + "Telephone AS 电话,LicenseNo AS 驾驶证号,Certificate AS 抵押证件 " _
     + " FROM Customer WHERE Id='" + Trim(sCustId) + "'"
  AdoCustomer.Refresh
  Set DataGrid2.DataSource = AdoCustomer
  DataGrid2.Columns(0).Width = 1000
  DataGrid2.Columns(1).Width = 1000
  DataGrid2.Columns(2).Width = 800
  DataGrid2.Columns(3).Width = 800
  DataGrid2.Columns(4).Width = 1600
  DataGrid2.Columns(5).Width = 1600
  DataGrid2.Columns(6).Width = 1000
End Sub

Private Sub txtOtherCost_Change()
  '更改费用总额
  txtTotal = (Val(txtCost) + Val(txtOTCost) + Val(txtOKMCost)) * Val(txtRate) / 10 + Val(txtOtherCost)
  txtRealPay = Val(txtTotal) - Val(txtDeposit)
End Sub

Private Sub txtRealRTime_LostFocus()
  '更改实际归还时间后重新计算超时小时
  nDiff = DateDiff("h", CDate(Trim(txtReturnTime)), CDate(Trim(txtRealRTime)))
  '如果小时数为负数，则超时费用为0
  If nDiff <= 0 Then
    txtOTCost = 0
    txtWeekEndCount = 0
  Else
    txtOTCost = Val(txtOTPrice) * nDiff
    txtWeekEndCount = nDiff
  End If
  '只有租车费用上乘以折扣，其他费用不乘以折扣
  txtTotal = (Val(txtCost) + Val(txtOTCost) + Val(txtOKMCost)) * Val(txtRate) / 10 + Val(txtOtherCost)
  txtRealPay = Val(txtTotal) - Val(txtDeposit)
End Sub

Private Sub txtReturnKM_Change()
  '计算超公里数和超公里费用
  '计算日期相差的天数
  Dim nD As Integer
  Dim nL As Long  '规定公里总数
  Dim nR As Long  '实际归还的公里数
  nD = DateDiff("d", CDate(Trim(txtLeaseTime)), CDate(Trim(txtRealRTime)))
  '实际归还的公里数和规定公里数
  nL = Val(txtDayKM) * nD
  nR = Val(txtReturnKM) - Val(txtOutKM)
  If nR > nL Then
    txtOKMCost = Val(txtOKMPrice) * (nR - nL)
  Else
    txtOKMCost = 0
  End If
  '更改费用总额
  txtTotal = (Val(txtCost) + Val(txtOTCost) + Val(txtOKMCost)) * Val(txtRate) / 10 + Val(txtOtherCost)
  txtRealPay = Val(txtTotal) - Val(txtDeposit)
End Sub