frmfphz.frm

一个简单的用vb制作的公司贸易管理系统

    If RstQD.EOF Then Exit Sub

    FrmSELL_QD.LblFPHM.Caption = RstQD.Fields(0)
    FrmSELL_QD.lblxsrq.Caption = RstQD.Fields(1)
    FrmSELL_QD.TxtSHR.Caption = RstQD.Fields(2)
    FrmSELL_QD.TxtSHR1.Caption = RstQD.Fields(3)

    FrmSELL_QD.lblJE.Caption = Format(RstQD.Fields(4), "0.00")
    FrmSELL_QD.lblSE.Caption = Format(RstQD.Fields(5), "0.00")
    FrmSELL_QD.lblJSHJ.Caption = Format(RstQD.Fields(6), "0.00")

    FrmSELL_QD.Grid.Clear
    FrmSELL_QD.Grid.Rows = 1
    FrmSELL_QD.Grid.FormatString = "序号|^  商  品  名  称   |^ 货  号 |^ 规  格  |^ 村  料  |^ 数  量 |^ 单  价 |^ 金   额  |^ 税  率  |^ 税  额  |^ 金 税 合 计"

    Do While Not RstQD.EOF
        FrmSELL_QD.Grid.Rows = FrmSELL_QD.Grid.Rows + 1
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 0) = RstQD.Fields(17)
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 1) = RstQD.Fields(7)
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 2) = RstQD.Fields(8)
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 3) = RstQD.Fields(9)
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 4) = RstQD.Fields(10)
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 5) = RstQD.Fields(11)
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 6) = Format(RstQD.Fields(12), "0.00")
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 7) = Format(RstQD.Fields(13), "0.00")
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 8) = RstQD.Fields(14)
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 9) = Format(RstQD.Fields(15), "0.00")
        FrmSELL_QD.Grid.TextMatrix(FrmSELL_QD.Grid.Rows - 1, 10) = Format(RstQD.Fields(16), "0.00")
        SumSL = SumSL + Val(RstQD.Fields(11))

        RstQD.MoveNext
    Loop
    FrmSELL_QD.lblSL.Caption = SumSL
    FrmSELL_QD.Show 1
End Sub

Private Sub Command4_Click()
    Call FPPrint

End Sub

Private Sub Command5_Click()
    Unload Me
End Sub

Private Sub Command6_Click()
    Call ReSet
    Call FillGrid
End Sub

Private Sub Command7_Click()
    Dim Rst1, Rst2, Rst3 As ADODB.Recordset

    If MsgBox("你真的要删除该发票，此操作不可逆！", vbOKCancel + 48, "警告") = vbOK Then
        '删除销售单总表
        '---------------------------------------------------------------------------------------

        SQL = "delete from 销售总表 where 销售id='" & Grid.TextMatrix(Grid.RowSel, 0) & "'"

        Set RstDel = ExecuteSQL(SQL, Msgtext)

        '---------------------------------------------------------------------------------------
        '删除销售单明细表，并更新库存动态表
        '---------------------------------------------------------------------------------------
        'Set Rst1 = New Recordset
        '            SQL = "select fphm,sl,spid from xsd_mx where fphm='" & Grid.TextMatrix(Grid.RowSel, 0) & "'"
        '            'Rst1.Open SQL, Db, 1, 3
        '            Set Rst1 = ExecuteSQL(SQL, MsgText)

        '            Do While Not Rst1.EOF
        '               ' Set Rst2 = New Recordset
        '                    SQL = "update kcdtb set sl=sl+" & Rst1.Fields(1) & " where spid =" & Rst1.Fields(2)
        '                    'Rst2.Open SQL, Db, 1, 3
        '                Set Rst2 = ExecuteSQL(SQL, MsgText)
        '
        '                    Rst1.MoveNext
        '            Loop

        SQL = "delete  from 销售表 where 销售id='" & Grid.TextMatrix(Grid.RowSel, 0) & "'"

        Set Rst3 = ExecuteSQL(SQL, Msgtext)

        '---------------------------------------------------------------------------------------
    End If
    Call ReSet
    Call FillGrid
End Sub

Private Sub Command8_Click()
    If Grid.TextMatrix(Grid.RowSel, 7) = "是" Then
        MsgBox "该笔货款已经结帐，请勿重复结帐！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    Else
        If MsgBox("请确认该笔货款已到帐！", vbOKCancel + vbInformation, "提示") = vbOK Then

            SQL = "update 销售总表 set 结账是否=1 where 销售id='" & Grid.TextMatrix(Grid.RowSel, 0) & "'"
            Set Rst = ExecuteSQL(SQL, Msgtext)

        End If
    End If

    Call ReSet
    Call FillGrid

End Sub

Private Sub Form_Load()
    DTP1.Value = Date$
    DTP2.Value = Date$

    Call ReSet
    Call FillGrid
    CmdFlag = 0
End Sub
Sub ReSet()
    Grid.Clear
    Grid.Rows = 1
    Grid.FormatString = "合 同 编 号|^  客   户   名   称  |^ 落 单 日 期 |^ 出 货 日 期  |^ 总 金 额 |^ 税  额 |^ 金 税 合 计 |^结帐否"
End Sub
Sub FillGrid()

    SQL = "select * from 销售总表" & SQLTJ & " order by 销售ID"

    Set Rst = ExecuteSQL(SQL, Msgtext)

    If Rst.EOF Then
        If CmdFlag = 1 Then
            MsgBox "找不到此发票，请确认发票输入正确！", vbOKOnly + vbInformation, "提示"
        ElseIf CmdFlag = 2 Then
            MsgBox "在此日期段无发票！", vbOKOnly + vbInformation, "提示"
        End If
        SQLTJ = ""
        Exit Sub
    End If

    Do While Not Rst.EOF
        Grid.Rows = Grid.Rows + 1
        Grid.TextMatrix(Grid.Rows - 1, 0) = Rst.Fields(0)
        Grid.TextMatrix(Grid.Rows - 1, 1) = Rst.Fields(1)
        Grid.TextMatrix(Grid.Rows - 1, 2) = Rst.Fields(2)
        Grid.TextMatrix(Grid.Rows - 1, 3) = Rst.Fields(3)
        Grid.TextMatrix(Grid.Rows - 1, 4) = Rst.Fields(4)
        Grid.TextMatrix(Grid.Rows - 1, 5) = Rst.Fields(5)
        Grid.TextMatrix(Grid.Rows - 1, 6) = Rst.Fields(6)
        If Rst.Fields(7) = 1 Then
            Grid.TextMatrix(Grid.Rows - 1, 7) = "是"
        Else
            Grid.TextMatrix(Grid.Rows - 1, 7) = "否"
        End If
        'Grid.TextMatrix(Grid.Rows - 1, 8) = Rst.Fields(6)
        Rst.MoveNext
    Loop

    SQLTJ = ""
    CmdFlag = 0
End Sub

Private Sub Grid_DblClick()
    Call Command3_Click
End Sub

Private Sub Grid_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        Call Command3_Click
    End If
End Sub

Private Sub Option1_Click()

    SQLTJ = " where 结账是否=0 "

    Call ReSet
    Call FillGrid
End Sub

Private Sub Option2_Click()

    SQLTJ = " where 结账是否<>0 "

    Call ReSet
    Call FillGrid
End Sub

Private Sub Option3_Click()
    Call Command6_Click
End Sub

Private Sub FPPrint()
    Dim t As Integer
    Dim j As Integer
    Dim N As Integer

    Set XSDExcel = New Excel.Application
    XSDExcel.Visible = True

    'Set XSDExcel = Nothing
    XSDExcel.SheetsInNewWorkbook = 1
    Set zsbworkbook = XSDExcel.Workbooks.Open(App.Path + "\" + "sheet\销售单汇总.xlt")
    With XSDExcel.ActiveSheet

        For t = 1 To Grid.Rows - 1
            a = "A" + CStr(t + 4)
            b = "B" + CStr(t + 4)
            c = "C" + CStr(t + 4)
            d = "D" + CStr(t + 4)
            e = "E" + CStr(t + 4)
            f = "F" + CStr(t + 4)
            g = "G" + CStr(t + 4)
            h = "H" + CStr(t + 4)

            .Range(a).Value = Grid.TextMatrix(t, 0)
            .Range(b).Value = Grid.TextMatrix(t, 1)
            .Range(c).Value = Grid.TextMatrix(t, 2)
            .Range(d).Value = Grid.TextMatrix(t, 3)
            .Range(e).Value = Grid.TextMatrix(t, 4)
            .Range(f).Value = Grid.TextMatrix(t, 5)
            .Range(g).Value = Grid.TextMatrix(t, 6)
            .Range(h).Value = Grid.TextMatrix(t, 7)

        Next t

    End With
    'dd = MsgBox("yes or no", vbYesNo + vbSystemModal)
    'If dd = vbNo Then Exit Sub
    '    XSDExcel.ActiveSheet.PageSetup.Orientation = xlPortrait       'xlLandscape
    '    XSDExcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4
    XSDExcel.Caption = "打印预览"
    XSDExcel.ActiveWindow.SelectedSheets.PrintPreview
    '    XSDExcel.ActiveSheet.PrintOut
    XSDExcel.DisplayAlerts = False
    XSDExcel.Quit
    XSDExcel.DisplayAlerts = True
    Set XSDExcel = Nothing

Exit Sub

    'Errorhandler:
    '    MsgBox "请正确安装EXCEL或检查打印机状况！", vbOKOnly + vbCritical

Exit Sub

End Sub