main_qtgl_ztfwgl_fw.frm

一个比较完整的数据库编程技巧”洗浴管理系统“

      Width           =   5655
   End
   Begin VB.Label Label2 
      BackColor       =   &H00FFFFFF&
      BackStyle       =   0  'Transparent
      Caption         =   "登记时间："
      Height          =   180
      Left            =   60
      TabIndex        =   6
      Top             =   150
      Width           =   975
   End
End
Attribute VB_Name = "main_qtgl_ztfwgl_fw"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim rs1 As New ADODB.Recordset     '定义数据集对象
Dim txtSQL As String     '定义字符串变量
Dim i, j As Integer     '定义整型变量
Private Sub Combo1_Click()
  MS1.TextMatrix(MS1.Row, 9) = Format(Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5)) * (Val(Combo2.Text) / 10), "0.00")
End Sub

Private Sub Form_Load()
 '定义MS1表的总行数、总列数
 MS1.Rows = 102: MS1.Cols = 10
 '定义MS1表的宽度
 MS1.ColWidth(0) = 12 * 25 * 1: MS1.ColWidth(1) = 12 * 25 * 3
 MS1.ColWidth(2) = 12 * 25 * 5: MS1.ColWidth(3) = 12 * 25 * 6
 MS1.ColWidth(4) = 12 * 25 * 3: MS1.ColWidth(5) = 12 * 25 * 3
 MS1.ColWidth(6) = 12 * 25 * 3: MS1.ColWidth(7) = 12 * 25 * 3
 MS1.ColWidth(8) = 12 * 25 * 2: MS1.ColWidth(9) = 12 * 25 * 4
 '设置固定行、列
 MS1.FixedRows = 1: MS1.FixedCols = 1
 '定义MS1表的表头
 MS1.TextMatrix(0, 0) = "xh": MS1.TextMatrix(0, 1) = "简称"
 MS1.TextMatrix(0, 2) = "项目编号": MS1.TextMatrix(0, 3) = "名称"
 MS1.TextMatrix(0, 4) = "单位": MS1.TextMatrix(0, 5) = "单价"
 MS1.TextMatrix(0, 6) = "数量": MS1.TextMatrix(0, 7) = "消费状态"
 MS1.TextMatrix(0, 8) = "折扣": MS1.TextMatrix(0, 9) = "金额小计"
 '定义MS1表的列序号
 For i = 1 To 101
     MS1.TextMatrix(i, 0) = i
 Next i
 Labtime.Caption = Now   '设置登记时间
 Combo1.AddItem ("点单"): Combo1.AddItem ("赠单")   '初始化设置
 Combo1.ListIndex = 0
 '添加折扣列表
 For i = 0 To 10
  Combo2.AddItem (i)
 Next i
 Combo2.ListIndex = 10
 '设置窗体标题栏的内容
 Me.Caption = Me.Caption & "     操作员： " & frm_main.St1.Panels(3).Text
End Sub
Private Sub Form_Unload(Cancel As Integer)
 main_qtgl_ztfwgl.Enabled = True
End Sub
Private Sub grid1_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then
  If Adodc1.Recordset.RecordCount > 0 Then     '判断是否有记录
   If Adodc1.Recordset.Fields("项目编号") <> "" Then
    '赋值给MS1表格
    If Adodc1.Recordset.Fields("简称") <> "" Then MS1.TextMatrix(MS1.Row, 1) = Trim(Adodc1.Recordset.Fields("简称"))
    If Adodc1.Recordset.Fields("项目编号") <> "" Then MS1.TextMatrix(MS1.Row, 2) = Trim(Adodc1.Recordset.Fields("项目编号"))
    If Adodc1.Recordset.Fields("名称") <> "" Then MS1.TextMatrix(MS1.Row, 3) = Trim(Adodc1.Recordset.Fields("名称"))
    If Adodc1.Recordset.Fields("单位") <> "" Then MS1.TextMatrix(MS1.Row, 4) = Trim(Adodc1.Recordset.Fields("单位"))
    If Adodc1.Recordset.Fields("单价") <> "" Then MS1.TextMatrix(MS1.Row, 5) = Format(Adodc1.Recordset.Fields("单价"), "0.00")
    '赋值给text1.text
    Text1.Text = MS1.Text
    Text1.SetFocus
    MS1.Col = 6     '到达第6列
    grid1.Visible = False
   Else
    MsgBox ("无数据选择!!!")
    grid1.Visible = False
    Text1.SetFocus
   End If
  End If
  Text1.SetFocus
 End If
 If KeyCode = vbKeyEscape Then     '按ESC键grid1不可见
  grid1.Visible = False
  Text1.SetFocus     'text1获得焦点
 End If
End Sub
Private Sub MS1_Click()     '单击MS1表，text1表可见并获得焦点
  If MS1.Row >= 1 And MS1.TextMatrix(MS1.Row - 1, 6) <> "" Then
     Text1.Visible = True
     Text1.SetFocus
  End If
  If MS1.Col <> 7 Then
     MS1.TextMatrix(MS1.Row, 7) = Combo1.Text     '赋值给第7列
     Combo1.Visible = False
  End If
  If MS1.Col <> 8 Then
     MS1.TextMatrix(MS1.Row, 8) = Combo2.Text     '赋值给第8列
     Combo2.Visible = False
  End If
End Sub
Private Sub MS1_Entercell()     '确定text1在MS1表中的位置
  Dim X As String, y As String, p As String
  If MS1.CellWidth <= 0 Or MS1.CellHeight <= 0 Then Exit Sub
  X = MS1.TextMatrix(MS1.FixedRows, MS1.Col)
  y = MS1.TextMatrix(MS1.Row, 0)
 If y <> "" Then
    If MS1.Col - MS1.LeftCol <= 3 Then
       MS1.LeftCol = MS1.LeftCol + 1
    End If
 If MS1.CellWidth > 0 And MS1.CellHeight > 0 Then
    Text1.Width = MS1.CellWidth: Text1.Height = MS1.CellHeight
    Text1.Left = MS1.CellLeft + MS1.Left
    Text1.Top = MS1.CellTop + MS1.Top
 End If
  X = MS1.TextMatrix(MS1.FixedRows, MS1.Col)
  y = MS1.TextMatrix(MS1.Row, 0)
  p = MS1.TextMatrix(MS1.Row, MS1.Col)
  Text1.Text = MS1.Text: Text1.SelStart = 0
  Text1.SelLength = Len(Text1.Text)
 End If
End Sub
Private Sub MS1_RowColChange()
'格式化MS1表的第5列、第9列
 For i = 1 To 101
     If MS1.TextMatrix(i, 2) <> "" And MS1.TextMatrix(i, 6) <> "" Then
        MS1.TextMatrix(MS1.Row, 5) = Format(MS1.TextMatrix(MS1.Row, 5), "#0.00")
        MS1.TextMatrix(MS1.Row, 9) = Val(MS1.TextMatrix(MS1.Row, 5)) * Val(MS1.TextMatrix(MS1.Row, 6))
        MS1.TextMatrix(MS1.Row, 9) = Format(MS1.TextMatrix(MS1.Row, 9), "#0.00")
        Dim a, B As Single
        pz.Text = i     '品种数
        a = Val(MS1.TextMatrix(i, 9)) + a    '求合计金额
        B = Val(MS1.TextMatrix(i, 6)) + B    '求合计数量
        hjsl.Text = B
        hjje.Text = Format(a, "0.00")     '格式化合计金额
     End If
 Next i
End Sub
Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then     '按回车键
  grid1.Visible = False     'grid1不可见
  If MS1.Col = 1 Then
   If Adodc1.Recordset.RecordCount > 0 Then
    '赋值给MS1表格
     If Adodc1.Recordset.Fields("简称") <> "" Then MS1.TextMatrix(MS1.Row, 1) = Trim(Adodc1.Recordset.Fields("简称"))
     If Adodc1.Recordset.Fields("项目编号") <> "" Then MS1.TextMatrix(MS1.Row, 2) = Trim(Adodc1.Recordset.Fields("项目编号"))
     If Adodc1.Recordset.Fields("名称") <> "" Then MS1.TextMatrix(MS1.Row, 3) = Trim(Adodc1.Recordset.Fields("名称"))
     If Adodc1.Recordset.Fields("单位") <> "" Then MS1.TextMatrix(MS1.Row, 4) = Trim(Adodc1.Recordset.Fields("单位"))
     If Adodc1.Recordset.Fields("单价") <> "" Then MS1.TextMatrix(MS1.Row, 5) = Format(Adodc1.Recordset.Fields("单价"), "0.00")
     Text1.SetFocus
     Text1.Text = MS1.Text     '赋值给text1.text
     grid1.Visible = False
     MS1.Col = 5     '到达第5列
   End If
  End If
  If MS1.Col = 9 Then
    MS1.Row = MS1.Row + 1
    MS1.Col = 1
   Else
    If MS1.Col + 1 <= MS1.Cols - 1 Then
       MS1.Col = MS1.Col + 1
     Else
       If MS1.Row + 1 <= MS1.Rows - 1 Then
        MS1.Row = MS1.Row + 1
        MS1.Col = 1
       End If
    End If
   End If
 End If
 '按向上键text向上移动
 If KeyCode = vbKeyUp Then
    If MS1.Row > 1 Then MS1.Row = MS1.Row - 1
 End If
 '按向下键text向下移动
 If KeyCode = vbKeyDown Then
    If MS1.Row < 99 Then MS1.Row = MS1.Row + 1
 End If
 '按向左键text向左移动
 If KeyCode = vbKeyLeft Then
    If Text1.Text <> "" Then
       Text1.SelStart = 0
       Text1.SelLength = Len(Text1.Text)
    End If
    If MS1.Col - 9 <= MS1.Cols + 1 Then
       MS1.Col = MS1.Col - 1
       If MS1.Col = 0 Then MS1.Col = 1
      Else
       If MS1.Row + 1 <= MS1.Row - 1 Then
          MS1.Row = MS1.Row + 1
          MS1.Col = 1
       End If
    End If
 End If
 '按向右键text向右移动
 If KeyCode = vbKeyRight Then
    If Text1.Text <> "" Then
       Text1.SelStart = 0
       Text1.SelLength = Len(Text1.Text)
    End If
    If MS1.Col + 1 <= MS1.Cols - 1 Then
       MS1.Col = MS1.Col + 1
      Else
       If MS1.Row + 1 <= MS1.Rows - 1 Then
          MS1.Row = MS1.Row + 1
          MS1.Col = 1
       End If
    End If
 End If
 '按PageDown键
 If KeyCode = vbKeyPageDown Then
  If MS1.Col = 1 Then
   '查询消费项目信息
   Adodc1.RecordSource = "select * from 消费项目价格表"
   Adodc1.Refresh
   grid1.Visible = True
   grid1.SetFocus
  End If
 End If
End Sub
Private Sub Combo1_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then     '按回车键
   Combo1.Visible = False     'Combo1不可见
   MS1.Col = 8    '达到第8列
   Combo2.Visible = True     'Combo2不可见
   '确定combo2在MS1表格中的位置
   Combo2.Width = MS1.CellWidth
   Combo2.Left = MS1.CellLeft + MS1.Left
   Combo2.Top = MS1.CellTop + MS1.Top
   Combo2.SetFocus
   MS1.TextMatrix(MS1.Row, 7) = Combo1.Text     '赋值给MS1.TextMatrix(MS1.Row, 7)
 End If
End Sub
Private Sub Combo2_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then     '按回车键
  MS1.Col = 8     '到达第8列
  MS1.TextMatrix(MS1.Row, 8) = Combo2.Text     '赋值给MS1.TextMatrix(MS1.Row, 8)
  '计算金额小计
  MS1.TextMatrix(MS1.Row, 9) = Format(Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5)) * (Val(Combo2.Text) / 10), "0.00")
  If MS1.Col = 8 Then
   MS1.Row = MS1.Row + 1
   MS1.Col = 1
   Combo2.Visible = False
   Text1.Visible = True
   Text1.SetFocus
  End If
 End If
End Sub
Private Sub Combo2_Click()
 '计算金额小计
 MS1.TextMatrix(MS1.Row, 9) = Format(Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5)) * (Val(Combo2.Text) / 10), "0.00")
End Sub
Private Sub Text1_Change()
 MS1.Text = Text1.Text   '将text1的值赋给MS1
 If MS1.Col = 1 Then
  If Text1.Text <> "" Then
   '筛选项目名称或简称符合text1的记录
   Adodc1.RecordSource = "select * from 消费项目价格表 where (名称 like '" + Text1.Text + "'+ '%')or( 简称  like '" + Text1.Text + "'+'%')"
   Adodc1.Refresh
   If Adodc1.Recordset.RecordCount > 0 Then     '当记录大于零时
      grid1.Visible = True: Text1.Visible = True
      Text1.SetFocus
   End If
  End If
 End If
 If MS1.Col = 3 Then
  If MS1.TextMatrix(MS1.Row, 2) = "" Then
   MsgBox ("无项目编号,请重新输入!!!")
   MS1.Col = 2
  End If
 End If
 If MS1.Col = 6 Then
  '求金额
  MS1.TextMatrix(MS1.Row, 9) = Format(Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5)) * (Val(Combo2.Text) / 10), "0.00")
 End If
 If MS1.Col = 5 Then
  '求金额
  MS1.TextMatrix(MS1.Row, 9) = Format(Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5)) * (Val(Combo2.Text) / 10), "0.00")
 End If
  If MS1.Col = 7 Then
   Text1.Visible = False: Combo1.Visible = True
   '确定Combo1在MS1表格中的位置
   Combo1.Width = MS1.CellWidth
   Combo1.Left = MS1.CellLeft + MS1.Left
   Combo1.Top = MS1.CellTop + MS1.Top
   Combo1.SetFocus     'Combo1获得焦点
 End If
 If MS1.Col = 8 Then
  '求金额
  MS1.TextMatrix(MS1.Row, 9) = Format(Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5)) * (Val(Combo2.Text) / 10), "0.00")
  Text1.Visible = False: Combo2.Visible = True
  '确定Combo2在MS1表格中的位置
  Combo2.Width = MS1.CellWidth
  Combo2.Left = MS1.CellLeft + MS1.Left
  Combo2.Top = MS1.CellTop + MS1.Top
  Combo2.SetFocus     'Combo2获得焦点
 End If
End Sub
Private Sub Cmddj_Click()
  '文本框的大小和位置等于MS1中网格的大小和位置
  Text1.Width = MS1.CellWidth: Text1.Height = MS1.CellHeight
  Text1.Left = MS1.CellLeft + MS1.Left: Text1.Top = MS1.CellTop + MS1.Top
  MS1.Enabled = True
  '设置控件有效或无效
  Cmdbc.Enabled = True: Cmdqx.Enabled = True
  Cmddj.Enabled = False: Text1.Enabled = True
  Text1.Visible = True: Text1.SetFocus
  '清空MS1表格中的内容
  For i = 1 To 101
      For j = 1 To 9
          MS1.TextMatrix(i, j) = ""
      Next j
  Next i
  pz.Text = "0"
  hjsl.Text = "0"
  hjje.Text = "0"
End Sub
Private Sub Cmdbc_Click()
 txtSQL = "select * from 临时消费信息表"
 Set rs1 = ESQL(txtSQL)
 If MS1.TextMatrix(1, 1) <> "" And MS1.TextMatrix(1, 6) <> "" Then
   For i = 1 To 101
    If MS1.TextMatrix(i, 1) <> "" And MS1.TextMatrix(i, 6) <> "" Then
     If Trim(MS1.TextMatrix(i, 7)) <> "点单" And Trim(MS1.TextMatrix(i, 7)) <> "赠单" Then
      MsgBox "消费状态必须是点单或赠单！"
     Exit Sub
     End If
    '添加新记录到临时消费信息表中
     rs1.AddNew
     If MS1.TextMatrix(i, 1) <> "" Then rs1.Fields("简称") = MS1.TextMatrix(i, 1)
     If MS1.TextMatrix(i, 2) <> "" Then rs1.Fields("项目编号") = MS1.TextMatrix(i, 2)
     If MS1.TextMatrix(i, 3) <> "" Then rs1.Fields("名称") = MS1.TextMatrix(i, 3)
     If MS1.TextMatrix(i, 4) <> "" Then rs1.Fields("单位") = MS1.TextMatrix(i, 4)
     If MS1.TextMatrix(i, 5) <> "" Then rs1.Fields("单价") = MS1.TextMatrix(i, 5)
     If MS1.TextMatrix(i, 6) <> "" Then rs1.Fields("数量") = Val(MS1.TextMatrix(i, 6))
     If MS1.TextMatrix(i, 7) <> "" Then rs1.Fields("消费状态") = MS1.TextMatrix(i, 7)
     If MS1.TextMatrix(i, 8) <> "" Then rs1.Fields("折扣") = Val(MS1.TextMatrix(i, 8))
     If MS1.TextMatrix(i, 9) <> "" Then rs1.Fields("金额小计") = Val(MS1.TextMatrix(i, 9))
     If Labxh.Caption <> "" Then rs1.Fields("箱号") = Trim(Labxh.Caption)
     If Labdt.Caption <> "" Then rs1.Fields("所在大厅") = Trim(Labdt.Caption)
     If Labtime.Caption <> "" Then rs1.Fields("登记时间") = Trim(Labtime.Caption)
     rs1.Fields("隐藏状态") = "消费中"
     rs1.Update     '更新记录
    End If
   Next i
  '设置控件有效或无效
   Cmdbc.Enabled = False: Cmddj.Enabled = True
   Cmdqx.Enabled = False: Text1.Visible = False
   MS1.Enabled = False: grid1.Visible = False
  Else
   MsgBox ("填写数据不完整,请核对,再保存")
   MS1.Col = 1: MS1.Row = 1    '到达第1行  到达第1列
   grid1.Visible = False
 End If
End Sub
Private Sub Cmdqx_Click()     '取消操作
 For i = 1 To 101          '清空数据
     For j = 1 To 9
         MS1.TextMatrix(i, j) = ""
     Next j
 Next i
 '设置控件有效或无效
 MS1.Enabled = False
 Cmddj.Enabled = True
 Text1.Visible = False     'text1可见
 pz.Text = "0"
 hjje.Text = "0.00"
 hjsl.Text = "0"
 Cmddj.SetFocus     'Cmddj获得焦点
End Sub
Private Sub CmdEnd_Click()
 main_qtgl_ztfwgl.Enabled = True
 Unload Me
End Sub