frmyh_yetjbqc.frm

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

                    
                Else
                    txtDwyf.Tag = Val(txtDwyf.Tag) + .Fields("je").value
                    
                End If
                .MoveNext
            Loop
        End If
    End With
    
    txtYhtzqye.text = Format(txtYhtzqye.Tag, "##,##0.00")
    txtYhys.text = Format(txtYhys.Tag, "##,##0.00")
    txtYhyf.text = Format(txtYhyf.Tag, "##,##0.00")
    txtDwtzqye.text = Format(txtDwtzqye.Tag, "##,##0.00")
    txtDwys.text = Format(txtDwys.Tag, "##,##0.00")
    txtDwyf.text = Format(txtDwyf.Tag, "##,##0.00")
    
    txtYhtzhye.text = Format(Val(Format(txtYhtzqye.Tag, "###0.00")) + _
                             Val(Format(txtDwys.Tag, "###0.00")) - _
                             Val(Format(txtDwyf.Tag, "###0.00")), "##,##0.00")
    txtDwtzhye.text = Format(Val(Format(txtDwtzqye.Tag, "###0.00")) + _
                             Val(Format(txtYhys.Tag, "###0.00")) - _
                             Val(Format(txtYhyf.Tag, "###0.00")), "##,##0.00")
End Sub

'点击对账单期初按钮
Private Sub cmdDzdqc_Click()
    frmYH_Yhdzdqc.Show 1
    Call FillText
End Sub

'点击日记账期初按钮
Private Sub cmdRjzqc_Click()
    frmYH_Dwrjzqc.Show 1
    Call FillText
End Sub

Private Sub dtpQyrq_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        SendKeys "{tab}"
    End If
End Sub
Private Sub dtpQyrq_Validate(Cancel As Boolean)
    If IsTheSameYear Then
        If Year(Format(dtpQyrq.value, "yyyy-mm-dd")) < CInt(SubSysBeginYear) Then
            MsgBox "银行对账启用日期不能小于账务子系统启用日期！", vbOKOnly + vbInformation
            dtpQyrq.value = sQyrq
            Cancel = True
            Exit Sub
        ElseIf Year(Format(dtpQyrq.value, "yyyy-mm-dd")) = CInt(SubSysBeginYear) Then
                If Month(Format(dtpQyrq.value, "yyyy-mm-dd")) < CInt(SubSysBeginMonth) Then
                    MsgBox "银行对账启用日期不能小于账务子系统启用日期！", vbOKOnly + vbInformation
                    dtpQyrq.value = sQyrq
                    Cancel = True
                    Exit Sub
                End If
        End If
        '如果银行对账启用日期被修改, 检查是否允许修改
        If sQyrq <> Format(dtpQyrq.value, "yyyy-mm-dd") Then
            Set rSt = New ADODB.Recordset
            rSt.CursorLocation = adUseClient
            
            Select Case g_FLAT
                Case "SQL"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "'  AND qcbz = 1" & _
                           " AND rq >= '" & Format(dtpQyrq.value, "yyyy-mm-dd") & "'"
                Case "ORACLE"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND qcbz = 1" & _
                           " AND rq >= TO_DATE('" & Format(dtpQyrq.value, "yyyy-mm-dd") & _
                           "','YYYY-MM-DD')"
            End Select
            rSt.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rSt.Fields("numJls").value > 0 Then
                MsgBox "该账户存在日期大于或等于启用日期的期初银行对账单记录, 不能修改启用日期！", vbOKOnly + vbInformation
                dtpQyrq.value = sQyrq
                Cancel = True
                Exit Sub
            End If
            Set rSt = New ADODB.Recordset
            rSt.CursorLocation = adUseClient
            Select Case g_FLAT
                Case "SQL"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_pzsj" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND kjqj = 21" & _
                           " AND pzrq >= '" & Format(dtpQyrq.value, "yyyy-mm-dd") & "'"
                Case "ORACLE"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_pzsj" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND kjqj = 21" & _
                           " AND pzrq >= TO_DATE('" & _
                           Format(dtpQyrq.value, "yyyy-mm-dd") & "','YYYY-MM-DD')"
            End Select
            rSt.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rSt.Fields("numJls").value > 0 Then
                MsgBox "该账户存在日期大于或等于启用日期的期初银行日记账记录, 不能修改启用日期！", vbOKOnly + vbInformation
                dtpQyrq.value = sQyrq
                Cancel = True
                Exit Sub
            End If
            Set rSt = New ADODB.Recordset
            rSt.CursorLocation = adUseClient
            Select Case g_FLAT
                Case "SQL"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                            " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND qcbz = 2" & _
                            " AND rq < '" & Format(dtpQyrq.value, "yyyy-mm-dd") & "'"
                Case "ORACLE"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                            " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND qcbz = 2" & _
                            " AND rq < TO_DATE('" & Format(dtpQyrq.value, "yyyy-mm-dd") & _
                            "','YYYY-MM-DD')"
            End Select
            rSt.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rSt.Fields("numJls").value > 0 Then
                MsgBox "该账户存在日期小于启用日期的银行对账单记录, 不能修改启用日期！", vbOKOnly + vbInformation
                dtpQyrq.value = sQyrq
                Cancel = True
                Exit Sub
            End If
        End If
    End If
End Sub


Private Sub dtpQyrq_LostFocus()
    If IsTheSameYear Then
        If sJzrq < sQyrq Then
            sJzrq = CStr(Format(DateAdd("d", -1, CDate(dtpQyrq.value)), "yyyy-mm-dd"))
        End If
        If IsExistQyrq Then
            Select Case g_FLAT
                Case "SQL"
                    adoCmd.CommandText = "UPDATE tZW_Yhdzqyrq SET qyrq = '" & _
                        Format(dtpQyrq.value, "yyyy-mm-dd") & "', jzrq = '" & _
                        sJzrq & "' WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "'"
                Case "ORACLE"
                    adoCmd.CommandText = "UPDATE tZW_Yhdzqyrq SET qyrq = TO_DATE('" & _
                        Format(dtpQyrq.value, "yyyy-mm-dd") & "','YYYY-MM-DD'), jzrq =TO_DATE('" & _
                        sJzrq & "','YYYY-MM-DD') WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "'"
            End Select
        Else
            Select Case g_FLAT
                Case "SQL"
                    adoCmd.CommandText = "INSERT INTO tZW_Yhdzqyrq VALUES('" & _
                        frmYH_Yhkmxz.Kmdm & "','" & Format(dtpQyrq.value, "yyyy-mm-dd") & _
                        "','" & sJzrq & "')"
                Case "ORACLE"
                    adoCmd.CommandText = "INSERT INTO tZW_Yhdzqyrq VALUES('" & _
                        frmYH_Yhkmxz.Kmdm & "',TO_DATE('" & Format(dtpQyrq.value, "yyyy-mm-dd") & _
                        "','YYYY-MM-DD'),TO_DATE('" & sJzrq & "','YYYY-MM-DD'))"
            End Select
            IsExistQyrq = True
        End If
        adoCmd.Execute
        If sQyrq <> Format(dtpQyrq.value, "yyyy-mm-dd") Then
    '        Call FillText
            sQyrq = Format(dtpQyrq.value, "yyyy-mm-dd")
        End If
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
   
    If IsTheSameYear Then
        If Year(Format(dtpQyrq.value, "yyyy-mm-dd")) < CInt(SubSysBeginYear) Then
            MsgBox "银行对账启用日期不能小于账务子系统启用日期！", vbOKOnly + vbInformation
            dtpQyrq.value = sQyrq
            Cancel = 1
            Exit Sub
        ElseIf Year(Format(dtpQyrq.value, "yyyy-mm-dd")) = CInt(SubSysBeginYear) Then
                If Month(Format(dtpQyrq.value, "yyyy-mm-dd")) < CInt(SubSysBeginMonth) Then
                    MsgBox "银行对账启用日期不能小于账务子系统启用日期！", vbOKOnly + vbInformation
                    dtpQyrq.value = sQyrq
                    Cancel = 1
                    Exit Sub
                End If
        End If
        If sQyrq <> Format(dtpQyrq.value, "yyyy-mm-dd") Then
            Set rSt = New ADODB.Recordset
            rSt.CursorLocation = adUseClient
            Select Case g_FLAT
                Case "SQL"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND qcbz = 1" & _
                           " AND rq >= '" & Format(dtpQyrq.value, "yyyy-mm-dd") & "'"
                Case "ORACLE"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND qcbz = 1" & _
                           " AND rq >= TO_DATE('" & Format(dtpQyrq.value, "yyyy-mm-dd") & _
                           "','YYYY-MM-DD')"
            End Select
            rSt.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rSt.Fields("numJls").value > 0 Then
                MsgBox "该账户存在日期大于或等于启用日期的期初银行对账单记录, 不能修改启用日期！", vbOKOnly + vbInformation
                dtpQyrq.value = sQyrq
                Cancel = 1
                Exit Sub
            End If
            Set rSt = New ADODB.Recordset
            rSt.CursorLocation = adUseClient
            Select Case g_FLAT
                Case "SQL"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_pzsj" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND kjqj = 21" & _
                           " AND pzrq >= '" & Format(dtpQyrq.value, "yyyy-mm-dd") & "'"
                Case "ORACLE"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_pzsj" & glo.sOperateYear & _
                           " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND kjqj = 21" & _
                           " AND pzrq >= TO_DATE('" & _
                           Format(dtpQyrq.value, "yyyy-mm-dd") & "','YYYY-MM-DD')"
            End Select
            rSt.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rSt.Fields("numJls").value > 0 Then
                MsgBox "该账户存在日期大于或等于启用日期的期初银行日记账记录, 不能修改启用日期！", vbOKOnly + vbInformation
                dtpQyrq.value = sQyrq
                Cancel = 1
                Exit Sub
            End If
            Set rSt = New ADODB.Recordset
            rSt.CursorLocation = adUseClient
            Select Case g_FLAT
                Case "SQL"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                            " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND qcbz = 2" & _
                            " AND rq < '" & Format(dtpQyrq.value, "yyyy-mm-dd") & "'"
                Case "ORACLE"
                    sSQL = "SELECT COUNT(*) numJls FROM tZW_Yhdzd" & glo.sOperateYear & _
                            " WHERE kmdm = '" & frmYH_Yhkmxz.Kmdm & "' AND qcbz = 2" & _
                            " AND rq < TO_DATE('" & Format(dtpQyrq.value, "yyyy-mm-dd") & _
                            "','YYYY-MM-DD')"
            End Select
            rSt.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rSt.Fields("numJls").value > 0 Then
                MsgBox "该账户存在日期小于启用日期的银行对账单记录, 不能修改启用日期！", vbOKOnly + vbInformation
                dtpQyrq.value = sQyrq
                Cancel = 1
                Exit Sub
            End If
        End If
        
        Call dtpQyrq_LostFocus
    End If
    Cancel = 0
    Call txtYhtzqye_LostFocus
    Call txtDwtzqye_LostFocus
    Unload frmP
End Sub


Private Sub mnuExit_Click()
    Call Operate("EXIT")
End Sub

Private Sub mnuHelp_Click()
    Call Operate("HELP")
End Sub

Private Sub mnuPreview_Click()
    Call Operate("PREVIEW")
End Sub

Private Sub mnuPrint_Click()
    Call Operate("PRINT")
End Sub

Private Sub tbrYetjbqc_ButtonClick(ByVal Button As MSComctlLib.Button)
    Call Operate(UCase(Button.Key))
End Sub

Private Sub Operate(strKey As String)
    Select Case strKey
        Case "PRINT"
'            Call DogCheck
            Call ShowPrintResult("PRINT")
        Case "PREVIEW"
'            Call DogCheck
            Call ShowPrintResult("PREVIEW")
        Case "HELP"
            Call ShowHelp
        Case "EXIT"
            Unload Me
    End Select
End Sub


Private Sub txtDwtzqye_GotFocus()
    txtDwtzqye.text = Format(txtDwtzqye.text, "###0.00")
    txtDwtzqye.Alignment = 0
    txtDwtzqye.SelStart = 0
    txtDwtzqye.SelLength = Len(txtDwtzqye.text)
End Sub

Private Sub txtDwtzqye_LostFocus()
    Dim maxJlhm As Integer
    txtDwtzqye.Alignment = 1
    If txtDwtzqye.text = "" Then
        txtDwtzqye.text = "0.00"
    Else
        txtDwtzqye.text = Format(txtDwtzqye.text, "##,##0.00")
    End If
    txtDwtzhye.text = Format(Val(Format(txtDwtzqye.text, "###0.00")) + _
                             Val(Format(txtYhys.text, "###0.00")) - _
                             Val(Format(txtYhyf.text, "###0.00")), "##,##0.00")
    If Val(txtDwtzhye.text) = 0 Then
        glo.cnnMain.Execute "Delete from tZW_Pzsj" + glo.sOperateYear + " where kjqj=20 and kmdm='" + frmYH_Yhkmxz.Kmdm + "'"
        Exit Sub
    End If
    If IsExistDwtzqye Then
        Select Case g_FLAT
            Case "SQL"
                adoCmd.CommandText = "UPDATE tZW_pzsj" & glo.sOperateYear & _
                                     " SET pzrq = '" & Format(dtpQyrq.value, "yyyy-mm-dd") & "'," & _
                                          "je = " & Format(txtDwtzqye.text, "###0.00") & _
                                     " WHERE kjqj = 20 AND kmdm = '" & frmYH_Yhkmxz.Kmdm & "'"
            Case "ORACLE"
                adoCmd.CommandText = "UPDATE tZW_pzsj" & glo.sOperateYear & _
                                     " SET pzrq = TO_DATE('" & Format(dtpQyrq.value, "yyyy-mm-dd") & _
                                     "','YYYY-MM-DD')," & _
                                          "je = " & Format(txtDwtzqye.text, "###0.00") & _
                                     " WHERE kjqj = 20 AND kmdm = '" & frmYH_Yhkmxz.Kmdm & "'"
        End Select
    Else
        Set rstTemp = New ADODB.Recordset
        rstTemp.CursorLocation = adUseClient
        sSQL = "SELECT MAX(jlhm) maxJlhm FROM tZW_pzsj" & glo.sOperateYear & " WHERE kjqj = 20"
        rstTemp.Open sSQL, 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
        
        Select Case g_FLAT
            Case "SQL"
                adoCmd.CommandText = "INSERT INTO tZW_pzsj" & glo.sOperateYear & "(id,kjqj,pzzl,pzbh,jlhm,pzrq,kmdm,fx,je,xgbz) " & _