main_rcyw_xsth.frm

商品进销存管理系统 采用VB和SQL2000开发 具有很强的实用性

    If KeyCode = vbKeyPageDown Then  '按PageDown键，dblProvider显示所有记录
        dblProvider.ReFill
        dblProvider.Visible = True
        dblProvider.SetFocus
    End If
End Sub
Private Sub dgdStock_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        With adoXS.Recordset
            If .RecordCount > 0 Then    '判断是否有记录
                If .Fields("商品名称") <> "" Then
                    '赋值给Flex1表格
                    If .Fields("商品编号") <> "" Then Flex1.TextMatrix(Flex1.Row, 2) = Trim(.Fields("商品编号"))
                    If .Fields("商品名称") <> "" Then Flex1.TextMatrix(Flex1.Row, 1) = Trim(.Fields("商品名称"))
                    If .Fields("批号") <> "" Then Flex1.TextMatrix(Flex1.Row, 3) = Trim(.Fields("批号"))
                    If .Fields("产地") <> "" Then Flex1.TextMatrix(Flex1.Row, 4) = Trim(.Fields("产地"))
                    If .Fields("规格") <> "" Then Flex1.TextMatrix(Flex1.Row, 5) = Trim(.Fields("规格"))
                    If .Fields("包装") <> "" Then Flex1.TextMatrix(Flex1.Row, 6) = Trim(.Fields("包装"))
                    If .Fields("单位") <> "" Then Flex1.TextMatrix(Flex1.Row, 7) = Trim(.Fields("单位"))

                    If .Fields("销售价") <> "" Then Flex1.TextMatrix(Flex1.Row, 9) = .Fields("销售价")
                    '赋值给txtInput
                    txtInput.text = Flex1.text
                    txtInput.SetFocus
                    Flex1.Col = 8
                    dgdStock.Visible = False
                Else
                    MsgBox ("无数据选择！")
                    dgdStock.Visible = False
                    txtInput.SetFocus
                End If
            End If
            txtInput.SetFocus
        End With
    End If
    If KeyCode = vbKeyEscape Then    '按ESC键dgdStock不可见
        dgdStock.Visible = False
        txtInput.SetFocus     'txtInput获得焦点
    End If
End Sub
Private Sub dblProvider_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        ProviderNumber = Val(dblProvider.BoundText)
        txtProvider.text = dblProvider.text
        dblProvider.Visible = False
        txtProvider.SetFocus
    End If
End Sub
Private Sub Flex1_Click()     '单击Flex1表，txtInput表可见并获得焦点
    If Flex1.Row > 1 And Flex1.TextMatrix(Flex1.Row - 1, 7) <> "" Then
        txtInput.Visible = True
        txtInput.SetFocus
    End If
    If Flex1.Row = 1 Then
        txtInput.Visible = True
        txtInput.SetFocus
    End If
End Sub

Private Sub Flex1_entercell()    '确定txtInput在Flex1表中的位置
    Dim X, Y, P As String
    If Flex1.CellWidth <= 0 Or Flex1.CellHeight <= 0 Then Exit Sub
    X = Flex1.TextMatrix(Flex1.FixedRows, Flex1.Col)
    Y = Flex1.TextMatrix(Flex1.Row, 0)
    If Y <> "" Then
        If Flex1.Col - Flex1.LeftCol <= 3 Then
            Flex1.LeftCol = Flex1.LeftCol + 1
        End If
        If Flex1.CellWidth > 0 And Flex1.CellHeight > 0 Then
            txtInput.Width = Flex1.CellWidth
            txtInput.Height = Flex1.CellHeight
            txtInput.Left = Flex1.CellLeft + Flex1.Left
            txtInput.Top = Flex1.CellTop + Flex1.Top
        End If
        X = Flex1.TextMatrix(Flex1.FixedRows, Flex1.Col)
        Y = Flex1.TextMatrix(Flex1.Row, 0)
        P = Flex1.TextMatrix(Flex1.Row, Flex1.Col)
        txtInput.text = Flex1.text
    End If
End Sub
Private Sub Flex1_RowColChange()
    '格式化Flex1表的第7列、第8列、第9列、第11列
    For i = 1 To 101
        With Flex1
            .TextMatrix(.Row, 9) = Format(.TextMatrix(.Row, 9), "0.0000")
            .TextMatrix(.Row, 10) = Val(.TextMatrix(.Row, 8)) * Val(.TextMatrix(.Row, 9))
            .TextMatrix(.Row, 10) = Format(.TextMatrix(.Row, 10), "0.0000")
        End With
    Next i
End Sub

Private Sub txtInput_KeyDown(KeyCode As Integer, Shift As Integer)
    With Flex1
        txtInput.text = .text
        If KeyCode = vbKeyReturn Then
            dgdStock.Visible = False
            If .Col = 1 Then
                If adoXS.Recordset.RecordCount > 0 Then
                    '赋值给表格
                    .TextMatrix(.Row, 2) = adoXS.Recordset.Fields("商品编号")
                    .TextMatrix(.Row, 1) = adoXS.Recordset.Fields("商品名称")
                    .TextMatrix(.Row, 3) = adoXS.Recordset.Fields("批号")
                    .TextMatrix(.Row, 4) = adoXS.Recordset.Fields("产地")
                    .TextMatrix(.Row, 5) = adoXS.Recordset.Fields("规格")
                    .TextMatrix(.Row, 6) = adoXS.Recordset.Fields("包装")
                    .TextMatrix(.Row, 7) = adoXS.Recordset.Fields("单位")
                    .TextMatrix(.Row, 9) = adoXS.Recordset.Fields("销售价")
                    dgdStock.Visible = False
                    .Col = 6
                End If
            End If

            If .Col = 11 Then
                .Row = .Row + 1
                .Col = 1
            Else
                If .Col + 1 <= .Cols - 1 Then
                    .Col = .Col + 1
                Else
                    If .Row + 1 <= .Rows - 1 Then
                        .Row = .Row + 1
                        .Col = 1
                    End If
                End If

            End If

            txtInput.SetFocus
            txtInput.SelStart = 0
            txtInput.SelLength = Len(txtInput)
        End If
        If KeyCode = vbKeyUp Then
            If .Row > 1 Then .Row = .Row - 1
        End If
        If KeyCode = vbKeyDown Then
            If .Row < 99 Then .Row = .Row + 1
        End If
        If KeyCode = vbKeyLeft Then
            If .Col - 9 <= .Cols + 1 Then
                .Col = .Col - 1
                If .Col = 0 Then .Col = 1
            Else
                If .Row + 1 <= .Row - 1 Then
                    .Row = .Row + 1
                    .Col = 1
                End If
            End If
        End If
        If KeyCode = vbKeyRight Then
            If .Col + 1 <= .Cols - 1 Then
                .Col = .Col + 1
            Else
                If .Row + 1 <= .Rows - 1 Then
                    .Row = .Row + 1
                    .Col = 1
                End If
            End If
        End If
        If KeyCode = vbKeyPageDown Then
            If .Col = 1 Then
                adoXS.RecordSource = "select * from tb_xsd where 票号='" + txtxsph + "'order by 商品编号"
                adoXS.Refresh
                dgdStock.Visible = True
                dgdStock.SetFocus
            End If
        End If
    End With
End Sub
Private Sub txtInput_Change()
    Flex1.text = txtInput.text   '将txtInput的值赋给Flex1
    If Flex1.Col = 1 Then
        If txtInput.text = "" Then
            dgdStock.Visible = False
        Else
            '筛选商品名称符合txtInput的记录
            adoXS.RecordSource = "select * from tb_xsd where 商品名称 like '" + txtInput + "'+ '%'and 票号='" + txtxsph + "'order by 商品编号"
            adoXS.Refresh
            If adoXS.Recordset.RecordCount > 0 Then
                dgdStock.Visible = True
                txtInput.SetFocus
            End If
        End If
    End If
    If Flex1.Col = 3 Then
        If Flex1.TextMatrix(Flex1.Row, 1) = "" Then
            MsgBox "无商品名称,请重新输入！", , Me.Caption
            Flex1.Col = 1
        End If
    End If
    If Flex1.Col = 8 Or Flex1.Col = 9 Or Flex1.Col = 10 Then
        '求金额
        Flex1.TextMatrix(Flex1.Row, 10) = Val(Flex1.TextMatrix(Flex1.Row, 8)) * Val(Flex1.TextMatrix(Flex1.Row, 9))
    End If
    Dim a, b, c As Single
    For i = 1 To 101
        If Flex1.TextMatrix(i, 1) <> "" And Flex1.TextMatrix(i, 8) <> "" Then
            lblBreed = i     '品种数
            a = Val(Flex1.TextMatrix(i, 10)) + a    '求合计金额
            b = Val(Flex1.TextMatrix(i, 8)) + b    '求合计数量
        End If
    Next i
    lblCount = b
    lblSum = Format(a, "0.0000")     '格式化合计金额
End Sub
Private Sub cmdRegister_Click()
    txtHandle.SetFocus
    '文本框的大小和位置等于Flex1中网格的大小和位置
    txtInput.Width = Flex1.CellWidth
    txtInput.Height = Flex1.CellHeight
    txtInput.Left = Flex1.CellLeft + Flex1.Left
    txtInput.Top = Flex1.CellTop + Flex1.Top
    Flex1.Enabled = True
    Dim tmpNote As Integer
    rs1.Open "tb_xstd order by 退货票号", Cnn, adOpenStatic
    '创建进货票号
    If rs1.RecordCount > 0 Then
        If Not rs1.EOF Then rs1.MoveLast
        If rs1.Fields("退货票号") <> "" Then
            tmpNote = Val(Right(Trim(rs1.Fields("退货票号")), 4)) + 1
            txtthph.text = Date & "xsth" & Format(tmpNote, "0000")
        End If
    Else
        txtthph.text = Date & "xsth" & "0001"
    End If
    rs1.Close
    '清空Flex1网格中的内容
    For i = 1 To 100
        For j = 1 To 10
            Flex1.TextMatrix(i, j) = ""
        Next j
    Next i
    '清空文本框内容
    txtInput.text = ""
    txtHandle.text = ""
    lblBreed.Caption = "0"
    lblCount = "0"
    lblSum = "0"
    '设置控件可用或不可用状态
    cmdSave.Enabled = True
    cmdCancel.Enabled = True
    cmdRegister.Enabled = False
    txtInput.Enabled = True
End Sub

Private Sub cmdSave_Click()
    For i = 1 To 100
        If Flex1.TextMatrix(i, 1) <> "" And Flex1.TextMatrix(i, 8) <> "" Then
            Ado_Update.RecordSource = "select * from tb_xstd"
            Ado_Update.Refresh
'            rs1.Open "tb_xstd", Cnn, adOpenStatic
            '添加新记录到"tb_warehouse_detailed"表中
             
            Ado_Update.Recordset.AddNew
            Ado_Update.Recordset.Fields("商品名称") = Flex1.TextMatrix(i, 1)
            Ado_Update.Recordset.Fields("商品编号") = Flex1.TextMatrix(i, 2)
            Ado_Update.Recordset.Fields("批号") = Flex1.TextMatrix(i, 3)
            Ado_Update.Recordset.Fields("产地") = Flex1.TextMatrix(i, 4)
            Ado_Update.Recordset.Fields("规格") = Flex1.TextMatrix(i, 5)
            Ado_Update.Recordset.Fields("包装") = Flex1.TextMatrix(i, 6)
            Ado_Update.Recordset.Fields("单位") = Flex1.TextMatrix(i, 7)
            Ado_Update.Recordset.Fields("数量") = Val(Flex1.TextMatrix(i, 8))
            Ado_Update.Recordset.Fields("退货价") = Val(Flex1.TextMatrix(i, 9))
            Ado_Update.Recordset.Fields("备注") = Flex1.TextMatrix(i, 11)
            Ado_Update.Recordset.Fields("客户全称") = lblkh
            Ado_Update.Recordset.Fields("销售日期") = lblrq
            Ado_Update.Recordset.Fields("销售票号") = txtxsph
            Ado_Update.Recordset.Fields("销售经手人") = lbljsr
            Ado_Update.Recordset.Fields("退货日期") = txtDate
            Ado_Update.Recordset.Fields("退货票号") = txtthph
            Ado_Update.Recordset.Fields("退货经手人") = txtHandle
            Ado_Update.Recordset.Update
'            rs1.Close
            Cnn.Execute ("update tb_kc set 库存=库存+" & Val(Flex1.TextMatrix(i, 8)) & " where 商品编号='" & Flex1.TextMatrix(i, 2) & "'and 批号='" & Flex1.TextMatrix(i, 3) & "'")
        End If
    Next i
    RegInfo Me
    txtInput.Visible = False
    dgdStock.Visible = False
    Flex1.Enabled = False
    cmdRegister.Enabled = True
    cmdSave.Enabled = False
End Sub
Private Sub cmdCancel_Click()     '取消操作
    For i = 1 To 101
        For j = 1 To 11
            Flex1.TextMatrix(i, j) = ""
        Next j
    Next i
    Flex1.Enabled = False
    txtInput.Visible = False
    lblBreed = "0"
    lblSum.Caption = "0"
    lblCount.Caption = "0"
    '  txtProvider.text = ""
    cmdRegister.Enabled = True
    txtxsph.SetFocus
End Sub
Private Sub cmdQuit_Click()
    Unload Me
End Sub
Private Sub txtxsph_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        adoXS.RecordSource = "select * from tb_xsd where 票号='" + txtxsph + "'"
        adoXS.Refresh
        With adoXS.Recordset
            If .RecordCount > 0 Then
                If .Fields("客户全称") <> "" Then lblkh = .Fields("客户全称")
                If .Fields("经手人") <> "" Then lbljsr = .Fields("经手人")
                lblrq = .Fields("日期")
                cmdRegister.Enabled = True
            End If
        End With
    End If
End Sub