frmmain.frm

一个自己编写的小型超市管理系统,实现比较简单的功能,供初学者学习参考用.

      Begin VB.CommandButton cmdQueren 
         Caption         =   "确认"
         Height          =   375
         Left            =   -68640
         TabIndex        =   17
         Top             =   7020
         Width           =   1455
      End
      Begin VB.TextBox txtDate 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   16
         Top             =   5940
         Width           =   2535
      End
      Begin VB.TextBox txtNumber 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   15
         Top             =   5100
         Width           =   1455
      End
      Begin VB.TextBox txtJin 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   14
         Top             =   4500
         Width           =   1455
      End
      Begin VB.TextBox txtLing 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   13
         Top             =   3900
         Width           =   1455
      End
      Begin VB.TextBox txtGuige 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   12
         Top             =   3300
         Width           =   975
      End
      Begin VB.TextBox txtSC 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   11
         Top             =   2580
         Width           =   2295
      End
      Begin VB.TextBox txtName 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   10
         Top             =   1860
         Width           =   1455
      End
      Begin VB.TextBox txtTXM 
         Appearance      =   0  'Flat
         Height          =   390
         Left            =   -72120
         TabIndex        =   2
         Top             =   1260
         Width           =   1455
      End
      Begin VB.Label Label20 
         Alignment       =   2  'Center
         Caption         =   "超市信息管理系统"
         BeginProperty Font 
            Name            =   "楷体_GB2312"
            Size            =   42
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   1815
         Left            =   -74040
         TabIndex        =   29
         Top             =   3360
         Width           =   11895
      End
      Begin VB.Label Label11 
         Caption         =   "数量"
         Height          =   375
         Left            =   -69120
         TabIndex        =   21
         Top             =   1440
         Width           =   735
      End
      Begin VB.Label Label10 
         Caption         =   "条形码"
         Height          =   315
         Left            =   -72840
         TabIndex        =   20
         Top             =   1440
         Width           =   735
      End
      Begin VB.Label Label9 
         Caption         =   "如：2006-02-07"
         Height          =   375
         Left            =   -69360
         TabIndex        =   19
         Top             =   6120
         Width           =   1695
      End
      Begin VB.Label Label8 
         Alignment       =   1  'Right Justify
         Caption         =   "采购日期"
         Height          =   255
         Left            =   -73200
         TabIndex        =   9
         Top             =   6060
         Width           =   975
      End
      Begin VB.Label Label7 
         Alignment       =   1  'Right Justify
         Caption         =   "采购数量"
         Height          =   255
         Left            =   -73200
         TabIndex        =   8
         Top             =   5220
         Width           =   975
      End
      Begin VB.Label Label6 
         Alignment       =   1  'Right Justify
         Caption         =   "进价"
         Height          =   255
         Left            =   -73200
         TabIndex        =   7
         Top             =   4560
         Width           =   975
      End
      Begin VB.Label Label5 
         Alignment       =   1  'Right Justify
         Caption         =   "零售价"
         Height          =   255
         Left            =   -73200
         TabIndex        =   6
         Top             =   3960
         Width           =   975
      End
      Begin VB.Label Label4 
         Alignment       =   1  'Right Justify
         Caption         =   "商品规格"
         Height          =   255
         Left            =   -73200
         TabIndex        =   5
         Top             =   3360
         Width           =   975
      End
      Begin VB.Label Label3 
         Alignment       =   1  'Right Justify
         Caption         =   "生产厂商"
         Height          =   255
         Left            =   -73200
         TabIndex        =   4
         Top             =   2700
         Width           =   975
      End
      Begin VB.Label Label2 
         Alignment       =   1  'Right Justify
         Caption         =   "商品名称"
         Height          =   255
         Left            =   -73200
         TabIndex        =   3
         Top             =   1980
         Width           =   975
      End
      Begin VB.Label Label1 
         Alignment       =   1  'Right Justify
         Caption         =   "条形码"
         Height          =   255
         Left            =   -73200
         TabIndex        =   1
         Top             =   1380
         Width           =   975
      End
   End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim rsKucun As ADODB.Recordset
Dim stqSql As String, strMsg As String
Dim flag As Boolean
Dim Money As Double
Private Sub cmdAdd_Click()
    strsql = "select * from 库存表 where 条形码 = '" & txtTiao.Text & "'"
    Set rsKucun = ExecuteSQL(strsql, strMsg)
    
    If rsKucun.PageCount = 0 Then
        MsgBox "不存在此商品！"
        Exit Sub
    End If
    
    rsKucun.MoveFirst
    MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 0) = rsKucun.Fields("条形码").Value
    MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 1) = rsKucun.Fields("商品名称").Value
    MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 2) = txtShu.Text
    MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 3) = rsKucun.Fields("零售价").Value
    
    MSFlexGrid1.Rows = Val(MSFlexGrid1.Rows) + 1
    MSFlexGrid1.Row = Val(MSFlexGrid1.Row) + 1
End Sub

Private Sub cmdChakan_Click()
    strsql = "select * from 库存表 where 条形码 = '" & txtTXM.Text & "'"
    Set rsKucun = ExecuteSQL(strsql, strMsg)
    
    If rsKucun.PageCount = 0 Then   '不存在库存记录
        flag = False
        txtName.Text = ""
        txtSC.Text = ""
        txtGuige.Text = ""
        txtLing.Text = ""
        
    Else    '存在库存记录时
        flag = True
        rsKucun.MoveFirst
        txtName.Text = rsKucun.Fields("商品名称").Value
        txtSC.Text = rsKucun.Fields("生产厂商").Value
        txtGuige.Text = rsKucun.Fields("商品规格").Value
        txtLing.Text = rsKucun.Fields("零售价").Value
    End If
End Sub

Private Sub cmdCls_Click()
    MSFlexGrid1.Clear
    MSFlexGrid1.Row = 1
    MSFlexGrid1.Rows = 2
    MSFlexGrid1.TextMatrix(0, 0) = "条形码"
    MSFlexGrid1.TextMatrix(0, 1) = "商品名称"
    MSFlexGrid1.TextMatrix(0, 2) = "数量"
    MSFlexGrid1.TextMatrix(0, 3) = "零售价"
End Sub

Private Sub cmdQT_Click()
    Dim rsResult As ADODB.Recordset
    If Trim(Combo1.Text) = "" Then
        MsgBox "请选择类型"
        Exit Sub
    End If
    
    strsql = "select * from " & Trim(Combo1.Text) & " where 条形码 = '" & txtTiaoJS.Text & "'"
    Set rsResult = ExecuteSQL(strsql, strMsg)
    Set DataGrid1.DataSource = rsResult
    
End Sub

Private Sub cmdQueren_Click()
    Dim rsJinhuo As ADODB.Recordset
    
    '修改进货表
    strsql = "select * from 进货表"
    Set rsJinhuo = ExecuteSQL(strsql, strMsg)
    rsJinhuo.AddNew
    rsJinhuo.Fields("条形码").Value = txtTXM.Text
    rsJinhuo.Fields("进价").Value = txtJin.Text
    rsJinhuo.Fields("采购数量").Value = txtNumber.Text
    rsJinhuo.Fields("日期时间").Value = txtDate.Text
    rsJinhuo.UpdateBatch adAffectCurrent
    
    '修改库存表
    If flag = True Then
        rsKucun.Fields("库存量").Value = Val(rsKucun.Fields("库存量").Value) + Val(txtNumber.Text)
        rsKucun.UpdateBatch adAffectCurrent
    Else
        rsKucun.AddNew
        rsKucun.Fields("条形码") = txtTXM.Text
        rsKucun.Fields("商品名称") = txtName.Text
        rsKucun.Fields("生产厂商") = txtSC.Text
        rsKucun.Fields("库存量") = txtNumber.Text
        rsKucun.Fields("商品规格") = txtGuige.Text
        rsKucun.Fields("零售价") = txtLing.Text
        rsKucun.UpdateBatch adAffectCurrent
    End If
    
    MsgBox "提交成功！"
End Sub



Private Sub cmdQuerryK_Click()
    Dim str1 As String, str2 As String
    Dim rsResult As ADODB.Recordset
    str1 = "": str2 = ""
    
    strsql = "select * from 库存表"
    
    If Trim(txtTiaoQ.Text) <> "" Then
        str1 = " 条形码 = '" & Trim(txtTiaoQ.Text) & "' "
    End If
    If Trim(txtNameQ.Text) <> "" Then
        str2 = " 商品名称 like '%" & Trim(txtNameQ.Text) & "%' "
    End If
    
    If Trim(txtTiaoQ.Text) <> "" And Trim(txtNameQ.Text) <> "" Then
        If Trim(Combo2.Text) = "" Then
            MsgBox "请选择连词"
            Exit Sub
        End If
        strsql = strsql + " where " & str1 & Combo2.Text & str2
    Else
        If Trim(txtTiaoQ.Text) <> "" Or Trim(txtNameQ.Text) <> "" Then
            strsql = strsql + " where " & str1 & str2
        End If
    End If
    
    Set rsResult = ExecuteSQL(strsql, strMsg)
    Set DataGrid1.DataSource = rsResult
End Sub

Private Sub cmdSQ_Click()
    Dim rsResult As ADODB.Recordset
    If Trim(Combo1.Text) = "" Then
        MsgBox "请选择类型"
        Exit Sub
    End If
    
    strsql = "select * from " & Trim(Combo1.Text) & _
            "  where 条形码 in (select 条形码 from 库存表 where 商品名称  like '%" & Trim(txtSName.Text) & "%') "
    Set rsResult = ExecuteSQL(strsql, strMsg)
    Set DataGrid1.DataSource = rsResult
    
End Sub

Private Sub cmdTijiao_Click()
    Dim i As Integer, j As Double
    Dim rsShouhuo As ADODB.Recordset
    
    Money = 0
    Set rsShouhuo = ExecuteSQL("select * from 售货表", strMsg)
    For i = 1 To MSFlexGrid1.Row - 1
        rsShouhuo.AddNew
        rsShouhuo.Fields("条形码").Value = MSFlexGrid1.TextMatrix(i, 0)
        rsShouhuo.Fields("数量").Value = MSFlexGrid1.TextMatrix(i, 2)
        rsShouhuo.Fields("应收金额").Value = Val(MSFlexGrid1.TextMatrix(i, 2)) * Val(MSFlexGrid1.TextMatrix(i, 3))
        rsShouhuo.Fields("日期时间").Value = Format(Date, "yyyy-mm-dd") & " " & Format(Time, "hh-nn-ss")
        
        Money = Money + Val(MSFlexGrid1.TextMatrix(i, 2)) * Val(MSFlexGrid1.TextMatrix(i, 3))
        '修改库存量
        strsql = "select * from 库存表 where 条形码 = '" & MSFlexGrid1.TextMatrix(i, 0) & "'"
        Set rsKucun = ExecuteSQL(strsql, strMsg)
        rsKucun.MoveFirst
        rsKucun.Fields("库存量").Value = Val(rsKucun.Fields("库存量").Value) - Val(txtShu.Text)
        rsKucun.UpdateBatch adAffectCurrent
    Next i
    rsShouhuo.UpdateBatch adAffectCurrent
    
    j = Val(InputBox("请输入实收金额", , , Me.Width / 3, Me.Height / 3))
    
    MsgBox "提交成功！ 找零" & j - Money & "元"
End Sub

Private Sub cmdTQ_Click()
    Dim rsResult As ADODB.Recordset
    If Trim(Combo1.Text) = "" Then
        MsgBox "请选择类型"
        Exit Sub
    End If
    
    strsql = "select * from " & Trim(Combo1.Text) & " where 日期时间 between  '" & txtStart.Text & "' and '" & txtEnd.Text & "'"
    Set rsResult = ExecuteSQL(strsql, strMsg)
    Set DataGrid1.DataSource = rsResult
    
End Sub

Private Sub Form_Load()
    
    MSFlexGrid1.ColWidth(0) = 2000
    MSFlexGrid1.ColWidth(1) = 2000
    MSFlexGrid1.ColWidth(2) = 2000
    MSFlexGrid1.ColWidth(3) = 2000
    
    MSFlexGrid1.TextMatrix(0, 0) = "条形码"
    MSFlexGrid1.TextMatrix(0, 1) = "商品名称"
    MSFlexGrid1.TextMatrix(0, 2) = "数量"
    MSFlexGrid1.TextMatrix(0, 3) = "零售价"
    
End Sub



Private Sub txtEnd_KeyUp(KeyCode As Integer, Shift As Integer)
    cmdTQ_Click
End Sub

Private Sub txtShu_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then cmdAdd_Click
End Sub





Private Sub txtSName_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then cmdSQ_Click
End Sub

Private Sub txtTiaoJS_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then cmdQT_Click
End Sub

Private Sub txtTXM_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then cmdChakan_Click
End Sub