frmleaseedit.frm

vb与access数据库的操作实例

      Left            =   240
      TabIndex        =   19
      Top             =   150
      Width           =   720
   End
End
Attribute VB_Name = "FrmLeaseEdit"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public Modify As Boolean  'False:插入；true:修改
Public ContractNo As String '合同编号
Public sCarNo As String  '车牌号
Public sCustId As String '客户编号
Public sLTime As String  '租赁时间（小时、分钟和秒数）

Dim lCost As Long   '总费用
Dim nCnt As Integer '工作日数量或周个数或月个数
Dim nECnt As Integer '周末个数


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(txtCarNo)) = 0 Then
    MsgBox ("请输入车牌号")
    txtCarNo.SetFocus
    Exit Sub
  End If
  If Len(Trim(txtCustId)) = 0 Then
    MsgBox ("请输入客户号")
    txtCustId.SetFocus
    Exit Sub
  End If
  If Len(Trim(cob_Mode.Text)) = 0 Then
    MsgBox ("请选择租赁模式")
    cob_Mode.SetFocus
    Exit Sub
  End If
  If Len(Trim(txtLeaseTime)) = 0 Then
    MsgBox ("请输入租赁时间")
    txtLeaseTime.SetFocus
    Exit Sub
  End If
  '如果为按日的租赁模式，则工作日和周末个数不能同时为0
  If Trim(cob_Mode.Text) = "日" Then
    If Val(txtWorkDays) = 0 And Val(txtWeekEndCount) = 0 Then
      MsgBox ("请输入按日租赁的工作日或者周末个数")
      txtWorkDays.SetFocus
      Exit Sub
    End If
  Else              '按周、月租赁则租赁周数、月份数不能为0
    If Val(txtWorkDays) = 0 Then
      MsgBox ("请输入租赁的周数或者月份数")
      txtWorkDays.SetFocus
      Exit Sub
    End If
  End If
  With MyLease
    .CarNo = MakeStr(txtCarNo)          '车牌号
    .CustId = MakeStr(txtCustId)        '客户编号
    .LeaseMode = MakeStr(cob_Mode.Text) '租赁模式
    '记录当前租赁价格
    If MakeStr(cob_Mode.Text) = "日" Then   '如果租赁模式为按日，则记录工作日价格和周末价格
      .Price1 = AdoPrice.Recordset.Fields(1)
      .Price2 = AdoPrice.Recordset.Fields(2)
      .WorkDays = Val(txtWorkDays)
      .WeekEndCount = Val(txtWeekEndCount)
    ElseIf MakeStr(cob_Mode.Text) = "周" Then   '如果按周或者月，则只在Price1中记录价格
      .Price1 = AdoPrice.Recordset.Fields(3)
      .Price2 = 0
      '按周模式租赁，则WorkDays中保存租赁周的个数
      .WorkDays = Val(txtWorkDays)
      .WeekEndCount = 0
    ElseIf MakeStr(cob_Mode.Text) = "月" Then  '如果按周或者月，则只在Price1中记录价格
      .Price1 = AdoPrice.Recordset.Fields(4)
      .Price2 = 0
      '按月模式租赁，则WorkDays中保存租赁月的个数
      .WorkDays = Val(txtWorkDays)
      .WeekEndCount = 0
    End If
    .DayKM = AdoPrice.Recordset.Fields(5)   '日限公里数
    .OPrice1 = AdoPrice.Recordset.Fields(6) '租金（与租赁模式相关）
    .OPrice2 = AdoPrice.Recordset.Fields(7) '周末租金
    .LeaseTime = MakeStr(txtLeaseTime)      '租赁时间
    .ReturnTime = MakeStr(txtReturnTime)    '应返回时间
    .Deposit = AdoPrice.Recordset.Fields(0) '押金
    .OutKM = Val(txtOutKM)                  '出车公里数
    .Rate = AdoCustomer.Recordset.Fields(5) '享受折扣
    .Total = Val(txtCost)                   '消费总额
    .CreateDate = Date                      '创建时间
    .UserName = MakeStr(txtUserName)        '创建人
    If Modify = False Then        '添加记录
      '判断合同编号是否重复
      If .In_DB(Trim(txtContractNo)) = True Then
        MsgBox ("已经存在此合同编号")
        txtContractNo.SetFocus
        Exit Sub
      End If
      .ContractNo = MakeStr(txtContractNo)  '合同编号
      .Status = "出租"                      '合同状态
      .Insert
    Else                          '修改记录
      .Update (ContractNo)
    End If
  End With
  Unload Me
End Sub

Private Sub Cmd_SchMember_Click()
  '按照输入的客户号，得到客户信息
  If Len(Trim(txtCustId)) = 0 Then
    MsgBox ("请输入客户号")
    txtCustId.SetFocus
    Exit Sub
  End If
  '判断是否存在此车牌号以及相关信息
  If MyCustomer.In_DB(Trim(txtCustId)) = False Then
    MsgBox ("不存在此客户号")
    txtCustId.SetFocus
    Exit Sub
  End If
  sCustId = Trim(txtCustId) '提取客户编号
  CustomerRefresh           '刷新客户信息
End Sub

Private Sub Cmd_SearchCar_Click()
  '按照输入的车牌号，得到车辆信息
  If Len(Trim(txtCarNo)) = 0 Then
    MsgBox ("请输入车牌号")
    txtCarNo.SetFocus
    Exit Sub
  End If
  '判断是否存在此车牌号以及相关信息
  If MyCar.GetInfo(Trim(txtCarNo)) = False Then
    MsgBox ("不存在此车牌号")
    txtCarNo.SetFocus
    Exit Sub
  Else
    If Trim(MyCar.Status) <> "待命" Then  '判断此车状态是否为待命
      MsgBox ("此车已经出租")
      txtCarNo.SetFocus
    End If
  End If
  sCarNo = Trim(txtCarNo) '提取此车车牌号
  CarRefresh              '刷新车辆信息
  CarPriceRefresh         '刷新租赁价格
End Sub
'更改租赁模式时更新费用金额
Private Sub cob_Mode_Click()
  '需要先选择车牌和客户信息
  If sCarNo = "" Or sCustId = "" Then
    MsgBox ("请先选择车辆和客户信息")
    Exit Sub
  End If
  nCnt = Val(txtWorkDays)
  nECnt = Val(txtWeekEndCount)
  '设置部分控件可用
  txtWorkDays.Enabled = True
  Label13.Enabled = True
  Label14.Enabled = True
  '如果租赁模式为按日租赁，则工作日和周末个数可用
  If Trim(cob_Mode.Text) = "日" Then
    Label13.Caption = "工作日"
    Label19.Enabled = True
    Label20.Enabled = True
    txtWeekEndCount.Enabled = True
    '总费用=工作日租赁价格*工作日个数+周末租金*周末个数
    lCost = Val(AdoPrice.Recordset.Fields(1)) * nCnt + Val(AdoPrice.Recordset.Fields(2)) * nECnt
    '以日为单位将 工作日+周末个数*2=天数 添加到租赁日期而计算返回时间
    txtReturnTime = Trim(DateAdd("d", nCnt + nECnt * 2, CDate(txtLeaseTime)))
  ElseIf Trim(cob_Mode.Text) = "周" Then
    Label13.Caption = "周数"
    Label19.Enabled = False
    Label20.Enabled = False
    txtWeekEndCount.Enabled = False
    '总费用=周租金*周数
    lCost = Val(AdoPrice.Recordset.Fields(3)) * nCnt
    '以日为单位将 周个数*7=天数 添加到租赁日期而计算返回时间
    txtReturnTime = Trim(DateAdd("d", nCnt * 7, CDate(txtLeaseTime)))
  ElseIf Trim(cob_Mode.Text) = "月" Then
    Label13.Caption = "月份数"
    Label19.Enabled = False
    Label20.Enabled = False
    txtWeekEndCount.Enabled = False
    '总费用=月租金*月个数
    lCost = Val(AdoPrice.Recordset.Fields(4)) * nCnt
    '以月为单位将月个数添加到租赁日期而计算返回时间
    txtReturnTime = Trim(DateAdd("m", nCnt, CDate(txtLeaseTime)))
  End If
  '总体消费金额还要乘以折扣，不是会员只能乘以1
  txtCost = lCost * Val(AdoCustomer.Recordset.Fields(5))
End Sub
'刷新汽车信息
Public Sub CarRefresh()
  AdoCar.ConnectionString = Conn
  AdoCar.RecordSource = "SELECT 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 = 1200
  DataGrid1.Columns(2).Width = 1000
  DataGrid1.Columns(3).Width = 1000
  DataGrid1.Columns(4).Width = 1600
End Sub
'刷新汽车租赁价格信息
Public Sub CarPriceRefresh()
  AdoPrice.ConnectionString = Conn
  AdoPrice.RecordSource = "SELECT Deposit AS 押金,DayPrice AS 工作日价格," _
     + "WeekEndPrice AS 周末价格,WeekPrice AS 周价格,MonthPrice AS 月价格, " _
     + "DayKM AS 日限公里,OverTimePrice AS 超时价格,OverKMPrice AS 超公里价格 " _
     + " FROM Cars WHERE  CarNO='" + Trim(sCarNo) + "'"
  AdoPrice.Refresh
  Set DataGrid2.DataSource = AdoPrice
  DataGrid2.Columns(0).Width = 800
  DataGrid2.Columns(1).Width = 1000
  DataGrid2.Columns(2).Width = 1000
  DataGrid2.Columns(3).Width = 1000
  DataGrid2.Columns(4).Width = 1000
  DataGrid2.Columns(5).Width = 1000
  DataGrid2.Columns(6).Width = 1000
  DataGrid2.Columns(7).Width = 1000
End Sub

'刷新客户信息
Public Sub CustomerRefresh()
  AdoCustomer.ConnectionString = Conn
  AdoCustomer.RecordSource = "SELECT c.Name AS 姓名,c.Sex AS 性别," _
     + "c.Telephone AS 电话,c.LicenseNo AS 驾驶证号," _
     + "IIF(c.Flag=0,'',m.TypeName) AS 会员类型," _
     + "IIF(c.Flag=0,1,m.Rate) AS 折扣 " _
     + " FROM Customer c LEFT outer JOIN MemberType m" _
     + " ON c.TypeId=m.Id where c.Id='" + Trim(sCustId) + "'"
  AdoCustomer.Refresh
  Set DataGrid3.DataSource = AdoCustomer
  DataGrid3.Columns(0).Width = 1000
  DataGrid3.Columns(1).Width = 700
  DataGrid3.Columns(2).Width = 1600
  DataGrid3.Columns(3).Width = 1600
  DataGrid3.Columns(4).Width = 1000
  DataGrid3.Columns(5).Width = 1000
End Sub

Private Sub Form_Load()
  '加载租赁模式
  cob_Mode.AddItem "日", 0
  cob_Mode.AddItem "周", 1
  cob_Mode.AddItem "月", 2
End Sub

Private Sub txtWeekEndCount_Change()
  If sCarNo = "" Or sCustId = "" Then
    MsgBox ("请先选择车辆和客户信息")
    Exit Sub
  End If
  '更改时间同时计算租车的总费用，查看租赁模式，如果为日，则按天价格计算总费用，否则不变
  nCnt = Val(txtWorkDays)
  nECnt = Val(txtWeekEndCount)
  If Trim(cob_Mode.Text) = "日" Then
    lCost = Val(AdoPrice.Recordset.Fields(1)) * nCnt + Val(AdoPrice.Recordset.Fields(2)) * nECnt
    txtReturnTime = Trim(DateAdd("d", nCnt + nECnt * 2, CDate(txtLeaseTime)))
  End If
  '总体消费金额还要乘以折扣，不是会员只能乘以1
  txtCost = lCost * Val(AdoCustomer.Recordset.Fields(5))
End Sub

'更改时间同时计算租车的总费用
'查看租赁模式，如果为日，则按天价格计算总费用；如果按周，则将计算日期相差的天数；
'如果按月，则计算日期相差的月份而后乘以月价格
Private Sub txtWorkDays_Change()
  If sCarNo = "" Or sCustId = "" Then
    MsgBox ("请先选择车辆和客户信息")
    Exit Sub
  End If
  nCnt = Val(txtWorkDays)       '租赁的工作日个数或周个数或月个数
  nECnt = Val(txtWeekEndCount)  '租赁的周末个数
  If Trim(cob_Mode.Text) = "日" Then
    '总费用=工作日租赁价格*工作日个数+周末租金*周末个数
    lCost = Val(AdoPrice.Recordset.Fields(1)) * nCnt + Val(AdoPrice.Recordset.Fields(2)) * nECnt
    '以日为单位将 工作日+周末个数*2=天数 添加到租赁日期而计算返回时间
    txtReturnTime = Trim(DateAdd("d", nCnt + nECnt * 2, CDate(txtLeaseTime)))
  ElseIf Trim(cob_Mode.Text) = "周" Then
    '总费用=周租金*周数
    lCost = Val(AdoPrice.Recordset.Fields(3)) * nCnt
    '以日为单位将 周个数*7=天数 添加到租赁日期而计算返回时间
    txtReturnTime = Trim(DateAdd("d", nCnt * 7, CDate(txtLeaseTime)))
  ElseIf Trim(cob_Mode.Text) = "月" Then
    '总费用=月租金*月个数
    lCost = Val(AdoPrice.Recordset.Fields(4)) * nCnt
    '以月为单位将月个数添加到租赁日期而计算返回时间
    txtReturnTime = Trim(DateAdd("m", nCnt, CDate(txtLeaseTime)))
  End If
  '总体消费金额还要乘以折扣，不是会员只能乘以1
  txtCost = lCost * Val(AdoCustomer.Recordset.Fields(5))
End Sub