frmyh_dwrjzqc.frm

一个用VB写的财务软件源码

    End Select
End Sub

Private Sub MenuEnable()

End Sub

Private Sub MenuDisable()

End Sub

'新增加一行
Private Sub AddNewRow()
    With tbr
        .Buttons("print").Enabled = False
        .Buttons("preview").Enabled = False
        .Buttons("new").Enabled = False
        .Buttons("save").Enabled = True
        .Buttons("cancel").Enabled = True
        .Buttons("delete").Enabled = False
        .Buttons("filter").Enabled = False
        .Buttons("exit").Enabled = False
        mnuPrint.Enabled = False
        mnuPreview.Enabled = False
        mnuNew.Enabled = False
        mnuSave.Enabled = True
        mnuCancel.Enabled = True
        mnuDelete.Enabled = False
        mnuFilter.Enabled = False
        mnuExit.Enabled = False
    End With

    With mfgDwrjzqc
        CurrentRowNum = .Rows
        .AddItem "" & vbTab & "" & vbTab & "" & vbTab & "" & vbTab & "" & vbTab & "" & vbTab & _
        "" & vbTab & "" & vbTab & "" & vbTab & "" & vbTab & "" & vbTab & "" & vbTab & CurrentRowNum
        .RowHeight(CurrentRowNum) = cboEdit.Height
        .Row = CurrentRowNum
        .Col = 2
    End With
End Sub

'调用查询条件输入窗体
Private Sub QueryConditionInput()
    Myfrmcx.Show 1
    With Myfrmcx
        If .IsOk Then
            If .chkNoFilter = 1 Then
                Call FillGrid(sSQL)
                .chkNoFilter = 0
            Else
                Call FillGrid(GetQueryStr)
            End If
        Else
            If .chkNoFilter = 1 Then
                .chkNoFilter = 0
            End If
        End If
    End With
End Sub

'从"frmYH_yhcxtj"查询条件窗体中得到查询字符串
Private Function GetQueryStr() As String
    With Myfrmcx
        If .chkNoFilter.value = 0 Then
            GetQueryStr = "SELECT kjqj,jlhm,pzzl,pzbh,pzrq,pzzy,fx,je," & _
                            "yhdz_jsfscode,yhdz_bill,yhdz_date,yhdz_lqbz" & _
                            " FROM tZW_Pzsj" & glo.sOperateYear & " WHERE "
            If .txtQsrq.text <> "____-__-__" Then
                Select Case g_FLAT
                    Case "SQL"
                        GetQueryStr = GetQueryStr & "pzrq >= '" & .txtQsrq.text & "' AND "
                    Case "ORACLE"
                        GetQueryStr = GetQueryStr & "pzrq >= TO_DATE('" & .txtQsrq.text & _
                                    "','YYYY-MM-DD') AND "
                End Select
            End If
            If .txtJsrq.text <> "____-__-__" Then
                Select Case g_FLAT
                    Case "SQL"
                        GetQueryStr = GetQueryStr & "pzrq <= '" & .txtJsrq.text & "' AND "
                    Case "ORACLE"
                        GetQueryStr = GetQueryStr & "pzrq <= TO_DATE('" & .txtJsrq.text & _
                                    "','YYYY-MM-DD') AND "
                End Select
            End If
            If .cboJsfs.text <> "" Then
                GetQueryStr = GetQueryStr & "yhdz_jsfsCode = '" & Left(.cboJsfs.text, InStr(.cboJsfs.text, " ") - 1) & "' AND "
            End If
            If .txtBill.text <> "" Then
                GetQueryStr = GetQueryStr & "yhdz_bill = '" & .txtBill.text & "' AND "
            End If
            If .optJf.value Then
                GetQueryStr = GetQueryStr & "fx = '借' AND "
            ElseIf .optDf.value Then
                GetQueryStr = GetQueryStr & "fx = '贷' AND "
            End If
            If .txtQsje.text <> "" Then
                GetQueryStr = GetQueryStr & "je >= " & Val(Format(.txtQsje.text, "###0.00")) & " AND "
            End If
            If .txtJsje.text <> "" Then
                GetQueryStr = GetQueryStr & "je <= " & Val(Format(.txtJsje.text, "###0.00")) & " AND "
            End If
            GetQueryStr = GetQueryStr & "kmdm = '" & frmYH_Yhkmxz.kmdm & "' AND kjqj = 21 AND yhdz_hxbz = 0 ORDER BY pzrq"
        End If
    End With
End Function

'增加当前行数据到表
Private Sub InsertCurrentRow()
    Dim maxJlhm As Integer
    On Error GoTo HandleErr
    IsChangeCurrentTable = True                 '当前表格数据内容已改变
    Set rstTemp = New ADODB.Recordset
    rstTemp.CursorLocation = adUseClient
    sSQLTemp = "SELECT MAX(jlhm) maxJlhm FROM tZW_pzsj" & glo.sOperateYear & _
            " WHERE kjqj = 21"
    rstTemp.Open sSQLTemp, glo.cnnMain, adOpenStatic, adLockReadOnly
    If rstTemp.BOF And rstTemp.EOF Then
        maxJlhm = 0
    ElseIf IsNull(rstTemp.Fields("maxJlhm").value) Then
        maxJlhm = 0
    Else
        maxJlhm = rstTemp.Fields("maxJlhm").value
    End If
    With mfgDwrjzqc
        .TextMatrix(OldRow, 0) = 21
        .TextMatrix(OldRow, 1) = maxJlhm + 1
        '如果借方金额不为零
        If .TextMatrix(OldRow, 7) <> "" Then
            Select Case g_FLAT
                Case "SQL"
                    adoCmd.CommandText = "INSERT INTO tZW_Pzsj" & glo.sOperateYear & _
                        "(ID,kmdm,kjqj,jlhm,pzrq,pzzl,pzbh,yhdz_jsfsCode,yhdz_jsfs,yhdz_Bill,fx,je,yhdz_Date,pzzy,xgbz) " & _
                        "VALUES(" + CStr(GetMaxID("tZW_Pzsj" + glo.sOperateYear, "ID")) + ",'" & frmYH_Yhkmxz.kmdm & "',21," & maxJlhm + 1 & ",'" & _
                                .TextMatrix(OldRow, 2) & "','" & _
                                .TextMatrix(OldRow, 3) & "','" & _
                                .TextMatrix(OldRow, 4) & "','" & _
                                 Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & "','" & _
                                 Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & "','" & _
                                .TextMatrix(OldRow, 6) & "','借'," & _
                                Val(Format(.TextMatrix(OldRow, 7), "###0.00")) & "," & _
                                IIf(.TextMatrix(OldRow, 9) = "", "Null,'", "'" & _
                                Format(.TextMatrix(OldRow, 9), "yyyy-mm-dd") & "','") & _
                                .TextMatrix(OldRow, 10) & "',2)"
                Case "ORACLE"
'====================================================2002.9.5 yao revise=========================================
'                    adoCmd.CommandText = "INSERT INTO tZW_Pzsj" & glo.sOperateYear & _
'                        "(kmdm,kjqj,jlhm,pzrq,pzzl,pzbh,yhdz_jsfsCode,yhdz_Bill,fx,je,yhdz_Date,pzzy,xgbz) " & _
'                        "VALUES('" & frmYH_Yhkmxz.Kmdm & "',21," & maxJlhm + 1 & ",TO_DATE('" & _
'                                .TextMatrix(OldRow, 2) & "','YYYY-MM-DD'),'" & _
'                                .TextMatrix(OldRow, 3) & "','" & _
'                                .TextMatrix(OldRow, 4) & "','" & _
'                                 Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & "','" & _
'                                 Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & "','" & _
'                                .TextMatrix(OldRow, 6) & "','借'," & _
'                                Val(Format(.TextMatrix(OldRow, 7), "###0.00")) & "," & _
'                                IIf(.TextMatrix(OldRow, 9) = "", "Null,'", _
'                                "TO_DATE('" & .TextMatrix(OldRow, 9) & "','YYYY-MM-DD'),'") & _
'                                .TextMatrix(OldRow, 10) & "',2)"
                    adoCmd.CommandText = "INSERT INTO tZW_Pzsj" & glo.sOperateYear & _
                        "(ID,kmdm,kjqj,jlhm,pzrq,pzzl,pzbh,yhdz_jsfsCode,yhdz_jsfs,yhdz_Bill,fx,je,yhdz_Date,pzzy,xgbz) " & _
                        "VALUES(" + CStr(GetMaxID("tZW_Pzsj" + glo.sOperateYear, "ID")) + ",'" & frmYH_Yhkmxz.kmdm & "',21," & maxJlhm + 1 & ",TO_DATE('" & _
                                .TextMatrix(OldRow, 2) & "','YYYY-MM-DD'),'" & _
                                .TextMatrix(OldRow, 3) & "','" & _
                                .TextMatrix(OldRow, 4) & "','" & _
                                 Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & "','" & _
                                 Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & "','" & _
                                .TextMatrix(OldRow, 6) & "','借'," & _
                                Val(Format(.TextMatrix(OldRow, 7), "###0.00")) & "," & _
                                IIf(.TextMatrix(OldRow, 9) = "", "Null,'", _
                                "TO_DATE('" & .TextMatrix(OldRow, 9) & "','YYYY-MM-DD'),'") & _
                                .TextMatrix(OldRow, 10) & "',2)"
'==================================================================================================================
            End Select
        Else
        '如果贷方金额不为零
            Select Case g_FLAT
                Case "SQL"
                    adoCmd.CommandText = "INSERT INTO tZW_Pzsj" & glo.sOperateYear & _
                        "(ID,kmdm,kjqj,jlhm,pzrq,pzzl,pzbh,yhdz_jsfsCode,yhdz_jsfs,yhdz_Bill,fx,je,yhdz_Date,pzzy,xgbz) " & _
                        "VALUES(" + CStr(GetMaxID("tZW_Pzsj" + glo.sOperateYear, "ID")) + ",'" & frmYH_Yhkmxz.kmdm & "',21," & maxJlhm + 1 & ",'" & _
                                .TextMatrix(OldRow, 2) & "','" & _
                                .TextMatrix(OldRow, 3) & "','" & _
                                .TextMatrix(OldRow, 4) & "','" & _
                                 Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & "','" & _
                                 Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & "','" & _
                                .TextMatrix(OldRow, 6) & "','贷'," & _
                                Val(Format(.TextMatrix(OldRow, 8), "###0.00")) & "," & _
                                IIf(.TextMatrix(OldRow, 9) = "", "Null,'", "'" & _
                                Format(.TextMatrix(OldRow, 9), "yyyy-mm-dd") & "','") & _
                                .TextMatrix(OldRow, 10) & "',2)"
                Case "ORACLE"
                    adoCmd.CommandText = "INSERT INTO tZW_Pzsj" & glo.sOperateYear & _
                        "(ID,kmdm,kjqj,jlhm,pzrq,pzzl,pzbh,yhdz_jsfsCode,yhdz_jsfs,yhdz_Bill,fx,je,yhdz_Date,pzzy,xgbz) " & _
                        "VALUES(" + CStr(GetMaxID("tZW_Pzsj" + glo.sOperateYear, "ID")) + ",'" & frmYH_Yhkmxz.kmdm & "',21," & maxJlhm + 1 & ",TO_DATE('" & _
                                .TextMatrix(OldRow, 2) & "','YYYY-MM-DD'),'" & _
                                .TextMatrix(OldRow, 3) & "','" & _
                                .TextMatrix(OldRow, 4) & "','" & _
                                 Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & "','" & _
                                 Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & "','" & _
                                .TextMatrix(OldRow, 6) & "','贷'," & _
                                Val(Format(.TextMatrix(OldRow, 8), "###0.00")) & "," & _
                                IIf(.TextMatrix(OldRow, 9) = "", "Null,'", _
                                "TO_DATE('" & .TextMatrix(OldRow, 9) & "','YYYY-MM-DD'),'") & _
                                .TextMatrix(OldRow, 10) & "',2)"
            End Select
        End If
        adoCmd.Execute
    End With
    Exit Sub
HandleErr:
    MsgBox Err.Description, vbInformation, "提示"
    Exit Sub
End Sub
'修改数据库中表的当前行数据
Private Sub UpdateCurrentRow()

    IsChangeCurrentTable = True                         '当前表格数据内容已改变
    With mfgDwrjzqc
        If .TextMatrix(OldRow, 7) <> "" Then            '借方金额不为零
            Select Case g_FLAT
                Case "SQL"
                      adoCmd.CommandText = "UPDATE tZW_Pzsj" & glo.sOperateYear & _
                                        " SET pzrq = '" & .TextMatrix(OldRow, 2) & _
                                            "',pzzl = '" & .TextMatrix(OldRow, 3) & _
                                            "',pzbh = '" & .TextMatrix(OldRow, 4) & _
                                            "',yhdz_jsfsCode = '" & Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & _
                                            "',yhdz_jsfs = '" & Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & _
                                            "',yhdz_Bill = '" & .TextMatrix(OldRow, 6) & _
                                            "',fx = '借" & _
                                            "',je = " & Val(Format(.TextMatrix(OldRow, 7), "###0.00")) & _
                                            ",yhdz_Date = " & IIf(.TextMatrix(OldRow, 9) = "", "Null", _
                                            "'" & .TextMatrix(OldRow, 9) & "'") & _
                                            ",pzzy = '" & .TextMatrix(OldRow, 10) & _
                                            "' WHERE kjqj = " & .TextMatrix(OldRow, 0) & _
                                            " AND jlhm = " & .TextMatrix(OldRow, 1) & _
                                            " AND pzzl = '" & OldPzzl & _
                                            "' AND pzbh = '" & OldPzbh & "'"
                Case "ORACLE"
                    adoCmd.CommandText = "UPDATE tZW_Pzsj" & glo.sOperateYear & _
                                        " SET pzrq = TO_DATE('" & .TextMatrix(OldRow, 2) & _
                                            "','YYYY-MM-DD'),pzzl = '" & .TextMatrix(OldRow, 3) & _
                                            "',pzbh = '" & .TextMatrix(OldRow, 4) & _
                                            "',yhdz_jsfsCode = '" & Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & _
                                            "',yhdz_jsfs = '" & Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & _
                                            "',yhdz_Bill = '" & .TextMatrix(OldRow, 6) & _
                                            "',fx = '借" & _
                                            "',je = " & Val(Format(.TextMatrix(OldRow, 7), "###0.00")) & _
                                            ",yhdz_Date = " & IIf(.TextMatrix(OldRow, 9) = "", "Null", _
                                            "TO_DATE('" & .TextMatrix(OldRow, 9) & "','YYYY-MM-DD')") & _
                                            ",pzzy = '" & .TextMatrix(OldRow, 10) & _
                                            "' WHERE kjqj = " & .TextMatrix(OldRow, 0) & _
                                            " AND jlhm = " & .TextMatrix(OldRow, 1) & _
                                            " AND pzzl = '" & OldPzzl & _
                                            "' AND pzbh = '" & OldPzbh & "'"
            End Select
        Else
            '贷方金额不为零
            Select Case g_FLAT
                Case "SQL"
                    adoCmd.CommandText = "UPDATE tZW_Pzsj" & glo.sOperateYear & _
                                        " SET pzrq = '" & .TextMatrix(OldRow, 2) & _
                                            "',pzzl = '" & .TextMatrix(OldRow, 3) & _
                                            "',pzbh = '" & .TextMatrix(OldRow, 4) & _
                                            "',yhdz_jsfsCode = '" & Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & _
                                            "',yhdz_jsfs = '" & Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & _
                                            "',yhdz_Bill = '" & .TextMatrix(OldRow, 6) & _
                                            "',fx = '贷" & _
                                            "',je = " & Val(Format(.TextMatrix(OldRow, 8), "###0.00")) & _
                                            ",yhdz_Date = " & IIf(.TextMatrix(OldRow, 9) = "", "Null", "'" & .TextMatrix(OldRow, 9) & "'") & _
                                            ",pzzy = '" & .TextMatrix(OldRow, 10) & _
                                            "' WHERE kjqj = " & .TextMatrix(OldRow, 0) & _
                                            " AND jlhm = " & .TextMatrix(OldRow, 1) & _
                                            " AND pzzl = '" & OldPzzl & _
                                            "' AND pzbh = '" & OldPzbh & "'"
                Case "ORACLE"
                    adoCmd.CommandText = "UPDATE tZW_Pzsj" & glo.sOperateYear & _
                                        " SET pzrq = TO_DATE('" & .TextMatrix(OldRow, 2) & _
                                            "','YYYY-MM-DD'),pzzl = '" & .TextMatrix(OldRow, 3) & _
                                            "',pzbh = '" & .TextMatrix(OldRow, 4) & _
                                            "',yhdz_jsfsCode = '" & Left(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") - 1) & _
                                            "',yhdz_jsfs = '" & Mid(.TextMatrix(OldRow, 5), InStr(.TextMatrix(OldRow, 5), " ") + 1) & _
                                            "',yhdz_Bill = '" & .TextMatrix(OldRow, 6) & _
                                            "',fx = '贷" & _
                                            "',je = " & Val(Format(.TextMatrix(OldRow, 8), "###0.00")) & _
                                            ",yhdz_Date = " & IIf(.TextMatrix(OldRow, 9) = "", "Null", _
                                            "TO_DATE('" & .TextMatrix(OldRow, 9) & "','YYYY-MM-DD')") & _
                                            ",pzzy = '" & .TextMatrix(OldRow, 10) & _
                                            "' WHERE kjqj = " & .TextMatrix(OldRow, 0) & _
                                            " AND jlhm = " & .TextMatrix(OldRow, 1) & _
                                            " AND pzzl = '" & OldPzzl & _
                                            "' AND pzbh = '" & OldPzbh & "'"
            End Select
        End If
        adoCmd.Execute
    End With
End Sub

'按日期自动排序
Private Sub AutoDateSort()
    With mfgDwrjzqc