form_tong.frm

运用VB和SQL Server实现

If Me.Check5.Value = 1 Then
    Me.Check4.Value = 0
    Me.Check6.Value = 0
End If
Me.Combo2 = ""
Me.Text4 = ""
Me.Text5 = ""
Me.Text6 = ""
End Sub

Private Sub Check6_Click()
If Me.Check6.Value = 1 Then
    Me.Check5.Value = 0
    Me.Check4.Value = 0
End If
Me.Combo2 = ""
Me.Text4 = ""
Me.Text5 = ""
Me.Text6 = ""
End Sub

Private Sub Command1_Click()    '对借出款进行统计
Dim rsdb As New adodb.Recordset
Dim sum As Integer
Dim Num As Integer
sum = 0
If Me.Check1.Value = 1 Then     '按借出日期统计
    If Me.DTPicker1.Value = "" Or Me.DTPicker2.Value = "" Then
        MsgBox "不能空白"
        Exit Sub
    End If
    '查询相关记录并显示
    Me.Adodc_Tong.RecordSource = "select LendMoneyID as 编号," & _
                                 "LendMoneyDateOut as 借出日期," & _
                                 "LendMoneyDateIn as 预还日期," & _
                                 "LendMoneyNameOut as 出借人," & _
                                 "LendMoneyNameIn as 借贷人," & _
                                 "LendMoneyNum as 借出金额," & _
                                 "LendMoneyBZ as 备注 from LendMoney where LendMoneyDateOut " & _
                                 "between " & "'" & Me.DTPicker1.Value & "'" & " and " & "'" & _
                                 Me.DTPicker2.Value & "'"
    Me.Adodc_Tong.Refresh
    '统计金额
    sum = 0
    rsdb.Open "select * from LendMoney where LendMoneyDateOut between " & "'" & _
                Me.DTPicker1.Value & "'" & " and " & "'" & Me.DTPicker2.Value & _
                "'", RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        For I = 1 To rsdb.RecordCount
            sum = sum + Val(rsdb.Fields("LendMoneyNum").Value)
            If Not rsdb.EOF Then rsdb.MoveNext
        Next I
    End If
    rsdb.Close
    '显示金额
    Me.Text3.Text = str(sum)
    MsgBox "统计结果"
ElseIf Me.Check2.Value = 1 Then '按借出人统计
    If Me.Combo1.Text = "" Then
        MsgBox "不能空白"
        Exit Sub
    End If
    '查找对借出人姓名对应的编号
    rsdb.Open "select * from Family where FamilyName=" & "'" & Me.Combo1.Text & _
              "'", RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        Num = Val(rsdb.Fields("FamilyID").Value)
    End If
    rsdb.Close
    '查询相关记录并显示
    Me.Adodc_Tong.RecordSource = "select LendMoneyID as 编号," & _
                                 "LendMoneyDateOut as 借出日期," & _
                                 "LendMoneyDateIn as 预还日期," & _
                                 "LendMoneyNameOut as 出借人," & _
                                 "LendMoneyNameIn as 借贷人," & _
                                 "LendMoneyNum as 借出金额," & _
                                 "LendMoneyBZ as 备注 from LendMoney where " & _
                                 "LendMoneyNameOut=" & Num
    Me.Adodc_Tong.Refresh
    '统计金额
    sum = 0
    rsdb.Open "select * from LendMoney where LendMoneyNameOut=" & _
              Num, RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        For I = 1 To rsdb.RecordCount
            sum = sum + Val(rsdb.Fields("LendMoneyNum").Value)
            If Not rsdb.EOF Then rsdb.MoveNext
        Next I
    End If
    rsdb.Close
    '显示金额
    Me.Text3.Text = str(sum)
    MsgBox "统计结果"
ElseIf Me.Check3.Value = 1 Then     '按借出金额统计
    If Me.Text1.Text = "" Or Me.Text2.Text = "" Then
        MsgBox "不能空白"
        Exit Sub
    End If
    
    If Not IsNumeric(Me.Text1.Text) Then
        MsgBox "必须是数字"
        Exit Sub
    End If
    
    If Not IsNumeric(Me.Text2.Text) Then
        MsgBox "必须是数字"
        Exit Sub
    End If
    
    '查询相关记录并显示
    Me.Adodc_Tong.RecordSource = "select LendMoneyID as 编号," & _
                                 "LendMoneyDateOut as 借出日期," & _
                                 "LendMoneyDateIn as 预还日期," & _
                                 "LendMoneyNameOut as 出借人," & _
                                 "LendMoneyNameIn as 借贷人," & _
                                 "LendMoneyNum as 借出金额," & _
                                 "LendMoneyBZ as 备注 from LendMoney where LendMoneyNum " & _
                                 "between " & Val(Me.Text1.Text) & " and " & Val(Me.Text2.Text)
    Me.Adodc_Tong.Refresh
    '统计金额
    sum = 0
    rsdb.Open "select * from LendMoney where LendMoneyNum between " & _
                    Val(Me.Text1.Text) & " and " & Val(Me.Text2.Text), _
                    RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        For I = 1 To rsdb.RecordCount
            sum = sum + Val(rsdb.Fields("LendMoneyNum").Value)
            If Not rsdb.EOF Then rsdb.MoveNext
        Next I
    End If
    rsdb.Close
    '显示金额
    Me.Text3.Text = str(sum)
    MsgBox "统计结果"
End If
End Sub

Private Sub Command2_Click()
Unload Me
End Sub

Private Sub Command3_Click()    '借入款统计
Dim rsdb As New adodb.Recordset
Dim sum As Integer
Dim Num As Integer
sum = 0
If Me.Check4.Value = 1 Then     '按借入日期统计
    If Me.DTPicker3.Value = "" Or Me.DTPicker4.Value = "" Then
        MsgBox "不能空白"
        Exit Sub
    End If
    '查询相关记录并显示
    Me.Adodc_Tong1.RecordSource = "select BorrowMoneyID as 编号," & _
                                  "BorrowMoneyDateIn as 借入日期," & _
                                  "BorrowMoneyDateOut as 预还日期," & _
                                  "BorrowMoneyNameIn as 借入人," & _
                                  "BorrowMoneyNameOut as 出借人," & _
                                  "BorrowMoneyNum as 借入金额," & _
                                  "BorrowMoneyBZ as 备注 from BorrowMoney where BorrowMoneyDateIn between " & _
                                  "'" & Me.DTPicker3.Value & "'" & " and " & "'" & Me.DTPicker4.Value & "'"
    Me.Adodc_Tong1.Refresh
    '统计金额
    sum = 0
    rsdb.Open "select * from BorrowMoney where BorrowMoneyDateIn between " & "'" & Me.DTPicker3.Value & _
    "'" & " and " & "'" & Me.DTPicker4.Value & "'", RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        For I = 1 To rsdb.RecordCount
            sum = sum + Val(rsdb.Fields("BorrowMoneyNum").Value)
            If Not rsdb.EOF Then rsdb.MoveNext
        Next I
    End If
    rsdb.Close
    '显示金额
    Me.Text6.Text = str(sum)
    MsgBox "统计结果"
ElseIf Me.Check5.Value = 1 Then     '按借入人统计
    If Me.Combo2.Text = "" Then
        MsgBox "不能空白"
        Exit Sub
    End If
    '查找对借入人姓名对应的编号
    rsdb.Open "select * from Family where FamilyName=" & "'" & Me.Combo2.Text & "'", RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        Num = Val(rsdb.Fields("FamilyID").Value)
    End If
    rsdb.Close
    '查询相关记录并显示
    Me.Adodc_Tong1.RecordSource = "select BorrowMoneyID as 编号," & _
                                  "BorrowMoneyDateIn as 借入日期," & _
                                  "BorrowMoneyDateOut as 预还日期," & _
                                  "BorrowMoneyNameIn as 借入人," & _
                                  "BorrowMoneyNameOut as 出借人," & _
                                  "BorrowMoneyNum as 借入金额," & _
                                  "BorrowMoneyBZ as 备注 from BorrowMoney where BorrowMoneyNameIn=" & Num
    Me.Adodc_Tong1.Refresh
    '统计金额
    sum = 0
    rsdb.Open "select * from BorrowMoney where BorrowMoneyNameIn=" & Num, RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        For I = 1 To rsdb.RecordCount
            sum = sum + Val(rsdb.Fields("BorrowMoneyNum").Value)
            If Not rsdb.EOF Then rsdb.MoveNext
        Next I
    End If
    rsdb.Close
    '显示金额
    Me.Text6.Text = str(sum)
    MsgBox "统计结果"
ElseIf Me.Check6.Value = 1 Then     '按借入金额统计
    If Me.Text4.Text = "" Or Me.Text5.Text = "" Then
        MsgBox "不能空白"
        Exit Sub
    End If
    
    If Not IsNumeric(Me.Text4.Text) Then
        MsgBox "必须是数字"
        Exit Sub
    End If
    
    If Not IsNumeric(Me.Text5.Text) Then
        MsgBox "必须是数字"
        Exit Sub
    End If
    '查询相关记录并显示
    Me.Adodc_Tong1.RecordSource = "select BorrowMoneyID as 编号," & _
                                  "BorrowMoneyDateIn as 借入日期," & _
                                  "BorrowMoneyDateOut as 预还日期," & _
                                  "BorrowMoneyNameIn as 借入人," & _
                                  "BorrowMoneyNameOut as 出借人," & _
                                  "BorrowMoneyNum as 借入金额," & _
                                  "BorrowMoneyBZ as 备注 from BorrowMoney where BorrowMoneyNum between " & Val(Me.Text4.Text) & " and " & Val(Me.Text5.Text)
    Me.Adodc_Tong1.Refresh
    '统计金额
    sum = 0
    rsdb.Open "select * from BorrowMoney where BorrowMoneyNum between " & Val(Me.Text4.Text) & " and " & Val(Me.Text5.Text), RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.EOF Then rsdb.MoveFirst
        For I = 1 To rsdb.RecordCount
            sum = sum + Val(rsdb.Fields("BorrowMoneyNum").Value)
            If Not rsdb.EOF Then rsdb.MoveNext
        Next I
    End If
    rsdb.Close
    '显示金额
    Me.Text6.Text = str(sum)
    MsgBox "统计结果"
End If
End Sub

Private Sub Form_Load()    '窗体装载
Dim rsdb As New adodb.Recordset
'填充借出人与借入人框
rsdb.Open "select * from Family", RemoteCnn, adOpenStatic, adLockReadOnly, -1
    If rsdb.RecordCount > 0 Then
        If Not rsdb.BOF Then rsdb.MoveFirst
        For I = 1 To rsdb.RecordCount
            Me.Combo1.AddItem rsdb.Fields("FamilyName").Value
            Me.Combo2.AddItem rsdb.Fields("FamilyName").Value
            If Not rsdb.EOF Then rsdb.MoveNext
        Next I
    End If
rsdb.Close
'打开借出款表并填充表格
Me.Adodc_Tong.ConnectionString = "Provider=MSDASQL.1;Persist Security " & _
"Info=False;User ID=" & UID & ";pwd=" & PWD & ";Data Source=" & MyDSN
Adodc_Tong.RecordSource = "select LendMoneyID as 编号," & _
                        "LendMoneyDateOut as 借出日期," & _
                        "LendMoneyDateIn as 预还日期," & _
                        "LendMoneyNameOut as 出借人," & _
                        "LendMoneyNameIn as 借贷人," & _
                        "LendMoneyNum as 借出金额," & _
                        "LendMoneyBZ as 备注 from LendMoney "
Adodc_Tong.Refresh
'打开借入款表并填充表格
Me.Adodc_Tong1.ConnectionString = "Provider=MSDASQL.1;Persist Security " & _
"Info=False;User ID=" & UID & ";pwd=" & PWD & ";Data Source=" & MyDSN
Adodc_Tong1.RecordSource = "select BorrowMoneyID as 编号," & _
                          "BorrowMoneyDateIn as 借入日期," & _
                          "BorrowMoneyDateOut as 预还日期," & _
                          "BorrowMoneyNameIn as 借入人," & _
                          "BorrowMoneyNameOut as 出借人," & _
                          "BorrowMoneyNum as 借入金额," & _
                          "BorrowMoneyBZ as 备注 from BorrowMoney "
Adodc_Tong1.Refresh
End Sub